db/schema.sql

-- WS1.2 append-only substrate schema (Postgres).
--
-- The three-layer core (document / report_version / annotation) plus the W3C PROV tables
-- (activity / artifact / activity_input), the INSERT-only writer role, the mutation-blocking
-- triggers, actor stamping via SET LOCAL app.actor, and the current / agreement views.
--
-- Grounded in docs/pilot_architecture.md: column names track the current stores this
-- replaces (cases, reports, observations, field_confirmations, llm_calls). This file is the target
-- schema; WS1.3 wires it under Alembic and WS1.4 routes every write through a data-access layer.
-- Not yet applied to a running database: the WS1 exit criteria (app runs on Postgres locally,
-- UPDATE annotation fails a test, as-of returns pre-amendment state) gate that.

BEGIN;

-- ---------------------------------------------------------------------------
-- Roles. app_writer is the only principal the app uses: it may INSERT into the
-- append-only tables and SELECT the views, and nothing else. security_officer is
-- the sole role allowed to DELETE, for the excision runbook (WS4.4). app_reader
-- is the analyst's SELECT-only principal.
-- ---------------------------------------------------------------------------
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_writer') THEN
        CREATE ROLE app_writer LOGIN;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_reader') THEN
        CREATE ROLE app_reader LOGIN;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'security_officer') THEN
        CREATE ROLE security_officer LOGIN;
    END IF;
END
$$;

-- ---------------------------------------------------------------------------
-- Actor stamping. Every write runs inside BEGIN; SET LOCAL app.actor = '...'; the
-- BEFORE INSERT trigger copies that setting onto created_by and refuses a write that
-- did not name an actor. Machine writes set app.actor = 'svc:extractor'; a human write
-- sets it to the reviewer identity.
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION stamp_actor() RETURNS trigger AS $$
DECLARE
    actor text := current_setting('app.actor', true);
BEGIN
    IF actor IS NULL OR actor = '' THEN
        RAISE EXCEPTION 'no app.actor set: every write must SET LOCAL app.actor first';
    END IF;
    NEW.created_by := actor;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ---------------------------------------------------------------------------
-- Append-only enforcement. UPDATE and DELETE raise, except for security_officer,
-- which excision uses under the ticketed runbook. INSERT is the only ordinary write.
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION block_mutation() RETURNS trigger AS $$
BEGIN
    IF current_user <> 'security_officer' THEN
        RAISE EXCEPTION 'append-only: % on % is not permitted', TG_OP, TG_TABLE_NAME;
    END IF;
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ---------------------------------------------------------------------------
-- Core layer 1: document. One row per ingested source document, keyed by the
-- sha256 of its bytes so a re-ingest dedupes (WS3). Replaces the case/patient anchor
-- role of the current cases table; case_barcode groups a patient's documents.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS document (
    document_id     text PRIMARY KEY,          -- sha256 of the source bytes
    case_barcode    text NOT NULL,
    project_id      text NOT NULL DEFAULT 'default',
    source          text NOT NULL,             -- upload | watch | pull
    filename        text,
    content_sha256  text NOT NULL,
    ingested_at     timestamptz NOT NULL DEFAULT now(),
    created_by      text NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_document_case ON document (case_barcode);

