Skip to main content

docs/database.md

Imported Content​

Database Persistence

Gateway persistence now relies on PostgreSQL and no longer uses in-memory resource state.

Required environment​

  • DATABASE_URL
  • PGSSLMODE (disable for local, require for managed providers)

Apply migrations​

  1. infra/sql/0001_init.sql
  2. infra/sql/0002_psa_resources.sql
  3. infra/sql/0003_idempotency.sql
  4. infra/sql/0004_event_pipeline.sql
  5. infra/sql/0005_job_queue.sql
  6. infra/sql/0006_security_hardening.sql
  7. infra/sql/0007_configuration_plane.sql
  8. infra/sql/0008_job_metadata.sql
  9. infra/sql/0009_workflow_execution_metadata.sql
  10. infra/sql/0010_worker_runtime.sql
  11. infra/sql/0011_portal_invites.sql

Or use scripts/apply-migrations.sh with DATABASE_URL set. Optional local sample data can be loaded with scripts/seed-dev-data.sh.

The psa_resources table stores validated JSON documents per resource type with strict workspace partitioning.

Notes​

  • Core workflow/audit tables (workflow_executions, audit_credentials) are used by execution routes.
  • Workflow execution metadata includes input_ref, output_ref, evidence_ref, duration_ms, retry_count, and failure_code.
  • Workflow callback payloads are captured in workflow_callbacks.
  • API resource CRUD routes persist to psa_resources.
  • Idempotency records are stored in idempotency_records.
  • Telemetry batches and routed actions are persisted in telemetry_ingest_batches and outbox_events.
  • Async import/export and other queued work are tracked in background_jobs with lifecycle fields (queued_at, started_at, completed_at, retry_count, artifact_url, error metadata).
  • Runtime worker freshness and status are tracked in worker_runtime_status.
  • Portal invite JWT lifecycle and revocation are tracked in portal_invites.
  • Webhook replay receipts are tracked in webhook_receipts.
  • Runtime control-plane config is stored in platform_settings, workspace_settings, and encrypted runtime_secrets.