0pt/sql/009_analytics_execution_metrics_governance.sql

-- 0pt analytics: execution metadata, metric governance, semantic registry,
-- quarantine (BI-P018, BI-P021, BI-P025, BI-P028, BI-P030, BI-P031).
--
-- Extends sql/008. Every statement is ADDITIVE and idempotent: ALTER TABLE ...
-- ADD COLUMN IF NOT EXISTS, CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE
-- FUNCTION, DROP TRIGGER IF EXISTS before CREATE TRIGGER. It can be replayed
-- onto an already-migrated database, which is what the restore drill needs.
--
-- Ordering: requires sql/008 (it alters two of its tables).
--
-- WHY COLUMNS RATHER THAN NEW TABLES for the metric fields: BI-P028 names
-- fifteen fields and BI-P002 already persists nine of them. A second metric
-- table would be exactly the failure BI-P028's DoD is about ("no production
-- metric exists only inside a dashboard tool" generalises to "one metric, one
-- home"), so the six missing fields are added where the metric already lives.

-- ═══════════════════════════════════════════════════════════════════════════
-- BI-P018 — QUERY EXECUTION MODEL
-- ═══════════════════════════════════════════════════════════════════════════
-- The fields BI-P018 lists that sql/008's QueryExecution did not carry.
--
-- `compiled_query_hash` and NOT the statement: BI-P018 says "without
-- unnecessarily storing sensitive raw SQL". There is deliberately no
-- `query_text` column, and the CHECK below makes adding one by accident
-- visible — a hash is 64 hex characters and nothing else.
ALTER TABLE opt_analytics_query_execution
  ADD COLUMN IF NOT EXISTS principal_id TEXT,
  ADD COLUMN IF NOT EXISTS workload TEXT,
  ADD COLUMN IF NOT EXISTS compiled_query_hash TEXT,
  ADD COLUMN IF NOT EXISTS datasets TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS metrics TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS cache_status TEXT NOT NULL DEFAULT 'miss',
  ADD COLUMN IF NOT EXISTS cost_units NUMERIC(20, 6);

-- NOTE ON THE GUARD SHAPE. `pg_constraint.conname` is NOT unique across
-- schemas: a bare `WHERE conname = '...'` matches a same-named constraint in
-- ANY schema and silently skips the ADD here. Found by running this file into
-- a second schema on a database that already had it in `public` — every CHECK
-- below was quietly absent, and the tests that should have proven them passed
-- because the INSERTs they expected to fail succeeded. Every guard therefore
-- scopes to the table via `conrelid = '<table>'::regclass`, which resolves
-- through search_path.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'opt_analytics_query_execution_hash_shape'
      AND conrelid = 'opt_analytics_query_execution'::regclass
  ) THEN
    ALTER TABLE opt_analytics_query_execution
      ADD CONSTRAINT opt_analytics_query_execution_hash_shape
      CHECK (compiled_query_hash IS NULL OR compiled_query_hash ~ '^[0-9a-f]{64}$');
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'opt_analytics_query_execution_cache_status'
      AND conrelid = 'opt_analytics_query_execution'::regclass
  ) THEN
    ALTER TABLE opt_analytics_query_execution
      ADD CONSTRAINT opt_analytics_query_execution_cache_status
      CHECK (cache_status IN ('hit','miss','bypass','stale_refused','not_cacheable'));
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'opt_analytics_query_execution_workload'
      AND conrelid = 'opt_analytics_query_execution'::regclass
  ) THEN
    ALTER TABLE opt_analytics_query_execution
      ADD CONSTRAINT opt_analytics_query_execution_workload
      CHECK (workload IS NULL OR workload IN
        ('dashboard','report','api','analyst_sql','ai_analyst','scheduled','internal'));
  END IF;
END $$;

-- Cost reporting is per tenant per window; index for it rather than making
-- every billing query a sequential scan of every tenant's executions.
CREATE INDEX IF NOT EXISTS opt_analytics_query_execution_cost_idx
  ON opt_analytics_query_execution (tenant_id, submitted_at DESC)
  WHERE cost_units IS NOT NULL;

