Skip to content

Postgres

dataland-postgres is the single relational store for the stack. Two services share it:

  • dataland-agent — conversations, messages, tickets, users, runs.
  • dataland-auth (auth_server.py, shipped inside the same image as the agent) — the staff/dev signup-login user store.

Both connect to the same dataland database. Isolation between them is by table, not by schema or by role. There are no foreign keys crossing the agent's identity tables and the auth server's auth_server_users table — they are independent identity systems by design.
Container dataland-postgres
Image postgres:16-alpine
Database dataland
Host ports 127.0.0.1:5432 (loopback) + ${POSTGRES_PUBLIC_BIND:-100.124.170.43}:5432 (tailnet) — never 0.0.0.0
Service-to-service dataland-postgres:5432 on the dataland-network Docker bridge
Memory / CPU mem_limit: 1g, mem_reservation: 256m, cpus: 1.0
Persistence named volume postgres-data → /var/lib/postgresql/data
Healthcheck pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB} (10s interval, 5 retries)

Recent changes

  • DAT-291 — deploy.sh now runs the agent's real boot guard (assert_boot_required_env) against the production .env before rebuilding, so a DATABASE_URL still carrying the bundled dataland:dataland credentials aborts the deploy instead of crash-looping the freshly built container. Postgres host bindings moved to the *_PUBLIC_BIND tailnet pattern.
  • DAT-286 — dataland-auth mirrors the CMS RS256 signing key (kid dataland-rs256-1) via data/extra_jwks.json; this is file-backed (the auth-data volume), not stored in Postgres. The Postgres-backed user store is unchanged.

Service definitions (compose)

The container is defined in dataland-infrastructure/compose.yml. DAT-80 sizes it: defaults assume a ~128 MB box, so on the 1 GB container the planner stats stay stale and most of the RAM is unused unless the budget is passed explicitly.

postgres:
  image: postgres:16-alpine
  container_name: dataland-postgres
  mem_limit: 1g
  mem_reservation: 256m
  cpus: 1.0
  command:
    - "postgres"
    - "-c"
    - "shared_buffers=256MB"  # (1)!
    - "-c"
    - "work_mem=8MB"  # (2)!
    - "-c"
    - "effective_cache_size=512MB"  # (3)!
    - "-c"
    - "max_connections=100"  # (4)!
  environment:
    POSTGRES_USER: ${POSTGRES_USER:-dataland}  # (5)!
    POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-dataland}
    POSTGRES_DB: ${POSTGRES_DB:-dataland}
  volumes:
    - postgres-data:/var/lib/postgresql/data
  ports:
    - "127.0.0.1:${POSTGRES_PORT:-5432}:5432"  # (6)!
    - "${POSTGRES_PUBLIC_BIND:-100.124.170.43}:${POSTGRES_PORT:-5432}:5432"  # (7)!
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-dataland} -d ${POSTGRES_DB:-dataland}"]  # (8)!
    interval: 10s
    timeout: 5s
    retries: 5
    start_period: 10s
  1. DAT-80 — passed explicitly because the postgres:16-alpine default assumes a ~128 MB box. Sized for the 1 GB container so the page cache and planner stats actually use the available RAM.
  2. DAT-80 — per-sort/hash working memory. Multiplied across concurrent operations, so kept modest against max_connections=100 to avoid OOM on the 1 GB box.
  3. DAT-80 — planner hint for how much memory the OS + Postgres can use for caching. It does not reserve RAM; it just steers plan choice toward index scans.
  4. Hard ceiling on backend connections. The agent's worst-case pool demand (workers × (pool_size + max_overflow) = 120) already exceeds this — see the pool-budget tip below. Raise this and shared_buffers together if you add workers.
  5. Defaults to the documented dataland:dataland local-dev role. A production volume must be initialized with rotated POSTGRES_USER / POSTGRES_PASSWORD — see Rotated, non-default credentials.
  6. Loopback binding for local host tooling and the SSH-tunnel workflow. The ${POSTGRES_PORT:-5432} default keeps both the published and container ports at 5432.
  7. DAT-73 — tailnet interface IP only (default 100.124.170.43), never 0.0.0.0. This is the trust boundary: published on exactly two host IPs, both non-public.
  8. Liveness probe run as the configured role against the configured DB; combined with the 10s interval and 5 retries it gives Compose a ~50s window before marking the container unhealthy.

