Database Schema & Data Lifecycle

This page is the canonical public description of what Invariant persists, when each record changes, and why each representation exists. SQLite and PostgreSQL implement one logical RuntimeStore contract with intentionally different physical encodings.

Persistence preserves committed truth; it does not provide automatic cross-process continuation. See the Recovery Contract Matrix.

Logical Data Model

sessions.active_run_id is the nullable pointer to the most recently bound execution. It intentionally has no database foreign key so Session and execution retention can be managed independently. Restoration fails closed when a non-null binding names a missing execution; it never silently converts a broken authority binding into an idle Session. A non-terminal run additionally requires the same workflow ID to be registered in the new process.

Both adapters also create an internal invariant_schema_migrations ledger.

What Is Durable vs. What Is Not

Doctrine: Session preserves durable continuity. Conversation history is an input to reasoning, not automatically part of durable runtime truth.

Durable in Invariant Schema:
- Session context (stored in sessions.context)
- Workflow state (stored in workflow_executions.data)
- Runtime events (stored in workflow_events)
- Outbox commands (stored in outbox_commands)
- Leases (stored in execution_leases)

Not automatically persisted:
- Raw chat transcript / message history

Session context is durable application context. Conversation transcripts are not automatically persisted by Invariant Beta.

If an application requires durable chat transcripts across process restarts:

• Recommended: Store conversational turns in a dedicated relational table (e.g. session_messages) or external conversation store.
• Reasoning Projection: Never send entire long-term transcripts back to the model. Project only the recent or relevant conversational context required for the current reasoning boundary.

Why State Exists in Two Forms

Invariant stores both immutable execution facts and a materialized current state:

Representation	Purpose	Update rule
workflow_events	Audit, provenance, deterministic replay	Append only during normal execution; unique (run_id, seq)
workflow_executions	Fast access to current workflow truth	Updated only inside an OCC-protected commitTransition()

The event log answers what happened. The materialized row answers what is true now. Keeping both avoids replaying the entire history for every request without sacrificing an immutable audit trail.

Newly written events use id = ${runId}:${seq}. revision equals durable event progression, so a transaction that appends events 8 through 10 advances revision 7 to 10.

Core Tables and Ownership

workflow_executions

One row per workflow run.

Logical field	Written by	Why it exists
run_id	Host/SDK before the pure Kernel	Stable UUID identity for the run
workflow_id, workflow_version	Initial transition	Identifies the immutable graph contract used by the run
status, current_node_id	Reducer through commitTransition()	Locates the current execution boundary
revision	Reducer through commitTransition()	OCC token and last durable event sequence
input	Initial transition	Immutable workflow parameters
data	Node completion reduction	Accumulated workflow state; later keys overwrite materialized values while prior values remain in events
error, cancellation_state	Failure/cancellation reduction	Durable terminal and compensation context
loop_state	Repeat-node reduction	Per-node durable iteration counters used to resume bounded .repeat() execution correctly
timestamps	Store	Operational creation and last-update time

PostgreSQL stores these fields in separate typed columns. SQLite stores the complete ExecutionState JSON document in state_json, while duplicating status, node, revision, and identity columns needed for queries and OCC.

Execution statuses are pending, running, waiting, cancelling, completed, failed, cancelled, and cancellation_failed.

workflow_events

Every durable execution fact is appended with a run-scoped monotonic seq. payload contains the complete serialized RuntimeEvent, while type, timestamp, and keys remain separately queryable. Deleting an execution row cascades to its events at the database level; normal runtime execution never rewrites committed events.

outbox_commands

Commands describe environmental work that must occur after durable intent is committed. A command is inserted in the same transaction as its originating events and materialized state. The initiating Session's in-process command drain invokes the handler only after that transaction succeeds.

status begins as pending. Completion or failure updates the status and processed_at in the same transition that records the corresponding completion/failure event. The core type also reserves claimed, but the public Beta does not expose a portable command-claim API.

id is the adapter-derived durable command-row identity. idempotency_key is the optional stable application/provider request identity resolved from the workflow's capability template. The current capability handler does not receive that resolved value, so it must still send a separate stable application request key to an external provider. The provider must enforce deduplication.

execution_leases

At most one row exists per run_id. Acquire or renewal changes worker_id, lease_id, and the Unix-epoch-millisecond expires_at. Release deletes the row. Leases provide mutual exclusion and runnable-run discovery; they do not attach a new Session or expose pending command payloads.

sessions

One row per application Session. context contains identity, tenant, preference, permission, or other application-owned facts shared across related runs. active_run_id records the latest execution bound by session.startWorkflow().

• revision is the CAS token for every Session mutation, including context and run binding.
• context_revision advances only when application context changes.
• loadOrCreateSession() loads the stored row before invoking hydrate() and reattaches its execution when the workflow graph is registered.
• restoreSession(id, userId) restores an existing row only when user_id matches.
• updateContext(patch) performs a shallow object merge and commits it with Session OCC.
• rehydrate() replaces the current context with the hydrator result and commits it with Session OCC.
• Writes preserve created_at, advance revision exactly once, and reject stale revisions or ownership changes.

