Use Single PostgreSQL Database with Typed Product Extension Tables

Historical decision text is preserved. Accepted describes the decision, not deployment of every capability discussed. Consult the Architecture and Infrastructure tabs for the verified running system.

Date: 2026-05-10
Status: Accepted
Deciders: Daniel Meza

Context and Problem Statement

Urza's Wallet stores financial products across three verticals — credit cards, pension funds, and home loans — each with different fields and comparison dimensions. We need a data model that:

1. Allows vertical-specific fields (e.g., cashback_rate_pct for credit cards, return_5yr_pct for pension funds, max_ltv_pct for home loans).
2. Supports cross-vertical queries (e.g., "all products from issuer BAC", "all products updated in the last 24 hours").
3. Accommodates adding a fourth or fifth vertical in the future without restructuring the schema.
4. Preserves relational integrity (foreign keys, NOT NULL constraints, type checking) per vertical.
5. Runs efficiently on a single Railway-managed PostgreSQL instance at Phase 1 traffic.

There are three main approaches to modeling multi-type entities in PostgreSQL. The choice has significant downstream consequences for query patterns, validation, and extensibility. Kleppmann (DDIA, Ch. 2) identifies the data model as "the most impactful single decision in application design" because it determines access patterns, and access patterns determine performance.

Decision Drivers

• Type safety per vertical: Credit card interest rates, pension fund returns, and mortgage LTV (Loan-to-Value) ratios require different data types, constraints, and validation rules. A single JSONB blob loses these constraints at the database level.
• Cross-vertical queries: Counting products per issuer, checking freshness across all verticals, and generating the sitemap all require querying across verticals. This is trivially a JOIN in a relational model; it requires denormalization or aggregation service calls in a multi-database model.
• Extensibility to new verticals: Adding a new vertical (e.g., savings accounts) should require adding a new extension table and a new route handler — not migrating existing tables or changing the base schema.
• Single database: Per ADR-001, the service-based architecture uses a shared PostgreSQL instance. Running separate databases per vertical (one for credit cards, one for pension funds) would require cross-database queries or a separate aggregation layer — over-engineered for Phase 1.
• PostgreSQL as the only data store: No document database, no wide-column store, no separate search index in Phase 1. PostgreSQL with tsvector handles the full-text search requirements at our initial product count (< 200 products across 3 verticals).

Considered Options

1. Single JSONB column (EAV (Entity-Attribute-Value) / document model): All vertical-specific fields stored in a product_data JSONB column on a single financial_products table.
2. Separate tables per vertical (no base table): credit_cards, pension_funds, home_loans as fully independent tables with no shared parent.
3. Base table + vertical extension tables (table-per-type): A financial_products table with shared fields, and per-vertical extension tables that reference it with a 1:1 FK.
4. Separate PostgreSQL database per vertical: Each vertical has its own Railway PostgreSQL service and schema.
5. PostgreSQL native table inheritance: Uses PostgreSQL's INHERITS clause to create subtables that share the parent's columns.

Decision Outcome

Option 3: Base table + vertical extension tables (table-per-type) is adopted.

The financial_products table stores all fields that are shared across every vertical:

• Product identity: id, product_name, issuer_id, vertical
• Lifecycle: status, updated_at, last_attempted_at, ingestion_failure_count
• Editorial: editorial_score, editorial_summary
• Provenance: source_url, is_stale

Each vertical has an extension table that stores all vertical-specific fields, with a 1:1 FK (Foreign Key) to financial_products.id:

• credit_cards(product_id PK FK, annual_fee_crc, interest_rate_annual_pct, network, reward_type, ...)
• pension_funds(product_id PK FK, fund_type, operadora, return_1yr_pct, return_5yr_pct, management_fee_pct, ...)
• home_loans(product_id PK FK, rate_type, max_ltv_pct, max_term_years, currency, ...)

A product detail query JOINs the base table with the appropriate extension table:

SELECT fp.*, cc.*
FROM financial_products fp
JOIN credit_cards cc ON cc.product_id = fp.id
WHERE fp.id = $1 AND fp.status = 'active';

A cross-vertical issuer query hits only the base table:

SELECT vertical, COUNT(*) as product_count
FROM financial_products
WHERE issuer_id = $1 AND status = 'active'
GROUP BY vertical;

Adding a new vertical (e.g., savings_accounts) requires:

1. A CREATE TABLE savings_accounts (product_id UUID PRIMARY KEY REFERENCES financial_products(id), ...) migration.
2. A new zod schema in the Canonical Mapper.
3. A new Express Router in the Vertical Router.
4. No changes to the financial_products table or any existing vertical's code.

Positive Consequences

• Type safety per vertical: Each extension table enforces NOT NULL, CHECK constraints, and FOREIGN KEY constraints on vertical-specific fields. A missing interest_rate_annual_pct for a credit card is a database-level error, not an application-level bug.
• Cross-vertical queries are simple JOINs: No aggregation service, no denormalization required.
• Extensibility: New verticals add a new table; existing tables and queries are unchanged.
• Indexing clarity: Indexes on vertical-specific fields (e.g., credit_cards(annual_fee_crc) for sorting by fee) live on the correct table, not in a JSONB gin index that scans all verticals.
• Migration safety: Adding a column to pension_funds does not lock the credit_cards table. Migrations are scoped to the affected vertical's extension table.
• Kleppmann alignment: DDIA (Designing Data-Intensive Applications) Ch. 2 explicitly notes that relational models with normalized schemas handle queries involving aggregation and cross-entity relationships more efficiently than document models when the schema is well-understood and relatively stable.

Negative Consequences / Trade-offs

• JOIN on every vertical-specific query: Every product detail query requires a JOIN between financial_products and the extension table. At 200 products total, this is negligible. At 10,000 products with heavy concurrent traffic, the JOIN may need a covering index — but this is a standard PostgreSQL optimization, not an architectural problem.
• Schema migration per new field: Adding a field to credit_cards requires a database migration. With JSONB, new fields require no migration. Mitigation: Railway-managed PostgreSQL supports zero-downtime column additions (ADD COLUMN with a DEFAULT value). The tradeoff is accepted because schema enforcement is more valuable than migration convenience at this stage.
• More tables than a pure JSONB approach: The schema has more tables but each table is well-typed. The DBA / developer experience is better for query writing and debugging.

Pros and Cons of the Options

The JSONB approach was the main competing option for its migration convenience. It was rejected because: (1) financial data has precise type requirements that should be enforced at the database level, not trusted to application validation alone; (2) JSONB queries are less readable and more prone to typos in field names; (3) pg_dump and data exports become harder to inspect and audit.

PostgreSQL INHERITS was evaluated and rejected due to its documented limitations: partitioned tables created with INHERITS do not support foreign key references to the parent table, do not inherit constraints, and create confusion when querying parent tables (which include child rows). The table-per-type approach achieves the same logical hierarchy without these caveats.

References

• Kleppmann, Designing Data-Intensive Applications, Ch. 2 (Data Models — Relational vs. Document), Ch. 3 (Storage and Retrieval — indexing)
• PostgreSQL documentation: table inheritance, partial indexes, covering indexes
• Architecture reference §5 (Data Architecture)