Model stable products separately from changing rate observations.

A durable financial-product schema has three layers: institution, the publisher’s product identity, and immutable observations of rates, terms, tiers, and qualifiers. Every observation carries its source, verbatim evidence, and as_of time so current inventory and history come from the same facts.

Last updated 2026-09-05TypeScript and SQLLoans and depositsEvidence-backed history

The boundary in TypeScript

TSStable identity plus immutable observation
type Product = Readonly<{
  id: string;
  institution_id: string;
  product_name: string;       // publisher's identity; do not rewrite
  product_type: string;       // normalized taxonomy
}>;
 
type RateObservation = Readonly<{
  product_id: string;
  rate: number | null;
  apr: number | null;
  apy: number | null;
  term_months: number | null;
  amount_min: number | null;
  amount_max: number | null;
  qualifiers: Readonly<Record<string, string | number | boolean>>;
  source_url: string;
  evidence: string;           // verbatim source snippet
  as_of: string;              // observation time
}>;
Do not make a normalized label the product’s identity. Preserve product_name, then classify the record into comparable facets.

Fields that define a comparable offer

product_type
A controlled category such as mortgage, auto loan, HELOC, personal loan, savings, or certificate.
rate, apr, apy
Separate nullable measures. A lending rate, published APR, and deposit APY are different facts.
term and tier bounds
Term, balance or amount range, LTV, credit tier, and other bounds describe where a value applies.
qualifiers
Program, occupancy, vehicle condition, relationship discount, and other source-specific conditions.
as_of
The timestamp of the observation. It supports freshness policies and historical queries.
source and evidence
The page URL and verbatim snippet that prove the published claim.

Store history; derive current inventory

//Append-only observations and a current view
CREATE TABLE rate_observation (
  product_id uuid NOT NULL REFERENCES product(id),
  observed_at timestamptz NOT NULL,
  rate numeric,
  apr numeric,
  apy numeric,
  term_months integer,
  amount_min numeric,
  amount_max numeric,
  qualifiers jsonb NOT NULL DEFAULT '{}',
  source_url text NOT NULL,
  evidence text NOT NULL,
  PRIMARY KEY (product_id, observed_at, term_months, amount_min)
);
 
-- Current inventory is a view over history, not an overwritten row.
CREATE VIEW current_rate AS
SELECT DISTINCT ON (product_id, term_months, amount_min) *
FROM rate_observation
ORDER BY product_id, term_months, amount_min, observed_at DESC;

Normalization without losing facts

  1. Preserve the source record

    Store the original product name, source, evidence, and observed time before classification.

  2. Classify into one taxonomy

    Use one controlled product classifier so API filters, analytics, and history agree.

  3. Extract qualifiers into facets

    Make term, tier, program, condition, and geography explicit rather than encoding them in a display string.

  4. Reject unprovable observations

    If a value lacks evidence or an observation time, keep it out of the served current view.

Frequently asked questions

Apply the model

Rate, APR, points, and feesField semantics and null handling.Rate Query APISee a production request and response shape.Loan marketplace architectureUse the model in a comparison product.Data-quality methodologyEvidence, validation, and freshness rules.