# Schema (/docs/schema)
The schema bundle is the canonical persistence layer for the
Supabase and Postgres-backed adapters. Twelve files, applied in
order, produce a complete runtime schema with RLS policies, the
shared `harness_set_updated_at()` trigger, and indexes tuned for
the access patterns the adapters use.
All files use `CREATE ... IF NOT EXISTS` and `DROP POLICY IF
EXISTS` — re-applying is safe. Schema evolution lands as
additional files; existing files are not edited in place.
## Applying the bundle [#applying-the-bundle]
```bash
npx pleach init --apply --target ./supabase/migrations
```
Then your usual Postgres / Supabase migration flow. See [CLI](/docs/cli)
for the flags and the manual `psql` apply path.
```bash
for f in supabase/migrations/*pleach*.sql; do
psql "$DATABASE_URL" -f "$f"
done
```
### Idempotent re-run [#idempotent-re-run]
Every file uses `CREATE ... IF NOT EXISTS` and `DROP POLICY IF
EXISTS`, so re-running the loop is safe. The shell helper below
re-applies and stops on the first failure — useful after
upgrading `@pleach/core` and pulling new files into the
migrations directory:
```bash
set -euo pipefail
for f in $(ls supabase/migrations/*pleach*.sql | sort); do
echo "applying $f"
psql "$DATABASE_URL" --single-transaction -v ON_ERROR_STOP=1 -f "$f"
done
```
`--single-transaction` rolls back a partial file on error; the
next re-run picks up from a clean state.
## The 12 files [#the-12-files]
| File | Table | Purpose |
| ---------------------------------- | -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `000_supabase_compat.sql` | — | Vanilla-Postgres compatibility prelude (guarded no-op on Supabase); makes the bundle apply on bare Postgres before the harness tables |
| `001_harness_sessions.sql` | `harness_sessions` | Core session state — JSONB state column, version, message count |
| `002_harness_checkpoints.sql` | `harness_checkpoints` | Per-channel snapshots; consumed by `SupabaseSaver` |
| `003_harness_event_log.sql` | `harness_event_log` | The full observable event stream — ULID-keyed, append-only |
| `004_harness_outbox.sql` | `harness_outbox` | Durable sync outbox; backs `SupabaseOutbox` |
| `005_harness_errors.sql` | `harness_errors` | Structured error propagation (when `enableErrorPropagation: true`) |
| `006_chat_session_links.sql` | `chat_session_links` | Provenance link from upstream chat rows to harness sessions |
| `007_harness_audit_records.sql` | `harness_audit_records` | Generic audit-record table — separate from the typed `AuditableCall` ledger |
| `008_harness_session_members.sql` | `harness_session_members` | Multi-user session access (for `useTeam` presence) |
| `009_harness_session_comments.sql` | `harness_session_comments` | Threaded comments on sessions |
| `010_auditable_calls.sql` | `harness_auditable_calls` | The typed per-call audit ledger — one row per LLM call, joinable by `tenantId` / `turnId` |
| `011_spawn_event_fields.sql` | `harness_event_log` | Adds three nullable SpawnEvent topology columns projected off the JSONB `payload` so root-turn rollup queries JOIN on indexed columns |
## `harness_sessions` (001) [#harness_sessions-001]
The primary table the storage adapter reads and writes.
| Column | Type | Notes |
| ---------------------------------------------- | -------------- | -------------------------------------- |
| `id` | `UUID` PK | `gen_random_uuid()` default |
| `user_id` | `TEXT` | Owner — RLS keys here |
| `organization_id` | `UUID?` | Multi-tenant scope |
| `title` | `TEXT?` | User-visible session title |
| `version` | `INTEGER` | Optimistic-concurrency version counter |
| `schema_version` | `INTEGER` | Row-shape version |
| `state` | `JSONB` | The full `SessionState` payload |
| `message_count` | `INTEGER` | Denormalized for sidebar rendering |
| `created_at` / `updated_at` / `last_active_at` | `TIMESTAMPTZ` | Lifecycle timestamps |
| `deleted_at` | `TIMESTAMPTZ?` | Soft-delete marker |
Indexes: `(user_id)`, `(organization_id)`, `(user_id, last_active_at DESC)` filtered to non-deleted, `(id, version)` for optimistic-concurrency reads.
## `harness_checkpoints` (002) [#harness_checkpoints-002]
Each row is one channel snapshot at a stage boundary. Composite
key on `(session_id, checkpoint_id, channel_name)`. The
checkpointer reads all rows for a checkpoint id and rebuilds the
session state from the union.
## `harness_event_log` (003) [#harness_event_log-003]
Append-only event stream. `record_id TEXT PRIMARY KEY` (ULID) so
lexicographic order matches creation order — cursor pagination
without a separate timestamp index.
Indexes: `(session_id, record_id)` for per-session reads,
`(session_id, event_type, record_id)` for filtered reads (e.g.
"all `tool.failed` since cursor X").
## `harness_outbox` (004) [#harness_outbox-004]
The durable sync outbox. Rows hold operation payloads buffered for
transmission to the backend. The worker claims a row, attempts the
round-trip, and either commits or fails with retry semantics —
`next_retry_at` drives the exponential backoff.
| Column | Type | Notes |
| --------------------------- | ------------- | -------------------------------------------------------- |
| `id` | `UUID` PK | `gen_random_uuid()` default |
| `session_id` | `UUID` | The session the row mutates |
| `operation_type` | `TEXT` | `create` / `update` / `delete` / `append-event` |
| `payload` | `JSONB` | Operation-specific body (row to upsert, event to append) |
| `retry_count` | `INTEGER` | Bumped by the worker on each failure |
| `next_retry_at` | `TIMESTAMPTZ` | Backoff cursor — the poll index keys on this |
| `last_error` | `TEXT` | Last failure message; `NULL` on success |
| `status` | `TEXT` | `pending` / `in-flight` / `committed` / `failed` |
| `user_id` | `TEXT` | RLS scope |
| `created_at` / `updated_at` | `TIMESTAMPTZ` | Lifecycle timestamps |
Two indexes carry the access patterns: `(status, next_retry_at)`
filtered to `pending`/`in-flight` for the worker poll, and
`(session_id, created_at DESC)` for per-session debug listing.
## `harness_errors` (005) [#harness_errors-005]
Structured errors persisted when `enableErrorPropagation: true`
on the runtime. Carries the same shape as `HarnessError` — code,
message, recovery hint, cause — plus the session and turn it
fired against. Useful for cross-session error analytics.
## `chat_session_links` (006) [#chat_session_links-006]
Provenance link between an upstream chat row (the host application's
chat schema) and the harness session. Set via
`SessionRuntimeConfig.chatId`. Lets billing or product analytics join
the harness ledger back to a customer-facing chat surface.
`chat_id UUID PRIMARY KEY` — one chat binds to at most one session at
a time, last write wins. `session_id` is intentionally NOT a foreign
key to `harness_sessions(id)` so the link can be re-bound after a
hard-delete. Reverse lookup uses
`(session_id, created_at DESC)`.
## `harness_audit_records` (007) [#harness_audit_records-007]
Generic audit-record table for events that don't fit the typed
`AuditableCall` shape. Used by `@pleach/compliance` and custom
audit hooks for cross-cutting records that don't map to a single
LLM call.
## `harness_session_members` (008) [#harness_session_members-008]
Multi-user session access. Rows associate a `user_id` with a
`session_id` plus a role (`owner` / `editor` / `viewer`).
Consumed by `useTeam` presence and RLS policies that allow shared
read.
## `harness_session_comments` (009) [#harness_session_comments-009]
Threaded comments on sessions — replies, mentions, reactions.
Optional surface; the runtime itself doesn't consume it.
## `harness_auditable_calls` (010) [#harness_auditable_calls-010]
The typed per-call audit ledger. See [AuditableCall row](/docs/auditable-call-row)
for the column-by-column walk and the join patterns.
Key invariants:
* `record_id TEXT PRIMARY KEY` (ULID; lex-sortable)
* `session_id UUID REFERENCES harness_sessions(id) ON DELETE RESTRICT` — deleting a session with audit history requires an explicit retention policy decision
* `stage_id` constrained to `{anchor-plan, tool-loop, synthesize, post-turn}`
* `actor_kind` constrained to `{user, guest, system, scheduled}`
* `payload JSONB` — typed slots for `cacheHit` / `family-lock-resolution` / etc.
## `harness_config_manifest` (rolling out) [#harness_config_manifest-rolling-out]
additive · rolling out
A content-addressable snapshot of the runtime substrate — the
plugin set, system prompts, graph node bodies, channel
definitions, and post-stage filters active in a session. The
primary key is the snapshot's content hash, so identical
substrates share a row. It lands as an additive file
(`012_harness_config_manifest.sql`) alongside a nullable
`manifest_hash` column on `harness_event_log`, so existing rows
stay valid through the rollout.
See [Config manifest](/docs/config-manifest) for the column
walk, the Merkle hash algorithm, retention, and the query shapes.
## RLS template [#rls-template]
Every table ships the same two-policy template:
```sql
-- 1. Service role has full access (for server-side adapters).
CREATE POLICY _service_role ON
FOR ALL TO service_role
USING (true) WITH CHECK (true);
-- 2. Owner-scoped policies for anon clients.
CREATE POLICY _owner_select ON
FOR SELECT USING (user_id = auth.uid()::text);
CREATE POLICY _owner_insert ON
FOR INSERT WITH CHECK (user_id = auth.uid()::text);
-- update + delete policies follow the same shape
```
Service-role clients bypass RLS by construction. Anon clients
must match the policy — passing `userId` on the runtime + a
correctly-signed JWT is what threads the request through.
For shared sessions (per `harness_session_members`), the owner
policy widens to "user\_id is owner OR member\_id is the current
user." The bundle ships the simple case; layer your own multi-user
policies on top.
### Multi-tenant policy keyed on `organization_id` [#multi-tenant-policy-keyed-on-organization_id]
When sessions belong to an org rather than a single user, gate
reads on the `organization_id` column and the JWT claim that
carries it. The same shape works for every table with an
`organization_id`:
```sql
DROP POLICY IF EXISTS harness_sessions_org_select ON harness_sessions;
CREATE POLICY harness_sessions_org_select ON harness_sessions
FOR SELECT
USING (
organization_id::text = auth.jwt() ->> 'organization_id'
AND deleted_at IS NULL
);
```
Pair this with a JWT signer that stamps `organization_id` on
every token — Supabase Auth hooks or your own session issuer.
## The shared trigger function [#the-shared-trigger-function]
Every table that has an `updated_at` column uses one shared
trigger:
```sql
CREATE OR REPLACE FUNCTION harness_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```
Created once (in file 001); reused everywhere. Don't redefine it
in your own migrations — the bundle's version is the one the
adapters expect.
## Custom columns [#custom-columns]
Adding columns is safe — the adapters serialize/deserialize the
`state` JSONB, not the row schema. To add fields the adapters
read directly (like a custom denormalized `last_synced_at`), use
an additive migration that lands after the bundle and update the
adapter via a custom subclass.
## Where to go next [#where-to-go-next]