The initial workflow transition and its Session binding share one database transaction. A Session can therefore own at most one winning non-terminal start even when different processes race: only one expected Session revision can commit, and the losing execution row is rolled back.

SessionConfig.contextSchema is reserved metadata in the current Beta and is not evaluated at persistence boundaries. Validate untrusted, hydrated, and previously persisted context in application code.

Atomic Write Lifecycle

Validated incoming event
        │
        ▼
Pure Kernel derives events + next state + commands
        │
        ▼
BEGIN database transaction
  1. lock/read current execution revision
  2. reject unless revision == expectedRevision
  3. update/insert materialized execution
  4. append all durable events
  5. insert new outbox commands
  6. acknowledge completed/failed commands
  7. lock/read Session owner + revision when a SessionWrite is present
  8. reject owner mismatch or stale Session revision
  9. bind the Session to the newly started run
COMMIT
        │
        ▼
Live Session may invoke the next environmental command

All database changes succeed or all roll back. An execution- or Session-OCC loser exposes no proposed state, events, commands, revision, acknowledgement, or Session binding.

Physical Adapter Differences

Concern	SQLite	PostgreSQL
Execution JSON	Entire ExecutionState in state_json	input, data, error, and cancellation_state JSONB columns
JSON encoding	TEXT containing JSON	Native JSONB
Timestamps	Unix epoch milliseconds (INTEGER)	TIMESTAMPTZ, except event/lease epoch milliseconds
Write serialization	Local SQLite transaction	PostgreSQL transaction plus SELECT ... FOR UPDATE
Journaling	WAL for file-backed databases	PostgreSQL WAL managed by the server
Schema application	Constructor runs ordered migrations	initializeSchema() runs ordered migrations
Process-loss durability	File-backed only; :memory: disappears	Durable according to PostgreSQL configuration

Do not copy physical rows directly between adapters. Stop writes and perform an explicit logical export/import because the execution encodings and timestamp types differ.

Physical Column Reference

Constraints shown below are part of the storage contract. NN means NOT NULL; PK and FK identify primary and foreign keys.

SQLite v3

Table	Columns and constraints
invariant_schema_migrations	version INTEGER PK, applied_at INTEGER NN
workflow_executions	run_id TEXT PK, workflow_id TEXT NN, workflow_version TEXT NN, status TEXT NN, current_node_id TEXT, revision INTEGER NN DEFAULT 0, state_json TEXT NN, created_at INTEGER NN, updated_at INTEGER NN
workflow_events	id TEXT PK, run_id TEXT NN FK, seq INTEGER NN, type TEXT NN, payload TEXT NN, timestamp INTEGER NN; unique (run_id, seq); execution delete cascades
outbox_commands	id TEXT PK, run_id TEXT NN FK, seq INTEGER NN, node_id TEXT NN, type TEXT NN, payload TEXT NN, status TEXT NN DEFAULT 'pending', idempotency_key TEXT, created_at INTEGER NN, processed_at INTEGER; execution delete cascades
execution_leases	run_id TEXT PK/FK, worker_id TEXT NN, lease_id TEXT NN, expires_at INTEGER NN; execution delete cascades
sessions	id TEXT PK, user_id TEXT NN, context TEXT NN DEFAULT '{}', active_run_id TEXT, revision INTEGER NN DEFAULT 0, context_revision INTEGER NN DEFAULT 0, created_at INTEGER NN, updated_at INTEGER NN

SQLite indexes cover Session user, execution status, event (run_id, seq), command status, and lease expiry.

PostgreSQL v4

Table	Columns and constraints
invariant_schema_migrations	version INTEGER PK, description TEXT NN, applied_at TIMESTAMPTZ NN DEFAULT NOW()
workflow_executions	run_id VARCHAR(255) PK, workflow_id VARCHAR(255) NN, workflow_version VARCHAR(50) NN, status VARCHAR(50) NN, current_node_id VARCHAR(255), revision INTEGER NN DEFAULT 0, input JSONB NN, data JSONB NN, error JSONB, cancellation_state JSONB, loop_state JSONB, created_at TIMESTAMPTZ NN, updated_at TIMESTAMPTZ NN
workflow_events	id VARCHAR(255) PK, run_id VARCHAR(255) NN FK, seq INTEGER NN, type VARCHAR(100) NN, payload JSONB NN, timestamp BIGINT NN; unique (run_id, seq); execution delete cascades
outbox_commands	id VARCHAR(255) PK, run_id VARCHAR(255) NN FK, seq INTEGER NN, node_id VARCHAR(255) NN, type VARCHAR(100) NN, payload JSONB NN, status VARCHAR(50) NN DEFAULT 'pending', idempotency_key VARCHAR(255), created_at TIMESTAMPTZ NN, processed_at TIMESTAMPTZ; execution delete cascades
execution_leases	run_id VARCHAR(255) PK/FK, worker_id VARCHAR(255) NN, lease_id VARCHAR(255) NN, expires_at BIGINT NN; execution delete cascades
sessions	id VARCHAR(255) PK, user_id VARCHAR(255) NN, context JSONB NN DEFAULT '{}', active_run_id VARCHAR(255), revision BIGINT NN DEFAULT 0, context_revision BIGINT NN DEFAULT 0, created_at TIMESTAMPTZ NN, updated_at TIMESTAMPTZ NN