-- Core layer 2: report_version. A version of a report drawn from a document. An
-- amendment appends a new row with supersedes set to the prior version and version_no
-- incremented (WS1.5 wires amendments.py to write these; today nothing does).
CREATE TABLE IF NOT EXISTS report_version (
    report_version_id text PRIMARY KEY,
    document_id       text NOT NULL REFERENCES document(document_id),
    case_barcode      text NOT NULL,
    report_type       text NOT NULL,
    institution       text,
    report_date       date,
    staging_edition   text NOT NULL,
    supersedes        text REFERENCES report_version(report_version_id),
    version_no        integer NOT NULL DEFAULT 1,
    report_text_sha256 text,                   -- text stored as an artifact, referenced by hash
    is_authoritative  boolean,
    created_at        timestamptz NOT NULL DEFAULT now(),
    created_by        text NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rv_document ON report_version (document_id);
CREATE INDEX IF NOT EXISTS idx_rv_supersedes ON report_version (supersedes);

-- PROV: activity. One row per run (extraction, ocr, review, ingest, migration).
-- Replaces llm_calls, which is already append-only; adds the kind and version fields
-- the PROV graph needs.
CREATE TABLE IF NOT EXISTS activity (
    activity_id      text PRIMARY KEY,
    kind             text NOT NULL,            -- extraction | ocr | review | ingest | migration
    model            text,
    prompt_sha256    text,
    template_version text,
    params_json      jsonb,
    input_tokens     integer,
    output_tokens    integer,
    latency_ms       real,
    status           text NOT NULL DEFAULT 'ok',
    started_at       timestamptz NOT NULL DEFAULT now(),
    ended_at         timestamptz,
    created_by       text NOT NULL
);

-- PROV: artifact. A hashed thing an activity produced or consumed: a structured
-- extraction, a raw agent trace (PHI, stored as a blob_ref), or ocr_text.
CREATE TABLE IF NOT EXISTS artifact (
    artifact_id     text PRIMARY KEY,
    kind            text NOT NULL,             -- extraction | agent_trace | ocr_text | report_text
    produced_by_act text REFERENCES activity(activity_id),
    content_sha256  text NOT NULL,
    blob_ref        text,                      -- {data_root}/traces/{sha256}.json.zst for PHI blobs
    created_at      timestamptz NOT NULL DEFAULT now(),
    created_by      text NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_artifact_act ON artifact (produced_by_act);

-- PROV: activity_input. The edge "activity used artifact as input" (an extraction
-- reads an ocr_text or report_text artifact). New: no row-level link exists today.
CREATE TABLE IF NOT EXISTS activity_input (
    activity_id text NOT NULL REFERENCES activity(activity_id),
    artifact_id text NOT NULL REFERENCES artifact(artifact_id),
    role        text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    created_by  text NOT NULL,
    PRIMARY KEY (activity_id, artifact_id)
);

-- Core layer 3: annotation. One assertion about a field on a report_version: a
-- machine draft or a human correction. A correction appends a new row with supersedes
-- pointing at the machine draft and version_no incremented, so the machine value and
-- the human value both persist. produced_by_act links to the activity that made it.
-- Replaces observations (machine) and field_confirmations (human), which today
-- overwrite in place and share only a (case, field) key with no supersede edge.
CREATE TABLE IF NOT EXISTS annotation (
    annotation_id    text PRIMARY KEY,
    report_version_id text REFERENCES report_version(report_version_id),
    case_barcode     text NOT NULL,
    field_name       text NOT NULL,
    value_json       jsonb,
    value_kind       text,                     -- number | category | text | boolean
    absence_reason   text,                     -- the shared AbsenceCode vocabulary (CHECK below)
    absence_detail   text,                     -- the human-readable reason, e.g. "FIGO grade on serous"
    absence_candidates jsonb,                  -- for a constrained field, the set the evidence narrowed to
    confidence       real,
    evidence_json    jsonb,                    -- page, bbox, quote, resolved to an input artifact by hash
    status           text NOT NULL,            -- needs_review | confirmed | flagged | not_applicable
    verification_status text,                  -- FHIR VerificationResult.status (CHECK below): a machine
                                               -- draft is attested; a human review is validated/val-fail/...
    supersedes       text REFERENCES annotation(annotation_id),
    -- Which reader produced this value: a backend id from the settings selector (cloud_sonnet,
    -- cpu_batch, ...), 'visual' for a page located by ColPali, 'human' for a reviewer, 'migration'
    -- for a respelling. Two readers answering one field are two lineages, not one superseding the
    -- other, so a reviewer compares them instead of watching the second overwrite the first.
    method           text,
    version_no       integer NOT NULL DEFAULT 1,
    produced_by_act  text REFERENCES activity(activity_id),
    created_at       timestamptz NOT NULL DEFAULT now(),
    created_by       text NOT NULL,            -- svc:extractor for a draft, the reviewer for a correction
    CONSTRAINT absence_reason_vocab CHECK (
        absence_reason IS NULL OR absence_reason IN
        ('not_applicable', 'not_stated', 'indeterminate', 'determined_by_join', 'constrained')
    ),
    -- FHIR VerificationResult status (http://hl7.org/fhir/CodeSystem/verificationresult-status): the
    -- coded outcome of the field's human verification. NULL only for a raw seed row.
    CONSTRAINT verification_status_vocab CHECK (
        verification_status IS NULL OR verification_status IN
        ('attested', 'validated', 'in-process', 'req-revalid', 'val-fail', 'reval-fail', 'entered-in-error')
    )
);
CREATE INDEX IF NOT EXISTS idx_annotation_subject ON annotation (case_barcode, field_name);
CREATE INDEX IF NOT EXISTS idx_annotation_supersedes ON annotation (supersedes);
CREATE INDEX IF NOT EXISTS idx_annotation_act ON annotation (produced_by_act);

-- ---------------------------------------------------------------------------
-- Attach triggers: stamp the actor on insert, block update and delete.
-- ---------------------------------------------------------------------------
DO $$
DECLARE t text;
BEGIN
    FOREACH t IN ARRAY ARRAY['document','report_version','activity','artifact','activity_input','annotation']
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS %I_stamp ON %I', t, t);
        EXECUTE format('CREATE TRIGGER %I_stamp BEFORE INSERT ON %I FOR EACH ROW EXECUTE FUNCTION stamp_actor()', t, t);
        EXECUTE format('DROP TRIGGER IF EXISTS %I_noupd ON %I', t, t);
        EXECUTE format('CREATE TRIGGER %I_noupd BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION block_mutation()', t, t);
        EXECUTE format('DROP TRIGGER IF EXISTS %I_nodel ON %I', t, t);
        EXECUTE format('CREATE TRIGGER %I_nodel BEFORE DELETE ON %I FOR EACH ROW EXECUTE FUNCTION block_mutation()', t, t);
    END LOOP;
END
$$;

-- ---------------------------------------------------------------------------
-- Views. current is the live state: the newest non-superseded annotation per
-- (case, field). agreement is the evaluation number: the rate at which a machine
-- annotation was confirmed unchanged versus corrected by its human superseder,
-- by field and by model and prompt version (WS2.6).
-- ---------------------------------------------------------------------------
-- One recognized region of one page, with the geometry the viewer draws and the identity evidence
-- cites. Tesseract recomputed these on every call and stored none, so a value's box was resolved by
-- re-reading the page and could not be cited, corrected, or checked. A region is addressed by
-- `region_id`, which is stable across re-reads of the same page by the same engine.
CREATE TABLE IF NOT EXISTS ocr_region (
    region_id     text PRIMARY KEY,
    document_id   text NOT NULL,            -- the scan this page belongs to
    case_barcode  text NOT NULL,
    page_id       text NOT NULL,            -- stable page identity, document_id + page_no
    page_no       integer NOT NULL,         -- 1-based, the order the pages render in
    page_width    integer NOT NULL,         -- pixels of the rendered page the boxes are measured against
    page_height   integer NOT NULL,
    ordinal       integer NOT NULL,         -- reading order within the page
    level         text NOT NULL,            -- word | line | paragraph
    text          text NOT NULL,
    confidence    real,                     -- the engine's own confidence, 0-100 for tesseract
    x0            real NOT NULL,            -- normalized 0-1, so a viewer at any size scales it
    y0            real NOT NULL,
    x1            real NOT NULL,
    y1            real NOT NULL,
    polygon       jsonb,                    -- for a deskewed or rotated page, where a box is not enough
    ocr_engine    text NOT NULL,            -- tesseract
    ocr_version   text NOT NULL,            -- the engine version, so a re-read under a new one is visible
    created_at    timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ocr_region_level_vocab CHECK (level IN ('word', 'line', 'paragraph')),
    CONSTRAINT ocr_region_box_normalized CHECK (
        x0 >= 0 AND y0 >= 0 AND x1 <= 1 AND y1 <= 1 AND x1 >= x0 AND y1 >= y0
    )
);
CREATE INDEX IF NOT EXISTS ocr_region_page ON ocr_region (case_barcode, page_no, level, ordinal);
CREATE INDEX IF NOT EXISTS ocr_region_page_id ON ocr_region (page_id);

CREATE OR REPLACE VIEW current_annotation AS
SELECT a.*
FROM annotation a
WHERE NOT EXISTS (
    SELECT 1 FROM annotation s WHERE s.supersedes = a.annotation_id
);

-- The tip of each reader's own chain. `current_annotation` answers "what fills the form"; this answers
-- "what did each reader say", which is what a comparison needs. A row whose method is NULL predates the
-- column and reads as its author's method.
CREATE OR REPLACE VIEW current_annotation_by_method AS
SELECT a.*, coalesce(a.method, CASE WHEN a.created_by = 'svc:extractor' THEN 'unrecorded' ELSE 'human' END) AS reader
FROM annotation a
WHERE NOT EXISTS (
    SELECT 1 FROM annotation s
    WHERE s.supersedes = a.annotation_id
      AND coalesce(s.method, CASE WHEN s.created_by = 'svc:extractor' THEN 'unrecorded' ELSE 'human' END) = coalesce(a.method, CASE WHEN a.created_by = 'svc:extractor' THEN 'unrecorded' ELSE 'human' END)
);

CREATE OR REPLACE VIEW agreement AS
SELECT
    a.field_name,
    act.model,
    act.prompt_sha256,
    count(*)                                              AS machine_values,
    count(*) FILTER (WHERE h.annotation_id IS NOT NULL)   AS reviewed,
    count(*) FILTER (WHERE h.value_json IS NOT DISTINCT FROM a.value_json) AS agreed
FROM annotation a
JOIN activity act ON act.activity_id = a.produced_by_act AND act.kind = 'extraction'
LEFT JOIN annotation h ON h.supersedes = a.annotation_id
GROUP BY a.field_name, act.model, act.prompt_sha256;

-- as_of(t): the state that was current at timestamp t. The newest annotation per
-- (case, field) created at or before t whose superseder, if any, came after t.
CREATE OR REPLACE FUNCTION as_of(t timestamptz)
RETURNS SETOF annotation AS $$
    SELECT a.*
    FROM annotation a
    WHERE a.created_at <= t
      AND NOT EXISTS (
          SELECT 1 FROM annotation s
          WHERE s.supersedes = a.annotation_id AND s.created_at <= t
      );
$$ LANGUAGE sql STABLE;

-- ---------------------------------------------------------------------------
-- Grants. app_writer inserts and reads; app_reader reads the views only;
-- security_officer may delete for excision.
-- ---------------------------------------------------------------------------
GRANT INSERT, SELECT ON document, report_version, activity, artifact, activity_input, annotation TO app_writer;
GRANT SELECT ON current_annotation, current_annotation_by_method, agreement TO app_writer;
GRANT SELECT, INSERT ON ocr_region TO app_writer;
GRANT EXECUTE ON FUNCTION as_of(timestamptz) TO app_writer;

GRANT SELECT ON current_annotation, current_annotation_by_method, agreement TO app_reader;
GRANT SELECT ON ocr_region TO app_reader;
GRANT EXECUTE ON FUNCTION as_of(timestamptz) TO app_reader;

GRANT SELECT, DELETE ON document, report_version, activity, artifact, activity_input, annotation TO security_officer;

-- ---------------------------------------------------------------------------
-- Hash chain (verified-history variant, OFF by default). Adds a tamper-evident
-- chain over annotation: each row hashes its content plus the prior row's hash.
-- Enable only for the package variant that wants it (WS5.4), by adding the column
-- and creating the trigger below. Left uncreated here so the default schema stays
-- simple.
--
--   ALTER TABLE annotation ADD COLUMN prev_hash text, ADD COLUMN row_hash text;
--   CREATE OR REPLACE FUNCTION hash_chain() RETURNS trigger AS $chain$
--   DECLARE last_hash text;
--   BEGIN
--       SELECT row_hash INTO last_hash FROM annotation ORDER BY created_at DESC, annotation_id DESC LIMIT 1;
--       NEW.prev_hash := last_hash;
--       NEW.row_hash := encode(digest(coalesce(last_hash,'') || NEW.annotation_id || NEW.value_json::text, 'sha256'), 'hex');
--       RETURN NEW;
--   END;
--   $chain$ LANGUAGE plpgsql;
--   CREATE TRIGGER annotation_chain BEFORE INSERT ON annotation FOR EACH ROW EXECUTE FUNCTION hash_chain();

COMMIT;