Database Architecture

ELI5

Postgres holds everything that matters: users, links, keys, achievements, billing records, paid-link receipts and, very likely, click events too. Redis holds short-lived things: rate-limit counters and a five-minute redirect cache. ClickHouse is an optional analytics store the API uses only if it can reach one when it starts. Local development runs all three in Docker; production has a Railway Postgres and the API, and Redis and ClickHouse status there is not on record.

Technical Deep Dive

Source: purr-api migrations/000001…000015 (*.up.sql), cmd/server/main.go:82-122, 162-172, internal/database/postgres.go:21-31, internal/database/redis.go:89-149, internal/database/clickhouse.go:39-48 (origin/main bb6cfc2); smo1-io CLAUDE.md:72-79 and docs/launch/2026-10-09/session-ledger.md rows 2026-10-17T17:31Z, 2026-10-18T08:25Z, 2026-10-18T14:35Z, 2026-10-18T18:50Z (1f19667); purr-api docs/decisions/2026-10-24-sell-through-toll.md ruling 7, read 2026-10-25.

Stores

flowchart TB
    API[purr-api]
    subgraph PG["Postgres, source of truth"]
        U[users]
        L[links]
        K[api_keys]
        A["achievements, user_achievements, xp_events"]
        D[custom_domains]
        PA[protection_attempts]
        V[link_visitors]
        C[link_clicks fallback]
        B["webhook_events, payments, revoked_subscriptions"]
        X[x402_payments]
    end
    subgraph RD["Redis"]
        RL["rate:id:window"]
        RC["link:v3:id, 5 min"]
    end
    CH[(ClickHouse link_clicks, optional)]
    API --> PG
    API --> RD
    API -.->|"only if ping succeeded at boot"| CH

Postgres

• Migrations are embedded and run on every boot with golang-migrate (main.go:95-98). Production was at schema_migrations = 15, dirty=f on 2026-10-18.
• Production is Postgres 18; integration tests moved to postgres:18-alpine in purr-api #67. litter-box still runs 16 locally.
• User ids are text since 000005 (originally uuid, changed for BetterAuth-style ids; Airlock subjects fit the same column).
• Pool: pgxpool, MaxConns 25 by default (postgres.go:31).

Migration history

timeline
    title purr-api schema milestones
    000001-000002 : users, links, api_keys, achievements, xp_events, enums
    000003 : Polar billing columns and webhook_events
    000004-000006 : link protection, text user ids, redirect_mode
    000007-000010 : custom_domains, link_clicks, payments ledger, link_visitors
    000011 : x402-anon principal and x402_payments
    000012-000013 : production drift repairs, unlocked_at nullable, inet and enum conversions
    000014-000015 : drop BetterAuth tables and a duplicate index

000012 and 000013 exist because production’s oldest tables predate 000001/000004: user_achievements.unlocked_at was NOT NULL DEFAULT now() (it broke the demo seed) and protection_attempts.ip_address was varchar(45), which made the per-IP brute-force query error and be skipped in production until 000013 (2026-10-18).

Billing tables under the TOLL decision

Ruling 7 of the accepted 2026-10-24 decision: users.polar_customer_id, polar_subscription_id, subscription_status, subscription_current_period_end, subscription_cancel_at_period_end, subscription_updated_at, and the payments and revoked_subscriptions tables stop being written once sell-through lands. Dropping them is a separate operator-gated migration. Until the code changes, the Polar webhook still writes them (smo1-014).

Redis

Fixed-window rate counters rate:{identifier}:{window} and the redirect cache link:v3:{identifier} with a 5-minute TTL. If Redis fails at boot, purr-api runs without rate limiting (main.go:103-114). Whether production has a Redis is UNVERIFIED: the 2026-10-18 production census of the Railway project listed only Postgres and purr-api.

ClickHouse

Pinged once at boot from CLICKHOUSE_HOST (default localhost); on failure the Postgres link_clicks table is the click store. Columnar storage would make window aggregates over large click volumes much cheaper than Postgres row scans, which is why the code prefers it, but smo1’s production click volume does not need it yet and provisioning is deferred (M4.5).

Local stack (litter-box)

Postgres 16 (:5432), Redis 7 (:6379), ClickHouse 24 (:8123/:9000), Mailpit (:1025/:8025), Prometheus (:9090), Grafana (:3002). MinIO was removed 2026-10-21 (litter-box #16).

Key Terms

• schema_migrations — golang-migrate’s version table; 15 in production as of 2026-10-18
• x402-anon — the system user that owns paid anonymous links (000011)
• link_visitors — (link_id, visitor_id) set behind links.unique_visitors (000010)
• dormant columns — Polar-era billing state kept but unwritten after sell-through

Q&A

Q: Which database is the source of truth for users and links? A: Postgres, on Railway in production (Postgres 18).

Q: What is Redis used for? A: Rate-limit counters (origin tier limiter, x402 per-IP and facilitator budgets) and purr-api’s five-minute redirect cache for its own /:identifier route. It holds nothing durable.

Q: Why is ClickHouse better than Postgres for analytics? A: It stores columns separately and aggregates them vectorised, so counting and grouping millions of click rows by day, country or referrer is far cheaper than scanning Postgres rows. smo1 keeps a Postgres fallback because ClickHouse is not provisioned in production yet.

Q: Which tables and columns does the TOLL decision leave dormant, and which migration dropped the BetterAuth tables? A: The Polar ids and subscription_* columns on users, plus the payments and revoked_subscriptions tables. Migration 000014 dropped the BetterAuth user, account, session and verification tables, deployed 2026-10-18 after an offline backup.

Examples

Before writing a production check query, confirm the target with the railway-target-disambiguation procedure, then read schema_migrations: anything other than the latest migration number means the running purr-api has not booted the newest image yet, because migrations run at startup.