-- event_hash_trigger.sql
--
-- Published at https://intercis.io/evidence/event_hash_trigger.sql.
--
-- This is the database code that writes the hash on every audit row: the five
-- functions and the trigger, copied out of
-- infra/supabase/migrations/040_event_hash_chain.sql and
-- infra/supabase/migrations/054_events_chain_v2.sql on the Intercis main
-- branch. The checker that reads them back is
-- https://intercis.io/evidence/verify_event_chain.py. Read one against the
-- other; the formula in the middle is the same one printed on
-- https://intercis.io/security.
--
-- Left out of this excerpt, because none of it computes a hash: the ALTER
-- TABLE statements that add the two hash columns, the DO block that backfilled
-- rows written before the trigger existed, the SET NOT NULL on both columns,
-- the ALTER TABLE and CHECK constraint 054 adds for the chain_version column,
-- the COMMENT ON statements, 054's post-condition block, and the REVOKE blocks
-- that take EXECUTE on these functions away from PUBLIC, anon and
-- authenticated. Inside the function bodies and the trigger, nothing was
-- changed, added or removed, comments included.
--
-- PROVENANCE. The first three functions are 040 as written. Migration 041 later
-- re-issues 040's CREATE OR REPLACE statements with SET search_path =
-- pg_catalog, public, extensions, so the live definitions of those carry that
-- clause and the ones below do not; their bodies, and therefore the formula,
-- are unchanged. The last two -- events_chain_hash_v2() and the current
-- events_hash_chain_fn() -- are 054 as written, carrying the SET clause inline
-- because that is how 054 writes them. 054 was applied to production on
-- 2026-09-20. Questions: security@intercis.io.
--
--
-- TWO FORMATS, AND WHICH ROW IS IN WHICH
--
--   From 2026-09-20 the trigger stamps chain_version = 2 on every new row and
--   hashes it with events_chain_hash_v2(): every column of public.events except
--   raw_request, the version marker included.
--
--   A row written before that date has chain_version NULL, which is format v1:
--   the ten fields of migration 040 and nothing else about the row. Those rows
--   are NOT re-hashed and never will be. Recomputing hashes over rows already
--   written is the act this chain exists to detect, it would void any chain head
--   already exported, and it would establish that the format may be rewritten in
--   place. So the boundary between the two formats inside one tenant's chain is
--   a permanent, visible fact, and the verifier treats a v1 row that FOLLOWS a
--   v2 row as a break, because a downgrade is how an editor would escape the
--   wider hash.
--
--
-- HASH FORMULA (must stay in lockstep with scripts/verify_event_chain.py)
--
--   Every field is INJECTIVELY encoded before concatenation (security review
--   2026-08-21: naive concatenation let edits that move characters across
--   field boundaries -- e.g. agent_id='otlp-forwarderotlp'/action='.log' vs
--   'otlp-forwarder'/'otlp.log' -- produce identical hashes, and NULL<->''
--   coalesce flips were invisible):
--
--     enc(NULL) = 'N'
--     enc(s)    = length(s) || ':' || s        -- length in characters
--
--   'N' is distinct from enc('') = '0:', so NULL and empty string hash
--   differently, and the length prefix makes the field split unambiguous.
--
--   v2, a row written from 2026-09-20:
--
--     event_hash = hex(sha256(utf8(
--         enc(prev_event_hash)
--      || enc('2')                  -- the version marker, as literal text
--      || the TEN v1 fields, in v1 order and v1 text forms:
--           enc(id) || enc(tenant_id) || enc(agent_id) || enc(action)
--        || enc(target) || enc(verdict) || enc(policy)
--        || enc(injected: true->'t', false->'f') || enc(source)
--        || enc(created_at as 'YYYY-MM-DDTHH:MM:SS.ffffffZ' in UTC)
--      || enc(cloud_provider) || enc(region) || enc(workload_type)
--      || enc(cloud_account_id) || enc(cluster_name)
--      || enc(command_fingerprint)
--      || enc(cc_session_id) || enc(cc_agent_id) || enc(cc_parent_agent_id)
--      || enc(classifier_status) || enc(classifier_reason)
--      || enc(usage_input_tokens) || enc(usage_output_tokens) || enc(model)
--     )))
--
--   v1, a row written before it: the same string with enc('2') and everything
--   after enc(created_at) left out.
--
--   Enum columns are hashed as their label text, the two integer columns as
--   base-10 text with no padding and no '+', and NULL passes through the
--   encoder as 'N' in every one of them -- there is no coalesce to '' anywhere
--   in either body. Genesis is the same in both formats:
--   prev_event_hash of a tenant's first row = hex(sha256(utf8(tenant_id))).
--
--
-- WHAT IS HASHED, BY FORMAT. Every column of public.events as of migration 054:
--
--   column                                  added by   v1        v2
--   id, tenant_id, agent_id, action,
--   target, verdict, policy, injected,
--   source, created_at                      000/002    hashed    hashed
--   chain_version                           054        n/a       hashed as
--                                                                the marker
--   cloud_provider, region, workload_type,
--   cloud_account_id, cluster_name          013        outside   hashed
--   command_fingerprint                     029        outside   hashed
--   cc_session_id, cc_agent_id,
--   cc_parent_agent_id                      032        outside   hashed
--   classifier_status, classifier_reason    042        outside   hashed
--   usage_input_tokens,
--   usage_output_tokens, model              046        outside   hashed
--   raw_request                             000        outside   outside
--
-- raw_request is the deliberate exclusion, in both formats: migration 035 NULLs
-- it at 90 days (nightly pg_cron scrub), and hashing a column the database
-- itself rewrites on a schedule would break every chain the night the first row
-- aged out. Consequence, stated plainly: a forensic payload is not
-- tamper-evidenced by this chain, in either format.
--
-- The fourteen columns marked "outside / hashed" are the ones v1 left out. 013,
-- 029 and 032 predate the chain and were dropped from 040's field list with no
-- recorded reason; 042 and 046 added columns after it and did not fold them in,
-- and 042 states that trade in its own header (ACCEPTED GAP, lines 78-93). The
-- column that mattered most in that set is classifier_reason, because it is
-- where a fail-open is recorded, with classifier_status beside it saying whether
-- the control ran at all. On a v1 row a rewrite of either leaves event_hash
-- valid; on a v2 row it breaks the chain at that row. Every one of those rows
-- already written stays v1, so for them the gap is permanent.
--
-- A COLUMN ADDED BY A LATER MIGRATION IS OUTSIDE v2 UNTIL A v3. The field list
-- in events_chain_hash_v2() is written out by hand, so a migration that adds a
-- column will not fail, complain, or hash it. A hash cannot default-deny: the
-- bytes it digests have to be enumerated, and the SQL and the Python verifier
-- have to enumerate them identically. Closing that for a future column means a
-- v3 -- a new function, a new marker, a new branch in the verifier, and rows
-- stamped 3.
--
-- THE CEILING, UNCHANGED BY v2. There is no anchor outside this database. A
-- writer holding service_role can rewrite a row AND recompute every hash after
-- it in the same tenant, or disable the trigger, and neither this code nor the
-- verifier detects that. What v2 buys is the quiet single-column edit on new
-- rows. It buys nothing for the rows already written under v1. The chain is
-- tamper-evident, not tamper-proof.
--
-- CONCURRENCY / SERIALIZATION
-- Same-tenant inserts are serialized. The plan's head SELECT ... FOR UPDATE
-- is kept, but FOR UPDATE alone is insufficient under READ COMMITTED:
--   (a) a lock-waiting session re-checks only the locked row's WHERE clause
--       after the winner commits -- it does NOT re-run the ORDER BY, so both
--       sessions can read the SAME old head and fork the chain;
--   (b) a tenant's FIRST two concurrent inserts find no row to lock at all
--       and both take the genesis prev (fork).
-- So the trigger first takes pg_advisory_xact_lock keyed on the tenant,
-- which serializes same-tenant inserts for real (verified empirically with
-- two parallel sessions, 2026-08-21); the FOR UPDATE stays as belt-and-braces
-- on the head row itself. Cost note (see DECISIONS): same-tenant inserts are
-- fully serialized -- cross-tenant inserts are unaffected.
--
-- ORDER GUARANTEE
-- Chain order is (created_at, id). created_at defaults to now() = transaction
-- start, so a transaction that started earlier but inserts later could
-- otherwise land "behind" the head it chains to. The trigger bumps
-- NEW.created_at to head.created_at + 1 microsecond in that (rare) case so
-- (created_at, id) order always equals chain order. No writer supplies
-- created_at explicitly today (proxy/db.py insert_event, services/otlp).

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ─────────────────────────────────────────────────────────────────────────
-- Hash helpers (single source of truth for the formula inside the DB)
-- ─────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION public.events_chain_genesis(p_tenant uuid)
RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
  SELECT encode(digest(convert_to(coalesce(p_tenant::text, ''), 'UTF8'), 'sha256'), 'hex')
