Inspecting durable execution in PostgreSQL

The PostgreSQL backend uses @effect/sql-pg, Effect SQL transactions, and versioned migrations. DATABASE_URL selects the database. pnpm start reads .env and uses PostgreSQL when that setting is present. Without it, the server uses memory. No credentials belong in source control.

On this machine, the existing PostgreSQL backend exposes port 54329. Dedicated databases are tiny_temporal and tiny_temporal_test. The ignored .env contains their connection settings. The server uses 17233 here because 7233 is occupied. For a different machine, create empty databases on your PostgreSQL backend and fill in .env.example. Startup applies pending migrations without resetting stored data.

A workflow across two processes

Stop any other tiny-temporal server using the same database before this walkthrough. Two independently started servers both name their hosts history-1 and matching-1; each would keep taking the other's shard and queue leases.

pnpm persistence:start

This runs an activity once, records an approval signal, schedules a three-second timer, and stops the process. It prints a unique workflow ID. No runtime objects or JSON snapshot are passed to the next process.

pnpm db:inspect WORKFLOW_ID
pnpm persistence:resume WORKFLOW_ID
pnpm db:inspect WORKFLOW_ID

Before resuming, inspect the CommandCompleted activity event, SignalReceived, and the outstanding timer_tasks row. If the timer deadline passed while the server was stopped, the new History processor fires it after startup. The replacement worker replays the recorded activity result, consumes the recorded signal, and completes the same run. Executing greet activity should appear only in the first process.

db:inspect filters executions/history by the optional workflow ID. It also shows all queue and projection tables so you can see outstanding work across the database. Completed histories remain available after their queue rows are acknowledged.

Tables and ownership

Useful queries in any PostgreSQL client connected to tiny_temporal:

SELECT workflow_id, run_id, status FROM executions;

SELECT run_id, event_id, event_type, event_time, jsonb_pretty(data)
FROM history_events ORDER BY run_id, event_id;

SELECT task_id, task_queue, available_at, data FROM transfer_tasks;
SELECT run_id, command_sequence, due_at, data FROM timer_tasks;
SELECT physical_queue, range_id, ack_level, last_task_id FROM task_queues;
SELECT shard_id, range_id, owner FROM shards;
SELECT physical_queue, task_id, data FROM tasks ORDER BY physical_queue, task_id;

SELECT e.workflow_id, e.status AS execution_status, v.status AS projected_status
FROM executions e LEFT JOIN visibility v USING (run_id);

See Matching queue ownership and recovery for fencing, acknowledgement gaps, and the upstream source map. Queue metadata remains after its task rows are removed.

An empty queue is meaningful: its tasks have been acknowledged. Events remain as the durable record of completed work. Visibility may temporarily lag execution state, including if the server stops before projecting its final update.

Transaction and recovery boundaries

ExecutionStore.transact accepts a shard fence and a pure History transition. The PostgreSQL store locks history_partition, then locks the shard's row and checks that its range_id still equals the fence. A History host gets that range ID when it takes the shard's lease. The store then reads committed History state, applies the transition, and writes only changed records inside sql.withTransaction. If another host has taken the lease, the transaction fails with ShardOwnershipLostError and the host gives the shard up, as upstream's conditional shard updates do. Event inserts, current state, current-run pointers, transfer tasks, timer tasks and visibility intents commit together. A domain rejection, SQL failure, or defect rolls the whole transaction back. Success is returned after commit.

History acknowledges a transfer only after Matching accepts it. These are separate transactions, so a crash can redeliver work. Matching checks task starts against History to reject already-started or obsolete attempts. Activities can still execute more than once: an activity side effect and its completion report are not one transaction. Design activities to tolerate retries.

Pending timers are wall-clock timestamps. Started tasks have persisted deadlines/attempt history. After a server crash, processors read those records and resume delivery or timeout recovery. Matching's waiting pollers, caches, dispatch counters, read levels, and loaded managers are recreated in memory. Ownership ranges and acknowledgement levels persist in task_queues.

SQL failures fail operations rather than falling back to memory or reporting success. A failed background processor fails the server lifetime, which closes its listener and pools. Automatic restart/failover is not implemented. Restart the server after an operational database failure.

Relationship to upstream Temporal

Follow these alongside the toy code:

• Temporal PostgreSQL execution/task/history schema
• SQL execution persistence
• Persistence interfaces and workflow mutations
• History transfer task execution

The ownership and transactional intent follow Temporal. This database is not compatible with Temporal Server's database schema. It uses readable JSONB records and individual event rows instead of Temporal's encoded persistence structures and history batches. Wire-schema compatibility concerns RPC messages, not database tables.

For compactness, the toy also loads History state under one global database lock. This serializes mutations across independent database connections, but is deliberately unsuitable for large datasets. Shard leases fence History hosts and range IDs fence Matching queues, as upstream does. Granular storage operations, replication, archival and retention cleanup are outside this implementation.

Verification

pnpm test:postgres
pnpm test
pnpm typecheck

The PostgreSQL integration test uses only TEST_DATABASE_URL, with unique workflow IDs and retained artifacts. It checks migration of old backlog rows, deployment routing and child-backlog delivery after restart, user-data compare-and-swap across independent managers, range fencing and acknowledgement recovery through independent pools, domain rejection, actual SQL constraint rollback, shard-lease fencing between two History hosts that both claim a shard, and projection-task acknowledgement. It runs History, Matching, and Frontend+Worker as three processes and completes a stock SDK workflow through them. It then kills the complete server/worker process with SIGKILL and starts a fresh process to recover a timer, signal, queued Workflow Task and timed-out activity attempt.

The entrypoint is cmd/server/src/main.ts: pnpm start launches all server roles with autonomous loops and the real clock, and the roles call each other over internal RPC. With PostgreSQL, the roles can also run as separate processes:

pnpm start --service history               # History RPC on HISTORY_PORT (7234)
pnpm start --service matching              # Matching RPC on MATCHING_PORT (7235)
pnpm start --service frontend --service worker

Migration 005 adds the shards table and shifts stored shard IDs to start at 1. It also adds each queued task's workflow ID, History's routing key, and removes the activity inputs Matching no longer stores.