-- ═══════════════════════════════════════════════════════════════════════════
-- BI-P017 — ANALYST SQL STATEMENT HISTORY
-- ═══════════════════════════════════════════════════════════════════════════
-- The ONE place raw SQL is retained, and it is a separate table with a
-- separate retention posture precisely so that decision is visible.
--
-- `statement_normalized` (literals masked) is NOT NULL: it is always present
-- and is the searchable, non-PII form. `statement_raw` is nullable, so a
-- deployment that chooses not to retain it simply leaves it NULL rather than
-- losing the history entirely.
CREATE TABLE IF NOT EXISTS opt_analytics_sql_statement (
  statement_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT,
  principal_id TEXT NOT NULL,
  statement_normalized TEXT NOT NULL,
  statement_raw TEXT,
  statement_hash TEXT NOT NULL CHECK (statement_hash ~ '^[0-9a-f]{64}$'),
  referenced_objects TEXT[] NOT NULL DEFAULT '{}',
  outcome TEXT NOT NULL CHECK (outcome IN ('admitted','refused','succeeded','failed','cancelled')),
  error_code TEXT,
  refusal_reason TEXT,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at TIMESTAMPTZ,
  duration_ms INTEGER,
  rows_returned BIGINT,
  rows_read BIGINT,
  bytes_read BIGINT,
  correlation_id TEXT,
  -- A refusal must say why. An unreviewable refusal log is not an audit trail,
  -- and an analyst probing the boundary is exactly what an operator needs to see.
  CHECK (outcome <> 'refused' OR (refusal_reason IS NOT NULL AND length(btrim(refusal_reason)) > 0))
);
CREATE INDEX IF NOT EXISTS opt_analytics_sql_statement_tenant_idx
  ON opt_analytics_sql_statement (tenant_id, submitted_at DESC);
CREATE INDEX IF NOT EXISTS opt_analytics_sql_statement_refused_idx
  ON opt_analytics_sql_statement (tenant_id, submitted_at DESC)
  WHERE outcome = 'refused';

-- ═══════════════════════════════════════════════════════════════════════════
-- BI-P028 / BI-P031 — METRIC DOMAIN MODEL AND GOVERNANCE
-- ═══════════════════════════════════════════════════════════════════════════
ALTER TABLE opt_analytics_metric_definition
  ADD COLUMN IF NOT EXISTS metric_type TEXT NOT NULL DEFAULT 'simple',
  ADD COLUMN IF NOT EXISTS time_dimension TEXT,
  ADD COLUMN IF NOT EXISTS unit TEXT,
  ADD COLUMN IF NOT EXISTS currency TEXT,
  ADD COLUMN IF NOT EXISTS display_format TEXT NOT NULL DEFAULT 'decimal',
  ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'DRAFT',
  ADD COLUMN IF NOT EXISTS filters_json TEXT NOT NULL DEFAULT '[]',
  ADD COLUMN IF NOT EXISTS certification TEXT NOT NULL DEFAULT 'experimental',
  ADD COLUMN IF NOT EXISTS business_definition TEXT,
  ADD COLUMN IF NOT EXISTS technical_definition TEXT,
  ADD COLUMN IF NOT EXISTS meaning_hash TEXT,
  ADD COLUMN IF NOT EXISTS certified_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS certified_by TEXT;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'opt_analytics_metric_status'
     AND conrelid = 'opt_analytics_metric_definition'::regclass) THEN
    ALTER TABLE opt_analytics_metric_definition ADD CONSTRAINT opt_analytics_metric_status
      CHECK (status IN ('DRAFT','REVIEW','ACTIVE','DEPRECATED','RETIRED'));
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'opt_analytics_metric_type'
     AND conrelid = 'opt_analytics_metric_definition'::regclass) THEN
    ALTER TABLE opt_analytics_metric_definition ADD CONSTRAINT opt_analytics_metric_type
      CHECK (metric_type IN ('simple','ratio','derived','cumulative','conversion'));
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'opt_analytics_metric_format'
     AND conrelid = 'opt_analytics_metric_definition'::regclass) THEN
    ALTER TABLE opt_analytics_metric_definition ADD CONSTRAINT opt_analytics_metric_format
      CHECK (display_format IN ('integer','decimal','percent','currency','duration_ms','bytes'));
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'opt_analytics_metric_certification'
     AND conrelid = 'opt_analytics_metric_definition'::regclass) THEN
    ALTER TABLE opt_analytics_metric_definition ADD CONSTRAINT opt_analytics_metric_certification
      CHECK (certification IN ('experimental','certified','deprecated'));
  END IF;
  -- A currency-formatted metric with no currency is how SEK gets rendered as
  -- USD on a dashboard nobody double-checks.
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'opt_analytics_metric_currency_pairing'
     AND conrelid = 'opt_analytics_metric_definition'::regclass) THEN
    ALTER TABLE opt_analytics_metric_definition ADD CONSTRAINT opt_analytics_metric_currency_pairing
      CHECK (
        (display_format = 'currency' AND currency IS NOT NULL AND currency ~ '^[A-Z]{3}$')
        OR (display_format <> 'currency' AND currency IS NULL)
      );
  END IF;
  -- Certification is an ACT: it has an actor and a time, or it has not happened.
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'opt_analytics_metric_certification_act'
     AND conrelid = 'opt_analytics_metric_definition'::regclass) THEN
    ALTER TABLE opt_analytics_metric_definition ADD CONSTRAINT opt_analytics_metric_certification_act
      CHECK (
        certification <> 'certified'
        OR (certified_by IS NOT NULL AND certified_at IS NOT NULL
            AND business_definition IS NOT NULL AND technical_definition IS NOT NULL)
      );
  END IF;
