72 lines
4.0 KiB
SQL
72 lines
4.0 KiB
SQL
-- Migration 001: initial schema
|
|
-- Run with: psql $DATABASE_URL -f migrations/001_initial.sql
|
|
|
|
-- Enable the uuid-ossp extension for gen_random_uuid()
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- ── receipts ─────────────────────────────────────────────────────────────────
|
|
-- One row per submitted receipt image/form
|
|
CREATE TABLE IF NOT EXISTS receipts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
store_name TEXT,
|
|
receipt_date DATE NOT NULL,
|
|
image_url TEXT, -- S3/R2 URL after upload
|
|
city TEXT,
|
|
submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS receipts_date_idx ON receipts (receipt_date);
|
|
|
|
-- ── line_items ────────────────────────────────────────────────────────────────
|
|
-- Individual products parsed from a receipt
|
|
CREATE TABLE IF NOT EXISTS line_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
receipt_id UUID NOT NULL REFERENCES receipts(id) ON DELETE CASCADE,
|
|
raw_name TEXT NOT NULL, -- exactly as on the receipt
|
|
canonical_name TEXT, -- normalized key, e.g. "milk_whole_1gal"
|
|
category TEXT, -- "dairy", "produce", etc.
|
|
price_cents INT NOT NULL CHECK (price_cents > 0),
|
|
quantity NUMERIC NOT NULL DEFAULT 1 CHECK (quantity > 0)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS line_items_receipt_idx ON line_items (receipt_id);
|
|
CREATE INDEX IF NOT EXISTS line_items_canonical_idx ON line_items (canonical_name);
|
|
|
|
-- ── price_snapshots ───────────────────────────────────────────────────────────
|
|
-- Pre-aggregated monthly averages — rebuilt by the Go worker every hour.
|
|
-- The frontend reads from here, never from line_items directly.
|
|
CREATE TABLE IF NOT EXISTS price_snapshots (
|
|
canonical_name TEXT NOT NULL,
|
|
year_month DATE NOT NULL, -- truncated to first of the month
|
|
avg_price_cents INT NOT NULL,
|
|
sample_count INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (canonical_name, year_month)
|
|
);
|
|
|
|
-- ── canonical_items ───────────────────────────────────────────────────────────
|
|
-- Registry of known items and their human-readable display names.
|
|
-- Populated by hand / future admin UI.
|
|
CREATE TABLE IF NOT EXISTS canonical_items (
|
|
id TEXT PRIMARY KEY, -- e.g. "milk_whole_1gal"
|
|
display_name TEXT NOT NULL, -- "Whole Milk (1 gal)"
|
|
category TEXT NOT NULL,
|
|
unit TEXT, -- "gallon", "dozen", "lb"
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Seed some known items
|
|
INSERT INTO canonical_items (id, display_name, category, unit) VALUES
|
|
('milk_whole_1gal', 'Whole Milk', 'dairy', 'gallon'),
|
|
('milk_2pct_1gal', '2% Milk', 'dairy', 'gallon'),
|
|
('eggs_large_dozen', 'Eggs (large, dozen)', 'dairy', 'dozen'),
|
|
('butter_salted_1lb', 'Butter (salted)', 'dairy', 'lb'),
|
|
('bread_white_loaf', 'White Bread', 'bakery', 'loaf'),
|
|
('ground_beef_1lb', 'Ground Beef', 'meat', 'lb'),
|
|
('chicken_breast_1lb', 'Chicken Breast', 'meat', 'lb'),
|
|
('olive_oil_16oz', 'Olive Oil', 'pantry', '16oz'),
|
|
('rice_white_2lb', 'White Rice', 'pantry', '2lb bag'),
|
|
('apples_bag', 'Apples', 'produce', 'bag'),
|
|
('bananas_1lb', 'Bananas', 'produce', 'lb'),
|
|
('potatoes_5lb', 'Potatoes', 'produce', '5lb bag')
|
|
ON CONFLICT (id) DO NOTHING;
|