Retention, Privacy, Lineage, and Data Quality

CREATE TABLE retention_policy (    policy_code       TEXT PRIMARY KEY,    object_name       TEXT NOT NULL,    trigger_event     TEXT NOT NULL,    active_days       INTEGER NOT NULL CHECK (active_days >= 0),    archive_days      INTEGER NOT NULL CHECK (archive_days >= 0),    terminal_action   TEXT NOT NULL CHECK (        terminal_action IN ('delete','anonymize','review')    ),    owner             TEXT NOT NULL,    legal_basis       TEXT NOT NULL,    reviewed_at       TEXT NOT NULL) STRICT;INSERT INTO retention_policy VALUES('customer_profile','customer','account_closed',30,0,'anonymize','privacy','service + legal review','2026-08-01'),('sales_order','sales_order','order_completed',2555,365,'review','finance','financial recordkeeping','2026-08-01'),('security_audit','security_audit','event_created',365,730,'delete','security','security monitoring','2026-08-01');SELECT * FROM retention_policy ORDER BY object_name;

BEGIN IMMEDIATE;-- Example eligibility: already deleted and no non-cancelled orders.UPDATE customerSET email = 'deleted+' || customer_id || '@invalid.example',    full_name = 'Deleted Customer',    date_of_birth = NULL,    marketing_consent = 0WHERE customer_id = :customer_id  AND deleted_at IS NOT NULL  AND NOT EXISTS (      SELECT 1      FROM sales_order AS o      WHERE o.customer_id = customer.customer_id        AND o.status <> 'cancelled'  );-- Remove a token that is not needed for financial recordkeeping.UPDATE sales_orderSET payment_token = NULL,    customer_id = NULLWHERE customer_id = :customer_id  AND status = 'cancelled'  AND changes() = 1;COMMIT;

CREATE TABLE lineage_edge (    upstream_object   TEXT NOT NULL,    downstream_object TEXT NOT NULL,    transformation    TEXT NOT NULL,    owner             TEXT NOT NULL,    code_version      TEXT NOT NULL,    observed_at       TEXT NOT NULL,    PRIMARY KEY (upstream_object, downstream_object, transformation)) STRICT;INSERT INTO lineage_edge VALUES('commerce.customer','reporting.customer_order_summary','join + aggregate','analytics','git:4ab7c2e','2026-08-05'),('commerce.sales_order','reporting.customer_order_summary','join + aggregate','analytics','git:4ab7c2e','2026-08-05'),('reporting.customer_order_summary','dashboard.executive_sales','semantic projection','bi-team','dashboard:v12','2026-08-05');SELECT upstream_object, downstream_object, owner, code_versionFROM lineage_edgeORDER BY upstream_object, downstream_object;

CREATE TABLE data_quality_result (    check_name       TEXT NOT NULL,    measured_at      TEXT NOT NULL,    observed_value   REAL NOT NULL,    threshold_value  REAL NOT NULL,    comparison       TEXT NOT NULL CHECK (comparison IN ('<=','>=','=')),    passed           INTEGER NOT NULL CHECK (passed IN (0,1)),    details          TEXT,    PRIMARY KEY (check_name, measured_at)) STRICT;INSERT INTO data_quality_resultSELECT    'active_customer_email_completeness',    datetime('now'),    AVG(CASE WHEN email IS NOT NULL AND trim(email) <> '' THEN 1.0 ELSE 0.0 END),    1.0,    '>=',    AVG(CASE WHEN email IS NOT NULL AND trim(email) <> '' THEN 1.0 ELSE 0.0 END) >= 1.0,    'Expected 100% for active customer records'FROM customerWHERE deleted_at IS NULL;INSERT INTO data_quality_resultSELECT    'orphan_order_count',    datetime('now'),    COUNT(*),    0,    '=',    COUNT(*) = 0,    'Orders whose non-null customer_id does not resolve'FROM sales_order AS oLEFT JOIN customer AS c ON c.customer_id = o.customer_idWHERE o.customer_id IS NOT NULL  AND c.customer_id IS NULL;SELECT * FROM data_quality_result ORDER BY check_name;