0pt/sql/010_analytics_dashboard_releases.sql

-- 0pt analytics: dashboard releases (BI-P061 — DASHBOARD VERSIONING).
--
-- Extends sql/008. Additive and idempotent (CREATE TABLE IF NOT EXISTS,
-- CREATE OR REPLACE FUNCTION, DROP TRIGGER IF EXISTS before CREATE TRIGGER),
-- so it replays onto an already-migrated database like 009 does.
--
-- Ordering: requires sql/008 (it references opt_analytics_dashboard_version).
--
-- WHY A SEPARATE TABLE RATHER THAN A `state` COLUMN ON THE VERSION.
-- sql/008 puts a BEFORE UPDATE OR DELETE trigger on
-- opt_analytics_dashboard_version, because BI-P002 lists DashboardVersion as
-- immutable. A lifecycle column on that row would need UPDATEs, so adding one
-- would mean weakening the immutability everything else depends on. Instead
-- the frozen DOCUMENT stays immutable in the version table, and the RELEASE —
-- the thing that legitimately moves DRAFT -> PUBLISHED -> SUPERSEDED — lives
-- here with its own, much narrower, mutation rule:
--
--   * `state` may only advance draft -> published -> superseded. Any other
--     transition RAISEs, including published -> draft ("un-publishing" is how
--     a rollback gets faked without leaving a trace) and superseded -> *.
--   * Every content column is frozen on UPDATE. A release may change state and
--     nothing else; repointing a published release at a different version is
--     the exact edit that would make "immutable release" a lie.
--   * DELETE is refused outright. Release history is a ledger; a rollback is
--     an entry in it, never the removal of one.
--
-- THE INVARIANT THAT NEEDS THE DATABASE: at most ONE published release per
-- dashboard. An application check reads, decides, then writes, and two
-- requests that interleave between the read and the write both decide "yes".
-- The partial unique index below turns that race into a constraint violation
-- for the loser. tests/analytics/dashboard-release-postgres.realinfra.test.ts
-- runs the race with the index DROPPED first and shows two published releases
-- for one dashboard, so the index is known to be what prevents it.

-- ── the release ledger ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_dashboard_release (
  release_id TEXT PRIMARY KEY,
  dashboard_id TEXT NOT NULL REFERENCES opt_analytics_dashboard_definition (dashboard_id),
  dashboard_version_id TEXT NOT NULL REFERENCES opt_analytics_dashboard_version (dashboard_version_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version >= 1),
  state TEXT NOT NULL CHECK (state IN ('draft', 'published', 'superseded')),
  -- The BI-P060 canonical hash of the released document. Copied here so a
  -- rollback can be VERIFIED (the new release must carry the hash of the
  -- version it restores) without re-reading and re-hashing the document.
  document_hash TEXT NOT NULL CHECK (document_hash ~ '^[0-9a-f]{64}$'),
  -- Set when this release restores an earlier version. It is a NEW release of
  -- a NEW version, never a resurrected old row -- that is what keeps the
  -- history linear and the audit answerable ("what was live at 14:05?").
  rollback_of_version INTEGER CHECK (rollback_of_version >= 1),
  created_by TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at TIMESTAMPTZ,
  superseded_at TIMESTAMPTZ,
  superseded_by_release_id TEXT,
  UNIQUE (dashboard_id, version),
  -- A state's timestamps must match the state. A 'published' row with no
  -- published_at is the shape that makes an SLO or an audit answer wrong.
  CONSTRAINT opt_analytics_dashboard_release_timestamps CHECK (
    (state = 'draft' AND published_at IS NULL AND superseded_at IS NULL)
    OR (state = 'published' AND published_at IS NOT NULL AND superseded_at IS NULL)
    OR (state = 'superseded' AND published_at IS NOT NULL AND superseded_at IS NOT NULL)
  )
);

CREATE INDEX IF NOT EXISTS opt_analytics_dashboard_release_tenant_idx
  ON opt_analytics_dashboard_release (tenant_id, project_id, dashboard_id, version);

-- At most one PUBLISHED release per dashboard. This is the concurrency
-- guarantee; the application's check is only an early, friendlier error.
CREATE UNIQUE INDEX IF NOT EXISTS opt_analytics_dashboard_release_one_published
  ON opt_analytics_dashboard_release (dashboard_id)
  WHERE state = 'published';

-- At most one open DRAFT per dashboard. Two drafts race to publish and the
-- loser's edits vanish into a superseded release nobody asked for.
CREATE UNIQUE INDEX IF NOT EXISTS opt_analytics_dashboard_release_one_draft
  ON opt_analytics_dashboard_release (dashboard_id)
  WHERE state = 'draft';

-- ── transition enforcement ────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION opt_analytics_dashboard_release_guard() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    RAISE EXCEPTION
      'BI-P061: dashboard releases are a ledger; delete is refused (release %)', OLD.release_id
      USING ERRCODE = 'restrict_violation';
  END IF;

  -- Content is frozen for the life of the release.
  IF NEW.dashboard_id IS DISTINCT FROM OLD.dashboard_id
     OR NEW.dashboard_version_id IS DISTINCT FROM OLD.dashboard_version_id
     OR NEW.tenant_id IS DISTINCT FROM OLD.tenant_id
     OR NEW.project_id IS DISTINCT FROM OLD.project_id
     OR NEW.version IS DISTINCT FROM OLD.version
     OR NEW.document_hash IS DISTINCT FROM OLD.document_hash
     OR NEW.rollback_of_version IS DISTINCT FROM OLD.rollback_of_version
     OR NEW.created_at IS DISTINCT FROM OLD.created_at THEN
    RAISE EXCEPTION
      'BI-P061: a dashboard release is immutable apart from its state (release %)', OLD.release_id
      USING ERRCODE = 'restrict_violation';
  END IF;

  IF NEW.state IS DISTINCT FROM OLD.state THEN
    IF NOT ((OLD.state = 'draft' AND NEW.state = 'published')
            OR (OLD.state = 'published' AND NEW.state = 'superseded')) THEN
      RAISE EXCEPTION
        'BI-P061: illegal dashboard release transition % -> % (release %)',
        OLD.state, NEW.state, OLD.release_id
        USING ERRCODE = 'restrict_violation';
    END IF;
  END IF;

  -- A published timestamp, once set, is history.
  IF OLD.published_at IS NOT NULL AND NEW.published_at IS DISTINCT FROM OLD.published_at THEN
    RAISE EXCEPTION
      'BI-P061: published_at is history and cannot be rewritten (release %)', OLD.release_id
      USING ERRCODE = 'restrict_violation';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS opt_analytics_dashboard_release_guard ON opt_analytics_dashboard_release;
CREATE TRIGGER opt_analytics_dashboard_release_guard
  BEFORE UPDATE OR DELETE ON opt_analytics_dashboard_release
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_dashboard_release_guard();