Postgres backend does not provide multi-replica control-plane safety or leader fencing

[Bnjoroge1]

Severity

P1 / high production architecture and correctness

Review baseline

0bd0e314696e520aa47620cbe8f008d010e01e57

Summary

The Postgres store provides restart durability, but it does not make the Preloop server active-active. Runtime coordination remains in each process's in-memory InnerState; multiple server replicas pointed at the same Postgres database can independently load state and then diverge while accepting runner/API traffic.

There is no leader lease, fencing token, or database-backed transactional ownership mechanism preventing two live control-plane processes from mutating the same logical scheduler/session/job state.

Impact

Running multiple replicas behind a load balancer can result in:

• conflicting runner/session ownership;
• duplicate or missed job delivery;
• divergent queue and broker-message state;
• stale process-local state overwriting newer durable projections;
• unsafe failover during deployments/restarts;
• a false sense that switching SQLite -> Postgres provides horizontal scaling/HA.

Recommended remediation

Near-term:

• Enforce exactly one active writer with a renewable lease stored in Postgres.
• Include a monotonically increasing fencing token in every durable mutation and reject writes from an older leader epoch.
• Have standby replicas remain read-only / reject runner-protocol mutations until they hold the lease.
• Fail startup or clearly downgrade to standby mode when another live writer owns the store.

Longer-term:

• Move scheduling/session/claim transitions into database-backed transactional commands or a durable ordered command log so API replicas can be stateless.
• Separate read scaling from mutation ownership.

Acceptance criteria

• Two Preloop server processes cannot simultaneously act as control-plane writers for one store.
• Failover is fenced so a paused/partitioned old leader cannot resume writing after a new leader takes over.
• Runner protocol traffic is routed only to the active owner or uses shared transactional coordination.
• Documentation explicitly distinguishes Postgres durability from active-active scaling until true multi-writer support exists.