PostgreSQL

Purpose

Describe how Get2Dial uses PostgreSQL (with the TimescaleDB extension) as the system of record for tenancy, configuration and time-series call data.

Overview

PostgreSQL holds all durable control-plane state: tenants, users, campaigns, leads, queues, routing, dispositions, recordings and audit. The TimescaleDB extension (enabled in migration 001) turns the two high-volume tables into hypertables, partitioned on time in 7-day chunks:

cdr — Call Detail Records, partitioned on created_at. Composite primary key (id, created_at) because Timescale requires the partition key in every unique index.
audit_logs — the append-only control-plane mutation ledger, partitioned on occurred_at.

The schema is managed by ordered SQL migrations in migrations/ (currently 001–065), applied by the one-shot cmd/migrate runner.

Configuration

The control plane connects via DATABASE_URL (preferred) or the discrete DB_* variables:

DATABASE_URL=postgres://USER:PASSWORD@HOST:5432/get2dial?sslmode=require
# or the components (DATABASE_URL takes precedence when set):
DB_HOST=...           # default localhost
DB_PORT=5432
DB_USER=get2dial
DB_PASSWORD=...
DB_NAME=get2dial
DB_SSLMODE=require    # require between VPS hosts; disable only on a trusted private net
DB_POOL_SIZE=10

Schema changes run as a one-shot before any app service starts. See the Bootstrap runbook.

Examples

-- Recent CDRs for a tenant (hypertable, time-bounded)
SELECT created_at, direction, disposition
FROM cdr
WHERE tenant_id = $1
  AND created_at >= now() - interval '24 hours'
ORDER BY created_at DESC;

Notes

Run migrations as an idempotent, ordered step — never hand-edit schema in prod.
Time-series tables rely on TimescaleDB; confirm the extension is installed.
Row-level security is enabled on tenant tables (migration 005 onward), keyed on the app.tenant_id session variable — a NULL setting returns no rows (fail-closed). See Security.
Back up with logical dumps and WAL archiving — see Backups.