← back to Lifestyle Asset Intel
canonical schema + Birkin sample seed + pg pool wrapper
5afcf7c9c43ef45e84eb2b08ed34b981e4993f05 · 2026-05-09 21:36:40 -0700 · Steve Abrams
Migration 0001 lays out the 10-table canonical model from BLUEPRINT.md:
sources / brands / model_families / canonical_assets / raw_observations
(bronze) / transactions (silver) / valuation_snapshots (gold) / indices /
index_points / portfolio_holdings, plus schema_migrations bookkeeping.
Seed data is the blueprint's normalized 2022–2025 secondary-market series
for the Birkin 30 Togo Gold GHW (US), 8 source rows across tiers 1–3, the
birkin-30-togo-neutral index with 4 historical points, and a v0-stub
valuation snapshot whose breakdown jsonb mirrors the compositional
confidence formula in the blueprint.
lib/db.js carries the pg-pool gotcha guards (empty PGPASSWORD strip,
omit password key when blank) that NPH learned the hard way; macOS dev
defaults PGHOST=/tmp so unix-socket peer auth Just Works.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/0001_canonical_schema.sqlA db/schema.sqlA db/seed.sqlA lib/db.jsA scripts/migrate.js
Diff
commit 5afcf7c9c43ef45e84eb2b08ed34b981e4993f05
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat May 9 21:36:40 2026 -0700
canonical schema + Birkin sample seed + pg pool wrapper
Migration 0001 lays out the 10-table canonical model from BLUEPRINT.md:
sources / brands / model_families / canonical_assets / raw_observations
(bronze) / transactions (silver) / valuation_snapshots (gold) / indices /
index_points / portfolio_holdings, plus schema_migrations bookkeeping.
Seed data is the blueprint's normalized 2022–2025 secondary-market series
for the Birkin 30 Togo Gold GHW (US), 8 source rows across tiers 1–3, the
birkin-30-togo-neutral index with 4 historical points, and a v0-stub
valuation snapshot whose breakdown jsonb mirrors the compositional
confidence formula in the blueprint.
lib/db.js carries the pg-pool gotcha guards (empty PGPASSWORD strip,
omit password key when blank) that NPH learned the hard way; macOS dev
defaults PGHOST=/tmp so unix-socket peer auth Just Works.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
db/migrations/0001_canonical_schema.sql | 172 ++++++++++++++++++++++++++++
db/schema.sql | 2 +
db/seed.sql | 191 ++++++++++++++++++++++++++++++++
lib/db.js | 33 ++++++
scripts/migrate.js | 51 +++++++++
5 files changed, 449 insertions(+)
diff --git a/db/migrations/0001_canonical_schema.sql b/db/migrations/0001_canonical_schema.sql
new file mode 100644
index 0000000..3617a24
--- /dev/null
+++ b/db/migrations/0001_canonical_schema.sql
@@ -0,0 +1,172 @@
+-- 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;
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..f55633c
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,2 @@
+-- schema.sql — applies all migrations in order. Re-run safe (every migration uses IF NOT EXISTS).
+\i db/migrations/0001_canonical_schema.sql
diff --git a/db/seed.sql b/db/seed.sql
new file mode 100644
index 0000000..85caeef
--- /dev/null
+++ b/db/seed.sql
@@ -0,0 +1,191 @@
+-- seed.sql — minimal blueprint sample data for v0.
+-- Idempotent: re-running upserts; safe to run after schema.sql.
+
+-- ---------------------------------------------------------------------------
+-- sources (tier 1-3 — Tier 4 dealer/partner feeds and user receipts deferred)
+-- ---------------------------------------------------------------------------
+INSERT INTO sources (name, slug, tier, kind, weight, url, notes) VALUES
+ ('Sotheby''s', 'sothebys', 1, 'auction', 1.000, 'https://www.sothebys.com/en/', 'Closed-lot auction results — Tier 1 ground truth.'),
+ ('Christie''s', 'christies', 1, 'auction', 1.000, 'https://www.christies.com/', 'Closed-lot auction results — Tier 1 ground truth.'),
+ ('FASHIONPHILE', 'fashionphile', 2, 'marketplace', 0.800, 'https://www.fashionphile.com/', 'Verified resale w/ condition grades Giftable→Flawed.'),
+ ('Rebag', 'rebag', 2, 'quote', 0.700, 'https://www.rebag.com/', 'Clair instant quote, Consign/Trade/Buyout modes.'),
+ ('The RealReal', 'therealreal', 2, 'marketplace', 0.700, 'https://www.therealreal.com/', 'Pristine→As Is grades; demand-driven pricing algo.'),
+ ('StockX', 'stockx', 2, 'marketplace', 0.650, 'https://stockx.com/', 'New-condition only; cleaner store-fresh signal.'),
+ ('Chrono24', 'chrono24', 2, 'marketplace', 0.650, 'https://www.chrono24.com/', 'ChronoPulse index; watches-strongest source.'),
+ ('eBay', 'ebay', 3, 'marketplace', 0.350, 'https://www.ebay.com/', 'Active listings; sold-history gated behind Marketplace Insights.')
+ON CONFLICT (slug) DO UPDATE
+ SET name = EXCLUDED.name, tier = EXCLUDED.tier, kind = EXCLUDED.kind,
+ weight = EXCLUDED.weight, url = EXCLUDED.url, notes = EXCLUDED.notes,
+ updated_at = now();
+
+-- ---------------------------------------------------------------------------
+-- brands + families
+-- ---------------------------------------------------------------------------
+INSERT INTO brands (name, slug) VALUES
+ ('Hermès', 'hermes')
+ON CONFLICT (slug) DO NOTHING;
+
+INSERT INTO model_families (brand_id, name, slug)
+SELECT b.id, 'Birkin', 'birkin' FROM brands b WHERE b.slug = 'hermes'
+ON CONFLICT (brand_id, slug) DO NOTHING;
+
+INSERT INTO model_families (brand_id, name, slug)
+SELECT b.id, 'Kelly', 'kelly' FROM brands b WHERE b.slug = 'hermes'
+ON CONFLICT (brand_id, slug) DO NOTHING;
+
+-- ---------------------------------------------------------------------------
+-- canonical asset: Birkin 30 / Togo / Gold / GHW / Retourne / US
+-- ---------------------------------------------------------------------------
+INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
+SELECT
+ mf.id,
+ 'birkin-30-togo-gold-ghw-us',
+ '30',
+ 'Togo',
+ 'Gold',
+ 'GHW',
+ 'Retourne',
+ 'US',
+ jsonb_build_object('display_name', 'Birkin 30 — Togo — Gold — GHW (US)',
+ 'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
+FROM model_families mf
+JOIN brands b ON b.id = mf.brand_id
+WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
+ON CONFLICT (slug) DO NOTHING;
+
+-- ---------------------------------------------------------------------------
+-- transactions — blueprint's 2022/2023/2024/2025 normalized averages
+-- ---------------------------------------------------------------------------
+INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
+ expected_net_seller_proceeds, normalized_market_value, condition_grade,
+ condition_facets, currency, region, transacted_at)
+SELECT
+ ca.id,
+ s.id,
+ 19000.00, 19000.00, 14750.00, 19000.00,
+ 5,
+ jsonb_build_object('store_fresh', false, 'date_stamp_year', 2018),
+ 'USD', 'US',
+ '2022-08-15 00:00:00+00'::timestamptz
+FROM canonical_assets ca, sources s
+WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
+ON CONFLICT DO NOTHING;
+
+INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
+ expected_net_seller_proceeds, normalized_market_value, condition_grade,
+ condition_facets, currency, region, transacted_at)
+SELECT
+ ca.id,
+ s.id,
+ 20459.00, 20459.00, 16000.00, 20459.00,
+ 5,
+ jsonb_build_object('store_fresh', false, 'date_stamp_year', 2019),
+ 'USD', 'US',
+ '2023-08-15 00:00:00+00'::timestamptz
+FROM canonical_assets ca, sources s
+WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
+ON CONFLICT DO NOTHING;
+
+INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
+ expected_net_seller_proceeds, normalized_market_value, condition_grade,
+ condition_facets, currency, region, transacted_at)
+SELECT
+ ca.id,
+ s.id,
+ 21038.00, 21038.00, 16500.00, 21038.00,
+ 5,
+ jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021),
+ 'USD', 'US',
+ '2024-08-15 00:00:00+00'::timestamptz
+FROM canonical_assets ca, sources s
+WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
+ON CONFLICT DO NOTHING;
+
+INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
+ expected_net_seller_proceeds, normalized_market_value, condition_grade,
+ condition_facets, currency, region, transacted_at)
+SELECT
+ ca.id,
+ s.id,
+ 22300.00, 22300.00, 17500.00, 22300.00,
+ 5,
+ jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023),
+ 'USD', 'US',
+ '2025-08-15 00:00:00+00'::timestamptz
+FROM canonical_assets ca, sources s
+WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
+ON CONFLICT DO NOTHING;
+
+-- One Pristine FASHIONPHILE comp at the upper band
+INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
+ expected_net_seller_proceeds, normalized_market_value, condition_grade,
+ condition_facets, currency, region, transacted_at)
+SELECT
+ ca.id,
+ s.id,
+ 28500.00, 28500.00, 22000.00, 28500.00,
+ 6,
+ jsonb_build_object('store_fresh', true, 'date_stamp_year', 2024,
+ 'accessories_complete', true, 'plastic_intact', true),
+ 'USD', 'US',
+ '2026-04-12 00:00:00+00'::timestamptz
+FROM canonical_assets ca, sources s
+WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'fashionphile'
+ON CONFLICT DO NOTHING;
+
+-- ---------------------------------------------------------------------------
+-- index: birkin-30-togo-neutral
+-- ---------------------------------------------------------------------------
+INSERT INTO indices (slug, name, definition, methodology_version) VALUES
+ ('birkin-30-togo-neutral',
+ 'Birkin 30 — Togo — Neutral Colors',
+ jsonb_build_object('size','30','material','Togo','colors',jsonb_build_array('Gold','Black','Etoupe','Nata'),'region','US'),
+ 'v0-stub')
+ON CONFLICT (slug) DO NOTHING;
+
+INSERT INTO index_points (index_id, as_of, value, change_pct)
+SELECT i.id, '2022-12-31'::date, 19000.00, NULL FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
+ON CONFLICT (index_id, as_of) DO NOTHING;
+INSERT INTO index_points (index_id, as_of, value, change_pct)
+SELECT i.id, '2023-12-31'::date, 20459.00, 0.0768 FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
+ON CONFLICT (index_id, as_of) DO NOTHING;
+INSERT INTO index_points (index_id, as_of, value, change_pct)
+SELECT i.id, '2024-12-31'::date, 21038.00, 0.0283 FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
+ON CONFLICT (index_id, as_of) DO NOTHING;
+INSERT INTO index_points (index_id, as_of, value, change_pct)
+SELECT i.id, '2025-12-31'::date, 22300.00, 0.0600 FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
+ON CONFLICT (index_id, as_of) DO NOTHING;
+
+-- ---------------------------------------------------------------------------
+-- valuation_snapshot for today (lib/valuation.js will rewrite on each /api hit)
+-- ---------------------------------------------------------------------------
+INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90,
+ liquidity_score, expected_dts, confidence,
+ auth_risk, comp_count, methodology_version, breakdown)
+SELECT
+ ca.id,
+ CURRENT_DATE,
+ 19500.00, 22300.00, 28500.00,
+ 0.780, 21, 0.740, 0.080, 5,
+ 'v0-stub',
+ jsonb_build_object(
+ 'source_quality_weight', 0.25,
+ 'comparable_similarity_weight', 0.18,
+ 'sample_depth_weight', 0.12,
+ 'recency_weight', 0.10,
+ 'image_match_weight', 0.09,
+ 'authenticity_risk_penalty', -0.04,
+ 'condition_uncertainty_penalty', -0.05,
+ 'region_gap_penalty', 0.00,
+ 'fee_model_uncertainty_penalty', -0.01
+ )
+FROM canonical_assets ca
+WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
+ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
+ SET q10 = EXCLUDED.q10, q50 = EXCLUDED.q50, q90 = EXCLUDED.q90,
+ liquidity_score = EXCLUDED.liquidity_score,
+ expected_dts = EXCLUDED.expected_dts,
+ confidence = EXCLUDED.confidence,
+ auth_risk = EXCLUDED.auth_risk,
+ comp_count = EXCLUDED.comp_count,
+ breakdown = EXCLUDED.breakdown;
diff --git a/lib/db.js b/lib/db.js
new file mode 100644
index 0000000..a69d3be
--- /dev/null
+++ b/lib/db.js
@@ -0,0 +1,33 @@
+const { Pool } = require('pg');
+
+if (process.env.PGPASSWORD === '') delete process.env.PGPASSWORD;
+
+const pgConfig = {
+ host: process.env.PGHOST || 'localhost',
+ port: parseInt(process.env.PGPORT || '5432', 10),
+ database: process.env.PGDATABASE || 'lifestyle_asset_intel',
+ user: process.env.PGUSER || process.env.USER,
+ max: 10,
+ idleTimeoutMillis: 30000
+};
+if (process.env.PGPASSWORD && process.env.PGPASSWORD.length > 0) {
+ pgConfig.password = process.env.PGPASSWORD;
+}
+const pool = new Pool(pgConfig);
+
+pool.on('error', (err) => {
+ console.error('[pg pool error]', err.message);
+});
+
+module.exports = {
+ pool,
+ query: (text, params) => pool.query(text, params),
+ one: async (text, params) => {
+ const r = await pool.query(text, params);
+ return r.rows[0] || null;
+ },
+ many: async (text, params) => {
+ const r = await pool.query(text, params);
+ return r.rows;
+ }
+};
diff --git a/scripts/migrate.js b/scripts/migrate.js
new file mode 100644
index 0000000..3f299c6
--- /dev/null
+++ b/scripts/migrate.js
@@ -0,0 +1,51 @@
+#!/usr/bin/env node
+// migrate.js — applies any unapplied SQL files from db/migrations/ in lexical order.
+// Idempotent: tracks applied filenames in schema_migrations.
+
+require('dotenv').config();
+const fs = require('fs');
+const path = require('path');
+const { pool } = require('../lib/db');
+
+const MIGRATIONS_DIR = path.join(__dirname, '..', 'db', 'migrations');
+
+async function main() {
+ await pool.query(`
+ CREATE TABLE IF NOT EXISTS schema_migrations (
+ filename text PRIMARY KEY,
+ applied_at timestamptz NOT NULL DEFAULT now()
+ );
+ `);
+
+ const files = fs.readdirSync(MIGRATIONS_DIR)
+ .filter((f) => f.endsWith('.sql'))
+ .sort();
+
+ const { rows } = await pool.query('SELECT filename FROM schema_migrations');
+ const applied = new Set(rows.map((r) => r.filename));
+
+ let count = 0;
+ for (const f of files) {
+ if (applied.has(f)) {
+ console.log(`[migrate] skip ${f} (already applied)`);
+ continue;
+ }
+ const sql = fs.readFileSync(path.join(MIGRATIONS_DIR, f), 'utf8');
+ console.log(`[migrate] apply ${f}`);
+ await pool.query('BEGIN');
+ try {
+ await pool.query(sql);
+ await pool.query('INSERT INTO schema_migrations(filename) VALUES ($1) ON CONFLICT DO NOTHING', [f]);
+ await pool.query('COMMIT');
+ count++;
+ } catch (err) {
+ await pool.query('ROLLBACK');
+ console.error(`[migrate] FAILED ${f}:`, err.message);
+ process.exit(1);
+ }
+ }
+ console.log(`[migrate] done — applied ${count} migration(s)`);
+ await pool.end();
+}
+
+main().catch((err) => { console.error(err); process.exit(1); });
← 18d04fe initial scaffold — package, env, pm2, gitignore, blueprint,
·
back to Lifestyle Asset Intel
·
server + console + valuation API + smoke tests cfa428e →