Skip to Content
LYNKORIADocs

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.

AreaTables
Tenancyorganizations, projects, project_environments, service_api_keys
Consoleconsole_users, console_sessions, console_roles, audit_events
Callscall_sessions, media_participant_sessions, recordings, user_suspensions
Messagingconversations, conversation_members, conversation_events, messages, reactions, attachments
Presenceuser_presence (last seen; live state is in Redis)
Pushpush_devices, push_deliveries, notification_preferences
Delivery/usageoutbox_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 carry tenant_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/test prove 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

RoleUsed byPrivileges
lynkoria_migratemigrate job onlyOwns schema objects; CREATE on the database and public schema
lynkoria_appAPI runtime (DATABASE_URL*)SELECT, INSERT, UPDATE, DELETE on tables, sequence usage; no DDL
AdministratorOperators, grants scriptNever 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_MAX per instance (default 10), 5 s connect timeout, 30 s idle timeout.
  • DATABASE_URL_DIRECT: one direct session for LISTEN outbox (pooler transaction mode cannot hold LISTEN).
  • TLS: when DATABASE_CA_CERT is set, URL sslmode parameters 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 + 1 and 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

DataStorage
Service keysKey id plus HMAC-SHA256(pepper, secret); prefix and last 4 characters
Console passwordsscrypt (N=2^17, r=8, p=1), parameters stored per hash
Console sessionsSHA-256 of a 32-byte random token
Webhook secretsNot stored; derived from WEBHOOK_SECRET_MASTER and a version
Push device tokensStored (needed to deliver); deleted on provider "unregistered" responses
Message bodiesPlaintext in PostgreSQL; encrypted at rest by the managed database
AttachmentsPrivate 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).

On this page