Host binding is the trust boundary

DAT-73: Postgres is stateful and must be reachable for tooling and tailnet peers, but 0.0.0.0 once exposed 5432 on the public Spectrum IP. It is now bound only to 127.0.0.1 (local host tooling + the SSH-tunnel workflow) and POSTGRES_PUBLIC_BIND (a tailnet interface IP, default 100.124.170.43). Service-to-service traffic goes over the Docker network and is unaffected by host port binding. See Ports.

Connection strings

Each consumer has its own env var. The agent uses the async SQLAlchemy driver (asyncpg); the auth server uses raw asyncpg; Alembic rewrites to a sync driver at runtime.

Consumer Env var Default Driver
dataland-agent DATABASE_URL postgresql+asyncpg://dataland:dataland@dataland-postgres:5432/dataland SQLAlchemy async (asyncpg)
dataland-auth AUTH_DATABASE_URL (falls back to DATABASE_URL, then postgresql://dataland:dataland@localhost:5432/dataland) postgresql://dataland:dataland@dataland-postgres:5432/dataland raw asyncpg pool
Alembic migrations DATABASE_URL (else sqlalchemy.url in alembic.ini) same as the app sync (psycopg2)

The agent's DATABASE_URL carries the +asyncpg suffix; the auth server's AUTH_DATABASE_URL is a plain postgresql:// URL because auth_server.py normalizes any postgresql+asyncpg:// it receives down to postgresql:// before handing it to asyncpg.create_pool (_normalize_postgres_dsn in auth_server.py).

Agent engine (app/db/session.py)

engine = create_async_engine(
    DATABASE_URL,
    echo=False,
    pool_pre_ping=True,  # (1)!
    pool_size=20,  # (2)!
    max_overflow=40,  # (3)!
)
async_session = async_sessionmaker(engine, expire_on_commit=False)  # (4)!
  1. Validates a pooled connection with a cheap SELECT 1 before use, so a Postgres restart surfaces as a transparent reconnect instead of stale-connection errors on the next request.
  2. Steady-state connections per worker. Postgres only — the SQLite path used in tests skips pool sizing entirely.
  3. Burst capacity above pool_size, so each worker can reach 20 + 40 = 60 connections. Combined with the default 2 uvicorn workers, the worst case is 120 — above max_connections=100 (see the tip below).
  4. expire_on_commit=False keeps attributes loaded on committed objects, so the async handlers can still read them after the session commits without triggering a fresh lazy load.

pool_pre_ping=True validates a pooled connection before use (cheap SELECT 1), so a Postgres restart doesn't surface as stale-connection errors on the next request. Pool sizing (pool_size=20, max_overflow=40 → up to 60 connections per worker) is only applied for Postgres URLs; the SQLite path used in tests skips it.

Pool budget vs max_connections

The agent runs uvicorn --workers ${UVICORN_WORKERS:-2}. Each worker owns its own pool, so the worst-case agent connection count is workers × (pool_size + max_overflow) = 2 × 60 = 120 — which already exceeds max_connections=100. In practice the overflow is rarely touched, but if you raise UVICORN_WORKERS for the agent, raise Postgres max_connections (and shared_buffers) to match, or add PgBouncer.

Rotated, non-default credentials (DAT-146 / DAT-291)

The dataland:dataland user/password baked into every compose default and .env.example is a local-dev convenience only. A production deploy that inherits it is talking to publicly documented credentials.

This is enforced at three points:

flowchart TD
    A[.env on the VDS] --> B{deploy.sh pre-build guard<br/>DAT-291}
    B -- "default creds / placeholder" --> X[ABORT before rebuild]
    B -- ok --> C[docker compose up --build]
    C --> D{agent lifespan<br/>assert_boot_required_env<br/>APP_ENV=production}
    D -- "dataland:dataland or localhost" --> Y[RuntimeError → worker exits]
    D -- ok --> E[init_db → serve]

  1. Compose render-time — the agent's own docker-compose.yml uses the :? form so Compose errors before rendering if DATABASE_URL is unset: 

    - DATABASE_URL=${DATABASE_URL:?DATABASE_URL must be set in .env with per-environment unique credentials}  # (1)!

  2. The :? form makes Compose hard-fail at render time with this message if DATABASE_URL is unset or empty — unlike the :- default form, it never silently substitutes a value. This is the first of the three credential guards.

(The infrastructure compose.yml keeps a :- default for dev ergonomics; the boot guard below is what protects production there.)

  1. Pre-deploy guard (DAT-291) — deploy.sh runs the real guard from the current dataland/agent:latest image against the new .env before rebuilding: 

    docker run --rm --env-file .env dataland/agent:latest \
  /app/.venv/bin/python -c "from app.runtime import assert_boot_required_env; assert_boot_required_env()"  # (1)!

  2. DAT-291 — runs the exact boot guard from the last-built image against the new .env, so default/placeholder creds abort the deploy before the rebuild instead of crash-looping the freshly built container. Skipped on the first deploy when no :latest image exists yet.

If it fails, the deploy aborts before the rebuild — preventing the crash-loop outage that motivated the check. Skipped on the very first deploy when no :latest image exists yet.

  1. Boot-time guard (app/runtime.py) — when APP_ENV=production, assert_boot_required_env() raises RuntimeError from the lifespan (which exits the worker; Docker surfaces it as the container exit reason) if DATABASE_URL:
  2. is empty,
  3. still contains the literal dataland:dataland credentials (DEFAULT_DATABASE_CREDS), or
  4. points at localhost / 127.0.0.1 (a prod deploy must use the dataland-postgres network hostname or a managed-DB URL).

Development and CI keep the warn-only path, so a feature branch with default creds still boots.

What to rotate

Rotate POSTGRES_USER / POSTGRES_PASSWORD (initializes the role on a fresh postgres-data volume) and the credentials embedded in DATABASE_URL + AUTH_DATABASE_URL. They must all agree. Generate with e.g. openssl rand -hex 32. Changing POSTGRES_PASSWORD on an existing volume does not re-key the role — ALTER ROLE ... PASSWORD inside the running container, or recreate the volume from a backup.

Schema — agent tables

Defined as SQLAlchemy models in app/models.py (Base.metadata). Every created_at / updated_at is timestamptz post-DAT-61.

erDiagram
    users ||--o{ conversations : has
    users ||--o{ tickets : has
    users ||--o{ runs : has
    conversations ||--o{ messages : contains
    conversations ||--o{ runs : groups
    messages ||--o{ message_images : has
    messages ||--o{ message_sources : has
    messages ||--o{ message_suggestions : has
    conversations }o--|| tickets : "id == ticket_id (museum)"

users

The agent's mobile-visitor identity. users.id IS the JWT sub claim — there is no separate external_id column. The first authenticated request for a given sub writes the row; later requests refresh profile fields that changed in the token (app/db/user_repo.py::get_or_create_user).

Column Type Notes
id text PK the JWT sub
email text, null
full_name text, null DAT mirror of museum-wide JWT claim
location text, null
profile_photo_url text, null
joined_date timestamptz, null
access_permissions text, null JSON-encoded list
stripe_customer_id text, null
created_at timestamptz defaults to now() (app-side _utcnow)

conversations

Column Type Notes
id text PK UUID4, except museum conversations where id == ticket_id
user_id text FK → users.id, indexed
title text, null
mode text, null "general" or "museum"
messages_json text, null denormalized message blob
created_at / updated_at timestamptz updated_at has onupdate

tickets (DAT-66 / refined for DAT-296)

One museum ticket = one user = one museum-mode conversation. The current model is intentionally minimal: the conversation for a ticket is always the conversations row with id = ticket_id, so the mobile client uses ticket_id directly as conversation_id with no lookup. Registration is idempotent (app/db/ticket_repo.py::register_ticket).

Column Type Notes
id text PK the ticket_id
user_id text FK → users.id, indexed
first_seen timestamptz defaults to now()

DAT-296 simplified ticket registration

The old /register and /current endpoints were removed — the first /v1/chat/museum message registers the ticket, and conversation_id == ticket_id. The richer external_id / last_seen / visit_count columns created in migration 0008 were superseded by this slim model (user_ticket_mappings is gone). See Agent.

runs (DAT-65)

One agent invocation, made first-class instead of derived from messages.run_id. Telemetry fields are nullable so the app can fill them opportunistically.

Column Type Notes
id text PK the run id
conversation_id text FK → conversations.id, indexed
user_id text FK → users.id, indexed
started_at / finished_at timestamptz
model text, null e.g. gemini-3.5-flash (DAT-269)
prompt_tokens / completion_tokens int, null
cost_usd float, null
status text, null success / error / cancelled
error text, null

messages.run_id continues to point at this id without a hard FK so historical rows that predate the table aren't blocked.

messages

Column Type Notes
id text PK
conversation_id text FK → conversations.id, indexed
seq int, NOT NULL per-conversation ordering (unique with conversation_id, migration 0003)
run_id text, null soft link to runs.id
role text, indexed user / assistant / system / tool
tool, tool_call_id text, null
text text, null
feedback text, null like / dislike
created_at timestamptz, null tz-lifted in 0006

Message children

Cascade-deleted with their parent message (cascade="all, delete-orphan"):

  • message_images — title, artist, image_path, score, type (default artwork). Populated by search_artwork_images.
  • message_sources — title, uri, confidence, type (default document). RAG citations.
  • message_suggestions — text, sequence. Post-stream follow-up suggestions, now restored on conversation reload (DAT-284).

Each uses a BIGSERIAL-style integer PK (autoincrement) and an indexed message_id FK.

Schema — auth server table

auth_server.py does not use SQLAlchemy or Alembic. It creates and patches its own table at startup via raw asyncpg DDL in _init_db(). This table is independent of the agent's users table.

auth_server_users

Column Type Notes
id BIGSERIAL PK
sub text, UNIQUE UUID minted on signup; the JWT sub
email text, UNIQUE, NOT NULL lowercased on signup/login
hash text, NOT NULL Argon2id (DAT-145)
salt text, NOT NULL "argon2" sentinel for new rows; legacy rows keep their sha256 salt
full_name text, null
location text, NOT NULL DEFAULT ''
profile_photo_url text, null
stripe_customer_id text, null
created_at timestamptz, NOT NULL DEFAULT now()

_init_db() is idempotent: it CREATE TABLE IF NOT EXISTS, then ADD COLUMN any missing columns (checked against information_schema.columns), then backfills a UUID sub into any rows where it is NULL.

Password hashing — Argon2id (DAT-145)

New rows are hashed with Argon2id (argon2-cffi defaults: time_cost=3, memory_cost=64 MiB, parallelism=4, ~70–90 ms on the VDS). Legacy sha256(salt + password) rows still verify via the 64-hex-char path and are opportunistically re-hashed to Argon2id on their next successful login, so the table migrates itself as users return.

Migrations (Alembic)

Schema evolution lives in migrations/versions/ (config in alembic.ini, environment in migrations/env.py). The runs / tickets / profile-field DDL above all arrived as numbered revisions.

0001_baseline                # (1)!
0002_utm_active_uniq
0003_messages_conv_seq_uniq  # (2)!
0004_users_email_lower_uniq
0005_drop_messages_event
0006_messages_created_at_tz
0007_lift_timestamps_to_tz   # (3)!
0008_tickets_table           # (4)!
0009_runs_table              # (5)!
0010_user_profile_fields     # (6)!
  1. Empty marker — the pre-Alembic schema came from create_all. Adopting Alembic on an existing DB means alembic stamp 0001_baseline first, so revisions 0002+ apply cleanly.
  2. Adds UNIQUE(conversation_id, seq) — the constraint that enforces per-conversation message ordering described in the messages table above.
  3. DAT-61 — lifts every created_at / updated_at to timestamptz. After this all agent timestamps are tz-aware.
  4. DAT-66 — introduces the tickets table. Its richer external_id / last_seen / visit_count columns were later superseded by the DAT-296 slim model.
  5. DAT-65 — introduces the first-class runs telemetry table.
  6. Adds the full_name / location / profile_photo_url / joined_date / access_permissions / stripe_customer_id columns to users.

How the URL is resolved

migrations/env.py::_resolve_url() reads DATABASE_URL (falling back to sqlalchemy.url in alembic.ini) and rewrites the async driver to a sync one, because Alembic runs synchronously:

  • postgresql+asyncpg://… → postgresql+psycopg2://…
  • sqlite+aiosqlite:///… → sqlite:///…
  • bare postgresql://… → postgresql+psycopg2://…

So the same DATABASE_URL the app uses works for migrations — no separate migration DSN.

init_db vs migrations

app/db/session.py::init_db() runs at agent boot (from the lifespan, after the production env guard). It is not a migration runner:

  • It instruments the SQLAlchemy engine for observability.
  • It takes a Postgres advisory lock (pg_advisory_xact_lock(8675309)) so two boot-racing workers can't both call create_all().
  • It runs Base.metadata.create_all — which creates missing tables for a fresh DB but never alters existing ones.

The ad-hoc _ensure_schema_migrations DDL that used to ALTER/DROP on every boot was retired in DAT-147; 0001_baseline records that history. On an existing DB, create_all is a no-op and Alembic owns all forward changes.

Apply migrations before bringing the agent up

Operators run alembic upgrade head (with DATABASE_URL set) before the agent serves traffic. For a brand-new environment: let init_db build the schema from the current models, then alembic stamp head so Alembic agrees it's current. For an existing DB adopting Alembic: alembic stamp 0001_baseline, then alembic upgrade head.

docker exec -it dataland-agent /app/.venv/bin/alembic current  # (1)!
docker exec -it dataland-agent /app/.venv/bin/alembic upgrade head  # (2)!
  1. Run from inside the agent container, where DATABASE_URL is already set. Prints the revision Alembic believes is applied — use it to confirm a DB's state before upgrading.
  2. Applies every pending revision up to the latest. Operators run this before the agent serves traffic; init_db's create_all never alters existing tables, so Alembic owns all forward changes.

Reaching it

docker exec -it dataland-postgres psql -U dataland -d dataland  # (1)!

ssh -L 5432:127.0.0.1:5432 ege@100.124.170.43  # (2)!
psql -h 127.0.0.1 -p 5432 -U dataland -d dataland

psql -h 100.124.170.43 -p 5432 -U dataland -d dataland  # (3)!

docker exec dataland-postgres psql -U dataland -d dataland -c '
  SELECT relname, n_live_tup
  FROM pg_stat_user_tables
  ORDER BY n_live_tup DESC
  LIMIT 10
'  # (4)!
  1. On the host — exec straight into the container's psql, bypassing host port bindings entirely.
  2. From a workstation — tunnels the loopback binding (127.0.0.1:5432) over SSH, then connect psql to your now-local 127.0.0.1:5432. Use this when you are not on the tailnet.
  3. From a tailnet peer — connect directly to the POSTGRES_PUBLIC_BIND tailnet IP (default 100.124.170.43), no tunnel needed since that interface is bound by DAT-73.
  4. Top tables by live row count — reads pg_stat_user_tables for a quick size/activity snapshot. Counts are planner estimates (n_live_tup), not an exact COUNT(*).

Metrics

dataland-postgres-exporter (prometheus-community) exposes pg_stat_* snapshots, scraped by Prometheus and surfaced on Grafana → Dataland → Postgres. See Observability. The agent's SQLAlchemy engine is also instrumented (init_db → instrument_sqlalchemy_engine), so query traces appear in the agent's telemetry.

Volumes + backup

postgres-data    -> named volume, /var/lib/postgresql/data  # (1)!
auth-data        -> auth server's RSA keypair + extra_jwks.json (NOT Postgres data)  # (2)!
  1. Holds every conversation, message, ticket, run, and both user tables. Rotating POSTGRES_USER / POSTGRES_PASSWORD on a fresh role requires recreating this volume — back up first (see the danger admonition below).
  2. DAT-286 — file-backed auth secrets: the auth server's RSA keypair plus the mirrored CMS RS256 signing key (extra_jwks.json). This is a separate volume and is not Postgres data, so a Postgres reset does not touch it.

Back up before any destructive op

postgres-data holds every conversation, message, ticket, run, and both user tables. The DR procedure lives in dataland-infrastructure/reports/backup-restore.md. Run it before any reset-stack.sh or before recreating the postgres-data volume to rotate POSTGRES_USER / POSTGRES_PASSWORD.

See also

  • Agent — primary writer of the conversation/ticket/run schema.
  • Auth — auth_server.py, the auth_server_users store, JWKS, and the DAT-286 CMS-key mirror.
  • Ports — host-binding policy for 5432 and the other datastores.
  • Observability — Postgres exporter + SQLAlchemy instrumentation.