spb/qwhpi Public

1-- =============================================================================2-- QWHPI — Quebec Weekly Housing Price Index3-- Author  : Simon-Pierre Boucher4-- Contact : contact@spboucher.ai5-- File    : db/schema.sql6-- Purpose : Relational schema serving the API (loaded by engine export.py).7-- =============================================================================89CREATE TABLE IF NOT EXISTS geographies (10    geography_id    TEXT PRIMARY KEY,11    geography_name  TEXT NOT NULL,12    geography_level TEXT NOT NULL CHECK (geography_level IN ('province','region','municipality')),13    parent_id       TEXT REFERENCES geographies(geography_id)14);1516CREATE TABLE IF NOT EXISTS series (17    series_id       TEXT PRIMARY KEY,          -- '<geography_id>:<property_type>'18    geography_id    TEXT NOT NULL REFERENCES geographies(geography_id),19    property_type   TEXT NOT NULL CHECK (property_type IN ('all','unifamilial','condo','plex')),20    base_period     TEXT NOT NULL DEFAULT '2021 average = 100',21    UNIQUE (geography_id, property_type)22);2324CREATE TABLE IF NOT EXISTS model_runs (25    run_id          SERIAL PRIMARY KEY,26    model_version   TEXT NOT NULL,27    data_vintage    DATE NOT NULL,28    input_row_count BIGINT,29    input_hash      TEXT,30    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),31    UNIQUE (model_version, data_vintage)32);3334CREATE TABLE IF NOT EXISTS observations (35    series_id            TEXT NOT NULL REFERENCES series(series_id),36    week                 DATE NOT NULL,37    index                DOUBLE PRECISION,38    index_smoothed       DOUBLE PRECISION NOT NULL,39    representative_value DOUBLE PRECISION,40    transactions         INTEGER NOT NULL,41    effective_sample_size DOUBLE PRECISION,42    weekly_pct           DOUBLE PRECISION,43    four_week_pct        DOUBLE PRECISION,44    thirteen_week_pct    DOUBLE PRECISION,45    twenty_six_week_pct  DOUBLE PRECISION,46    yoy_pct              DOUBLE PRECISION,47    lower_95             DOUBLE PRECISION NOT NULL,48    upper_95             DOUBLE PRECISION NOT NULL,49    reliability_grade    CHAR(1) NOT NULL CHECK (reliability_grade IN ('A','B','C','D','E')),50    shrinkage_weight     DOUBLE PRECISION,51    is_partial_week      BOOLEAN NOT NULL DEFAULT FALSE,52    model_version        TEXT NOT NULL,53    data_vintage         DATE NOT NULL,54    PRIMARY KEY (series_id, week)55);56CREATE INDEX IF NOT EXISTS idx_obs_week ON observations (week);5758CREATE TABLE IF NOT EXISTS liquidity (59    series_id    TEXT NOT NULL REFERENCES series(series_id),60    week         DATE NOT NULL,61    transactions INTEGER NOT NULL,62    PRIMARY KEY (series_id, week)63);6465CREATE TABLE IF NOT EXISTS vintages (66    series_id                     TEXT NOT NULL REFERENCES series(series_id),67    week                          DATE NOT NULL,68    first_release_index           DOUBLE PRECISION,69    first_release_index_smoothed  DOUBLE PRECISION NOT NULL,70    first_release_vintage         DATE NOT NULL,71    PRIMARY KEY (series_id, week)72);7374-- Author credit: Simon-Pierre Boucher — contact@spboucher.ai75