Postgres, bi-temporal where it matters, provenance on everything.

Everything lives in Postgres with pgvector. Not Postgres plus a graph database plus a cache plus a queue - one engine, transactional, with hybrid retrieval good enough at this scale.

That is a deliberate constraint rather than minimalism for its own sake. A single-dependency system is one an enterprise buyer can deploy inside their own boundary as a conversation rather than a procurement project, and it keeps the whole store inside one transaction where it belongs.

A full graph database is judged premature here. Bi-temporal columns plus supersede links cover the need, and the question is revisited if multi-hop reasoning ever becomes a demonstrated bottleneck rather than an anticipated one.

The sections below go through these in detail. This is the index, so nothing in the store is a surprise.

Every tenant table carries `workspace_id`, not nullable, and is isolated by a row-level security policy keyed on it. A row that cannot name its workspace cannot be filtered, and that is a leak waiting for an occasion. Three tables sit outside that shape and are policed differently rather than left open: identity is global to a person and is readable only by the subject it belongs to, device_authorization is a credential in flight and is reachable only by the code being redeemed, and oauth_client is a registration holding nothing about anybody’s project. Only the migration ledger and the two waitlist tables carry no policy at all, because neither holds tenant data.

identity          -- a person, once, across every workspace
workspace         -- the tenant
workspace_member  -- identity × workspace, with role owner|admin|member
actor             -- a person or agent *inside* one workspace
team              -- a group, carrying a function
team_member       -- actor × team, with role lead|member
workspace_invitation -- an outstanding invitation, by email or link

identity is separate from actor, and the separation is load-bearing. An actor is a person within one workspace, which is what keeps asserted_by workspace-local and every provenance foreign key working. Collapsing the two into one global row per human would silently cap a person at one workspace, and unwinding that after real items exist means rewriting the provenance column.

actor_kind distinguishes human from agent, and it is not cosmetic. It is how rehydration decides what to trust and how conflict resolution decides who arbitrates.

Uniqueness on a human actor is (workspace_id, external_ref) rather than external_ref alone, and a constraint enforces that an identified human carries an identity. Looking a person up by external reference alone was a real bug: it silently capped every person at one workspace.

Retention and region are columns on `workspace`, not policies in a document. Region in particular is in the schema from the beginning: keying it after multi-region data already exists is a migration across regions rather than a schema change, and that is not a thing anybody survives cleanly.

Client provenance is stored where the client offers it and left null where it does not. The absence is reported as partial provenance rather than backfilled with a guess: a provenance chain with a hole in it is more useful than one with an invention in it.

The heart of the system. Every column earns its place.

CREATE TABLE context_item (
  id                UUID PRIMARY KEY,
  workspace_id      UUID NOT NULL,
  project_id        UUID NOT NULL,
  kind              item_kind NOT NULL,   -- decision | constraint |
                                          -- open_question | fact | artifact_ref
  title             TEXT NOT NULL,
  body              TEXT,
  status            item_status NOT NULL, -- active | superseded |
                                          -- disputed | retired

  -- provenance: who said this, and on what basis
  asserted_by       UUID NOT NULL REFERENCES actor(id),
  asserted_at       TIMESTAMPTZ NOT NULL,
  source_session_id UUID REFERENCES session(id),
  source_ref        TEXT,                 -- git sha, PR url, permalink

  -- trust and freshness
  confidence        REAL NOT NULL,
  human_confirmed   BOOLEAN NOT NULL,
  load_bearing      BOOLEAN NOT NULL,     -- if wrong, work goes wrong
  last_verified_at  TIMESTAMPTZ,
  decay_after       INTERVAL,             -- null = does not go stale

  -- bi-temporal validity
  valid_from        TIMESTAMPTZ NOT NULL,
  valid_to          TIMESTAMPTZ,          -- null = still valid
  supersedes_id     UUID REFERENCES context_item(id),
  superseded_by_id  UUID REFERENCES context_item(id),
  supersede_reason  TEXT,

  access_scope      access_scope NOT NULL, -- private | project | team |
                                           -- workspace | restricted
  purge_after       TIMESTAMPTZ           -- retention, per item
);

• `load_bearing` decides whether a contradiction blocks or merely logs. Getting it right is most of the product quality.
• `asserted_at` is not `valid_from`. When somebody said a thing and when it was true of the project are different facts, and keeping both is what answers *what did we believe on the third of March*.
• Supersede links point both ways and carry a reason, so a decision’s history reads as a sequence rather than a pile of rows.
• `purge_after` carries retention down to the individual item, so a retention policy is enforced by the store rather than by a promise.

The guaranteed-inclusion pass in rehydration has its own partial index - active, load-bearing, still valid, keyed by workspace and project. Without it the constraint guarantee would be correct but not cheap, and a guarantee that costs latency gets negotiated away.

CREATE TABLE context_item_embedding (
  workspace_id  UUID NOT NULL,
  item_id       UUID NOT NULL,
  model         TEXT NOT NULL,   -- provider-qualified
  dim           INTEGER NOT NULL,
  embedding     VECTOR(1536) NOT NULL,
  PRIMARY KEY (item_id, model)
);

