Skip to content

Device

Description

Identity, network segmentation, and sysinfo for devices on which uniform_resource are found

Table Definition
CREATE TABLE "device" (
"device_id" VARCHAR PRIMARY KEY NOT NULL,
"name" TEXT NOT NULL,
"state" TEXT CHECK(json_valid(state)) NOT NULL,
"boundary" TEXT NOT NULL,
"segmentation" TEXT CHECK(json_valid(segmentation) OR segmentation IS NULL),
"state_sysinfo" TEXT CHECK(json_valid(state_sysinfo) OR state_sysinfo IS NULL),
"elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL),
"created_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
"created_by" TEXT DEFAULT 'UNKNOWN',
"updated_at" TIMESTAMPTZ,
"updated_by" TEXT,
"deleted_at" TIMESTAMPTZ,
"deleted_by" TEXT,
"activity_log" TEXT,
UNIQUE("name", "state", "boundary")
)

Columns

NameTypeDefaultNullableChildrenComment
device_idVARCHARfalsedevice_party_relationship behavior ur_ingest_session uniform_resource orchestration_session{“isSqlDomainZodDescrMeta”:true,“isVarChar”:true}
nameTEXTfalseunique device identifier (defaults to hostname)
stateTEXTfalseshould be “SINGLETON” if only one state is allowed, or other tags if multiple states are allowed
boundaryTEXTfalsecan be IP address, VLAN, or any other device name differentiator
segmentationTEXTtruezero trust or other network segmentation
state_sysinfoTEXTtrueany sysinfo or other state data that is specific to this device (mutable)
elaborationTEXTtrueany elaboration needed for the device (mutable)
created_atTIMESTAMPTZCURRENT_TIMESTAMPtrue
created_byTEXT’UNKNOWN’true
updated_atTIMESTAMPTZtrue
updated_byTEXTtrue
deleted_atTIMESTAMPTZtrue
deleted_byTEXTtrue
activity_logTEXTtrue{“isSqlDomainZodDescrMeta”:true,“isJsonSqlDomain”:true}

Constraints

NameTypeDefinition
device_idPRIMARY KEYPRIMARY KEY (device_id)
sqlite_autoindex_device_2UNIQUEUNIQUE (name, state, boundary)
sqlite_autoindex_device_1PRIMARY KEYPRIMARY KEY (device_id)
-CHECKCHECK(json_valid(state))
-CHECKCHECK(json_valid(segmentation) OR segmentation IS NULL)
-CHECKCHECK(json_valid(state_sysinfo) OR state_sysinfo IS NULL)
-CHECKCHECK(json_valid(elaboration) OR elaboration IS NULL)

Indexes

NameDefinition
idx_device__name__stateCREATE INDEX “idx_device__name__state” ON “device”(“name”, “state”)
sqlite_autoindex_device_2UNIQUE (name, state, boundary)
sqlite_autoindex_device_1PRIMARY KEY (device_id)

Relations

er