PostgreSQL indexes cover Session user, execution status, event (run_id, seq), command status, and lease expiry.

Schema Versioning and Upgrades

SQLite

The constructor creates invariant_schema_migrations(version, applied_at) and applies each unapplied migration in its own transaction. Built-in migration v1 creates all five core tables and indexes; v2 adds nullable sessions.active_run_id; v3 adds revision and context_revision with legacy default 0.

Applications adding SQLite migrations must include the built-in migration before their own ordered versions:

import {
  initialMigrationV1,
  sessionActiveRunMigrationV2,
  sessionAuthorityMigrationV3,
  sqlite,
  type Migration,
} from "@invariant-tech/sqlite";

const applicationMigrationV4: Migration = {
  version: 4,
  up: (db) => {
    db.exec("CREATE TABLE application_metadata (key TEXT PRIMARY KEY, value TEXT NOT NULL)");
  },
};

const store = sqlite({
  filename: "./data/invariant.db",
  migrations: [
    initialMigrationV1,
    sessionActiveRunMigrationV2,
    sessionAuthorityMigrationV3,
    applicationMigrationV4,
  ],
});

Migration versions must be immutable after release. Add a new version; never edit an already applied migration.

PostgreSQL

initializeSchema() creates invariant_schema_migrations(version, description, applied_at) and applies unapplied built-in migrations atomically. Existing pre-ledger Beta databases are adopted safely because migration v1 uses idempotent DDL plus the additive cancellation_state alteration before recording version 1. Migration v2 adds nullable loop_state; v3 adds nullable sessions.active_run_id; v4 adds Session and context revision counters with legacy default 0. Old rows remain readable and acquire their first authoritative revision on the next successful CAS write.

const store = postgres({ connectionString: process.env.DATABASE_URL! });
await store.initializeSchema();

DBA-managed pipelines can execute the versioned SQL shipped with the package:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
  -f node_modules/@invariant-tech/postgres/dist/schema.sql

Do not run initializeSchema() concurrently with a separate migration pipeline. Choose one schema owner for each environment.

Backup, Restore, and Rollback

SQLite

Use SQLite's online backup command rather than copying a live database and WAL independently:

sqlite3 ./data/invariant.db ".backup './backups/invariant.db'"

For restore, stop application writes, retain the failed database for diagnosis, restore the backup as one database file, reopen the store, and verify invariant_schema_migrations, execution revision/event parity, and Session reads before accepting traffic.

PostgreSQL

Use normal PostgreSQL logical or physical backups. A minimal logical procedure is:

pg_dump "$DATABASE_URL" --format=custom --no-owner --file=invariant.dump
pg_restore --clean --if-exists --no-owner --dbname="$RESTORE_DATABASE_URL" invariant.dump

Restore into a separate database first, verify migration versions and application reads, and then switch traffic. Invariant migrations are forward-only; rollback means restoring a compatible backup and deploying the package version that owns that schema.

Disaster-recovery runbook

1. Define an application RPO (acceptable data-loss window) and RTO (acceptable recovery time). Back up more frequently than the RPO.
2. Keep backups encrypted, access-controlled, and outside the failure domain of the primary database. PostgreSQL deployments that require point-in-time recovery must also archive and test WAL restoration.
3. On an incident, stop or fence writers before selecting a recovery point.
4. Restore into an isolated database and deploy the exact application/package version compatible with that migration ledger.
5. Run the operational verification, record the recovered revision/time, and only then switch traffic.
6. Exercise this procedure on a schedule; an untested backup is not release evidence.

Retention and Deletion

The Beta does not automatically prune events, commands, Sessions, or executions. Retention, archival, PII redaction, and deletion are application/operator policies.

Because events and commands reference workflow_executions with ON DELETE CASCADE, deleting an execution also deletes its event history, outbox rows, and lease. Treat that as an audited destructive operation. Session rows are independent and require a separate retention decision.

Operational Verification

After a migration or restore, verify at minimum:

1. Migration versions are exactly those expected by the deployed package.
2. Every new-format execution's revision matches its last durable event sequence; legacy histories remain readable without backfill.
3. (run_id, seq) and event IDs are unique.
4. Materialized state and ordered replay reach the same final truth.
5. Pending and terminal command rows retain their expected idempotency identity and timestamps.
6. Session context loads for the correct user/tenant.
7. Session/context revisions round-trip and stale or wrong-owner writes roll back.
8. Expired leases are discoverable and active leases exclude competing owners.

Related Contracts

• PostgreSQL Adapter — Connection, initialization, and real-server verification.
• SQLite Adapter — Embedded-store configuration and durability modes.
• State & Event Sourcing — Reducer, event, revision, and overwrite semantics.
• Sessions & Hydration — Context lifecycle and validation boundary.
• Runtime Execution — RuntimeStore, OCC, dispatch, and recovery limits.