AI Context Middleware Explorer

ConstructionSqlNotebook.v001_seedDML (SQLite SQL Statements)

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;