Skip to content

Warehouse Schema

The Postgres warehouse — hosted on DigitalOcean Managed Postgres — is the single system of record for the whole platform. Every layer of the pipeline, every reporting surface, and both public-facing APIs read from or write to this one database, addressed by a single DSN, DATABASE_URL.

Connection

Setting Value
Database fdpool
Connection Pooled, port 25061
TLS sslmode=require

There is exactly one DSN for the whole platform. A second, independently-configured DSN pointing at the warehouse is not a supported configuration — see the DATABASE_URL_RO split below, which is a second role, not a second database.

Reaching the warehouse from Vercel

The two Vercel APIs (client-api and internal-api, see Client API) cannot reach the DigitalOcean cluster's private network directly. Instead, each connects through a pgbouncer proxy (fd-pgbouncer) that runs on the same VPS droplet as the outreach daemon (see Deployment):

  • Exposed publicly on port 25432, TLS required.
  • Runs in pool_mode=transaction.
  • Every Vercel-side connection string routes through it: postgresql://<role>:<password>@<droplet-ip>:25432/warehouse?sslmode=require.

Note

Each database role must have an entry in the proxy's user list before it can connect. Adding a new role to the warehouse is not sufficient by itself — the proxy also needs to be told about it.

Two roles, by design

The platform deliberately uses two distinct roles/DSNs as a security boundary rather than sharing one credential across both APIs:

API Role DSN Scope
client-api Read-only DATABASE_URL_RO The client_buildings view only
internal-api Read-write DATABASE_URL Full warehouse access

Note

This split means a leaked client-api credential can never write to the warehouse, and can never reach mail or refresh functionality — those live behind the read-write role that only internal-api holds.

Core tables and views

Table/view Purpose
buildings Building-grain master record: identity, contacts, commission verdict, sources, photos
floorplans Floorplan-grain rows — see Grains, Keys & Amenities Model for the grain model and plan_key identity
units Unit-grain rows nested under a floorplan
communications Append-only outreach/reply ledger across all channels (email, web form, LinkedIn, WhatsApp, Google Voice)
rent_observations Append-only per-source rent readings — see Rent Observation Ledger
refresh_queue Pending/in-flight/completed on-demand refresh requests
refresh_policy Per-property refresh cadence intent (standard vs. priority tier), primary key (metro, join_key)
refresh_settings Operator-tunable cadence knobs: standard_per_day, priority_per_day, paid_budget_usd_per_day, priority_ttl_days, planner_batch_max, claim_limit, cadence_enabled
coverage_daily, funnel_daily, outreach_daily Precomputed daily rollups feeding the CLI reporting commands and the internal dashboard
form_probes Discovered contact-form registry — see Outreach
outreach_experiment_assignments Persona/variant assignment tracking for outreach experiments
hql / phql views Filtered views over buildings for the two top lead-quality tiers
client_buildings view Read-only-safe view backing the public client-api feed
relive_buildings / relive_floorplans Comparison tables mirroring a partner's ("Relive") own inventory, for the internal comparison/dashboard surface — fully rebuilt (truncate + reload) on each ingest run from source files, never hand-edited
extension_buildings / extension_relive Views translating between this project's internal metro-slug vocabulary and the partner's own metro short-codes (see Metro vocabulary below) — inherited from the now-retired Chrome-extension backend, still used by internal-api's comparison endpoints

refresh_settings ships empty by default: code-level defaults apply unless a row explicitly overrides a knob, and any knob can also be overridden at deploy time via a matching REFRESH_<KEY> environment variable, used as an emergency brake.

Note

refresh_queue is explicitly excluded from any table-truncate/rebuild step. Unlike relive_buildings / relive_floorplans, it holds live operational state, not data derived from an external source file, so it is never safe to blow away and reload.

Metro vocabulary

Two coexisting naming schemes

buildings.metro_slug uses this project's own folder-slug naming (chicago, miami, ...). A separate, partner-facing short code (chi, mia, ...) is used in refresh_queue rows and by the Chrome-extension-derived comparison views. The extension_buildings view translates between the two in exactly one place — do not assume the two vocabularies are interchangeable anywhere else in the codebase.

DATABASE_URL is required

DATABASE_URL must be set for the pipeline to do anything meaningful — there is no CSV-only fallback mode. An unset or misconfigured DSN fails loudly rather than silently degrading to a partial or CSV-only run.

Never point at the retired Supabase instance

Supabase was the warehouse prior to a full cutover to DigitalOcean Managed Postgres. Writers explicitly refuse to write to a Supabase host if one is accidentally configured, rather than writing a stale copy and reporting success.

See also