Postgres

In production, Vega stores all structured metadata in Postgres using a JSONB document store pattern — not a traditional ORM with one table per entity. Understanding this pattern is key to querying and debugging the database.

The JSONB document pattern

Every domain entity table shares the same structure:

CREATE TABLE scans (
    record_key   TEXT PRIMARY KEY,     -- domain ID (e.g., scan_id)
    payload      JSONB NOT NULL,        -- the full DomainModel serialized as JSON
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The payload column holds the complete Pydantic DomainModel as JSON. No separate columns for individual fields (except indexes on common query paths). This means:

New fields require no migration — add a field with a default to the Pydantic model and it works immediately
Queries use JSONB operators — e.g., payload->>'state' = 'running' or payload @> '{"state":"running"}'
Migrations handle structural changes — new tables, indexes, and non-additive changes still require SQL migrations

What goes in Postgres vs S3

Postgres stores: anything that needs to be queried, filtered, or updated — project lists, scan status, finding severity counts, worker heartbeats, billing ledger, event sequences.

S3 stores: large binary or text objects that would be inefficient in a database — source code archives, scan event JSONL files, debug bundles, generated threat models.

The Postgres payload for a scan or artifact holds the S3 object reference (URI + SHA256 + size); the actual file lives in S3.

Tables

Table	Purpose
`workspace_projects`	`WorkspaceProject` records
`repositories`	`Repository` records
`source_snapshots`	`SourceSnapshot` records
`repository_ingest_jobs`	`IngestJob` records
`scans`	`ScanRecord` records
`findings`	`FindingRecord` records
`scan_artifacts`	`ArtifactRecord` references
`planning_artifacts`	Cached planning artifact bundles
`domain_events`	`DomainEvent` records (append-only, sequence-ordered)
`idempotency_keys`	Billing and external API idempotency
`usage_ledger`	Token/cost usage records
`billing_customers`	Stripe customer IDs
`billing_promotion_campaigns`	Promotional credit campaigns
`billing_spend_limits`	Per-user monthly spend limits
`github_connections`	GitHub App installation records
`repository_ingest_jobs`	Repository ingest job lifecycle
`user_sub2api_keys`	Per-user Sub2API key records
`sub2api_users`	Sub2API scan key lifecycle
`scan_log_segments`	Scan log read model
`users`, `api_keys`, `sessions`, `refresh_tokens`, etc.	Auxiliary JSONB records
`schema_migrations`	Applied migration tracking

`GenericRecordStore`

Many auxiliary entities use GenericRecordStore (adapters/storage/postgres/) — a single generic_records table keyed by (record_type, record_key):

CREATE TABLE generic_records (
    record_type  TEXT NOT NULL,
    record_key   TEXT NOT NULL,
    payload      JSONB NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (record_type, record_key)
);

Record types stored here include: users, api_keys, refresh_tokens, sessions, git_uploads, github_connections, worker_heartbeats, planning_artifacts, special_threat_modeling, billing_promotions, billing_entitlements, billing_holds, billing_spend_limits, sub2api_scan_keys, usage_ledger, finding_daily_rollups, workspace_revisions.

`domain_events` table

The domain events table has a different structure to support sequence-ordered polling:

CREATE TABLE domain_events (
    aggregate_id TEXT NOT NULL,      -- typically scan_id
    sequence     INTEGER NOT NULL,
    event_type   TEXT NOT NULL,
    payload      JSONB NOT NULL DEFAULT '{}'::jsonb,
    occurred_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (aggregate_id, sequence)
);
CREATE INDEX ON domain_events (aggregate_id, occurred_at);
CREATE INDEX ON domain_events (event_type, occurred_at);

AWS context

RDS Postgres is AWS's managed database service. Vega uses a Postgres-compatible RDS instance. AWS handles backups, patching, and failover.

Aurora Postgres is an AWS-native database engine with Postgres compatibility. The Terraform database module can be configured for either RDS Postgres or Aurora.

The Terraform module is at infra/terraform/modules/database/main.tf.

Connection configuration

VEGA_PERSISTENCE_BACKEND=postgres
VEGA_DATABASE_URL=postgresql://username:password@hostname:5432/vega

DATABASE_URL (without the VEGA_ prefix) is also accepted, for compatibility with tools that set it automatically.

In AWS, the database URL is stored in Secrets Manager and injected into ECS task definitions via VEGA_SECRETS_ARN. The application never sees the raw credential at deploy time.

Querying the database

Useful queries for debugging:

-- Find all running scans
SELECT record_key, payload->>'state', payload->>'created_at'
FROM scans
WHERE payload->>'state' = 'running';

-- Find findings for a specific scan
SELECT record_key, payload->>'severity', payload->>'title'
FROM findings
WHERE payload->>'scan_id' = 'your-scan-id';

-- Get recent domain events for a scan (ordered)
SELECT event_type, payload, sequence, occurred_at
FROM domain_events
WHERE aggregate_id = 'your-scan-id'
ORDER BY sequence;

-- Check worker heartbeats
SELECT record_key, payload->>'last_heartbeat_at'
FROM generic_records
WHERE record_type = 'worker_heartbeats'
ORDER BY payload->>'last_heartbeat_at' DESC;

-- Check applied migrations
SELECT * FROM schema_migrations ORDER BY applied_at;

Debugging

VEGA_PERSISTENCE_BACKEND=postgres but connecting to JSON: Check that the env var is actually set in the process. Check GET /api/ops/limits — it returns the active configuration.

Connection refused: 1. Is Postgres running? (pg_isready -h hostname -p 5432) 2. Is VEGA_DATABASE_URL correct? 3. In AWS: can the ECS task security group reach the RDS security group on port 5432?

Migrations not applied: Run python scripts/run-db-migrations.py (local) or scripts/aws/run-migrations.sh dev (AWS). Check the schema_migrations table.

JSONB query returns no results: Verify the field path. Use SELECT payload FROM scans LIMIT 1 to inspect the actual structure.

Slow queries: Use EXPLAIN ANALYZE. Add indexes on frequently queried JSONB paths in a new migration (e.g., CREATE INDEX ON scans ((payload->>'state'))).