END $$;

-- ── BI-P031's DoD, in the DATABASE ────────────────────────────────────────
-- "Certified metric cannot be modified in-place; new version required."
--
-- The TypeScript check (assertMetricEditAllowed) is layer 1. This trigger is
-- layer 2, and it is the one that holds when something writes to the table
-- without going through the application — a migration script, a psql session,
-- an admin tool. A compiler opinion does not survive `psql`; a trigger does.
--
-- The rule is on the MEANING HASH, so a change this trigger has never heard of
-- still counts as a change: new fields default to "changes the meaning" rather
-- than to "allowed", which is the fail-closed direction.
CREATE OR REPLACE FUNCTION opt_analytics_metric_certified_immutable() RETURNS trigger AS $$
BEGIN
  IF OLD.certification = 'certified'
     AND NEW.current_version = OLD.current_version
     AND NEW.meaning_hash IS DISTINCT FROM OLD.meaning_hash THEN
    RAISE EXCEPTION
      'BI-P031: metric % is CERTIFIED; its definition cannot be modified in place - mint version %',
      OLD.name, OLD.current_version + 1
      USING ERRCODE = 'restrict_violation';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS opt_analytics_metric_certified_guard ON opt_analytics_metric_definition;
CREATE TRIGGER opt_analytics_metric_certified_guard
  BEFORE UPDATE ON opt_analytics_metric_definition
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_metric_certified_immutable();

-- ═══════════════════════════════════════════════════════════════════════════
-- BI-P030 — SEMANTIC MODEL REGISTRY
-- ═══════════════════════════════════════════════════════════════════════════
-- Persisted SEPARATELY from any implementation engine, which is the whole
-- point of the prompt. There is no column here for a provider manifest, and
-- `assertOnlySemanticModelFields` refuses one in the application; the schema
-- refuses it by simply not having anywhere to put it.
--
-- sql/008's opt_analytics_semantic_model holds the current model; this table
-- holds the VERSION HISTORY BI-P030 enumerates, and its rows are immutable for
-- the same reason a MetricVersion's are: a dashboard pinned to version 7 must
-- mean the same thing tomorrow.
CREATE TABLE IF NOT EXISTS opt_analytics_semantic_model_version (
  semantic_model_version_id TEXT PRIMARY KEY,
  model_id TEXT NOT NULL,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version >= 1),
  datasets TEXT[] NOT NULL DEFAULT '{}',
  relationships_json TEXT NOT NULL DEFAULT '[]',
  metrics_json TEXT NOT NULL DEFAULT '[]',
  dimensions_json TEXT NOT NULL DEFAULT '[]',
  valid_from TIMESTAMPTZ NOT NULL DEFAULT now(),
  status TEXT NOT NULL CHECK (status IN ('draft','active','superseded','retired')),
  hash TEXT NOT NULL CHECK (hash ~ '^[0-9a-f]{64}$'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT,
  UNIQUE (tenant_id, model_id, version)
);
CREATE INDEX IF NOT EXISTS opt_analytics_semantic_model_version_tenant_idx
  ON opt_analytics_semantic_model_version (tenant_id, project_id, model_id, version DESC);

-- At most ONE active version per model per tenant. A partial unique index
-- rather than application discipline: two active versions is a silent
-- correctness failure in which half the dashboards read a different model.
CREATE UNIQUE INDEX IF NOT EXISTS opt_analytics_semantic_model_one_active
  ON opt_analytics_semantic_model_version (tenant_id, model_id)
  WHERE status = 'active';