Vectors live in their own table, keyed by item and model. A vector is meaningless without the model that produced it: two vectors from different models in one index give a cosine distance that means nothing, and the result is retrieval that is subtly wrong rather than broken - degradation that reads as bad ranking rather than as a bug.

The composite key is what lets two models be queryable at once during a backfill, which is the exact moment the guarantee is the only thing standing. It also keeps vectors off the rehydration read path by construction, rather than by a filter flag somebody can forget.

The index is HNSW rather than ivfflat. ivfflat trains its centroids when the index is built; built against an empty table it learns nothing and recall stays poor until somebody remembers to reindex. HNSW needs no training data and handles incremental inserts, which is the shape of every write here.

Cost is recorded on the checkpoint because that is where it is incurred - the extraction call is the one real marginal cost in the system, and measuring it anywhere else would be an estimate.

conflict.rationale is not optional metadata. The resolution without the reason is the half that could have been derived from the rows anyway.

CREATE TABLE context_item_grant (
  workspace_id  UUID NOT NULL,
  item_id       UUID NOT NULL,
  grantee_kind  grantee_kind NOT NULL,  -- team | actor
  grantee_id    UUID NOT NULL,
  granted_by    UUID NOT NULL,
  granted_at    TIMESTAMPTZ NOT NULL
);

Four of the five values in access_scope are a hierarchy - private, project, team, workspace - and are answered from the item’s own columns. The fifth, restricted, is answered from this table: an explicit list of named teams or actors, each grant attributed and dated.

A grant table nothing reads is a visibility mode the schema advertises and silently denies. Two places read it: the visibility predicate that filters a query, and the single-item read check. Both live at the query layer, so an item outside your scope is not returned to be filtered afterwards - it is not returned.

Everything secret here is stored as a hash and nothing can be shown again - a leaked row is not a usable credential. The two flows converge deliberately: an OAuth exchange mints an ordinary api_token row, so bearer resolution, revocation, expiry, and the tokens page all keep working unchanged, and there is no second credential system to keep in step with the first. See /docs/oauth.

device_authorization and oauth_client are the two tables here not keyed on a workspace, because at the moment they are written there is no workspace yet. Their policies are keyed on the code being redeemed instead, so a row is reachable only by whoever already holds the secret it is about.

Cost is in micros - millionths of a dollar - because a checkpoint costs single-digit thousandths of a dollar, which cents cannot express, and floating point drifts once a wallet has accumulated thousands of debits.

The usage period is incremented inside the same transaction that records the checkpoint, so it cannot drift from the thing it counts. It is a projection of the event spine, never a second source of truth. /docs/metering has the dials and the allowances.

Cost is recorded on the checkpoint as well as in checkpoint_usage, because the extraction call is the one real marginal cost in the system and measuring it anywhere else would be an estimate. A wallet_ledger debit exists only where the balance actually moved: a debit that took nothing is not a debit, and a check constraint refuses to record one.

These two are not tenant data - a signup precedes any workspace - so they are the only tables besides the migration ledger with no row-level security policy.

The unique constraint, rather than the loop that sends, is what stops a double send: re-running a campaign only reaches whoever it missed. Unsubscribing hard-deletes the address and cascades its send history away with it.

The waitlist is not a newsletter. The published privacy policy commits the address to one use - telling you when access opens - and the confirmation email promises one more email and nothing else. That is a commitment the schema is built to keep, not a preference.

Every write path emits a typed event into telemetry_event, carrying actor, project, session, timestamp, and item ids - never content. The table is partitioned by month from the beginning, because attaching partitioning to a large table later is a rewrite rather than a migration.

Its primary key is (id, occurred_at) rather than id alone, which is what partitioning by occurred_at requires, and the payload is a JSON object with a constraint saying so. Two indexes: one by workspace and event name for metering, one by workspace and project for reading a project’s history.

Coverage is enforced by a test, not by convention. A new write path with no event is a defect even when every other test passes - the record it feeds cannot be reconstructed after the fact.

Audit events live in their own table, audit_event, deliberately. Telemetry is opt-out and redacted; an audit log that can be either is not an audit log.

Its shape is different for the same reason. A row is an actor, an action, a target kind and id, a timestamp, and a metadata object - who did what to which object, and when. Membership changes, scope changes, resolutions, deletions. It is indexed both by workspace and time, for reading the record, and by target, for answering what happened to one object.

Usage accounting is a third thing again - a materialised projection of the event spine, incremented inside the checkpoint transaction so it cannot drift from the thing it counts. It is never a second source of truth. See /docs/metering.

Migrations are numbered, applied in order under an advisory lock, and never edited once applied. The schema they add up to is generated and checked in, so a reviewer sees the resulting shape in the diff instead of replaying every migration file in their head.

The two are verified against each other on every change. A migration that lands without its row-level security policy, or a schema that has drifted from its migrations, fails rather than merging.