snowspeeder
59645e59b5
Ports inventory management and SCA-style cupping scoring from hope_roaster: lot tracking with audit-logged consumption, cupping sessions with server-authoritative scoring and a live radar chart, and links from the planner (draw-from-lot, open-cupping-session). Also fixes the Blend/Single-origin toggle layout, replaces tooltips with an in-context "why" teaching layer, and stages the planner UI into pre-roast vs. post-roast phases.
46 lines
2.1 KiB
SQL
46 lines
2.1 KiB
SQL
-- Additive only: existing tables, rows, and columns are untouched.
|
|
CREATE TABLE green_bean_lots (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
origin text NOT NULL,
|
|
variety text NOT NULL DEFAULT '',
|
|
process text NOT NULL DEFAULT '',
|
|
producer text NOT NULL DEFAULT '',
|
|
purchase_date date,
|
|
initial_weight_g numeric NOT NULL,
|
|
remaining_weight_g numeric NOT NULL,
|
|
cost_total numeric,
|
|
moisture_pct numeric,
|
|
density_g_l numeric,
|
|
notes text NOT NULL DEFAULT '',
|
|
archived boolean NOT NULL DEFAULT false,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE bean_consumption (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
lot_id uuid NOT NULL REFERENCES green_bean_lots(id) ON DELETE CASCADE,
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
roast_plan_id uuid REFERENCES roast_plans(id) ON DELETE SET NULL,
|
|
weight_g numeric NOT NULL CHECK (weight_g > 0),
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
-- One draw-down per roast plan, ever (NULLs are distinct in Postgres, so plan-less manual
|
|
-- entries remain unlimited). This is the server-side backstop that makes the client's
|
|
-- "Draw from lot" button idempotent even if clicked twice or from two devices.
|
|
CREATE UNIQUE INDEX bean_consumption_one_per_plan ON bean_consumption(roast_plan_id);
|
|
CREATE TABLE cupping_sessions (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
roast_plan_id uuid REFERENCES roast_plans(id) ON DELETE SET NULL,
|
|
data jsonb NOT NULL,
|
|
total_score numeric NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX green_bean_lots_user ON green_bean_lots(user_id, archived, purchase_date DESC);
|
|
CREATE INDEX bean_consumption_lot ON bean_consumption(lot_id, created_at DESC);
|
|
CREATE INDEX bean_consumption_user ON bean_consumption(user_id);
|
|
CREATE INDEX cupping_sessions_user ON cupping_sessions(user_id, updated_at DESC);
|
|
CREATE INDEX cupping_sessions_plan ON cupping_sessions(roast_plan_id);
|