$$;

-- Injective field encoding: 'N' for NULL (distinct from '' -> '0:'),
-- otherwise character-length || ':' || value. Makes the concatenated hash
-- input unambiguous — no cross-field character shifts, no NULL<->'' flips.
CREATE OR REPLACE FUNCTION public.events_chain_field(p_value text)
RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
  SELECT CASE WHEN p_value IS NULL THEN 'N'
              ELSE length(p_value)::text || ':' || p_value END
$$;

-- The pre-review signature (no injected/source) must not linger as an
-- overload on databases where a draft of this migration was applied.
DROP FUNCTION IF EXISTS public.events_chain_hash(
  text, uuid, uuid, text, text, text, text, text, timestamptz);

-- FORMAT v1, from migration 040. This is the formula for every row written
-- before 2026-09-20, and migration 054 does not touch it: it is not dropped,
-- replaced or re-signed, and every hash already in the table stays byte for
-- byte what it was. It is still the only way to recompute one of those rows.
CREATE OR REPLACE FUNCTION public.events_chain_hash(
  p_prev     text,
  p_id       uuid,
  p_tenant   uuid,
  p_agent    text,
  p_action   text,
  p_target   text,
  p_verdict  text,
  p_policy   text,
  p_injected boolean,
  p_source   text,
  p_created  timestamptz
) RETURNS text
LANGUAGE sql STABLE
AS $$
  SELECT encode(digest(convert_to(
      public.events_chain_field(p_prev)
      || public.events_chain_field(p_id::text)
      || public.events_chain_field(p_tenant::text)
      || public.events_chain_field(p_agent)
      || public.events_chain_field(p_action)
      || public.events_chain_field(p_target)
      || public.events_chain_field(p_verdict)
      || public.events_chain_field(p_policy)
      || public.events_chain_field(
           CASE WHEN p_injected IS NULL THEN NULL
                WHEN p_injected THEN 't' ELSE 'f' END)
      || public.events_chain_field(p_source)
      || public.events_chain_field(
           to_char(p_created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"')),
      'UTF8'), 'sha256'), 'hex')
$$;

-- ─────────────────────────────────────────────────────────────────────────
-- FORMAT v2, from migration 054 — the previous hash, the version marker, the
-- ten v1 fields in v1 order, and the fourteen columns v1 left outside. The
-- order IS the format and may not be reordered or "tidied".
-- ─────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION public.events_chain_hash_v2(
  p_prev                text,
  p_id                  uuid,
  p_tenant              uuid,
  p_agent               text,
  p_action              text,
  p_target              text,
  p_verdict             text,
  p_policy              text,
  p_injected            boolean,
  p_source              text,
  p_created             timestamptz,
  p_cloud_provider      text,
  p_region              text,
  p_workload_type       text,
  p_cloud_account_id    text,
  p_cluster_name        text,
  p_command_fingerprint text,
  p_cc_session_id       text,
  p_cc_agent_id         text,
  p_cc_parent_agent_id  text,
  p_classifier_status   text,
  p_classifier_reason   text,
  p_usage_input_tokens  integer,
  p_usage_output_tokens integer,
  p_model               text
) RETURNS text
LANGUAGE sql STABLE
SET search_path = pg_catalog, public, extensions
AS $$
  SELECT encode(digest(convert_to(
      public.events_chain_field(p_prev)
      || public.events_chain_field('2')
      -- the ten v1 fields, in v1 order, in v1 text forms
      || public.events_chain_field(p_id::text)
      || public.events_chain_field(p_tenant::text)
      || public.events_chain_field(p_agent)
      || public.events_chain_field(p_action)
      || public.events_chain_field(p_target)
      || public.events_chain_field(p_verdict)
      || public.events_chain_field(p_policy)
      || public.events_chain_field(
           CASE WHEN p_injected IS NULL THEN NULL
                WHEN p_injected THEN 't' ELSE 'f' END)
      || public.events_chain_field(p_source)
      || public.events_chain_field(
           to_char(p_created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'))
      -- the fourteen v2 adds
      || public.events_chain_field(p_cloud_provider)
      || public.events_chain_field(p_region)
      || public.events_chain_field(p_workload_type)
      || public.events_chain_field(p_cloud_account_id)
      || public.events_chain_field(p_cluster_name)
      || public.events_chain_field(p_command_fingerprint)
      || public.events_chain_field(p_cc_session_id)
      || public.events_chain_field(p_cc_agent_id)
      || public.events_chain_field(p_cc_parent_agent_id)
      || public.events_chain_field(p_classifier_status)
      || public.events_chain_field(p_classifier_reason)
      || public.events_chain_field(p_usage_input_tokens::text)
      || public.events_chain_field(p_usage_output_tokens::text)
      || public.events_chain_field(p_model),
      'UTF8'), 'sha256'), 'hex')
$$;

-- ─────────────────────────────────────────────────────────────────────────
-- BEFORE INSERT trigger — extends the tenant's chain
--
-- This is the body migration 054 installed, which is 040's body as 041 left it
-- with two changes and nothing else: NEW.chain_version is stamped, and the hash
-- call is the v2 one. The advisory lock, the head SELECT ... FOR UPDATE, the
-- genesis fallback, the created_at bump and the prev-hash assignment are
-- unchanged. The trigger object itself was not dropped and recreated, so there
-- was no window in which events had no chaining trigger.
-- ─────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION public.events_hash_chain_fn()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = pg_catalog, public, extensions
AS $$
DECLARE
  v_head_hash    text;
  v_head_created timestamptz;
BEGIN
  -- Serialize same-tenant inserts. Advisory xact lock is released at
  -- commit/rollback; the later SELECT then runs on a fresh snapshot
  -- (READ COMMITTED + volatile function) and sees the winner's row.
  PERFORM pg_advisory_xact_lock(
    hashtextextended('events_hash_chain:' || coalesce(NEW.tenant_id::text, ''), 0)
  );

  SELECT e.event_hash, e.created_at
    INTO v_head_hash, v_head_created
    FROM public.events e
   WHERE e.tenant_id IS NOT DISTINCT FROM NEW.tenant_id
   ORDER BY e.created_at DESC, e.id DESC
   LIMIT 1
   FOR UPDATE;

  IF v_head_hash IS NULL THEN
    v_head_hash := public.events_chain_genesis(NEW.tenant_id);
  ELSIF NEW.created_at <= v_head_created THEN
    -- Keep (created_at, id) order identical to chain order (see 040's header).
    NEW.created_at := v_head_created + interval '1 microsecond';
  END IF;

  -- Decision 7: the format is the database's to declare, not the client's.
  -- Whatever chain_version the writer supplied is overwritten, exactly as
  -- event_hash and prev_event_hash are.
  NEW.chain_version := 2;

  NEW.prev_event_hash := v_head_hash;
  NEW.event_hash := public.events_chain_hash_v2(
    v_head_hash, NEW.id, NEW.tenant_id, NEW.agent_id, NEW.action,
    NEW.target, NEW.verdict, NEW.policy, NEW.injected, NEW.source,
    NEW.created_at,
    NEW.cloud_provider::text, NEW.region, NEW.workload_type::text,
    NEW.cloud_account_id, NEW.cluster_name, NEW.command_fingerprint,
    NEW.cc_session_id, NEW.cc_agent_id, NEW.cc_parent_agent_id,
    NEW.classifier_status, NEW.classifier_reason,
    NEW.usage_input_tokens, NEW.usage_output_tokens, NEW.model
  );
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS events_hash_chain ON public.events;
CREATE TRIGGER events_hash_chain
  BEFORE INSERT ON public.events
  FOR EACH ROW
  EXECUTE FUNCTION public.events_hash_chain_fn();