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

npx pleach init --apply --target ./supabase/migrations

Then your usual Postgres / Supabase migration flow. See CLI for the flags and the manual psql apply path.

for f in supabase/migrations/*pleach*.sql; do
  psql "$DATABASE_URL" -f "$f"
done

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:

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

harness_sessions (001)

The primary table the storage adapter reads and writes.

Indexes: (user_id), (organization_id), (user_id, last_active_at DESC) filtered to non-deleted, (id, version) for optimistic-concurrency reads.

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)

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)

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.

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)

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)

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)

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)

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)

Threaded comments on sessions — replies, mentions, reactions. Optional surface; the runtime itself doesn't consume it.

harness_auditable_calls (010)

The typed per-call audit ledger. See AuditableCall 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)

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 for the column walk, the Merkle hash algorithm, retention, and the query shapes.

RLS template

Every table ships the same two-policy template:

-- 1. Service role has full access (for server-side adapters).
CREATE POLICY <table>_service_role ON <table>
  FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- 2. Owner-scoped policies for anon clients.
CREATE POLICY <table>_owner_select ON <table>
  FOR SELECT USING (user_id = auth.uid()::text);

CREATE POLICY <table>_owner_insert ON <table>
  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

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:

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

Every table that has an updated_at column uses one shared trigger:

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

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