Data model & RLS

​

Design rules

1. Single validation boundary: every enum/constraint is defined once in packages/schema (Zod); DB CHECKs are generated from it; services never re-validate.
2. Append-and-supersede: no destructive merge anywhere. memory_events is append-only (INSERT-only grant for the runtime role).
3. Tenant isolation: app-layer scoping is the primary path (user_id as the leading column of every composite index); RLS is defense-in-depth on every user-owned table from day 1.
   • Every user-owned table carries user_id explicitly — including relation tables (memory_edges, facts, commitments, consolidation_proposals, memory_events). No relation table derives its tenant through a join; RLS must be evaluable on the row alone.
   • Cross-tenant references are structurally impossible: composite FKs (user_id, memory_id) so an edge/fact/commitment can only point at memories of the same user_id. An edge linking two tenants’ memories must be unrepresentable, not merely forbidden.
   • Tenant-invariant checklist (applied to every table in the schema PR, and to every future table): owns user_id? · RLS policy present? · composite FKs tenant-qualified? · indexes lead with user_id? · isolation test covers its query paths (including edges and facts, not just memories)?
4. Bi-temporal facts: valid_from/valid_to (world time) + recorded_at (system time).

​

Tables (v1)

​

Identity & access (7)

​

Memory core (7)

​

Operations (3)

​

RLS pattern

-- per user-owned table. NULLIF guard is mandatory: on a pooled connection,
-- current_setting(..., true) returns '' (not NULL) once the GUC has ever been
-- set-and-reset on that backend, and ''::uuid makes the policy THROW.
CREATE POLICY tenant_isolation ON memories
  USING (user_id = NULLIF(current_setting('app.user_id', true), '')::uuid)
  WITH CHECK (user_id = NULLIF(current_setting('app.user_id', true), '')::uuid);

// packages/db — the ONLY exported query entry point
await withTenant(userId, async (tx) => {
  // tx has already run: SET LOCAL app.user_id = $userId
  // all queries here are RLS-scoped AND explicitly WHERE-scoped
});

• SET LOCAL inside a transaction, per request — never bare SET (a pooled connection leaks session state otherwise).
• Raw pool access outside withTenant fails lint (custom Biome/ESLint rule).
• Defense-in-depth: queries still carry explicit WHERE user_id = ...; RLS catches the one you forget.
• Migrations/seeds: unpooled URL only.
• Roles/GRANTs: one idempotent scripts/provision-roles.sql, never per-migration guards.

​

Schema PR definition of done

1. Full column list with types, nullability, defaults
2. Unique constraints — including edge uniqueness: UNIQUE (user_id, from_id, to_id, edge_type) so duplicate edges are unrepresentable
3. Tenant-invariant checklist (above) applied and ticked per table in the PR description
4. FSM transition enforcement for commitments: legal transitions encoded as a CHECK or trigger (open → waiting → resolved | expired), illegal transitions rejected by the DB, not just the service
5. Fact validity constraints: valid_from <= valid_to CHECK; supersession sets valid_to atomically with edge creation (one transaction)
6. Zod → CHECK generation strategy: the codegen path from packages/schema enums to DB CHECK constraints, with a CI assertion that schema and CHECKs cannot drift (the migration round-trip test covers it)
7. Migration lint rules wired (forward-safe checks in CI): no NOT NULL without default, no blocking rewrites, unpooled-URL guard

​

Archived status in GET /api/v1/stats

​

Embeddings