# Postgres Schema & Migrations

CoreReflex persists everything in a single **PostgreSQL** database, accessed as **raw SQL through `pg`** — there is no ORM, no query builder, and no schema-as-code. The schema is defined entirely by hand-written, additive SQL files in `migrations/00N_*.sql` (currently `001`–`033`, 34 files — two files share the `014` prefix, see [the duplicate-number hazard](#migration-numbering-and-the-duplicate-014-hazard)), applied once each and recorded in a `schema_migrations` registry. The connection pool and query helpers live in `src/database.js`; the runner lives in `scripts/migrate.mjs`.

This doc is the reference for anyone touching the DB: how the migrator pattern works, how to run migrations (locally and against prod), what each migration adds, the numbering rules, and the conventions every table follows.

> Sibling docs: the route ladder that reads these tables is in [api-reference.md](api-reference.md); the asset/storage layer is in [../ASSET-SERVING.md](../ASSET-SERVING.md); the client-ops modules that own tables `007`–`010` are in [client-ops.md](client-ops.md); billing (`006` + the `011` credit-idempotency index + `026` storage grants) is in [billing.md](billing.md); the chat/support/self-healing tables (`027`) are in [chat-system.md](chat-system.md) and [self-healing.md](self-healing.md); the render worker that polls `render_jobs` is in [render-worker.md](render-worker.md).

**On this page**

- [How persistence works](#how-persistence-works)
- [The migrator pattern](#the-migrator-pattern)
- [Running migrations](#running-migrations)
- [Migration numbering and the duplicate-014 hazard](#migration-numbering-and-the-duplicate-014-hazard)
- [Applying migrations to prod](#applying-migrations-to-prod)
- [Required extensions](#required-extensions)
- [Migration-by-migration](#migration-by-migration)
- [Conventions](#conventions)

---

## How persistence works

All DB access goes through `src/database.js`:

| Export | Purpose |
|--------|---------|
| `getDatabasePool()` | Lazily creates and returns a singleton `pg.Pool` from `DATABASE_URL`. |
| `query(text, params)` | One-shot parameterized query against the pool. The standard call site. |
| `withClient(callback)` | Checks out a dedicated client from the pool and releases it in a `finally`. Use for any work that must run on a single connection. |
| `withTransaction(callback)` | Wraps `withClient` in a real `begin`/`commit` (`rollback` on throw). Use this — **not** `withClient` — whenever you need multi-statement atomicity or a transaction-scoped lock (`pg_advisory_xact_lock`); under bare `withClient` each statement autocommits, so such locks release immediately and guard nothing. |
| `createDatabasePool(connectionString?)` | Builds a `pg.Pool` from any URL (defaults to `DATABASE_URL`). The render worker calls this directly to open its own pool with a smaller `max`. |
| `checkDatabase()` | Health probe — reports `server_version` and whether the `vector` and `pgcrypto` extensions are installed. Surfaced by `GET /api/health`. (It does **not** check `citext`, which `001` also requires.) |
| `closeDatabasePool()` | Tears the pool down (tests, graceful shutdown). |

The pool is configured from the URL: `max` comes from `POSTGRES_POOL_MAX` (default `10`), and TLS is enabled when the URL carries `sslmode=require` **or** `POSTGRES_SSL` is truthy (with `rejectUnauthorized: false`, since the fleet uses a private CA). The `sslmode` query param is stripped from the URL before it reaches `pg`, because `pg` does not understand it — TLS is configured via the `ssl` option instead. This same `sslmode`-stripping logic is duplicated in `scripts/migrate.mjs`.

Queries are written as raw SQL strings with `$1, $2, …` placeholders. **Never interpolate values into SQL** — always bind parameters. (One deliberate, carefully guarded exception: `031`'s `assets.metadata` and every other `jsonb` column receive values only as bound parameters; there is no dynamic SQL anywhere in the app layer.)

---

## The migrator pattern

Migrations are **hand-written, additive, and idempotent**. The conventions are visible in every file under `migrations/`:

- **Additive only.** Migrations `add column if not exists`, `create table if not exists`, and `create index if not exists`. There are no destructive `drop`/`rename` migrations in the current set (the closest thing is `030` dropping a `not null` and swapping a named CHECK constraint — a widening, not a removal). New behavior lands as new columns/tables with safe defaults so the running product is unaffected until code opts in (e.g. `006` adds `subscription_status default 'none'` and `credits default 0`, leaving billing inert; `033` adds a nullable `ui_theme` that code treats as "default preset" when absent).
- **`if not exists` everywhere.** Combined with the `schema_migrations` ledger this makes a partial re-run safe.
- **One transaction per file.** Most files carry an explicit `begin; … commit;`. A handful do not (`021`, `023`–`026`, `033`) — but this is still safe, because the runner sends each file as a **single multi-statement simple query**, and Postgres wraps a multi-statement simple query in an implicit transaction: a mid-file error rolls the whole file back either way, and the file is *not* recorded as applied — you fix it and re-run. Prefer the explicit `begin;/commit;` in new files anyway; it documents intent and survives a future runner change. Corollary: statements that cannot run inside a transaction block (`create index concurrently`, `vacuum`) **cannot be used in a migration file** under this runner.
- **Migrator-owned.** Migrations run as the `corereflex_migrator` role, which **owns** all the tables and therefore can `grant` on them and `alter` them. The runtime app connects as a separate, lower-privilege role (see below).
- **A comment header explaining the why.** Every migration from `023` on opens with a prose block describing the feature, the privacy/tenancy posture, and which routes read/write the tables. Keep doing this — the headers are the closest thing to inline design docs the schema has.

### Two database roles (plus an optional third)

The app and the migrator connect with different credentials and different `sslmode` (visible in `.env.example`):

| Role | Env var | Privileges | Connection |
|------|---------|-----------|------------|
| `corereflex_app` | `DATABASE_URL` | `SELECT/INSERT/UPDATE/DELETE` on all tables (granted explicitly + via default privileges) | pooled via PgBouncer, `sslmode=require` |
| `corereflex_migrator` | `DATABASE_MIGRATION_URL` | owns all tables; can create/alter/grant | direct Postgres, `sslmode=disable` |
| `corereflex_readonly` | `DATABASE_READONLY_URL` | read-only (optional) | pooled, `sslmode=require` |

The migration runner prefers `DATABASE_MIGRATION_URL` and falls back to `DATABASE_URL`. The runtime always uses `DATABASE_URL`. The read-only role is provisioned in `.env.example` but **not yet wired into the app** — `src/database.js` only ever connects with `DATABASE_URL`.

### Migration 003: default-privilege auto-grant (and the bug that motivated it)

There is one important historical wrinkle, documented in the migration files themselves. The original schema-wide grant to `corereflex_app` ran **once at setup**, before migration `002` existed. So when `002` added `app_admins`, `admin_audit_events`, and `impersonation_sessions`, those three tables were never granted to the runtime role. Because `requireUser()` resolved app-admin status with a query against `app_admins`, every authenticated request hit `permission denied for table app_admins` and 500'd.

`migrations/003_grant_runtime_role.sql` fixes this permanently and makes the bug class unrepeatable:

```sql
grant usage on schema public to corereflex_app;
grant select, insert, update, delete on all tables in schema public to corereflex_app;
grant usage, select on all sequences in schema public to corereflex_app;

alter default privileges in schema public
  grant select, insert, update, delete on tables to corereflex_app;
alter default privileges in schema public
  grant usage, select on sequences to corereflex_app;
```

The `alter default privileges` lines mean **any table a future migration creates is auto-granted** to `corereflex_app`. That is why most later migrations carry no grants. Two exceptions grant explicitly anyway, as cheap insurance: `004` (`asset_votes`, with a comment noting that `003`'s default privileges should not be *relied on* if `003` was not recorded as applied) and `031` (`collections` + `collection_assets`, citing the same rule). An ungranted table 500s every request that touches it, which is exactly the `002` failure mode — the defensive grant is free and idempotent, so err on the side of including it.

> Defense-in-depth follow-up (also hardened in `src/session.js`): `requireUser()` now resolves admin status in a `try/catch` (`loadAdminRow`) that treats "permission denied / table missing" as simply "not an app admin," so a future grant gap can never again break sign-in for normal users.

---

## Running migrations

The runner is `scripts/migrate.mjs`, invoked via:

```bash
npm run db:migrate
```

It does the following:

1. Loads `.env` (via `src/env.js`) and picks `DATABASE_MIGRATION_URL || DATABASE_URL`.
2. Ensures the ledger exists:
   ```sql
   create table if not exists schema_migrations (
     id text primary key,
     applied_at timestamptz not null default now()
   );
   ```
3. Reads every `*.sql` file in `migrations/`, **sorted lexicographically** (this is why the `00N_` zero-padded prefix matters for ordering).
4. For each file, checks `schema_migrations` for the id (**the full filename without `.sql`**, e.g. `007_crm` — not just the number). If present, logs `skip`. Otherwise logs `apply`, executes the file's full SQL in one call, then `insert`s the id into `schema_migrations`.

Notes:

- Migrations are tracked by **filename id**, not a hash — editing an already-applied file does **not** re-run it. To change applied schema you write a new migration.
- Because the ledger id is the full filename, **renaming an applied migration file makes the runner see it as brand new** and re-apply it under the new id (harmless only because the files are idempotent), while the old ledger row goes stale. Never rename an applied migration without updating its `schema_migrations` row in the same maintenance window.
- There is a small crash window between "file applied" and "ledger row inserted" (they are two separate queries). If the runner dies in that window, the migration is applied but unrecorded; the next run re-applies it, and the `if not exists` idiom makes that a no-op. This is another reason the idempotency convention is load-bearing, not stylistic.
- The runner applies files in sorted order and **stops at the first failure** — a single un-runnable file blocks every file after it. This is exactly what the `003` gap did to prod (see [Applying migrations to prod](#applying-migrations-to-prod)).

### Connectivity: the fleet lives behind WireGuard

Postgres (direct + PgBouncer) and MinIO run on the fleet **Data box** on a private WireGuard mesh and are **not reachable directly** from a developer Mac. `scripts/db-tunnel.sh` opens an SSH tunnel that maps them to localhost so the `.env` URLs work:

```
55432 -> direct Postgres   (DATABASE_MIGRATION_URL)
56432 -> PgBouncer (TLS)    (DATABASE_URL, pooled)
59000 -> MinIO S3 API       (ASSET_ENDPOINT)
```

Keep the tunnel running while developing or running migrations against the fleet database. (Production migrations run through the same tunnel, against the same direct-Postgres port.)

---

## Migration numbering and the duplicate-014 hazard

The repo contains **two files with the `014` prefix**:

- `014_automation_schedule.sql`
- `014_email_verification.sql`

This happened because two concurrent work tracks each took "next number = 014" without coordinating. Nothing broke at runtime — and understanding *why* tells you what the real invariants are:

- The runner keys the ledger on the **full filename stem**, so `014_automation_schedule` and `014_email_verification` are two distinct ledger ids. Both applied cleanly, on prod and everywhere else.
- Sort order between them is decided by the **alphabetical tiebreak of the suffix** (`automation_schedule` before `email_verification`), not by anyone's intent. These two files touch unrelated tables, so the accidental ordering was harmless — but two same-numbered files that touch the *same* objects would apply in an order nobody chose.

So a duplicate number is not fatal, but it breaks the "prefix = unique ordinal" assumption humans and tooling rely on ("apply migration 014" is now ambiguous), and it is pure luck when the accidental ordering is safe. There is a standing backlog item (`planning/roadmap/CORE.md`, surfaced in `planning/TODO.md`) to rename one of the pair — if you pick that up, remember the rename rule above: update the `schema_migrations` row (`update schema_migrations set id = '<new stem>' where id = '<old stem>'`) in every environment in the same window as the file rename, or the runner will re-apply the file under its new name and leave an orphan ledger row.

### Numbering convention (prevents the collision)

1. **Next number = highest existing prefix + 1.** Check with `ls migrations | sort | tail -3`. As of this writing the last-used number is `033`, so the next migration is `034_*.sql`.
2. **Zero-pad to three digits** (`034`, not `34`) — lexicographic sort is the ordering mechanism.
3. **Grep before you create**: `ls migrations | grep '^034'` must come back empty. If it doesn't, take the next number; never share one.
4. **Concurrent sessions reserve the number by creating the file first.** An autonomous build loop may be running on the same working tree (see `planning/TODO.md`); the file on disk *is* the reservation. If two in-flight branches both need a migration, whoever lands second renumbers **before** merging — renumbering is only free while the file is unapplied everywhere.
5. **One migration per feature slice, descriptive stem** (`029_social_publishing`, not `029_misc`). The stem is the ledger id forever.

---

## Applying migrations to prod

Prod Postgres is only reachable through the SSH tunnel (`bash scripts/db-tunnel.sh`, see above). The routine is:

```bash
bash scripts/db-tunnel.sh        # in one terminal, keep open
npm run db:migrate               # in another — skips applied files, applies the rest
```

### The historical `003` reconcile (resolved — kept as a runbook)

Prod's `schema_migrations` once recorded only `{001, 002, 004}` — **not `003`**. `003_grant_runtime_role.sql` grants on the `002` admin tables, which on the prod cluster required superuser, so it was applied out-of-band by the DB superuser and never recorded in the ledger. Consequence: `npm run db:migrate` (running as `corereflex_migrator`) saw `003` as unapplied, tried to re-run it, and died with `permission denied for table app_admins` — **blocking every later migration** (the runner stops at the first failure).

The fix was to reconcile the ledger by hand, since `003`'s grants were demonstrably in effect:

```sql
insert into schema_migrations (id) values ('003_grant_runtime_role')
on conflict do nothing;
```

**Current status: resolved.** The gap was reconciled and the ledger re-verified clean and contiguous on 2026-06-27 and again on 2026-07-01 (both `014_*` stems present, no stray rows). The routine above now needs no reconcile step. The runbook stays here because the *failure class* recurs any time a migration needs privileges the migrator role lacks: if a future file must be applied out-of-band by a superuser, **record its ledger row manually immediately afterward**, or the runner wedges on it forever.

### Applied vs. pending on prod (as of 2026-07-06)

The live tracker is the ⚠️ `APPLY migration NNN on prod` flags in `planning/TODO.md` — check there before trusting this snapshot.

| Migrations | Prod status | Notes |
|------------|-------------|-------|
| `001`–`026` | **Applied** — ledger verified contiguous 2026-07-01 | Includes both `014_*` files; `023` and `024` verified end-to-end on prod 2026-06-28; `025`+`026` applied 2026-07-01. |
| `027`, `028` | **Applied** — verified 2026-07-03 | `027` also required the chat KB ingest (`kb:chat`), done 2026-07-03. |
| `029` | **Pending** | Social publishing degrades gracefully; activating also needs a workspace GoHighLevel key connected via the dashboard. |
| `030` | **Pending** | In-editor comment routes **500 on the missing columns** until applied — this is the most user-visible pending gap. |
| `031` | **Pending** | Collections/metadata. (The C6 content-credentials feature that shipped alongside has no migration but needs a stable `CREDENTIAL_SIGNING_KEY` in the prod env — see [configuration.md](configuration.md).) |
| `032` | **Pending** (no apply recorded) | Harmless gap by design: the `agent_runs` write is best-effort and never fails agent execution — but prod agent runs leave **no audit rows** until applied. |
| `033` | **Pending** | Workspace theme persistence; code degrades silently (device-local theme only) until applied. |

All five pending files are additive and idempotent; a single tunnel + `npm run db:migrate` run applies them together.

### Degrade-gracefully is the deploy contract

Notice the pattern in that table: every feature whose migration might trail its code deploy is written to **degrade, not crash**, when the schema is missing — `028`'s save hook try/catches the snapshot, `032`'s audit write is best-effort, `033`'s theme read returns the default. `030` is the counter-example (its routes 500 pre-migration) and the reason the rule exists. When you ship a feature + migration pair, assume the code reaches prod **before** the migration does, and make the code tolerate the old schema.

---

## Required extensions

Migration `001` enables three PostgreSQL extensions up front:

| Extension | Used for |
|-----------|----------|
| `vector` (pgvector) | `assets.embedding vector(1536)` + HNSW cosine indexes — semantic recall of upvoted prompts (`001`), workspace knowledge (`013`), and the global chat KB (`027`). |
| `pgcrypto` | `gen_random_uuid()` — the default for every UUID primary key. |
| `citext` | case-insensitive `email` columns on `app_users` and `app_admins`. |

`checkDatabase()` asserts that `vector` and `pgcrypto` are present, and `GET /api/health` reports them. (`citext` is not health-checked — a fresh cluster missing it fails at migration time instead.)

---

## Migration-by-migration

Each migration is a coherent slice of the product. Tables are listed with their purpose; consult the file for exact columns, checks, and indexes.

### 001 — `001_core_platform.sql` · Core platform

The foundation: identity, the tenant boundary, assets, and the campaign → composition → render pipeline. Enables the three extensions, then creates:

| Table | Purpose |
|-------|---------|
| `accounts` | Billing/owner container for one or more workspaces. (`plan`, Stripe link, `credits` are added in `004`/`006`; `storage_limit_bytes` in `025`.) |
| `app_users` | The auth principal. `email citext unique`, `password_hash`, `status` in `active`/`disabled`. (`email_verified_at` added by `014_email_verification`.) |
| `workspaces` | **The multi-tenant boundary.** `account_id` + globally-`unique slug`. Everything downstream is scoped to a workspace. (`brand_id` added in `025`; `ui_theme` in `033`.) |
| `memberships` | User ↔ workspace join with `role` in `owner`/`producer`/`reviewer`/`client`; `unique (workspace_id, user_id)`. |
| `auth_sessions` | Live JWT sessions: `token_hash unique`, `expires_at`, `revoked_at`. A session is live when `revoked_at is null and expires_at > now()`. |
| `brand_kits` | Per-workspace visual identity — `colors`, `typography`, `voice`, `prohibited_terms` (all `jsonb`). Superseded-but-still-supported once a workspace links an account `brands` row (`025`). |
| `assets` | The object-storage record: `bucket` + `object_key` (`unique (bucket, object_key)`), `content_type`, `byte_size`, `provenance jsonb`, and `embedding vector(1536)`. (`visibility`, `created_by`, `vote_score/count` added in `004`; `metadata` in `031`.) |
| `campaigns` | Project container: `goal`, `brief jsonb`, `status` in `draft`/`review`/`approved`/`rendering`/`launched`, optional `brand_kit_id`. |
| `qa_responses` | Brand-QA answers, `unique (campaign_id, field)`. |
| `storyboards` | Scene sequencing: `scenes jsonb`, `duration_seconds`. |
| `compositions` | The Remotion manifest: `hyperframes_html` + `manifest jsonb` (the editor IR), optional rendered `asset_id`. (`updated_at` added in `028` — before that, `created_at` was the only audit signal.) |
| `render_jobs` | Render output queue: `provider_route`, `status` in `queued`/`running`/`succeeded`/`failed`, `cost_estimate_cents`, `output_asset_id`, `error jsonb`. The **render worker polls this table** (retry columns added in `017`; prod worker ops in [auto-clips-infra.md](auto-clips-infra.md)). |
| `qa_reports` | Quality audit: `total_score` (0–100, checked), `status` in `ready`/`review`/`blocked`, `checks`/`blockers jsonb`. |
| `rollout_plans` | Launch variants (`variants jsonb`). |
| `review_events` | Append-only approval/comment trail per campaign. |

Indexes worth knowing: `assets_embedding_hnsw_idx` (HNSW, `vector_cosine_ops`) powers prompt-similarity recall; `auth_sessions_user_active_idx` is partial (`where revoked_at is null`); `campaigns_workspace_status_idx` and `assets_workspace_created_idx` back the hot workspace-scoped reads.

### 002 — `002_app_admins.sql` · Admin / God Mode

The app-wide admin model, separate from workspace membership.

| Table | Purpose |
|-------|---------|
| `app_admins` | App-level admins. `email citext unique`, `level` in `owner`/`admin`/`support`/`finance`/`ops`, `privileges jsonb` (e.g. `god_mode`, `manage_admins`, `impersonate_users`, `view_metrics`). |
| `admin_audit_events` | **Append-only** forensic log of admin actions (actor, target, `event_type`, `metadata`). |
| `impersonation_sessions` | Tracks support impersonation: which admin impersonated which user, the `impersonated_session_id`, `reason`, `ended_at`. Impersonation is **not invisible** — the impersonated user can see who is impersonating them. |

This migration also **seeds the owner admin** via an `insert … on conflict (email) do update`, granting the full privilege set. As noted above, `002` is the migration that exposed the grant gap fixed by `003`.

### 003 — `003_grant_runtime_role.sql` · Runtime-role grant fix

No tables. Re-grants `corereflex_app` across the whole schema and sets `alter default privileges` so all future tables auto-grant. See [the migrator-pattern section](#migration-003-default-privilege-auto-grant-and-the-bug-that-motivated-it) for the rationale and [Applying migrations to prod](#applying-migrations-to-prod) for the ledger gap it once caused there.

### 004 — `004_votes_visibility_plan.sql` · Plans, visibility, votes

Adds the community + rights layer with both `alter table … add column` and a new table.

Column additions:

- `accounts.plan` — `free`/`pro`/`enterprise`, default `free`.
- `assets.visibility` — `public`/`private`, default `public` (preserves the existing showcase).
- `assets.created_by` — per-user attribution (FK to `app_users`).
- `assets.vote_score` / `assets.vote_count` — **denormalized** tally maintained by a trigger.

New table:

| Table | Purpose |
|-------|---------|
| `asset_votes` | One de-duped ballot per `(asset_id, user_id)` (`unique` constraint), `value smallint in (-1, 1)`. Re-voting UPSERTs (flips); retracting deletes. |

A trigger function `apply_asset_vote()` (fired `after insert or update or delete on asset_votes`) keeps `assets.vote_score`/`vote_count` authoritative. **Never `UPDATE assets(vote_score)` directly** — write to `asset_votes` and let the trigger maintain the tally, or it will desync. The partial index `assets_gallery_rank_idx` (`where visibility = 'public'`) backs the gallery's score-then-recency ranking.

This migration grants `asset_votes` to `corereflex_app` explicitly (see the `003` note).

### 005 — `005_personas.sql` · Personas + Script Studio (backend)

> **Coming soon (UI).** The persona-capture Studio UI and the Script Studio UI are **not yet shipped**. The schema and backend exist; treat the product features ("Clone a persona", "Write a script") as roadmap.

| Table | Purpose |
|-------|---------|
| `personas` | A consented, named likeness+voice identity owned by a workspace. `status` in `draft`/`building`/`ready`/`disabled`; **`consent_status` in `pending`/`granted`/`revoked` is the render gate** (rendering is forbidden unless `granted`); `consent_actor`/`consent_at`/`consent_record` for attestation; `is_self`; `visibility` in `private`/`workspace`; `voice jsonb`; `primary_reference_id`. |
| `reference_media` | Source capture/import media for a persona — `kind` in `photo`/`video`/`generated_still`, `source` (`upload`/`gdrive`/`onedrive`/`dropbox`/`url`/`imagen`), `role` (`reference`/`primary`/`consent_proof`), optional `asset_id`. |
| `script_projects` | Native Script Studio projects: `lines jsonb` (ordered `{id, text, voiceId?, sceneHint?}`), `status` in `draft`/`ready`/`rendering`/`rendered`, optional `persona_id`/`campaign_id`. |

Note: persona-rendered talking-head clips do **not** get their own table — they land in `assets` with `provenance.kind='persona_clip'` (not `'generation'`), so they never leak into the public showcase feed (which filters on `kind='generation'`). The consent gate is enforced **in code**, not by a DB constraint.

### 006 — `006_billing.sql` · Stripe + credit ledger

> See [billing.md](billing.md). Inert by default: nothing meters until `BILLING_ENABLED` + Stripe keys are set.

Column additions to `accounts`: `stripe_customer_id` (with a partial unique index where non-null), `subscription_status` in `none`/`trialing`/`active`/`past_due`/`canceled` (default `none`), `subscription_period_end`, and `credits` (default `0`).

| Table | Purpose |
|-------|---------|
| `credit_ledger` | **Append-only** audit log of credit movements: `delta` (+grant/purchase, −usage), `reason` (`grant`/`purchase`/`usage:veo`/…), `balance_after`, `meta jsonb`. |

The **current balance lives on `accounts.credits`**; the ledger is the audit trail. `balance_after` is denormalized and is kept in sync by application code (no trigger). To repair, log a ledger entry rather than `UPDATE accounts(credits)` directly. Grant/purchase idempotency is enforced by the partial unique index added in [`011`](#011--011_billing_credit_idempotencysql--credit-grant-idempotency-security-hardening).

### 007 — `007_crm.sql` · CRM spine (client-ops phase 1)

> Pro-gated and off by default. See [client-ops.md](client-ops.md).

| Table | Purpose |
|-------|---------|
| `crm_contacts` | Person/company records. `status` in `lead`/`prospect`/`active`/`inactive`, `owner_id`, `cltv_cents`, `source`, `last_activity_at`, `meta jsonb`. **Email is unique per workspace, case-insensitive**: `unique index (workspace_id, lower(email)) where email is not null`. |
| `crm_activity` | **Append-only** timeline. `type` (`note`/`meeting_brief`/`task`/`booking`/`review`/…), `body`, and a `ref_table`/`ref_id` back-reference to the related record. |
| `crm_tags` | Workspace label taxonomy, `unique (workspace_id, name)`. |
| `crm_contact_tags` | Tag attachment, composite PK `(contact_id, tag_id)`. |
| `meeting_briefs` | Persisted AI briefs (previously discarded): `summary`, `action_items`/`decisions`/`chapters`/`followups jsonb`, `trace_id`, optional `contact_id`/`campaign_id`/`asset_key`. |

GDPR deletes are **hard deletes** (no `deleted_at`) — contact deletion cascades through activity/tags and nulls brief references; the explicit cascade is the design (see [Conventions](#conventions)).

### 008 — `008_tasks.sql` · Tasks / approvals (client-ops phase 2)

| Table | Purpose |
|-------|---------|
| `tasks` | Accountability items. `status` in `open`/`doing`/`done`/`cancelled`, `assignee_id`, optional `contact_id`/`campaign_id`, `due_at`, `source` (`manual`/`meeting_brief`/`rollout`/…). Seeded from meeting-brief action items and rollout owners. |

Indexes back the common reads: `(workspace_id, status, due_at nulls last)`, `(assignee_id, status)`, `(contact_id)`.

### 009 — `009_booking.sql` · Native scheduling (client-ops phase 3)

White-label, Calendly-style booking on the agency's own domain. Slot intersection and double-booking prevention live in `src/booking.js` (advisory locks — which is why `withTransaction` exists; see [How persistence works](#how-persistence-works)).

| Table | Purpose |
|-------|---------|
| `booking_calendars` | A bookable meeting type: `duration_min` (5–480), `buffer_before/after_min`, `timezone` (IANA), `location_type` (`video`/`phone`/`in_person`/`custom`), `max_days_out`, `min_notice_min`, `default_assignee_id`, `is_active`. **`slug` is globally unique** (`booking_calendars_slug_idx`) so public `/book/:slug` routing works across tenants — `unique (workspace_id, slug)` is *also* present. |
| `member_availability` | Per-user working hours: `weekday` (0–6, 0 = Sunday), `start_min`/`end_min` (0–1440). The free/busy source of truth. |
| `calendar_events` | Busy time: `starts_at`/`ends_at`, `status` in `pending`/`confirmed`/`cancelled`, `assignee_id`, optional `contact_id`/`campaign_id`. |
| `bookings` | The public booker's confirmed booking: `booker_name`/`booker_email`/`booker_phone`, `starts_at`/`ends_at`, `status` in `confirmed`/`cancelled`/`completed`/`no_show`, `public_token` (used for the `.ics` download). |

### 010 — `010_automation.sql` · Automation engine (client-ops phase 4)

Wires domain events CoreReflex already emits to native actions. **Direct-call only** — there are no time-delay / wait-for-event nodes yet (no durable job runner), which is a deliberate deferral to avoid silently dropping events.

| Table | Purpose |
|-------|---------|
| `automations` | A rule: `trigger_type` + `trigger_config jsonb`, a `graph jsonb` (`{"nodes":[],"edges":[]}` — same structured-JSON approach as `compositions.manifest`), `status` in `draft`/`active`/`paused`, `total_runs` counter. (`next_run_at` added by `014_automation_schedule`.) |
| `automation_runs` | Execution log: `status` in `running`/`completed`/`failed`, `trigger_data`/`steps jsonb`, `error`, `started_at`/`completed_at`. Recorded but never auto-pruned. |

### 011 — `011_billing_credit_idempotency.sql` · Credit-grant idempotency (security hardening)

> Part of the 2026-06-23 security pass. No tables — one index.

`grantCredits()` originally deduped credit grants with a `SELECT`-then-`INSERT`, which a **retried Stripe webhook could race past and double-credit** an account. This migration makes the dedupe a hard DB guarantee:

```sql
create unique index if not exists credit_ledger_grant_idem_idx
  on credit_ledger (account_id, reason) where delta > 0;
```

The index is **partial on `delta > 0`**, so it only constrains grants/purchases (positive deltas). Usage debits (`delta < 0`) are excluded, so the same `usage:*` reason can still be metered many times. With the index in place, `grantCredits(..., { dedupe: true })` switches to an `insert … on conflict (account_id, reason) where delta > 0 do nothing returning id` claim: a duplicate grant is a no-op even under a concurrent webhook retry, and the balance is only bumped when the ledger row was actually new. If the index is not yet applied (Postgres `42P10`), the code falls back to the older best-effort `SELECT` dedupe so an enabled-billing webhook never 500s.

### 012 — `012_webhook_idempotency.sql` · Stripe event idempotency

| Table | Purpose |
|-------|---------|
| `processed_webhook_events` | One row per processed Stripe event id. Prevents retried webhooks from re-firing downstream automations even when a credit grant was already deduped. |

### 013 — `013_knowledge.sql` · Workspace knowledge base

| Table | Purpose |
|-------|---------|
| `knowledge_docs` | Workspace-scoped RAG chunks for voice agents and grounded surfaces. Stores `title`, `chunk_index`, `content`, optional `embedding vector(1536)`, `source`, and `created_by`. HNSW cosine index backs semantic search. |

### 014 — `014_automation_schedule.sql` · Scheduled automation clock

Adds `automations.next_run_at` plus a partial due-work index for active `schedule` triggers. The schedule expression itself lives in `automations.trigger_config` (`everyMinutes` or `dailyAtUtcHour`/`dailyAtUtcMinute`).

### 014 — `014_email_verification.sql` · Email verification gates

Adds `app_users.email_verified_at` and the `email_verification_tokens` table (`token_hash`, `expires_at`, `used_at`) so users can sign in before verification while sensitive actions stay gated until the email is trusted.

> These two files sharing a prefix is the collision documented in [Migration numbering](#migration-numbering-and-the-duplicate-014-hazard). Both are applied everywhere under their full-filename ledger ids.

### 015 — `015_generation_jobs.sql` · Durable generation jobs

| Table | Purpose |
|-------|---------|
| `generation_jobs` | Durable status/result/error store for `/api/generate`. Rows are workspace-scoped and keep `status` (`queued`/`running`/`done`/`error`), `provider`, `vendor`, `private`, `spec`, `qa`, `result`, `error`, `fallback_from`, and timestamps. The app process still runs active jobs; polling can recover persisted terminal state after a restart. |

### 016 — `016_recipes.sql` · Programmable recipes (Workflow mode)

| Table | Purpose |
|-------|---------|
| `recipes` | Saved, versioned recipe **specs** (staged pipelines: `stages[]` with `requires`/`produces`/`successCriteria`). Workspace-scoped; the source of truth a run executes from. |
| `recipe_runs` | The run ledger — one row per execution, with `status`, a budget ledger (reserve/reconcile/refund), the resolved spec, per-stage results, and timestamps. The `status` CHECK gains `awaiting_human` in `019`. |

### 017 — `017_render_job_retry.sql` · Render retry / DLQ

Adds `attempts` and `locked_at` to `render_jobs` so a failed render can be retried with backoff and a stuck job can be reclaimed (dead-letter on exhaustion). Indexes back the claim query. Worker-side behavior in [render-worker.md](render-worker.md).

### 018 — `018_api_keys.sql` · Platform API keys

| Table | Purpose |
|-------|---------|
| `api_keys` | Hashed platform API keys (`cr_live_` prefix; only the hash is stored), workspace-scoped, with label, last-used, and revoke. Backs `requireApiUser` for the public Platform API / SDK / MCP — see [platform-api.md](platform-api.md). |

### 019 — `019_workflow_checkpoints.sql` · Staged-workflow checkpoints

| Table | Purpose |
|-------|---------|
| `recipe_run_stages` | Per-stage checkpoint rows for a staged run — `status`, completed artifacts, and a send-back `note`. Lets a run pause `awaiting_human` and resume on approve / send-back. |

Also widens the `recipe_runs` status CHECK to allow `awaiting_human` and defaults `checkpoint_policy` to `guided`.

### 020 — `020_content.sql` · Write pillar

| Table | Purpose |
|-------|---------|
| `content_documents` | Saved Write documents — `title`, a `body` jsonb (`{blocks:[{type,text}]}`), `template_id`, `status`, workspace-scoped. |
| `content_templates` | User-authored content templates (the `{{slot}}` library) alongside the bundled golden templates. |

### 021 — `021_brand_kit_unique_workspace.sql` · One brand kit per workspace

Dedupes any duplicate `brand_kits` rows per workspace (keeping the most recent), then adds the `brand_kits_workspace_uniq` unique index. Backs the atomic `INSERT … ON CONFLICT (workspace_id)` upsert in `saveWorkspaceBrandKit` (closes a concurrent-PUT race).

### 022 — `022_proofing.sql` · Proofing & approvals

| Table | Purpose |
|-------|---------|
| `proofs` | A deliverable shared for sign-off — `title`, `status` (`draft`/`in_review`/`approved`/`changes_requested`), workspace-scoped. |
| `proof_versions` | Ordered rounds; a CHECK enforces exactly one of `asset_id` / `document_id` / `external_url`. `unique (proof_id, version_no)`. |
| `proof_reviews` | Tokenized share links (`unique` token) — the unguessable grant a client reviews with, no login. |
| `proof_comments` | Comments from client (via a review) or team; `resolved_at` for triage. **Heavily extended by `030`** to double as the in-editor comment store. |
| `proof_decisions` | The audit trail of approve / request-changes decisions on a version. |

### 023 — `023_analytics_events.sql` · First-party analytics events

A privacy-first, own-stack event stream for product + marketing attribution (pageviews + key conversion events) — no third-party trackers, no CSP changes.

| Table | Purpose |
|-------|---------|
| `analytics_events` | One row per event: `event_name`, `path`, `referrer`, `session_id`, `props jsonb`, `user_agent`, `created_at`. **No PII**: the client IP is stored only as a short salted hash (`ip_hash` — rough uniqueness / abuse signal). `workspace_id` and `account_id` are **nullable** with `on delete set null`, because anonymous marketing-site traffic has no tenant — this is a deliberate exception to the workspace-scoping convention. |

Indexes: `(event_name, created_at desc)`, `(path, created_at desc)`, `(workspace_id, created_at desc)`, `(session_id)`. Written by `POST /api/analytics/event` (public, rate-limited); read by `GET /api/analytics/summary` (authed; the global view is app-admin only). Logic in `src/analytics.js`. Append-only, never pruned by the app — expect this to become the largest table by row count.

### 024 — `024_leads.sql` · Marketing lead capture

| Table | Purpose |
|-------|---------|
| `leads` | First-party email capture from the public site (interested visitors who have no account). `email`, `source`, `path`, salted `ip_hash` — nothing else. **No workspace column at all** (anonymous marketing). Deduped case-insensitively by the global unique index `leads_email_uniq on (lower(email))`. |

Written by `POST /api/leads` (public, rate-limited); read by `GET /api/leads` (admin-only).

### 025 — `025_brands.sql` · Brand cascade (account-owned brand layer)

The flagship Account → Brand → Project → service brand hierarchy. A Brand is a **living container of individually approved assets**, not a one-shot form: each logo / palette / voice / tagline / photo is its own row with its own draft → approved lifecycle, and the brand's *resolved kit* is exactly its **approved** assets. Pro+ gated in code.

| Table | Purpose |
|-------|---------|
| `brands` | Account-owned (note: **`account_id`, not `workspace_id`** — brands sit above the workspace boundary). `name`, `status` in `active`/`archived`. |
| `brand_assets` | One row per brand asset: `kind` in `logo`/`palette`/`typography`/`voice`/`tagline`/`guide`/`photo`/`icon`/`pattern`/`other`; `status` in `draft`/`approved`/`archived`; `label`, `data jsonb`, optional `asset_id` (`set null`), `sort_order`, `provenance jsonb`, `approved_at`/`approved_by`. |

Column additions:

- `workspaces.brand_id` (nullable, `on delete set null`) — workspaces become **Projects** that inherit an account brand. Unlinked workspaces keep using their legacy per-workspace `brand_kits` row; `getWorkspaceBrandKit` resolves through `brand_id` first and falls back. Nothing existing breaks until a workspace opts in.
- `accounts.storage_limit_bytes` (nullable `bigint`) — the per-account storage budget. Usage = sum of the account's `assets.byte_size`; `NULL` means "use the plan default in code"; add-on SKUs (`026`) raise the effective limit.

The partial index `brand_assets_approved` (`where status = 'approved'`) makes resolving the approved kit fast; `brand_assets_brand (brand_id, kind, sort_order)` backs the grouped browse.

### 026 — `026_storage_grants.sql` · Storage add-on grants

| Table | Purpose |
|-------|---------|
| `storage_grants` | Purchasable storage bundles that raise an account's effective storage limit on **any** plan: `account_id` (cascade), `bytes bigint`, `reason`. |

`reason` (the Stripe session id) carries a **global unique index** (`storage_grants_reason_uniq`), so a retried webhook cannot double-grant — the same idempotency pattern as `011`'s credit-grant index, expressed as a whole-column unique instead of a partial. Effective limit (computed in `billing.js`) = `coalesce(accounts.storage_limit_bytes, plan default) + sum(storage_grants.bytes)`. See [billing.md](billing.md).

### 027 — `027_chat_selfheal.sql` · Chat infrastructure + self-healing (7 tables)

The front-of-house pre-sales/Q&A bot, the back-of-house support + live-agent surface, and the bug → ticket → AI-fix-brief loop, shipped as one migration so later increments only *use* the tables. Full system docs: [chat-system.md](chat-system.md) and [self-healing.md](self-healing.md).

| Table | Purpose |
|-------|---------|
| `chat_kb` | The **global** bot RAG corpus (blog/docs/faq/pricing/case-studies/testimonials) — deliberately **not workspace-scoped**, because the pre-sales bot answers anonymous visitors. Mirrors `knowledge_docs`' embedding shape (`vector(1536)`, HNSW cosine index) and adds the schema's first **full-text search column**: `fts tsvector generated always as (to_tsvector('english', title ‖ heading ‖ content)) stored` with a GIN index — retrieval is hybrid (semantic + lexical), so it stays useful even when embeddings are momentarily unavailable. `source_path` is the stable re-ingest key (re-ingest deletes by it); `is_active` soft-toggles chunks. |
| `chat_conversations` | One row per conversation. `user_id` is **null for anonymous visitors** (keyed by `session_id` instead). `context_type` `marketing`/`dashboard`; `mode` drives the AI ↔ human handoff state machine (`ai`/`human_requested`/`human_active`); `status` `active`/`archived` (hard-delete app — a purge is a real `DELETE`, no tombstone); `category`, `priority` (`low`/`normal`/`high`/`urgent`), `assigned_admin_id`, visitor contact fields, handoff timestamps, `message_count`/`total_tokens` counters. |
| `chat_messages` | `role` in `user`/`assistant`/`system` plus a finer `sender_type` (`user`/`assistant`/`admin`/`system` — a human agent's message is `role='assistant', sender_type='admin'`); `sources jsonb` (the RAG citations), `feedback` (`helpful`/`not_helpful`), `tokens_used`, `admin_id`. Cascade-deletes with the conversation. |
| `chat_settings` | Admin-tunable KV (`key` PK, `value jsonb`); defaults live in `src/chat-config.js`. |
| `support_tickets` | A ticket may be **born from a chat conversation** (`source_chat_conversation_id` is `unique` — at most one ticket per conversation) or opened directly. `status` `open`/`in_progress`/`resolved`/`closed`; `priority`; `category`; `contact_email`. |
| `support_messages` | Ticket thread: `message`, `is_staff`, cascade with the ticket. |
| `self_heal_briefs` | Every bug ticket also mints a structured AI fix-brief (`brief_markdown`, `suspected_areas jsonb`). `status` tracks the loop: `queued` → `in_progress` → `pr_open` → `done`/`dismissed`; `pr_url` links the fix PR. The scheduled runner materializes queued briefs to `self-healing/inbox/*.md`, fixes, and opens a PR — human-in-the-loop throughout. |

### 028 — `028_composition_versions.sql` · Composition version history

Every editor save snapshots the manifest into an ordered, per-composition version list; restore copies a version's manifest back **after snapshotting the pre-restore state first**, so nothing is ever lost.

| Table | Purpose |
|-------|---------|
| `composition_versions` | `composition_id` (cascade) + `version_no` (`unique (composition_id, version_no)`), optional `label` (e.g. "Before restore"), `manifest_sha` (sha256 of the manifest JSON — lets the save hook **skip a snapshot when nothing changed**), the full `manifest jsonb`, `created_by` (`set null`). Index `(composition_id, version_no desc)` backs the history panel. |

Also adds `compositions.updated_at` — before this, the table had **no save timestamp at all**; `created_at` was the only audit signal. The app prunes each composition's history to the most recent N (currently 20). The save hook is deliberately **best-effort** (`try/catch`): a missing table never fails a save, which is what let the code deploy land before the prod migration. Routes: `GET /api/compositions/:id/versions`, `POST /api/compositions/:id/versions/:n/restore`.

### 029 — `029_social_publishing.sql` · Social publishing relay

Rather than months of per-platform OAuth app reviews, a workspace connects its GoHighLevel account once (GHL's social-media-posting API fronts Facebook / Instagram / X / LinkedIn / GMB / TikTok) and CoreReflex relays posts through it. Client code: `src/ghl.js` (fixed host — no SSRF surface), `src/social.js`, `src/crypto-box.js`.

| Table | Purpose |
|-------|---------|
| `social_connections` | The workspace's GHL link: `provider` (default `'ghl'`), `location_id`, `label`, and `api_key_enc` — the API key **encrypted at rest** (AES-256-GCM, packed `v1$salt$iv$tag$ct`, via `src/crypto-box.js`; **never returned to a client**, and never stored plaintext). `unique (workspace_id, provider)` — one live connection per workspace; reconnect updates in place. |
| `social_posts` | The relay ledger — **every publish attempt writes a row, including failures**, so a dropped relay is auditable. `connection_id`/`composition_id` (`set null`), `asset_key` (the published render/asset; nullable for ad-hoc posts), `summary`, `account_ids jsonb`, `media_url`, `post_type`, `status` (plain text, mirrors GHL's own states: `scheduled`/`published`/`failed`/`draft` — **no CHECK**, a deliberate deviation since the value set is GHL's, not ours), `schedule_date`, `external_post_id`, `error`, and `stats jsonb` (reserved for the follow-on analytics pull-back). |

Index `(workspace_id, created_at desc)` backs the recent-posts list. Publishing meters `usage:publish` credits **before** egress.

### 030 — `030_editor_comments.sql` · In-editor comments (extends `proof_comments`)

No new table — this **extends `022`'s `proof_comments`** so one table serves both external client review (proof-anchored) and team comments inside the editor (composition-anchored). One table on purpose: later review upgrades unify both surfaces. Column additions:

| Column | Purpose |
|--------|---------|
| `composition_id` | The editor anchor (FK to `compositions`, cascade). |
| `timecode_ms` | Playhead anchor — **milliseconds, never frames** (fps is render-switchable, so a frame number is not stable). Bounded to int4 by server-side sanitizers. |
| `element_id` | A manifest item id (`text`, **no FK** — the manifest is `jsonb`). Consumers must render a "detached" state when the id no longer resolves. |
| `region` | Normalized `{x,y,w,h}` on the canvas (values clamped 0–1 server-side) — draw-on-frame annotations. |
| `parent_id` | Self-FK (cascade) — threads replies. |
| `author_user_id` | Joins the real user for team comments (`set null`); proofing's `author_name` stays for external reviewers. |
| `mentions` | `jsonb` array of @mentioned user ids. |

Structural change: `proof_id` **drops its `not null`**, replaced by the named constraint `proof_comments_anchor_check` — `check (proof_id is not null or composition_id is not null)`, i.e. a comment belongs to a proof OR a composition (at least one). **Safety invariant that makes this sound**: every existing proofing query filters by `proof_id`, so editor comments (`proof_id is null`) can never leak into a tokenized `/proof/:token` view. Index `(composition_id, created_at desc)` backs the editor panel. Server logic in `src/comments.js` (workspace ownership resolved via the compositions → campaigns join; live updates on a `comp:` SSE channel).

> Deploy note: unlike most feature migrations, the comment routes **500 on the missing columns** until `030` is applied — see [Applied vs. pending on prod](#applied-vs-pending-on-prod-as-of-2026-07-06).

### 031 — `031_collections_metadata.sql` · DAM collections + asset metadata

Named, workspace-scoped folders over the assets table, plus custom metadata. Server logic in `src/collections.js`.

| Table | Purpose |
|-------|---------|
| `collections` | `name` per workspace, `unique (workspace_id, name)`, `created_by` (`set null`). |
| `collection_assets` | A **pure membership join** — composite PK `(collection_id, asset_id)`, `added_at`, and deliberately **no `byte_size`**: the storage meter sums `assets.byte_size` directly, so collection membership must never double-count storage. Both FKs cascade. |

Column addition: `assets.metadata jsonb` (default `'{}'`) — custom fields, kept **separate from `provenance`** so the many `provenance->>'kind'` filters stay clean.

Two boundaries live in code, not the schema: cross-tenant attach is blocked (`addToCollection` filters asset ids to the caller's workspace before insert — a crafted foreign id is silently dropped), and search inside a collection threads the `collectionId` into the semantic search path so it cannot leak workspace-wide matches. This migration also carries explicit defensive grants on the two new tables (the `004` rule).

### 032 — `032_agent_runs.sql` · Agent run ledger

Every suite-wide agent session (the conversational agent in `src/agent.js`) is captured as an auditable, resumable row — "what did it do and what did it cost?" — and is the substrate a future resume/replan step reads from. Write path: `src/agent-runs.js` (`start`/`finish`/`fail`).

| Table | Purpose |
|-------|---------|
| `agent_runs` | `workspace_id` (cascade); `composition_id` (`set null` — **null means an inline, stateless run** that never touched a stored composition); `created_by`; `message` (the user's natural-language request); `plan jsonb` (the full validated AgentPlan `{steps:[…]}` from `/plan`); `steps jsonb` (per-step execution record `[{id, tool, class, status, result?, note?}]` — the applied EngineRequests + generative outcomes in plan order); `status` (`planned`/`running`/`done`/`failed` — plain text, app-enforced, no CHECK); `credits` (credits **committed** by the run; 0 for read-only/engine-only plans); `verdict` (final plain-English summary or failure reason). |

Indexes: `(workspace_id, created_at desc)` and `(composition_id, created_at desc)`. **The write is best-effort at the call site**: an audit-write failure must never fail the actual agent execution (same rule as the `028` save hook). Routes: `/api/agent/execute` opens a run, marks it failed on throw, finishes it with committed credits; `GET /api/agent/runs` + `/:id` list/read (cross-tenant reads 404).

### 033 — `033_workspace_theme.sql` · Per-tenant UI theme

A single column addition:

```sql
alter table workspaces add column if not exists ui_theme jsonb;
```

`ui_theme` holds the workspace's saved white-label theme (`{preset, overrides}`), applied **server-side on every page load** (zero-flash — the `src/server.js` HTML handler stamps `data-theme` and a `:root{…}` override style before any script runs) and synced to members' browsers via `GET/PUT /api/theme`. Nullable — absent means the default preset. The value is run through a strict allowlist sanitizer (`src/theme-settings.js`) because it is interpolated into served HTML. Code degrades silently pre-migration (device-local theme only).

---

## Conventions

These hold across the schema; rely on them when writing new tables or queries.

- **Workspace scoping is the tenant boundary, enforced in the application layer — not by RLS.** Almost every business table carries `workspace_id uuid not null references workspaces(id) on delete cascade`, and `requireUser()` (in `src/session.js`) joins `memberships` on the session's `workspaceId` to authorize the request. There is **no row-level security** — a query that forgets its `where workspace_id = …` predicate can read across tenants. Every read/write must scope on `workspace_id`. (Raw connections like the render worker bypass `requireUser` entirely and must include `workspace_id` in their predicates themselves.)
- **Deliberate tenancy exceptions exist — know them.** `analytics_events` and the chat tables (`027`) have **nullable** user/workspace references because they serve anonymous visitors; `leads` has no tenant column at all; `chat_kb` is a **global** corpus by design; `brands`/`storage_grants` hang off `accounts` (above the workspace boundary); `app_admins`/`admin_audit_events` are app-global. When a table is *not* workspace-scoped, its migration header says why — write the same justification in yours.
- **UUID primary keys.** Every table uses `id uuid primary key default gen_random_uuid()` (from `pgcrypto`). The two exceptions are pure join tables with composite PKs (`crm_contact_tags`, `collection_assets`) and the KV table `chat_settings` (`key text primary key`).
- **`timestamptz` timestamps.** `created_at timestamptz not null default now()`, plus `updated_at` on mutable tables (maintained by app code, not a trigger).
- **`jsonb` for structured/semi-structured payloads.** Manifests, briefs, automation graphs, provenance, brand kits, ledger meta, agent plans — all `jsonb`, defaulting to `'{}'`/`'[]'`. This is the deliberate pattern for shapes that evolve faster than the relational schema.
- **`check` constraints encode enums** — usually. Status/role/level/visibility columns use `check (... in (...))` rather than Postgres `ENUM` types, so a new value is a one-line additive migration. Two knowing deviations: `social_posts.status` (the value set belongs to the external relay, not us) and `agent_runs.status` (app-enforced). Prefer the CHECK for anything CoreReflex itself owns.
- **Hard-delete policy (no soft delete).** There is no `deleted_at` anywhere. Deletes are real, and intentional cascade rules do the cleanup: `on delete cascade` for owned children (a workspace's data, a contact's activity/tags, a conversation's messages) and `on delete set null` for soft references (`created_by`, `assignee_id`, `campaign_id`, `asset_id`). GDPR contact deletion is an explicit hard cascade by design. The closest things to soft flags are lifecycle statuses (`archived`, `is_active`) — which hide, not delete.
- **Denormalized tallies are trigger- or app-maintained — not editable directly.** `assets.vote_score`/`vote_count` (trigger, `004`), `accounts.credits` (app code, `006`), and `chat_conversations.message_count`/`total_tokens` (app code, `027`) must be mutated through their owning path, never by a direct `UPDATE` of the denormalized column.
- **Append-only audit tables.** `admin_audit_events`, `credit_ledger`, `crm_activity`, `review_events`, `automation_runs`, `analytics_events`, and `social_posts` (rows gain status updates but are never deleted) are written and read back; none are pruned by the app.
- **Idempotency lives in unique indexes, not application checks.** Webhook/dedup guarantees are DB-level: `credit_ledger_grant_idem_idx` (`011`), `processed_webhook_events` PK (`012`), `storage_grants_reason_uniq` (`026`), `leads_email_uniq` (`024`). A `SELECT`-then-`INSERT` is not a dedupe — see the `011` rationale.
- **Secrets at rest are encrypted, never hashed-for-recovery and never plaintext.** `social_connections.api_key_enc` is the template: AES-256-GCM via `src/crypto-box.js`, packed `v1$salt$iv$tag$ct`, decryptable server-side, never surfaced in any API response. Use the same helper for any future third-party token.
- **Search columns**: semantic search is `vector(1536)` + an HNSW `vector_cosine_ops` index (`assets`, `knowledge_docs`, `chat_kb`); lexical search is a stored generated `tsvector` + GIN (`chat_kb.fts` — currently the only one). Hybrid retrieval combines both.
- **Ship code that tolerates the old schema.** Feature code reaches prod before its migration does (see [the deploy contract](#degrade-gracefully-is-the-deploy-contract)) — best-effort writes and default-on-absent reads are the house pattern.