← back to Lifestyle Asset Intel
db/migrations/0001_canonical_schema.sql
173 lines
-- 0001_canonical_schema.sql — initial canonical schema for lifestyle-asset-intel
--
-- Mirrors the canonical data model from BLUEPRINT.md:
-- sources → brands → model_families → canonical_assets
-- raw_observations (bronze) → transactions (silver) → valuation_snapshots (gold)
-- indices + index_points
-- portfolio_holdings (stub)
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- ---------------------------------------------------------------------------
-- sources: tier 1-4 source registry
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sources (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL UNIQUE,
slug text NOT NULL UNIQUE,
tier smallint NOT NULL CHECK (tier BETWEEN 1 AND 4),
kind text NOT NULL CHECK (kind IN ('auction','marketplace','quote','retail','user')),
weight numeric(5,3) NOT NULL DEFAULT 1.000,
enabled boolean NOT NULL DEFAULT true,
url text,
notes text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
-- ---------------------------------------------------------------------------
-- brands + model_families
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS brands (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL UNIQUE,
slug text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS model_families (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
brand_id uuid NOT NULL REFERENCES brands(id) ON DELETE CASCADE,
name text NOT NULL,
slug text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (brand_id, slug)
);
-- ---------------------------------------------------------------------------
-- canonical_assets: the product ID in the system
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS canonical_assets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
model_family_id uuid NOT NULL REFERENCES model_families(id) ON DELETE CASCADE,
slug text NOT NULL UNIQUE,
size text,
material text,
color text,
hardware text,
construction text,
year_min int,
year_max int,
region text NOT NULL DEFAULT 'US',
attrs jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS canonical_assets_family_idx ON canonical_assets(model_family_id);
-- ---------------------------------------------------------------------------
-- raw_observations: bronze layer, untouched source captures
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS raw_observations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_id uuid NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
external_id text,
kind text NOT NULL CHECK (kind IN ('listing','lot','quote','msrp','user_receipt')),
payload jsonb NOT NULL,
captured_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (source_id, external_id, kind)
);
CREATE INDEX IF NOT EXISTS raw_observations_captured_idx ON raw_observations(captured_at);
-- ---------------------------------------------------------------------------
-- transactions: silver layer, normalized sales/quotes
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transactions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
canonical_asset_id uuid NOT NULL REFERENCES canonical_assets(id) ON DELETE CASCADE,
source_id uuid NOT NULL REFERENCES sources(id) ON DELETE RESTRICT,
raw_observation_id uuid REFERENCES raw_observations(id) ON DELETE SET NULL,
gross_transaction_price numeric(12,2),
all_in_buyer_price numeric(12,2),
expected_net_seller_proceeds numeric(12,2),
normalized_market_value numeric(12,2),
currency text NOT NULL DEFAULT 'USD',
region text NOT NULL DEFAULT 'US',
-- ordinal grade: 0=As Is, 1=Fair, 2=Good, 3=Very Good, 4=Excellent, 5=Pristine, 6=Giftable/New
condition_grade smallint CHECK (condition_grade BETWEEN 0 AND 6),
condition_facets jsonb NOT NULL DEFAULT '{}'::jsonb,
transacted_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS transactions_asset_time_idx
ON transactions(canonical_asset_id, transacted_at DESC);
CREATE INDEX IF NOT EXISTS transactions_source_idx ON transactions(source_id);
-- ---------------------------------------------------------------------------
-- valuation_snapshots: gold layer, daily frozen quantile estimates
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS valuation_snapshots (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
canonical_asset_id uuid NOT NULL REFERENCES canonical_assets(id) ON DELETE CASCADE,
as_of date NOT NULL,
q10 numeric(12,2),
q50 numeric(12,2),
q90 numeric(12,2),
liquidity_score numeric(4,3) CHECK (liquidity_score BETWEEN 0 AND 1),
expected_dts int,
confidence numeric(4,3) CHECK (confidence BETWEEN 0 AND 1),
auth_risk numeric(4,3) CHECK (auth_risk BETWEEN 0 AND 1),
comp_count int NOT NULL DEFAULT 0,
methodology_version text NOT NULL DEFAULT 'v0-stub',
breakdown jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (canonical_asset_id, as_of, methodology_version)
);
-- ---------------------------------------------------------------------------
-- indices + index_points: benchmark families
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS indices (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
slug text NOT NULL UNIQUE,
name text NOT NULL,
definition jsonb NOT NULL DEFAULT '{}'::jsonb,
methodology_version text NOT NULL DEFAULT 'v0-stub',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS index_points (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
index_id uuid NOT NULL REFERENCES indices(id) ON DELETE CASCADE,
as_of date NOT NULL,
value numeric(12,2) NOT NULL,
change_pct numeric(7,4),
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (index_id, as_of)
);
-- ---------------------------------------------------------------------------
-- portfolio_holdings: stub for v0
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS portfolio_holdings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_email text NOT NULL,
canonical_asset_id uuid NOT NULL REFERENCES canonical_assets(id) ON DELETE CASCADE,
acquired_at date,
acquisition_basis numeric(12,2),
notes text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS portfolio_holdings_owner_idx ON portfolio_holdings(owner_email);
-- ---------------------------------------------------------------------------
-- migrations bookkeeping
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS schema_migrations (
filename text PRIMARY KEY,
applied_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO schema_migrations(filename) VALUES ('0001_canonical_schema.sql')
ON CONFLICT (filename) DO NOTHING;