Database

database

The driver field selects between SQLite (embedded, zero-dependency) and PostgreSQL (required for multi-instance deployments). When driver is omitted, the orchestrator infers postgres if host is set, otherwise sqlite.

SQLite (default for single-instance):

database:
  driver: sqlite
  path: "s3-orchestrator.db"     # default: s3-orchestrator.db

SQLite requires no external dependencies. The database file is created automatically on first start. Advisory lock-based leader election is replaced by a process-local mutex, so multi-instance deployments are not supported with SQLite.

PostgreSQL (required for multi-instance):

database:
  driver: postgres
  host: "db.example.com"        # required
  port: 5432                     # default: 5432
  database: "s3orchestrator"     # required
  user: "s3orchestrator"         # required
  password: "${DB_PASSWORD}"
  ssl_mode: "require"            # default: require (use "disable" for local dev)
  max_conns: 50                  # default: 50; size to 2-3x max_concurrent_requests
  min_conns: 10                  # default: 5
  max_conn_lifetime: "5m"        # default: 5m

Pool settings (max_conns, min_conns, max_conn_lifetime) control the pgx connection pool. Size max_conns to 2-3x your max_concurrent_requests setting. See Performance Tuning - Connection Pool Sizing for detailed guidance.

Engines and schema

SQLite is the default for single-instance use; PostgreSQL is required for multi-instance deployments.

The orchestrator supports two metadata-store engines:

SQLite (default) — embedded, zero-dependency, single-instance. Schema is applied at startup from a single consolidated schema.sql.
PostgreSQL — required for multi-instance deployments. Connects via pgx/v5 pools and auto-applies versioned migrations on startup using goose; migration files are embedded in the binary and tracked via a goose_db_version table so only unapplied migrations run.

Engine-agnostic orchestration lives in internal/store/core/ (transactional business logic against a TxAdapter interface). Each engine package (internal/store/postgres/, internal/store/sqlite/) is a thin adapter that implements the same TxAdapter, so the same code drives both engines.

The schema currently provisions:

Table	Purpose
`backend_quotas`	Per-backend byte limits, usage counters, and orphan bytes tracking
`object_locations`	Maps object keys to backends with size tracking
`multipart_uploads`	In-progress multipart upload metadata
`multipart_parts`	Individual parts for active multipart uploads
`backend_usage`	Monthly per-backend API request and data transfer counters
`cleanup_queue`	Retry queue for failed backend object deletions
`cleanup_dlq`	Dead-letter for `cleanup_queue` rows that exhausted retries; surfaces unrecoverable orphans for operator action
`pending_objects`	In-flight PUT intents recorded before the backend write so a DB outage can’t silently destroy the prior copy
`notification_outbox`	Durable webhook event delivery queue

Quota updates are transactional: object location inserts/deletes and quota counter changes happen atomically.

All Postgres SQL queries live in internal/store/postgres/sqlc/queries/ as annotated .sql files. Type-safe Go code is generated by sqlc into internal/store/postgres/sqlc/. To regenerate after editing queries:

make generate