Skip to content

Database & migrations

PostgreSQL 16+ is the only production-supported store. SQLite is a development convenience, not a migration path.

Provisioning Postgres

Ampora needs:

  • a dedicated database (ampora by default),
  • a dedicated role that owns DDL on that database,
  • TLS to the server (your connection string must include SSL Mode=Require),
  • the pgcrypto extension is not required (Ampora does its own AES-GCM-256 wrap in the application layer).

A minimal provisioning SQL:

CREATE USER ampora WITH PASSWORD '...';
CREATE DATABASE ampora OWNER ampora;
GRANT ALL PRIVILEGES ON DATABASE ampora TO ampora;

For multi-tenant Hard Isolation, also enable Row-Level Security (Ampora's migrations create the policies):

ALTER DATABASE ampora SET row_security = on;

Managed offerings (RDS, Cloud SQL, Aiven, Crunchy Bridge, …) work out of the box; just hand the connection string to Ampora and let it migrate.

Connection string

Host=db.acme.io;Port=5432;Database=ampora;Username=ampora;Password=...;SSL Mode=Require;Trust Server Certificate=false;Include Error Detail=false

Notes:

  • SSL Mode=Require is mandatory in production. Trust Server Certificate=false forces validation against the system trust store.
  • Include Error Detail=false keeps personal data out of error messages. true is fine in development, never in production.
  • Connection pooling is handled by Npgsql; use MaxPoolSize=... if you need to ceiling it (e.g. behind PgBouncer in transaction-pooling mode, set Server Compatibility Mode=NoTypeLoading and pin a small pool).

Migrations

Ampora uses Entity Framework Core's migrations. The image starts up:

  1. opens a connection,
  2. compares __EFMigrationsHistory to the bundled migration set,
  3. applies the missing ones inside a transaction.

The behaviour is gated by AMPORA_AUTO_MIGRATE:

  • 0 (default) — log a warning if the DB is behind, but do not apply. Use this when migrations should run as a separate Job and the running pods should never write DDL.
  • 1 — apply on startup. Suitable for single-instance and small deployments.

Running migrations out-of-band

For tightly controlled clusters, run a Job that exec's the same image with the --migrate-only flag:

apiVersion: batch/v1
kind: Job
metadata:
  name: ampora-migrate
  namespace: ampora
spec:
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      serviceAccountName: ampora
      containers:
        - name: migrate
          image: registry.acme.io/ampora/web:1.4.2
          args: ["--migrate-only"]
          envFrom:
            - configMapRef: { name: ampora-config }
            - secretRef:    { name: ampora-secrets }

The job exits 0 on success and non-zero with an error log on failure. Failure leaves the schema at the last successful migration — EF wraps each migration in a transaction.

Rollback

EF migrations are forward-only by default. Ampora does not ship "down" migrations. Roll forward, fix-forward, restore-from-backup if you need the previous schema. See Backup & restore.

Concurrent startup

Multiple Ampora pods starting at the same time race for the migration advisory lock — only one applies migrations, the others wait. There is no risk of double-application.

JSONB and indexes

The schema uses JSONB for:

  • agent_descriptions.labels — searched by Dynamic Group selectors.
  • lint_rules.body — DSL bodies, occasionally inspected.
  • audit_events.before_value / after_value — opaque snapshots.

Migration Phase11_JsonbIndexes promotes the high-cardinality columns from TEXT to JSONB and adds GIN indexes with jsonb_path_ops. Ensure your Postgres version supports jsonb_path_ops (≥ 9.5; trivially true for 16).

Backups

PostgreSQL backups are yours to take. Ampora's persistent state is entirely in PostgreSQL — back the database up and you back Ampora up.

  • For managed Postgres, enable PITR on the offering.
  • For self-managed, use pgBackRest or wal-g for incremental + WAL archiving.
  • Test restores: an untested backup is not a backup.

The application-level encryption key (KeyProtection:MasterKey) lives outside the database. Back it up alongside the DB, in your secret manager. Without the master key, encrypted-at-rest fields (CA private keys, peer secrets, GitOps credentials) are unrecoverable.

See Backup & restore for the full runbook.

Resetting the database (dev only)

docker compose down -v          # for the local stack
# or, for any Postgres:
psql -c "DROP DATABASE ampora;"
psql -c "CREATE DATABASE ampora OWNER ampora;"

Then restart Ampora; it will re-migrate from scratch.

Never do this in production. The audit log alone is irreplaceable.