paiddaily.io Postgres

This is the deep-dive on the specific Postgres deployment behind paiddaily.io. The engine itself is documented at Postgres; this page is about how it’s shaped, how it’s changed, and the conventions every engineer (and agent) has to respect when touching it.

What it is

A single self-hosted Postgres database (pg17 with pgvector, running in a container alongside the other GL data services). Two domain schemas live alongside each other:

• public — the consumer SaaS surface. Users, subscriptions, accounts, tickers, watchlists, positions, scanner saved-state. Anything user-facing or auth-adjacent.
• defi — the on-chain catalyst layer. Aerodrome pools and vote markets, Pendle markets and snapshots, opportunity rows, position state synced from wallets, veAERO locks. Anything that lives off-chain because it was pulled from chain.

Roughly two dozen tables per schema; forty-plus stored procedures in public; a comparable surface of upsert procs and sync-state machines in defi. The migration ledger is at eighty-eight files and growing — every schema change shows up there first.

Why Postgres for this product

The questions paiddaily.io asks are mostly tabular and transactional: "does this user have an active subscription," "what are the open positions for this account," "insert this Pendle market snapshot atomically with its catalyst link." Concurrent readers and concurrent writers. Real foreign keys. Real check constraints. Real transactions.

There was never a serious argument for any other primary store. The graph layer (Neo4j) holds canonical state for the system — Behavior nodes, Topic nodes, agent memory. Postgres holds canonical state for the product.

Self-hosted pg17, specifically, because:

• The engine is vanilla Postgres — every escape hatch is open, every extension installable.
• Auth is handled by a dedicated Keycloak realm, not bundled into the data layer.
• One shared Postgres container hosts each product’s database side-by-side (paiddaily, agents, etc.) — cheap, boring, one set of backups to manage.
• The graduation path to a managed Postgres (RDS, Neon, Cloud SQL) is pg_dump | psql. Zero lock-in.

The shape

Public schema — the SaaS surface

• users, accounts, subscriptions — identity and billing. Stripe webhooks land here.
• tickers, ticker_price, pendle_market, pendle_market_snapshot — the market state cache. Refreshed by background workers; the source of truth for everything downstream.
• positions, closed_trades, tastytrade_connections — the trader’s book of business.
• saved_scans, user_watchlist, user_goal — the per-user product state. What you saved, what you’re watching, what you said you’re working toward.
• pendle_catalyst, pendle_catalyst_market, pendle_catalyst_suggestion — the catalyst lineage. Suggestions get reviewed; approved suggestions become catalysts; catalysts attach to markets.
• pendle_regime_thresholds — the tunable knobs of the recommendation surface. Rows, not config files, so changes are auditable and rollback-able.
• domain_events, domain_event_type, domain_event_consumers, audit_log — the event-sourcing and audit layer. Every state-mutating proc writes here.

Defi schema — the on-chain layer

• aero_pool, aero_vote_market, aero_claim_event — Aerodrome state. Pools we track, vote markets where bribes live, claim events for veAERO holders.
• veaero_lock, veaero_lock_sync_state, veaero_alert_subscription, veaero_nudge_log — the veAERO surface: who has locks, when they expire, who wants reminders, who got nudged.
• pool_state, pool_state_sync_state, wallet_position_state, wallet_sync_cursor — the wallet-level position sync. Cursor-based incremental fetch; idempotent writes; replay-safe.
• opportunity — the unified opportunity surface that Plutus and the product surface read from. The "what should I do today" rows.
• slipstream_position, position_il_anchor — Aerodrome Slipstream concentrated-liquidity positions and the IL anchors used for P&L attribution.
• ticker, ticker_override, ticker_admin_review, ticker_research_run — the ticker research pipeline. Raw tickers in; admin overrides; LLM-driven research runs; reviewed results out.

The two schemas talk through joins; they don’t talk through application code. Foreign keys cross schemas where it’s safe; otherwise the procs do the lookup.

Conventions — the non-negotiables

These rules apply to every change. They came from incidents; they get enforced by review.

1. Migrations are the only schema authority

Every change to the database starts as a new file in database/migrations/NNN_what_it_does.sql. Ordered, append-only. The number namespace is shared across both schemas. Apply via psql -f; commit the migration and the regenerated schema mirror together.

Past migrations are never edited. Ever. A mistake in migration 042 is fixed by migration 092, not by rewriting 042.

2. No inline SQL in app code — procs are the API

