`db/migrations/0007_bills_reorders.sql`

57 lines
-- 0007_bills_reorders.sql
-- Adds two household-admin owner tables that also feed the calendar_reminder engine:
--   bill         — recurring/one-off money owed (utilities, rent, insurance, subscriptions,
--                  loans, cards, AND tax/government payments such as CRA — category='tax'|'government')
--   reorder_item — things bought repeatedly ("what we order a lot") + best-price tracking ("save money")
-- Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS bill (
  id             text PRIMARY KEY,
  user_id        text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  name           text NOT NULL,
  payee          text,
  category       text NOT NULL DEFAULT 'other',    -- utility|rent|mortgage|insurance|subscription|loan|card|phone|internet|tax|government|other
  amount         numeric(12,2),
  currency       text NOT NULL DEFAULT 'USD',
  cadence        text NOT NULL DEFAULT 'monthly',   -- once|weekly|biweekly|monthly|quarterly|semiannual|annual
  due_date       date,                              -- NEXT date this bill is due
  autopay        boolean NOT NULL DEFAULT false,
  account_hint   text,                              -- redacted only, e.g. "Visa ••1234" — never a full PAN/account number
  url            text,                              -- pay-online / account link
  status         text NOT NULL DEFAULT 'active',    -- active|paused|closed
  last_paid_at   date,
  notes          text,
  metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS bill_user_due_idx  ON bill (user_id, status, due_date);
CREATE INDEX IF NOT EXISTS bill_category_idx  ON bill (user_id, category);

CREATE TABLE IF NOT EXISTS reorder_item (
  id                   text PRIMARY KEY,
  user_id              text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  name                 text NOT NULL,
  merchant             text,                         -- where we usually buy it
  category             text,                         -- grocery|household|pet|office|health|other
  unit                 text,                         -- e.g. "12-pack", "1 gal", "each"
  typical_price        numeric(12,2),                -- what we usually pay
  best_price           numeric(12,2),                -- cheapest seen (drives "save money")
  best_price_source    text,                         -- where the cheapest price was
  currency             text NOT NULL DEFAULT 'USD',
  reorder_cadence_days integer,                      -- how often we repurchase
  last_ordered_at      date,
  next_due_date        date,                         -- computed: last_ordered_at + cadence
  url                  text,
  status               text NOT NULL DEFAULT 'active', -- active|paused|archived
  notes                text,
  metadata_jsonb       jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS reorder_user_due_idx ON reorder_item (user_id, status, next_due_date);

COMMIT;