-- A published version's CONTENT is immutable; only its status and valid_from
-- may move (draft -> active -> superseded -> retired).
CREATE OR REPLACE FUNCTION opt_analytics_semantic_version_content_immutable() RETURNS trigger AS $$
BEGIN
  IF NEW.hash IS DISTINCT FROM OLD.hash
     OR NEW.metrics_json IS DISTINCT FROM OLD.metrics_json
     OR NEW.dimensions_json IS DISTINCT FROM OLD.dimensions_json
     OR NEW.relationships_json IS DISTINCT FROM OLD.relationships_json
     OR NEW.datasets IS DISTINCT FROM OLD.datasets
     OR NEW.version IS DISTINCT FROM OLD.version THEN
    RAISE EXCEPTION
      'BI-P030: semantic model % v% is immutable; publish a new version instead',
      OLD.model_id, OLD.version
      USING ERRCODE = 'restrict_violation';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS opt_analytics_semantic_version_guard ON opt_analytics_semantic_model_version;
CREATE TRIGGER opt_analytics_semantic_version_guard
  BEFORE UPDATE ON opt_analytics_semantic_model_version
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_semantic_version_content_immutable();

-- ═══════════════════════════════════════════════════════════════════════════
-- BI-P025 / BI-P026 — QUARANTINE AND FRESHNESS
-- ═══════════════════════════════════════════════════════════════════════════
-- The quarantine record BI-P025's DoD acts on. The READ-side gate lives in the
-- application (`assertProjectionReadable`); this is its durable state, so a
-- process restart does not release a quarantine.
CREATE TABLE IF NOT EXISTS opt_analytics_projection_quarantine (
  tenant_id TEXT NOT NULL,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  quarantined BOOLEAN NOT NULL,
  caused_by TEXT[] NOT NULL DEFAULT '{}',
  reason TEXT,
  since TIMESTAMPTZ,
  released_at TIMESTAMPTZ,
  released_by TEXT,
  PRIMARY KEY (tenant_id, dataset_id),
  -- Release is an ACT with an operator behind it, never an automatic
  -- consequence of a later passing run: a flapping rule would otherwise
  -- quarantine and un-quarantine a dataset between two reads of one dashboard.
  CHECK (quarantined OR released_at IS NULL OR (released_by IS NOT NULL AND reason IS NOT NULL))
);

-- BI-P026 freshness state, per dataset. `UNKNOWN` is the DEFAULT, deliberately:
-- a dataset nobody has computed freshness for must not read as FRESH.
CREATE TABLE IF NOT EXISTS opt_analytics_dataset_freshness (
  tenant_id TEXT NOT NULL,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  state TEXT NOT NULL DEFAULT 'UNKNOWN'
    CHECK (state IN ('FRESH','DELAYED','STALE','FAILED','UNKNOWN')),
  age_ms BIGINT,
  last_successful_load_at TIMESTAMPTZ,
  last_run_at TIMESTAMPTZ,
  last_run_status TEXT CHECK (last_run_status IN ('succeeded','failed','running','never_run')),
  expected_interval_ms BIGINT NOT NULL,
  disclosure TEXT NOT NULL,
  computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, dataset_id),
  -- Every state must carry a disclosure a UI can render verbatim. A state with
  -- no words attached is a state a dashboard will render as a silent green dot.
  CHECK (length(btrim(disclosure)) > 0)
);

-- ═══════════════════════════════════════════════════════════════════════════
-- BI-P021 — USAGE LEDGER
-- ═══════════════════════════════════════════════════════════════════════════
-- What CL0UD quotas, AUD0 usage and B00K accounting read. Append-only.
--
-- `terminated_by_budget` is recorded because a query the budget killed still
-- COST what it burned before it died; not charging for it is how a tenant
-- learns that the cheapest query is one that gets cancelled.
CREATE TABLE IF NOT EXISTS opt_analytics_usage_ledger (
  usage_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT,
  principal_id TEXT NOT NULL,
  query_id TEXT NOT NULL,
  engine TEXT NOT NULL,
  rows_scanned BIGINT NOT NULL DEFAULT 0,
  bytes_scanned BIGINT NOT NULL DEFAULT 0,
  storage_read_bytes BIGINT NOT NULL DEFAULT 0,
  execution_ms BIGINT NOT NULL DEFAULT 0,
  cost_units NUMERIC(20, 6) NOT NULL,
  terminated_by_budget BOOLEAN NOT NULL DEFAULT false,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS opt_analytics_usage_ledger_window_idx
  ON opt_analytics_usage_ledger (tenant_id, recorded_at DESC);

DROP TRIGGER IF EXISTS opt_analytics_usage_ledger_immutable ON opt_analytics_usage_ledger;
CREATE TRIGGER opt_analytics_usage_ledger_immutable
  BEFORE UPDATE OR DELETE ON opt_analytics_usage_ledger
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_forbid_mutation();