App code calls a function; the function does the work. State-mutating logic lives in PL/pgSQL stored procedures. This is non-negotiable per the project’s engineering standards (Smith owns the rule).

Why: makes diffs reviewable, makes the contract obvious, makes the auth boundary the database itself, makes tests possible against the unit that actually runs in production.

3. Every state-mutating proc emits a domain event

ADR 0007 — every proc that writes data also writes a row to domain_events via fn_emit_domain_event. The shape is (event_type, aggregate_id, payload_jsonb, occurred_at). Consumers subscribe by event type via domain_event_consumers.

This gives the system:

• A free audit log.
• A natural message bus without adding Kafka / Redpanda.
• The ability to add new downstream behavior (notification, analytics, ML feature) without touching the originating proc.

4. pgTAP tests against the live schema

Every non-trivial proc has tests in database/tests/*.sql. SQL-native unit tests, run by pg_prove. They catch signature drift, constraint violations, and the "this used to return three rows but now returns two" class of bug.

5. Schema mirror is regenerated, not hand-edited

database/schemas/{public,defi}/{tables,views,functions}/*.sql is the derived view of the live database, regenerated by database/scripts/refresh_schemas.py after every applied migration. It exists for code review and grep, not as a write surface. Editing it directly is wrong by definition.

6. Sync state belongs in dedicated tables

For every external system the database mirrors (Aerodrome RPC, Pendle API, wallets, veAERO locks), there is a corresponding *_sync_state or *_sync_cursor table. Incremental, idempotent, replay-safe. Reruns produce the same end state, not duplicate rows.

How it changes

The shape grew by accretion:

• Phase 1 (migrations ~001-040) — the SaaS skeleton. Users, subscriptions, tickers, positions. The scanner / planner / journal surfaces.
• Phase 2 (~040-065) — the Aerodrome on-chain layer. Pools, vote markets, opportunity rows, veAERO. Wallet sync cursors and idempotent writers landed in this band.
• Phase 3 (~065-085) — Pendle. Markets, snapshots, catalysts, the regime thresholds tunable surface. The suggestion / catalyst / catalyst_market triple modeled the real-world lineage.
• Phase 4 (~085-current) — Boros (Pendle-on-Pendle yield), continued catalyst refinement, the recommendation surface tightening.

The migration ledger is the historical record of what the product is actually doing; the schema mirror is the snapshot of where it is right now.

What this database is not

• Not a vector store. Embeddings, if and when they land, will use pgvector in this same database — not a separate engine. The principle: don’t add a moving part you don’t need.
• Not a graph database. Canonical state for the system (Behavior, Topic, Agent memory) lives in Neo4j. Postgres holds canonical state for the product.
• Not the analytics warehouse. When historical aggregations get heavy, the right move is exporting Parquet and querying with DuckDB, not turning Postgres into a warehouse.
• Not the cache. Today there isn’t one. When the rate-limit / session-store / hot-aggregate need shows up, Redis (or Valkey) goes in front, not on top of Postgres.

Gotchas specific to this deployment

• Auth lives in Keycloak, not in the database. Identity is a Keycloak sub claim; public.users mirrors the bits we need locally and joins on keycloak_sub. Never add a foreign key into an external auth schema.
• Connection pooling matters as concurrency grows. Postgres processes connections, not threads — a hundred idle clients without a pooler will hurt. PgBouncer in transaction mode for the API; sessions only for migrations and admin work.
• domain_events grows unbounded by default. It is intentional — the event log is durable history — but there is a retention/archive story to plan for as the volume grows. Today, manageable. Tomorrow, partitioned.
• The schema mirror is a read-only artifact. A PR that edits files under database/schemas/ without a matching migration is broken by definition; CI catches it.
• Cross-schema queries are fine; cross-schema foreign keys require thought. When defi.opportunity references public.users, the cascade behavior on a user delete matters. Decide explicitly.

Where to read the source

• Migrations: database/migrations/NNN_*.sql (in the gl-paiddaily repo)
• Schema mirror: database/schemas/{public,defi}/{tables,views,functions}/*.sql
• pgTAP tests: database/tests/*.sql
• Operational README: database/README.md in the repo

Related

• Postgres — the engine itself; conventions, extensions, when to reach for it generally.
• Neo4j — the system-state graph that sits alongside this product-state Postgres.
• DuckDB — the analytics surface that will eventually consume this database’s exports.