Storage & Portability

Wakeplane is SQLite-first for local installs and now has an explicit Postgres production backend seam. SQLite remains the default; Postgres is selected by configuration when an operator wants a managed production database.

Why SQLite first

SQLite is the right choice for the current pre-1.0 phase:

• Zero infrastructure dependency. The database is a single file. You do not need to provision, connect to, or manage an external database.
• Simple deployment. Copy the binary and the database file. That is the entire deployment.
• Single-writer model. SQLite is excellent for one writer. Wakeplane runs as a single process with SetMaxOpenConns(1). There is no write contention.
• Embedded migrations. Schema migrations run automatically at startup via embedded SQL files. No migration tooling required.

Current constraints

• SQLite local mode is one writer. Wakeplane is still a single-process daemon; distributed worker deployments are not part of this phase.
• SQLite is file-based. The database must be a local file accessible by the daemon process. Network file systems (NFS, EFS) are not recommended.
• Postgres mode uses a connection pool and row locks for run claiming, but real disposable-Postgres verification is still required before calling the backend production-complete.

Configuration

WAKEPLANE_STORE=sqlite              # default
WAKEPLANE_DB_PATH=./wakeplane.db   # path to SQLite file

For Postgres:

WAKEPLANE_STORE=postgres
WAKEPLANE_DATABASE_URL=postgres://wakeplane:secret@db.example.com:5432/wakeplane

Migrations are dialect-owned under internal/store/migrations/{sqlite,postgres} and run on startup. Keep SQLite for local trusted installs; use Postgres when you need an external production database, backups, and operational database tooling.

Backup

The database is a single file. Back it up with SQLite’s backup API:

sqlite3 /var/lib/wakeplane/data.db ".backup /backups/wakeplane-$(date +%Y%m%d).db"

Do not copy the file while the daemon is running. Use the SQLite backup API or stop the daemon first.

For Postgres:

pg_dump "$WAKEPLANE_DATABASE_URL" > "wakeplane-$(date +%Y%m%d).sql"

SQLite to Postgres schedule bridge

The supported bridge in this phase is schedule export/import:

# Against the SQLite-backed daemon.
wakeplane schedule export > schedules.json

# Start a fresh Postgres-backed daemon, then import.
WAKEPLANE_STORE=postgres \
WAKEPLANE_DATABASE_URL=postgres://wakeplane:secret@db.example.com:5432/wakeplane \
wakeplane serve

wakeplane schedule import --file schedules.json
wakeplane status

schedule export emits an import-compatible manifest with schedule definitions. It does not move run history, execution receipts, request audit rows, worker leases, or dead letters. Preserve those with database-native backups when you need a full historical restore.

What is stored

• Schedules: name, enabled, timezone, schedule spec (cron/interval/once), target spec (HTTP/shell/workflow), policy, retry config, next_run_at
• Runs: occurrence key, attempt, status, lease ownership (claimed_by_worker_id, claim_expires_at), timing (started_at, finished_at), result, error
• Leases: worker ID, run ID, expires_at
• Dead letters: occurrence key, reason, payload
• Receipts: executor output attached to a run (stdout, HTTP response, workflow result)

All timestamps are stored as UTC RFC3339 strings. IDs are application-generated ULIDs stored as TEXT - no SERIAL or AUTOINCREMENT dependency.

What is already portable

The application logic (domain, planner, dispatcher, API, CLI) has zero dependency on storage internals. If you wanted to add a different storage backend, you would only need to change code inside internal/store. Everything above the store package is already portable.

Specifically portable:

• All application logic
• Schema structure (tables, foreign keys, indices, constraints are standard SQL)
• IDs (application-generated ULIDs)
• Cursor pagination (uses ORDER BY created_at DESC, id DESC - standard SQL)
• Transaction isolation (default levels compatible with standard databases)
• Query patterns (SELECT, INSERT, UPDATE, DELETE, JOIN, COUNT - standard SQL)

Postgres Hardening Status

Verification item	Status
Driver and connection config	Done
Dialect-owned migrations	Done
Postgres placeholder binding and lease upsert	Done
Postgres row-lock claim path	Done
Store/app/dispatcher/CLI Postgres test suite	Done
Disposable Postgres execution	Done
Native Postgres timestamp/boolean/JSON column types	Later

Verification path

The recommended verification path for Postgres changes:

1. Run scripts/test-postgres-store.sh against local Postgres binaries, Docker, or an externally supplied WAKEPLANE_POSTGRES_TEST_URL.
2. Fix any store/app/dispatcher/CLI parity failures exposed by real Postgres.
3. Run the soak, restart-recovery, and backup/restore drills after backend changes.
4. Move to native Postgres timestamp/boolean/JSON column types only behind an explicit migration.

This work stays behind the store boundary. Scheduler, dispatcher, run ledger, policy, and operator surfaces remain separate.

Reference docs

• SQLite Audit - complete inventory of SQLite-specific assumptions
• Storage Interface - full store method contract and dialect seam design
• Storage Portability - portability summary and implementation order