The Canonical Model (implementation)

-- Every table is tenant-scoped. tenant_id is present even though each tenant has
-- its own database, because (a) defense-in-depth, (b) it lets RLS policies be uniform,
-- (c) it eases a future move to a pooled model. See security/43.

CREATE TABLE sources (
  id            text PRIMARY KEY,              -- 'ais.spire', 'ofac.sdn'
  tenant_id     uuid NOT NULL,
  family        text NOT NULL,                 -- MARITIME|ENERGY|SANCTIONS|NEWS|...
  connector     text NOT NULL,                 -- connector type/driver
  config        jsonb NOT NULL DEFAULT '{}',   -- non-secret config (secrets live in Secret Manager)
  status        text NOT NULL DEFAULT 'healthy',
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE observations (
  id              bigint GENERATED ALWAYS AS IDENTITY,   -- surfaced as OBS-<id>
  tenant_id       uuid NOT NULL,
  source_id       text NOT NULL REFERENCES sources(id),
  observed_property text NOT NULL,             -- 'vessel_position', 'designation', ...
  observed_at     timestamptz NOT NULL,        -- EVENT time (when it was true)
  ingested_at     timestamptz NOT NULL DEFAULT now(),  -- SYSTEM time (when we learned it)
  entity_ref      text,                        -- resolved entity id (nullable until resolved)
  geom            geography(Point,4326),       -- PostGIS; nullable
  payload         jsonb NOT NULL,              -- normalized fields
  classification  text NOT NULL DEFAULT 'OPEN',
  provenance      jsonb NOT NULL,              -- {signature, signer, source_hash, received_at}
  PRIMARY KEY (id, observed_at)
) PARTITION BY RANGE (observed_at);            -- monthly partitions

CREATE TABLE entities (
  id            text PRIMARY KEY,              -- 'VESSEL-SF12'
  tenant_id     uuid NOT NULL,
  type          text NOT NULL,                 -- vessel|cable|org|person|channel|...
  canonical_name text NOT NULL,
  attrs         jsonb NOT NULL DEFAULT '{}',   -- resolved/merged attributes
  risk          text,                          -- designated|exposed|clear|null
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE entity_aliases (                  -- DETERMINISTIC resolution keys ONLY
  entity_id     text NOT NULL REFERENCES entities(id),
  key_type      text NOT NULL,                 -- 'mmsi','imo','lei','isin', gov ids — UNIQUE keys only
  key_value     text NOT NULL,
  confidence    real NOT NULL DEFAULT 1.0,
  PRIMARY KEY (key_type, key_value)            -- deterministic keys are globally unique → can't fork
);
-- NOTE: `name_norm` and other PROBABILISTIC signals are NOT stored here — a normalized name
-- is not unique (two real entities can share one) and would violate this PK. They live as
-- similarity FEATURES used only in Stage 2 matching (see 21), e.g. a pg_trgm index on
-- entities.canonical_name, never as a unique alias.

CREATE TABLE events (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     uuid NOT NULL,
  entity_id     text REFERENCES entities(id),
  kind          text NOT NULL,                 -- 'ais_gap','throughput_drop','designation'
  occurred_at   timestamptz NOT NULL,
  detected_at   timestamptz NOT NULL DEFAULT now(),
  confidence    real,
  summary       text NOT NULL,
  obs_refs      bigint[] NOT NULL,             -- the observations that fused into this
  attrs         jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE cases (
  id            text PRIMARY KEY,              -- 'CASE-014'
  tenant_id     uuid NOT NULL,
  title         text NOT NULL,
  status        text NOT NULL DEFAULT 'OPEN',  -- OPEN|WATCH|CLOSED
  priority      text NOT NULL DEFAULT 'MEDIUM',
  hypothesis    text,
  owner_id      uuid,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE case_bindings (                   -- events + entities bound to a case
  case_id   text NOT NULL REFERENCES cases(id),
  ref_type  text NOT NULL,                     -- 'event'|'entity'
  ref_id    text NOT NULL,
  PRIMARY KEY (case_id, ref_type, ref_id)
);

CREATE TABLE reports (
  id            text PRIMARY KEY,              -- 'RPT-038'
  tenant_id     uuid NOT NULL,
  title         text NOT NULL,
  status        text NOT NULL DEFAULT 'DRAFT', -- DRAFT|PUBLISHED
  origin        text NOT NULL,                 -- derived-from-case|stand-alone|imported
  derived_from  text REFERENCES cases(id),
  body          jsonb,                         -- structured doc; cites obs/events
  signed_by     uuid, signed_at timestamptz,
  created_at    timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE observations ADD COLUMN supersedes bigint;        -- the obs id this one corrects (nullable)
ALTER TABLE observations ADD COLUMN superseded_by bigint;     -- set on the prior row when corrected
-- AsOf rule: the "current" fact at time T is the latest non-superseded observation
-- whose ingested_at <= T (system time) for the property/entity; superseded rows remain
-- queryable for audit and for reconstructing what we believed at a past T.

{
  "signer": "ingestor:ais.spire@v1.4",
  "source_hash": "sha256:…",        // hash of the RAW payload as received
  "record_hash": "sha256:…",        // hash of the CANONICAL record (see below)
  "received_at": "2026-06-03T14:02:11Z",
  "alg": "ed25519",
  "signature": "…"                  // sign(record_hash) with the per-tenant Ed25519 key
}