code_notebook_sql_cell_migratable_version (view) Content

code_notebook_sql_cell_migratable_version (view) Schema


01JXADE9V3ARNW8RMY39GP0Y4H	ConstructionSqlNotebook	session_ephemeral_table	CREATE TEMP TABLE IF NOT EXISTS "session_state_ephemeral" ( "key" TEXT PRIMARY KEY NOT NULL, "value" TEXT NOT NULL );	b739acd000cf37091bbb365085506f975345351d	1	2025-09-02 14:39:57
01JXADE9V33XF4J780NF7D3YKG	ConstructionSqlNotebook	surveilr_table_size	CREATE TABLE IF NOT EXISTS surveilr_table_size ( table_name TEXT PRIMARY KEY, table_size_mb REAL ); DELETE FROM surveilr_table_size; INSERT INTO surveilr_table_size (table_name, table_size_mb) SELECT name, ROUND(SUM(pgsize) / (1024.0 * 1024), 2) FROM dbstat GROUP BY name;	0213397f35c9eaf525cce0fa6276a02243f5d877	1	2025-09-02 14:39:57
01JXADE9V2JHW63SWJ05RQJ79B	ConstructionSqlNotebook	v001_once_initialDDL	-- code provenance: `RssdInitSqlNotebook.v001_once_initialDDL` (file:///Users/mac/Downloads/www.surveilr.com/lib/std/lifecycle.sql.ts) CREATE TABLE IF NOT EXISTS "party_type" ( "party_type_id" ULID PRIMARY KEY NOT NULL, "code" TEXT /* UNIQUE COLUMN / NOT NULL, "value" TEXT NOT 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("code") ); CREATE TABLE IF NOT EXISTS "party" ( "party_id" VARCHAR PRIMARY KEY NOT NULL, "party_type_id" ULID NOT NULL, "party_name" TEXT NOT 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, FOREIGN KEY("party_type_id") REFERENCES "party_type"("party_type_id") ); CREATE TABLE IF NOT EXISTS "party_relation_type" ( "party_relation_type_id" ULID PRIMARY KEY NOT NULL, "code" TEXT / UNIQUE COLUMN / NOT NULL, "value" TEXT NOT 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("code") ); CREATE TABLE IF NOT EXISTS "party_relation" ( "party_relation_id" VARCHAR PRIMARY KEY NOT NULL, "party_id" VARCHAR NOT NULL, "related_party_id" VARCHAR NOT NULL, "relation_type_id" ULID NOT 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, FOREIGN KEY("party_id") REFERENCES "party"("party_id"), FOREIGN KEY("related_party_id") REFERENCES "party"("party_id"), FOREIGN KEY("relation_type_id") REFERENCES "party_relation_type"("party_relation_type_id"), UNIQUE("party_id", "related_party_id", "relation_type_id") ); CREATE TABLE IF NOT EXISTS "gender_type" ( "gender_type_id" ULID PRIMARY KEY NOT NULL, "code" TEXT / UNIQUE COLUMN / NOT NULL, "value" TEXT NOT 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("code") ); CREATE TABLE IF NOT EXISTS "sex_type" ( "sex_type_id" ULID PRIMARY KEY NOT NULL, "code" TEXT / UNIQUE COLUMN / NOT NULL, "value" TEXT NOT 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("code") ); CREATE TABLE IF NOT EXISTS "person_type" ( "person_type_id" ULID PRIMARY KEY NOT NULL, "code" TEXT / UNIQUE COLUMN / NOT NULL, "value" TEXT NOT 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("code") ); CREATE TABLE IF NOT EXISTS "person" ( "person_id" ULID PRIMARY KEY NOT NULL, "party_id" VARCHAR NOT NULL, "person_type_id" ULID NOT NULL, "person_first_name" TEXT NOT NULL, "person_middle_name" TEXT, "person_last_name" TEXT NOT NULL, "previous_name" TEXT, "honorific_prefix" TEXT, "honorific_suffix" TEXT, "gender_id" ULID NOT NULL, "sex_id" ULID NOT 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, FOREIGN KEY("party_id") REFERENCES "party"("party_id"), FOREIGN KEY("person_type_id") REFERENCES "person_type"("person_type_id"), FOREIGN KEY("gender_id") REFERENCES "gender_type"("gender_type_id"), FOREIGN KEY("sex_id") REFERENCES "sex_type"("sex_type_id") ); CREATE TABLE IF NOT EXISTS "organization" ( "organization_id" ULID PRIMARY KEY NOT NULL, "party_id" VARCHAR NOT NULL, "name" TEXT NOT NULL, "alias" TEXT, "description" TEXT, "license" TEXT NOT NULL, "federal_tax_id_num" TEXT, "registration_date" DATE NOT 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, FOREIGN KEY("party_id") REFERENCES "party"("party_id") ); CREATE TABLE IF NOT EXISTS "organization_role_type" ( "organization_role_type_id" ULID PRIMARY KEY NOT NULL, "code" TEXT / UNIQUE COLUMN / NOT NULL, "value" TEXT NOT 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("code") ); CREATE TABLE IF NOT EXISTS "organization_role" ( "organization_role_id" VARCHAR PRIMARY KEY NOT NULL, "person_id" VARCHAR NOT NULL, "organization_id" VARCHAR NOT NULL, "organization_role_type_id" ULID NOT 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, FOREIGN KEY("person_id") REFERENCES "party"("party_id"), FOREIGN KEY("organization_id") REFERENCES "party"("party_id"), FOREIGN KEY("organization_role_type_id") REFERENCES "organization_role_type"("organization_role_type_id"), UNIQUE("person_id", "organization_id", "organization_role_type_id") ); CREATE TABLE IF NOT EXISTS "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") ); CREATE TABLE IF NOT EXISTS "device_party_relationship" ( "device_party_relationship_id" VARCHAR PRIMARY KEY NOT NULL, "device_id" VARCHAR NOT NULL, "party_id" VARCHAR NOT 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, FOREIGN KEY("device_id") REFERENCES "device"("device_id"), FOREIGN KEY("party_id") REFERENCES "party"("party_id"), UNIQUE("device_id", "party_id") ); CREATE TABLE IF NOT EXISTS "behavior" ( "behavior_id" VARCHAR PRIMARY KEY NOT NULL, "device_id" VARCHAR NOT NULL, "behavior_name" TEXT NOT NULL, "behavior_conf_json" TEXT CHECK(json_valid(behavior_conf_json)) NOT NULL, "assurance_schema_id" VARCHAR, "governance" TEXT CHECK(json_valid(governance) OR governance 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, FOREIGN KEY("device_id") REFERENCES "device"("device_id"), FOREIGN KEY("assurance_schema_id") REFERENCES "assurance_schema"("assurance_schema_id"), UNIQUE("device_id", "behavior_name") ); CREATE TABLE IF NOT EXISTS "ur_ingest_resource_path_match_rule" ( "ur_ingest_resource_path_match_rule_id" VARCHAR PRIMARY KEY NOT NULL, "namespace" TEXT NOT NULL, "regex" TEXT NOT NULL, "flags" TEXT NOT NULL, "nature" TEXT, "priority" TEXT, "description" TEXT, "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("namespace", "regex") ); CREATE TABLE IF NOT EXISTS "ur_ingest_resource_path_rewrite_rule" ( "ur_ingest_resource_path_rewrite_rule_id" VARCHAR PRIMARY KEY NOT NULL, "namespace" TEXT NOT NULL, "regex" TEXT NOT NULL, "replace" TEXT NOT NULL, "priority" TEXT, "description" TEXT, "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("namespace", "regex", "replace") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session" ( "ur_ingest_session_id" VARCHAR PRIMARY KEY NOT NULL, "device_id" VARCHAR NOT NULL, "behavior_id" VARCHAR, "behavior_json" TEXT CHECK(json_valid(behavior_json) OR behavior_json IS NULL), "ingest_started_at" TIMESTAMPTZ NOT NULL, "ingest_finished_at" TIMESTAMPTZ, "session_agent" TEXT CHECK(json_valid(session_agent)) NOT 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, FOREIGN KEY("device_id") REFERENCES "device"("device_id"), FOREIGN KEY("behavior_id") REFERENCES "behavior"("behavior_id"), UNIQUE("device_id", "created_at") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_fs_path" ( "ur_ingest_session_fs_path_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "root_path" TEXT NOT 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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), UNIQUE("ingest_session_id", "root_path", "created_at") ); CREATE TABLE IF NOT EXISTS "uniform_resource" ( "uniform_resource_id" VARCHAR PRIMARY KEY NOT NULL, "device_id" VARCHAR NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "ingest_fs_path_id" VARCHAR, "ingest_session_imap_acct_folder_message" VARCHAR, "ingest_issue_acct_project_id" VARCHAR, "uri" TEXT NOT NULL, "content_digest" TEXT NOT NULL, "content" BLOB, "nature" TEXT, "size_bytes" INTEGER, "last_modified_at" TIMESTAMPTZ, "content_fm_body_attrs" TEXT CHECK(json_valid(content_fm_body_attrs) OR content_fm_body_attrs IS NULL), "frontmatter" TEXT CHECK(json_valid(frontmatter) OR frontmatter 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, FOREIGN KEY("device_id") REFERENCES "device"("device_id"), FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("ingest_fs_path_id") REFERENCES "ur_ingest_session_fs_path"("ur_ingest_session_fs_path_id"), FOREIGN KEY("ingest_session_imap_acct_folder_message") REFERENCES "ur_ingest_session_imap_acct_folder_message"("ur_ingest_session_imap_acct_folder_message_id"), FOREIGN KEY("ingest_issue_acct_project_id") REFERENCES "ur_ingest_session_plm_acct_project"("ur_ingest_session_plm_acct_project_id"), UNIQUE("device_id", "content_digest", "uri", "size_bytes") ); CREATE TABLE IF NOT EXISTS "uniform_resource_transform" ( "uniform_resource_transform_id" VARCHAR PRIMARY KEY NOT NULL, "uniform_resource_id" VARCHAR NOT NULL, "uri" TEXT NOT NULL, "content_digest" TEXT NOT NULL, "content" BLOB, "nature" TEXT, "size_bytes" INTEGER, "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, FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id"), UNIQUE("uniform_resource_id", "content_digest", "nature", "size_bytes") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_fs_path_entry" ( "ur_ingest_session_fs_path_entry_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "ingest_fs_path_id" VARCHAR NOT NULL, "uniform_resource_id" VARCHAR, "file_path_abs" TEXT NOT NULL, "file_path_rel_parent" TEXT NOT NULL, "file_path_rel" TEXT NOT NULL, "file_basename" TEXT NOT NULL, "file_extn" TEXT, "captured_executable" TEXT CHECK(json_valid(captured_executable) OR captured_executable IS NULL), "ur_status" TEXT, "ur_diagnostics" TEXT CHECK(json_valid(ur_diagnostics) OR ur_diagnostics IS NULL), "ur_transformations" TEXT CHECK(json_valid(ur_transformations) OR ur_transformations 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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("ingest_fs_path_id") REFERENCES "ur_ingest_session_fs_path"("ur_ingest_session_fs_path_id"), FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_task" ( "ur_ingest_session_task_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "uniform_resource_id" VARCHAR, "captured_executable" TEXT CHECK(json_valid(captured_executable)) NOT NULL, "ur_status" TEXT, "ur_diagnostics" TEXT CHECK(json_valid(ur_diagnostics) OR ur_diagnostics IS NULL), "ur_transformations" TEXT CHECK(json_valid(ur_transformations) OR ur_transformations 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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_imap_account" ( "ur_ingest_session_imap_account_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "email" TEXT, "password" TEXT, "host" TEXT, "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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), UNIQUE("ingest_session_id", "email") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_imap_acct_folder" ( "ur_ingest_session_imap_acct_folder_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "ingest_account_id" VARCHAR NOT NULL, "folder_name" TEXT NOT 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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("ingest_account_id") REFERENCES "ur_ingest_session_imap_account"("ur_ingest_session_imap_account_id"), UNIQUE("ingest_account_id", "folder_name") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_imap_acct_folder_message" ( "ur_ingest_session_imap_acct_folder_message_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "ingest_imap_acct_folder_id" VARCHAR NOT NULL, "message" TEXT NOT NULL, "message_id" TEXT NOT NULL, "subject" TEXT NOT NULL, "from" TEXT NOT NULL, "cc" TEXT CHECK(json_valid(cc)) NOT NULL, "bcc" TEXT CHECK(json_valid(bcc)) NOT NULL, "status" TEXT[] NOT NULL, "date" DATE, "email_references" TEXT CHECK(json_valid(email_references)) NOT 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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("ingest_imap_acct_folder_id") REFERENCES "ur_ingest_session_imap_acct_folder"("ur_ingest_session_imap_acct_folder_id"), UNIQUE("message", "message_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_account" ( "ur_ingest_session_plm_account_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "provider" TEXT NOT NULL, "org_name" TEXT NOT 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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), UNIQUE("provider", "org_name") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_acct_project" ( "ur_ingest_session_plm_acct_project_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "ingest_account_id" VARCHAR NOT NULL, "parent_project_id" TEXT, "name" TEXT NOT NULL, "description" TEXT, "id" TEXT, "key" TEXT, "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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("ingest_account_id") REFERENCES "ur_ingest_session_plm_account"("ur_ingest_session_plm_account_id"), UNIQUE("name", "description") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_acct_project_issue" ( "ur_ingest_session_plm_acct_project_issue_id" VARCHAR PRIMARY KEY NOT NULL, "ingest_session_id" VARCHAR NOT NULL, "ur_ingest_session_plm_acct_project_id" VARCHAR NOT NULL, "uniform_resource_id" VARCHAR, "issue_id" TEXT NOT NULL, "issue_number" INTEGER, "parent_issue_id" TEXT, "title" TEXT NOT NULL, "body" TEXT, "body_text" TEXT, "body_html" TEXT, "state" TEXT NOT NULL, "assigned_to" TEXT, "user" VARCHAR NOT NULL, "url" TEXT NOT NULL, "closed_at" TEXT, "issue_type_id" VARCHAR, "time_estimate" INTEGER, "aggregate_time_estimate" INTEGER, "time_original_estimate" INTEGER, "time_spent" INTEGER, "aggregate_time_spent" INTEGER, "aggregate_time_original_estimate" INTEGER, "workratio" INTEGER, "current_progress" INTEGER, "total_progress" INTEGER, "resolution_name" TEXT, "resolution_date" TEXT, "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, FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), FOREIGN KEY("ur_ingest_session_plm_acct_project_id") REFERENCES "ur_ingest_session_plm_acct_project"("ur_ingest_session_plm_acct_project_id"), FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id"), FOREIGN KEY("user") REFERENCES "ur_ingest_session_plm_user"("ur_ingest_session_plm_user_id"), FOREIGN KEY("issue_type_id") REFERENCES "ur_ingest_session_plm_issue_type"("ur_ingest_session_plm_issue_type_id"), UNIQUE("title", "issue_id", "state", "assigned_to") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_acct_label" ( "ur_ingest_session_plm_acct_label_id" VARCHAR PRIMARY KEY NOT NULL, "ur_ingest_session_plm_acct_project_id" VARCHAR NOT NULL, "ur_ingest_session_plm_acct_project_issue_id" VARCHAR NOT NULL, "label" TEXT NOT 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, FOREIGN KEY("ur_ingest_session_plm_acct_project_id") REFERENCES "ur_ingest_session_plm_acct_project"("ur_ingest_session_plm_acct_project_id"), FOREIGN KEY("ur_ingest_session_plm_acct_project_issue_id") REFERENCES "ur_ingest_session_plm_acct_project_issue"("ur_ingest_session_plm_acct_project_issue_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_milestone" ( "ur_ingest_session_plm_milestone_id" VARCHAR PRIMARY KEY NOT NULL, "ur_ingest_session_plm_acct_project_id" VARCHAR NOT NULL, "title" TEXT NOT NULL, "milestone_id" TEXT NOT NULL, "url" TEXT NOT NULL, "html_url" TEXT NOT NULL, "open_issues" INTEGER, "closed_issues" INTEGER, "due_on" TIMESTAMPTZ, "closed_at" TIMESTAMPTZ, "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, FOREIGN KEY("ur_ingest_session_plm_acct_project_id") REFERENCES "ur_ingest_session_plm_acct_project"("ur_ingest_session_plm_acct_project_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_acct_relationship" ( "ur_ingest_session_plm_acct_relationship_id" VARCHAR PRIMARY KEY NOT NULL, "ur_ingest_session_plm_acct_project_id_prime" VARCHAR NOT NULL, "ur_ingest_session_plm_acct_project_id_related" TEXT NOT NULL, "ur_ingest_session_plm_acct_project_issue_id_prime" VARCHAR NOT NULL, "ur_ingest_session_plm_acct_project_issue_id_related" TEXT NOT NULL, "relationship" TEXT, "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, FOREIGN KEY("ur_ingest_session_plm_acct_project_id_prime") REFERENCES "ur_ingest_session_plm_acct_project"("ur_ingest_session_plm_acct_project_id"), FOREIGN KEY("ur_ingest_session_plm_acct_project_issue_id_prime") REFERENCES "ur_ingest_session_plm_acct_project_issue"("ur_ingest_session_plm_acct_project_issue_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_user" ( "ur_ingest_session_plm_user_id" VARCHAR PRIMARY KEY NOT NULL, "user_id" TEXT NOT NULL, "login" TEXT NOT NULL, "email" TEXT, "name" TEXT, "url" TEXT NOT 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("user_id", "login") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_comment" ( "ur_ingest_session_plm_comment_id" VARCHAR PRIMARY KEY NOT NULL, "ur_ingest_session_plm_acct_project_issue_id" VARCHAR NOT NULL, "comment_id" TEXT NOT NULL, "node_id" TEXT NOT NULL, "url" TEXT NOT NULL, "body" TEXT, "body_text" TEXT, "body_html" TEXT, "user" VARCHAR NOT 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, FOREIGN KEY("ur_ingest_session_plm_acct_project_issue_id") REFERENCES "ur_ingest_session_plm_acct_project_issue"("ur_ingest_session_plm_acct_project_issue_id"), FOREIGN KEY("user") REFERENCES "ur_ingest_session_plm_user"("ur_ingest_session_plm_user_id"), UNIQUE("comment_id", "url", "body") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_reaction" ( "ur_ingest_session_plm_reaction_id" VARCHAR PRIMARY KEY NOT NULL, "reaction_id" TEXT NOT NULL, "reaction_type" TEXT NOT 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("reaction_type") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_issue_reaction" ( "ur_ingest_session_plm_issue_reaction_id" VARCHAR PRIMARY KEY NOT NULL, "ur_ingest_plm_reaction_id" VARCHAR NOT NULL, "ur_ingest_plm_issue_id" VARCHAR NOT NULL, "count" INTEGER NOT NULL DEFAULT 1, "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, FOREIGN KEY("ur_ingest_plm_reaction_id") REFERENCES "ur_ingest_session_plm_reaction"("ur_ingest_session_plm_reaction_id"), FOREIGN KEY("ur_ingest_plm_issue_id") REFERENCES "ur_ingest_session_plm_acct_project_issue"("ur_ingest_session_plm_acct_project_issue_id"), UNIQUE("ur_ingest_plm_issue_id", "ur_ingest_plm_reaction_id") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_plm_issue_type" ( "ur_ingest_session_plm_issue_type_id" VARCHAR PRIMARY KEY NOT NULL, "avatar_id" TEXT, "description" TEXT NOT NULL, "icon_url" TEXT NOT NULL, "id" TEXT NOT NULL, "name" TEXT NOT NULL, "subtask" BOOLEAN NOT NULL, "url" TEXT NOT 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("id", "name") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_attachment" ( "ur_ingest_session_attachment_id" VARCHAR PRIMARY KEY NOT NULL, "uniform_resource_id" VARCHAR, "name" TEXT, "uri" TEXT NOT NULL, "content" BLOB, "nature" TEXT, "size" INTEGER, "checksum" TEXT, "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, FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id"), UNIQUE("uniform_resource_id", "checksum", "nature", "size") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_udi_pgp_sql" ( "ur_ingest_session_udi_pgp_sql_id" VARCHAR PRIMARY KEY NOT NULL, "sql" TEXT NOT NULL, "nature" TEXT NOT NULL, "content" BLOB, "behaviour" TEXT CHECK(json_valid(behaviour) OR behaviour IS NULL), "query_error" TEXT, "uniform_resource_id" VARCHAR, "ingest_session_id" VARCHAR, "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, FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id"), FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"), UNIQUE("sql", "ingest_session_id") ); CREATE TABLE IF NOT EXISTS "orchestration_nature" ( "orchestration_nature_id" TEXT PRIMARY KEY NOT NULL, "nature" TEXT NOT 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("orchestration_nature_id", "nature") ); CREATE TABLE IF NOT EXISTS "orchestration_session" ( "orchestration_session_id" VARCHAR PRIMARY KEY NOT NULL, "device_id" VARCHAR NOT NULL, "orchestration_nature_id" TEXT NOT NULL, "version" TEXT NOT NULL, "orch_started_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, "orch_finished_at" TIMESTAMPTZ, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), "args_json" TEXT CHECK(json_valid(args_json) OR args_json IS NULL), "diagnostics_json" TEXT CHECK(json_valid(diagnostics_json) OR diagnostics_json IS NULL), "diagnostics_md" TEXT, FOREIGN KEY("device_id") REFERENCES "device"("device_id"), FOREIGN KEY("orchestration_nature_id") REFERENCES "orchestration_nature"("orchestration_nature_id") ); CREATE TABLE IF NOT EXISTS "orchestration_session_entry" ( "orchestration_session_entry_id" VARCHAR PRIMARY KEY NOT NULL, "session_id" VARCHAR NOT NULL, "ingest_src" TEXT NOT NULL, "ingest_table_name" TEXT, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("session_id") REFERENCES "orchestration_session"("orchestration_session_id") ); CREATE TABLE IF NOT EXISTS "orchestration_session_state" ( "orchestration_session_state_id" VARCHAR PRIMARY KEY NOT NULL, "session_id" VARCHAR NOT NULL, "session_entry_id" VARCHAR, "from_state" TEXT NOT NULL, "to_state" TEXT NOT NULL, "transition_result" TEXT CHECK(json_valid(transition_result) OR transition_result IS NULL), "transition_reason" TEXT, "transitioned_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("session_id") REFERENCES "orchestration_session"("orchestration_session_id"), FOREIGN KEY("session_entry_id") REFERENCES "orchestration_session_entry"("orchestration_session_entry_id"), UNIQUE("orchestration_session_state_id", "from_state", "to_state") ); CREATE TABLE IF NOT EXISTS "orchestration_session_exec" ( "orchestration_session_exec_id" VARCHAR PRIMARY KEY NOT NULL, "exec_nature" TEXT NOT NULL, "session_id" VARCHAR NOT NULL, "session_entry_id" VARCHAR, "parent_exec_id" VARCHAR, "namespace" TEXT, "exec_identity" TEXT, "exec_code" TEXT NOT NULL, "exec_status" INTEGER NOT NULL, "input_text" TEXT, "exec_error_text" TEXT, "output_text" TEXT, "output_nature" TEXT CHECK(json_valid(output_nature) OR output_nature IS NULL), "narrative_md" TEXT, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("session_id") REFERENCES "orchestration_session"("orchestration_session_id"), FOREIGN KEY("session_entry_id") REFERENCES "orchestration_session_entry"("orchestration_session_entry_id"), FOREIGN KEY("parent_exec_id") REFERENCES "orchestration_session_exec"("orchestration_session_exec_id") ); CREATE TABLE IF NOT EXISTS "orchestration_session_issue" ( "orchestration_session_issue_id" UUID PRIMARY KEY NOT NULL, "session_id" VARCHAR NOT NULL, "session_entry_id" VARCHAR, "issue_type" TEXT NOT NULL, "issue_message" TEXT NOT NULL, "issue_row" INTEGER, "issue_column" TEXT, "invalid_value" TEXT, "remediation" TEXT, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("session_id") REFERENCES "orchestration_session"("orchestration_session_id"), FOREIGN KEY("session_entry_id") REFERENCES "orchestration_session_entry"("orchestration_session_entry_id") ); CREATE TABLE IF NOT EXISTS "orchestration_session_issue_relation" ( "orchestration_session_issue_relation_id" UUID PRIMARY KEY NOT NULL, "issue_id_prime" UUID NOT NULL, "issue_id_rel" TEXT NOT NULL, "relationship_nature" TEXT NOT NULL, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("issue_id_prime") REFERENCES "orchestration_session_issue"("orchestration_session_issue_id") ); CREATE TABLE IF NOT EXISTS "orchestration_session_log" ( "orchestration_session_log_id" UUID PRIMARY KEY NOT NULL, "category" TEXT, "parent_exec_id" UUID, "content" TEXT NOT NULL, "sibling_order" INTEGER, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("parent_exec_id") REFERENCES "orchestration_session_log"("orchestration_session_log_id") ); CREATE TABLE IF NOT EXISTS "uniform_resource_graph" ( "name" VARCHAR PRIMARY KEY NOT NULL, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL) ); CREATE TABLE IF NOT EXISTS "uniform_resource_edge" ( "graph_name" VARCHAR NOT NULL, "nature" TEXT NOT NULL, "node_id" TEXT NOT NULL, "uniform_resource_id" VARCHAR NOT NULL, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), FOREIGN KEY("graph_name") REFERENCES "uniform_resource_graph"("name"), FOREIGN KEY("uniform_resource_id") REFERENCES "uniform_resource"("uniform_resource_id"), UNIQUE("graph_name", "nature", "node_id", "uniform_resource_id") ); CREATE TABLE IF NOT EXISTS "surveilr_osquery_ms_node" ( "surveilr_osquery_ms_node_id" VARCHAR PRIMARY KEY NOT NULL, "node_key" TEXT NOT NULL, "host_identifier" TEXT NOT NULL, "tls_cert_subject" TEXT, "os_version" TEXT NOT NULL, "platform" TEXT NOT NULL, "last_seen" TIMESTAMP NOT NULL, "status" TEXT NOT NULL DEFAULT 'active', "osquery_version" TEXT, "osquery_build_platform" TEXT NOT NULL, "device_id" VARCHAR NOT NULL, "behavior_id" VARCHAR, "accelerate" INTEGER NOT NULL DEFAULT 60, "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, FOREIGN KEY("device_id") REFERENCES "device"("device_id"), FOREIGN KEY("behavior_id") REFERENCES "behavior"("behavior_id"), UNIQUE("host_identifier", "os_version"), UNIQUE("node_key") ); CREATE TABLE IF NOT EXISTS "ur_ingest_session_osquery_ms_log" ( "ur_ingest_session_osquery_ms_log_id" VARCHAR PRIMARY KEY NOT NULL, "node_key" TEXT NOT NULL, "log_type" TEXT NOT NULL, "log_data" TEXT CHECK(json_valid(log_data)) NOT NULL, "applied_jq_filters" TEXT CHECK(json_valid(applied_jq_filters) OR applied_jq_filters 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, FOREIGN KEY("node_key") REFERENCES "surveilr_osquery_ms_node"("node_key"), UNIQUE("node_key", "log_type", "log_data") ); CREATE TABLE IF NOT EXISTS "osquery_policy" ( "osquery_policy_id" VARCHAR PRIMARY KEY NOT NULL, "policy_group" TEXT, "policy_name" TEXT NOT NULL, "osquery_code" TEXT NOT NULL, "policy_description" TEXT NOT NULL, "policy_pass_label" TEXT NOT NULL DEFAULT 'Pass', "policy_fail_label" TEXT NOT NULL DEFAULT 'Fail', "policy_pass_remarks" TEXT, "policy_fail_remarks" TEXT, "osquery_platforms" TEXT, "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("policy_name", "osquery_code") ); CREATE TABLE IF NOT EXISTS "surveilr_table_size" ( "table_name" VARCHAR PRIMARY KEY NOT NULL, "table_size_mb" INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS "surveilr_osquery_ms_distributed_query" ( "query_id" VARCHAR PRIMARY KEY NOT NULL, "node_key" TEXT NOT NULL, "query_name" TEXT NOT NULL, "query_sql" TEXT NOT NULL, "discovery_query" TEXT, "status" TEXT NOT NULL, "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL), "interval" INTEGER NOT 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, FOREIGN KEY("node_key") REFERENCES "surveilr_osquery_ms_node"("node_key") ); CREATE TABLE IF NOT EXISTS "surveilr_osquery_ms_distributed_result" ( "surveilr_osquery_ms_distributed_result_id" VARCHAR PRIMARY KEY NOT NULL, "query_id" VARCHAR NOT NULL, "node_key" TEXT NOT NULL, "results" TEXT CHECK(json_valid(results)) NOT NULL, "status_code" INTEGER NOT 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, FOREIGN KEY("query_id") REFERENCES "surveilr_osquery_ms_distributed_query"("query_id"), FOREIGN KEY("node_key") REFERENCES "surveilr_osquery_ms_node"("node_key") ); CREATE TABLE IF NOT EXISTS "surveilr_osquery_ms_carve" ( "surveilr_osquery_ms_carve_id" VARCHAR PRIMARY KEY NOT NULL, "node_key" TEXT NOT NULL, "session_id" TEXT / UNIQUE COLUMN / NOT NULL, "carve_guid" TEXT / UNIQUE COLUMN */ NOT NULL, "carve_size" INTEGER NOT NULL, "block_count" INTEGER NOT NULL, "block_size" INTEGER NOT NULL, "received_blocks" INTEGER NOT NULL DEFAULT 0, "carve_path" TEXT, "status" TEXT NOT NULL, "start_time" TIMESTAMPTZ NOT NULL, "completion_time" TIMESTAMPTZ, "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, FOREIGN KEY("node_key") REFERENCES "surveilr_osquery_ms_node"("node_key"), UNIQUE("session_id"), UNIQUE("carve_guid") ); CREATE TABLE IF NOT EXISTS "surveilr_osquery_ms_carved_extracted_file" ( "surveilr_osquery_ms_carved_extracted_file_id" VARCHAR PRIMARY KEY NOT NULL, "carve_guid" TEXT NOT NULL, "path" TEXT NOT NULL, "size_bytes" INTEGER NOT NULL, "content_digest" TEXT NOT NULL, "nature" TEXT, "extracted_at" TIMESTAMPTZ NOT 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, FOREIGN KEY("carve_guid") REFERENCES "surveilr_osquery_ms_carve"("carve_guid") ); CREATE INDEX IF NOT EXISTS "idx_party__party_type_id__party_name" ON "party"("party_type_id", "party_name"); CREATE INDEX IF NOT EXISTS "idx_party_relation__party_id__related_party_id__relation_type_id" ON "party_relation"("party_id", "related_party_id", "relation_type_id"); CREATE INDEX IF NOT EXISTS "idx_organization_role__person_id__organization_id__organization_role_type_id" ON "organization_role"("person_id", "organization_id", "organization_role_type_id"); CREATE INDEX IF NOT EXISTS "idx_device__name__state" ON "device"("name", "state"); CREATE INDEX IF NOT EXISTS "idx_device_party_relationship__device_id__party_id" ON "device_party_relationship"("device_id", "party_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_fs_path__ingest_session_id__root_path" ON "ur_ingest_session_fs_path"("ingest_session_id", "root_path"); CREATE INDEX IF NOT EXISTS "idx_uniform_resource__device_id__uri" ON "uniform_resource"("device_id", "uri"); CREATE INDEX IF NOT EXISTS "idx_uniform_resource_transform__uniform_resource_id__content_digest" ON "uniform_resource_transform"("uniform_resource_id", "content_digest"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_fs_path_entry__ingest_session_id__file_path_abs" ON "ur_ingest_session_fs_path_entry"("ingest_session_id", "file_path_abs"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_task__ingest_session_id" ON "ur_ingest_session_task"("ingest_session_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_imap_acct_folder__ingest_session_id__folder_name" ON "ur_ingest_session_imap_acct_folder"("ingest_session_id", "folder_name"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_imap_acct_folder_message__ingest_session_id" ON "ur_ingest_session_imap_acct_folder_message"("ingest_session_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_imap_account__ingest_session_id__email" ON "ur_ingest_session_imap_account"("ingest_session_id", "email"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_account__provider__org_name" ON "ur_ingest_session_plm_account"("provider", "org_name"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_acct_project__name__description" ON "ur_ingest_session_plm_acct_project"("name", "description"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_acct_project_issue__title__issue_id__state__assigned_to" ON "ur_ingest_session_plm_acct_project_issue"("title", "issue_id", "state", "assigned_to"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_acct_label__ur_ingest_session_plm_acct_project_issue_id" ON "ur_ingest_session_plm_acct_label"("ur_ingest_session_plm_acct_project_issue_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_milestone__ur_ingest_session_plm_acct_project_id" ON "ur_ingest_session_plm_milestone"("ur_ingest_session_plm_acct_project_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_acct_relationship__ur_ingest_session_plm_acct_project_id_prime" ON "ur_ingest_session_plm_acct_relationship"("ur_ingest_session_plm_acct_project_id_prime"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_user__user_id__login" ON "ur_ingest_session_plm_user"("user_id", "login"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_comment__ur_ingest_session_plm_acct_project_issue_id" ON "ur_ingest_session_plm_comment"("ur_ingest_session_plm_acct_project_issue_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_reaction__ur_ingest_session_plm_reaction_id" ON "ur_ingest_session_plm_reaction"("ur_ingest_session_plm_reaction_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_issue_reaction__ur_ingest_session_plm_issue_reaction_id" ON "ur_ingest_session_plm_issue_reaction"("ur_ingest_session_plm_issue_reaction_id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_plm_issue_type__id" ON "ur_ingest_session_plm_issue_type"("id"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_attachment__uniform_resource_id__content" ON "ur_ingest_session_attachment"("uniform_resource_id", "content"); CREATE INDEX IF NOT EXISTS "idx_ur_ingest_session_udi_pgp_sql__ingest_session_id" ON "ur_ingest_session_udi_pgp_sql"("ingest_session_id"); CREATE INDEX IF NOT EXISTS "idx_orchestration_nature__orchestration_nature_id__nature" ON "orchestration_nature"("orchestration_nature_id", "nature"); CREATE INDEX IF NOT EXISTS "idx_uniform_resource_edge__uniform_resource_id" ON "uniform_resource_edge"("uniform_resource_id"); CREATE INDEX IF NOT EXISTS "idx_surveilr_osquery_ms_node__node_key" ON "surveilr_osquery_ms_node"("node_key"); CREATE INDEX IF NOT EXISTS "idx_surveilr_osquery_ms_carved_extracted_file__path__carve_guid" ON "surveilr_osquery_ms_carved_extracted_file"("path", "carve_guid");	fdfa66dd79f056d692eb72a2c1569e5806066d9b	0	2025-09-02 14:39:57
01JXADE9V3AYS65V03EPJWFEYY	ConstructionSqlNotebook	v001_seedDML	INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('ignore .git and node_modules paths', 'default', '/(\.git\|node_modules)/', 'IGNORE_RESOURCE', NULL, NULL, 'Ignore any entry with `/.git/` or `/node_modules/` in the path.', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('typical ingestion extensions', 'default', '\.(?P<nature>md\|mdx\|html\|json\|jsonc\|puml\|txt\|toml\|yml\|xml\|tap\|csv\|tsv\|ssv\|psv\|tm7)$', 'CONTENT_ACQUIRABLE', '?P<nature>', NULL, 'Ingest the content for text and structured data extensions (md, mdx, html, json, jsonc, puml, txt, toml, yml, xml, tap, csv, tsv, ssv, psv, tm7). Assume the nature is the same as the extension.', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-[NATURE] style capturable executable', 'default', 'surveilr\[(?P<nature>[^\]])\]', 'CAPTURABLE_EXECUTABLE', '?P<nature>', NULL, 'Any entry with `surveilr-[XYZ]` in the path will be treated as a capturable executable extracting `XYZ` as the nature', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-SQL capturable executable', 'default', 'surveilr-SQL', 'CAPTURABLE_EXECUTABLE \| CAPTURABLE_SQL', NULL, NULL, 'Any entry with surveilr-SQL in the path will be treated as a capturable SQL executable and allow execution of the SQL', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('pdf-docx-transform-metadata', 'default', '\.(?P<nature>pdf\|docx)$', 'CONTENT_ACQUIRABLE \| CONTENT_ACQUIRABLE_TRANSFORM_MARKITDOWN \| CONTENT_ACQUIRABLE_METADATA', '?P<nature>', '1', 'PDF and DOCX documents with full transformation and metadata extraction', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('images-metadata', 'default', '\.(?P<nature>jpg\|jpeg\|png\|gif\|bmp\|tiff\|svg\|webp)$', 'CONTENT_ACQUIRABLE \| CONTENT_ACQUIRABLE_METADATA', '?P<nature>', '2', 'Images with metadata extraction', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('media-metadata', 'default', '\.(?P<nature>mp4\|mp3)$', 'CONTENT_ACQUIRABLE \| CONTENT_ACQUIRABLE_METADATA', '?P<nature>', '3', 'Media files with metadata extraction', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('jsonl-content-replace', 'default', '\.(?P<nature>jsonl)$', 'CONTENT_ACQUIRABLE \| CONTENT_REPLACE_JSON_LINES', '?P<nature>', '4', 'JSONL files for line-by-line JSON ingestion', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_rewrite_rule" ("ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('.plantuml -> .puml', 'default', '(\.plantuml)$', '.puml', NULL, 'Treat .plantuml as .puml files', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "ur_ingest_resource_path_rewrite_rule" ("ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('.text -> .txt', 'default', '(\.text)$', '.txt', NULL, 'Treat .text as .txt files', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "ur_ingest_resource_path_rewrite_rule" ("ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('.yaml -> .yml', 'default', '(\.yaml)$', '.yml', NULL, 'Treat .yaml as .yml files', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "party_type" ("party_type_id", "code", "value", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), 'ORGANIZATION', 'Organization', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "party_type" ("party_type_id", "code", "value", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), 'PERSON', 'Person', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "orchestration_nature" ("orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-transform-csv', 'Transform CSV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "orchestration_nature" ("orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-transform-xml', 'Transform XML', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "orchestration_nature" ("orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-transform-html', 'Transform HTML', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('filesystem', '{}') ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('imap', '{}') ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('plm', '{}') ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('osquery-ms', '{}') ON CONFLICT DO NOTHING; DROP VIEW IF EXISTS "plm_graph"; CREATE VIEW IF NOT EXISTS "plm_graph" AS SELECT ure.graph_name, ure.nature, ur.uniform_resource_id, ur.uri, ur_ingest_plm.ur_ingest_session_plm_acct_project_issue_id, ur_ingest_plm.issue_id, ur_ingest_plm.ur_ingest_session_plm_acct_project_id as project_id, ur_ingest_plm.title, ur_ingest_plm.body FROM uniform_resource_edge ure JOIN uniform_resource ur ON ure.uniform_resource_id = ur.uniform_resource_id JOIN ur_ingest_session_plm_acct_project_issue ur_ingest_plm ON ure.node_id = ur_ingest_plm.ur_ingest_session_plm_acct_project_issue_id WHERE ure.graph_name = 'plm'; ; DROP VIEW IF EXISTS "imap_graph"; CREATE VIEW IF NOT EXISTS "imap_graph" AS SELECT ure.graph_name, ur.uniform_resource_id, ur.nature, ur.uri, ur.content, ur_ingest_imap.ur_ingest_session_imap_acct_folder_message_id, ur_ingest_imap.ingest_imap_acct_folder_id, ur_ingest_imap.message_id FROM uniform_resource_edge ure JOIN uniform_resource ur ON ure.uniform_resource_id = ur.uniform_resource_id JOIN ur_ingest_session_imap_acct_folder_message ur_ingest_imap ON ure.node_id = ur_ingest_imap.ur_ingest_session_imap_acct_folder_message_id WHERE ure.graph_name = 'imap'; ; DROP VIEW IF EXISTS "filesystem_graph"; CREATE VIEW IF NOT EXISTS "filesystem_graph" AS SELECT ure.graph_name, ur.uniform_resource_id, ur.nature, ur.uri, ur_ingest_fs_path.ur_ingest_session_fs_path_id, ur_ingest_fs_path.root_path FROM uniform_resource_edge ure JOIN uniform_resource ur ON ure.uniform_resource_id = ur.uniform_resource_id JOIN ur_ingest_session_fs_path ur_ingest_fs_path ON ure.node_id = ur_ingest_fs_path.ur_ingest_session_fs_path_id WHERE ure.graph_name = 'filesystem'; ; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Ad tracking is limited (macOS)', 'SELECT CASE WHEN EXISTS (SELECT 1 FROM managed_policies WHERE domain=''com.apple.AdLib'' AND name=''forceLimitAdTracking'' AND value=''1'' LIMIT 1) THEN ''true'' ELSE ''false'' END AS policy_result;', 'Checks that a mobile device management (MDM) solution configures the Mac to limit advertisement tracking.', 'Pass', 'Fail', NULL, 'Contact your IT administrator to ensure your Mac is receiving a profile that disables advertisement tracking.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Antivirus healthy (Linux)', 'SELECT score FROM (SELECT CASE WHEN COUNT() = 2 THEN ''true'' ELSE ''false'' END AS score FROM processes WHERE (name = ''clamd'') OR (name = ''freshclam'')) WHERE score = ''true'';', 'Checks that both ClamAV''s daemon and its updater service (freshclam) are running.', 'Pass', 'Fail', NULL, 'Ensure ClamAV and Freshclam are installed and running.', '["linux","windows","darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Antivirus healthy (macOS)', 'SELECT score FROM (SELECT case when COUNT(*) = 2 then 1 ELSE 0 END AS score FROM plist WHERE (key = ''CFBundleShortVersionString'' AND path = ''/Library/Apple/System/Library/CoreServices/XProtect.bundle/Contents/Info.plist'' AND value>=2162) OR (key = ''CFBundleShortVersionString'' AND path = ''/Library/Apple/System/Library/CoreServices/MRT.app/Contents/Info.plist'' and value>=1.93)) WHERE score == 1;', 'Checks the version of Malware Removal Tool (MRT) and the built-in macOS AV (Xprotect). Replace version numbers with the latest version regularly.', 'Pass', 'Fail', NULL, 'To enable automatic security definition updates, on the failing device, select System Preferences > Software Update > Advanced > Turn on Install system data files and security updates.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Antivirus healthy (Windows)', 'SELECT 1 from windows_security_center wsc CROSS JOIN windows_security_products wsp WHERE antivirus = ''Good'' AND type = ''Antivirus'' AND signatures_up_to_date=1;', 'Checks the status of antivirus and signature updates from the Windows Security Center.', 'Pass', 'Fail', NULL, 'Ensure Windows Defender or your third-party antivirus is running, up to date, and visible in the Windows Security Center.', '["windows"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Automatic installation of application updates is enabled (macOS)', 'SELECT 1 FROM managed_policies WHERE domain=''com.apple.SoftwareUpdate'' AND name=''AutomaticallyInstallAppUpdates'' AND value=1 LIMIT 1;', 'Checks that a mobile device management (MDM) solution configures the Mac to automatically install updates to App Store applications.', 'Pass', 'Fail', NULL, 'Contact your IT administrator to ensure your Mac is receiving a profile that enables automatic installation of application updates.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Automatic installation of operating system updates is enabled (macOS)', 'SELECT 1 FROM managed_policies WHERE domain=''com.apple.SoftwareUpdate'' AND name=''AutomaticallyInstallMacOSUpdates'' AND value=1 LIMIT 1;', 'Checks that a mobile device management (MDM) solution configures the Mac to automatically install operating system updates.', 'Pass', 'Fail', NULL, 'Contact your IT administrator to ensure your Mac is receiving a profile that enables automatic installation of operating system updates.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Ensure ''Minimum password length'' is set to ''14 or more characters''', 'SELECT 1 FROM security_profile_info WHERE minimum_password_length >= 14;', 'This policy setting determines the least number of characters that make up a password for a user account.', 'Pass', 'Fail', NULL, 'Automatic method: Ask your system administrator to establish the recommended configuration via GP, set the following UI path to 14 or more characters ''Computer ConfigurationPoliciesWindows SettingsSecurity SettingsAccount PoliciesPassword PolicyMinimum password length''', '["windows"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING;	2fe40dd7a7f8a0d9c60af5047ddb0b449a5d9146	1	2025-09-02 14:39:57

Error in query number 6

We are sorry, but an error occurred while generating this page. You should contact the site's administrator.

Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:

            SELECT 'text' AS component,
                (SELECT CASE WHEN $current_page > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || ')' ELSE ' END) || ' ' ||
                                 ⬆️
                '(Page ' || $current_page || ' of ' || $total_pages || ') ' ||

Backtrace

sql parser error: Expected: ), found: $current_page at Line: 42, Column: 35

"console/content/view/code_notebook_sql_cell_migratable_version.auto.sql" contains a syntax error preventing SQLPage from parsing and preparing its SQL statements.

Note: You can hide error messages like this one from your users by setting the 'environment' configuration option to 'production'.