# Database Overview

How to set up and manage databases across projects.

## Architecture

- **Local**: Docker container (source of truth)
- **Production**: Fly.io Managed Postgres (or Unmanaged for legacy projects)

## Setup

See `source/` for the full template (docker-compose, Makefile, schema, .env).

## Migrations

See `migrations.md` for the full workflow.

## Port standard

Each module gets a block of 100 ports. The first port (x00) is always the database.

    x00  →  Database (Postgres, Docker)
    x01  →  API / Backend
    x02  →  Webapp / Frontend
    x03  →  Worker / Daemon
    x04+ →  Additional services

Current assignment:

    7100  media-buyer  DB
    7200  psmailer     DB
    7300  leaderboard  DB
    7500  radar        DB
    7600  crm          DB

Modules without a dedicated DB (use the Rust API or have none): api (7002), broker (7004), pulse (7005), support (7006).

## Database inventory on Fly.io

### Managed Postgres (fly mpg)

| Cluster ID | Name | Org | Region | Connected apps |
|---|---|---|---|---|
| 1zvn90kj527rkpew | cryptonite-postgres-cluster | personal | iad | leaderboard-api, leaderboard-simulator |

Connection: `flyctl mpg proxy 16380:5432 -a cryptonite-db`
User: fly-user / DB: fly-db
Used by: API, Pulse, Leaderboard

### Unmanaged Postgres (fly apps)

| App | Module | Tunnel port | User | DB |
|---|---|---|---|---|
| psmailer-db | PSMailer | 5433 | psmailer_m | psmailer_m |
| cryptonite-crm-db | CRM/Outlook | 5439 | (see secrets) | (see secrets) |
| cryptonite-radar-api-db | Radar API | - | (see secrets) | (see secrets) |
| cryptonite-radar-db | Radar | - | (see secrets) | (see secrets) |
| leaderboard-db-cryptonite | Leaderboard (legacy) | - | - | - |
| cast-db | Cast (org: charms-inc) | - | - | - |

---

## Rule: idle_in_transaction_session_timeout (MANDATORY)

**Every new database on Fly.io MUST set:**

```sql
ALTER ROLE <user> SET idle_in_transaction_session_timeout = '1800000';  -- 30 minutes
```

**Why:** an idle transaction holds MVCC snapshots, blocking vacuum and
causing progressive bloat. A hung pg_dump or a zombie connection can
accumulate hours undetected. With this timeout, PostgreSQL
automatically kills any transaction that stays idle for more than 30
minutes.

**Applied on (2026-04-05):**
- ✅ cryptonite-postgres-cluster (fly-user) — 30 min
- ✅ psmailer-db (psmailer_m) — 30 min
- ⬜ cryptonite-crm-db — pending (needs prod credentials)
- ⬜ cryptonite-radar-api-db — pending
- ⬜ cryptonite-radar-db — pending

**Checklist when creating a new DB:**
1. Create the DB on Fly.io (managed or unmanaged)
2. Set `idle_in_transaction_session_timeout = '1800000'` on the primary role
3. Verify with `SHOW idle_in_transaction_session_timeout;`
4. Add an entry to this inventory

---

## Theory and standards

See `../stack/databases.md` for theory, connections, and best practices.