Database
PostgreSQL schema ownership, tenancy, roles, timeouts and access patterns.
Reviewed 2026-11-13 against packages/db/src/schema.ts, packages/db/drizzle/*.sql and infra/opentofu/platform/files/database-grants.sql.
PostgreSQL is the source of truth for every durable record. Redis/Valkey holds only presence leases, rate-limit counters and worker locks; losing Redis loses no data.
Schema
26 tables in two migrations (0000_init.sql, 0001_platform_extensions.sql), defined with Drizzle in packages/db/src/schema.ts.
| Area | Tables |
|---|---|
| Tenancy | organizations, projects, project_environments, service_api_keys |
| Console | console_users, console_sessions, console_roles, audit_events |
| Calls | call_sessions, media_participant_sessions, recordings, user_suspensions |
| Messaging | conversations, conversation_members, conversation_events, messages, reactions, attachments |
| Presence | user_presence (last seen; live state is in Redis) |
| Push | push_devices, push_deliveries, notification_preferences |
| Delivery/usage | outbox_events, webhook_events (inbound dedupe), project_usage_daily |
Migrations are additive (expand → migrate → contract). The App Platform migrate pre-deploy job runs node dist/migrate.js with the migration role before new code receives traffic.
Tenant isolation
- Every product row carries
environment_id; messaging rows also carrytenant_id. Both come from the verified service key or messaging token, never from request bodies. - Composite foreign keys
(conversation_id, environment_id, tenant_id)on members, messages, events and attachments make a cross-tenant reference impossible at the schema level, independent of application code. - Console data is scoped by
organization_id; role checks filter grants to the project's organization. - Tests in
apps/api/testprove cross-project, cross-environment and cross-tenant denial against a real database (see security).
Row-level security was evaluated and is not enabled. All access goes through one runtime role whose queries are already scoped and tested, and RLS would need a per-request SET of tenant context, which the transaction pooler does not preserve between statements. Composite keys give the schema-level guarantee RLS would add for messaging. Revisit if a second application or analyst access shares the database (ADR 007).
Roles and connections
| Role | Used by | Privileges |
|---|---|---|
lynkoria_migrate | migrate job only | Owns schema objects; CREATE on the database and public schema |
lynkoria_app | API runtime (DATABASE_URL*) | SELECT, INSERT, UPDATE, DELETE on tables, sequence usage; no DDL |
| Administrator | Operators, grants script | Never configured in the API |
database-grants.sql (applied by OpenTofu on every environment, terraform_data.database_grants) revokes public schema access from PUBLIC, grants runtime DML through default privileges, and sets statement_timeout = 30s and idle_in_transaction_session_timeout = 60s on lynkoria_app. Role-level settings work through the transaction pooler, which rejects them as connection parameters. A local test proved runtime DDL is denied (2026-11-12 review).
Connections (packages/db/src/client.ts):
DATABASE_URL: pooled (PgBouncer transaction mode),DATABASE_POOL_MAXper instance (default 10), 5 s connect timeout, 30 s idle timeout.DATABASE_URL_DIRECT: one direct session forLISTEN outbox(pooler transaction mode cannot hold LISTEN).- TLS: when
DATABASE_CA_CERTis set, URLsslmodeparameters are stripped and the CA is verified (rejectUnauthorized: true); production refuses to start without it.
Indexes and access patterns
Hot paths have targeted indexes: active calls per environment, open participant sessions, conversations by activity, members by user, message retention scans, attachments by message, pending outbox and push deliveries, recordings by call/environment, audit by time and project, keys by environment and sessions by user. Messages are read by (conversation_id, seq), the primary ordering key.
Console queries: calls per project newest first (call_sessions_project_started) and delivery history per environment (outbox_events_env_created), both DESC NULLS FIRST to match the queries (migration 0005). Usage analytics read only the rollups project_usage_hourly (primary key environment and hour) and project_usage_daily, never raw rows (ADR 025).
Concurrency control:
- A send locks the conversation row, allocates
last_seq + 1and inserts message, event and outbox rows in one transaction. - The concurrent-room quota uses a per-environment advisory lock.
- The outbox worker claims rows with
FOR UPDATE SKIP LOCKED, so multiple workers never double-send. - History snapshots and read watermarks use
REPEATABLE READ.
Load results are in load testing. Database contention at target concurrency has not been measured on managed infrastructure.
Sensitive data
| Data | Storage |
|---|---|
| Service keys | Key id plus HMAC-SHA256(pepper, secret); prefix and last 4 characters |
| Console passwords | scrypt (N=2^17, r=8, p=1), parameters stored per hash |
| Console sessions | SHA-256 of a 32-byte random token |
| Webhook secrets | Not stored; derived from WEBHOOK_SECRET_MASTER and a version |
| Push device tokens | Stored (needed to deliver); deleted on provider "unregistered" responses |
| Message bodies | Plaintext in PostgreSQL; encrypted at rest by the managed database |
| Attachments | Private bucket objects; only keys are stored |
Encryption at rest is provided by DigitalOcean Managed PostgreSQL and Spaces, not by the application. Backups are managed with point-in-time recovery; restoring them has not been exercised yet (blockers).
Retention and deletion
audit_events is append-only: triggers reject UPDATE, DELETE and TRUNCATE (migration 0002). Hourly usage rows older than 35 days are deleted by the hourly retention job; daily usage rows are kept.
The retention worker erases expired messages (emitting ordered deletion tombstones), removes reactions and deletes attachment objects, including the quarantine copy. User-deleted files and abandoned uploads older than a day are cleaned without a policy. Recordings are deleted after RECORDING_RETENTION_DAYS. Retention per product is still an open decision (ADR 012).