`db/migrations/002_data_orders.sql`

58 lines
-- Doctor data marketplace: mirror of Counsel & Bar's data_orders for the
-- LA County physician directory. Buyers pick specialties + cities, get a CSV.
-- Two purchase kinds:
--   'one_time'    — single CSV download, snapshot at purchase time
--   'subscription'— monthly recurring, unlimited downloads while active
--
-- Distinct from any future per-doctor upgrade-listing flow (different SKU,
-- different buyer). Stripe is the source of truth on payment status; download_token
-- is the gate (random 32-byte hex, stored as plain text for direct lookup).

CREATE TABLE IF NOT EXISTS data_orders (
  id                      BIGSERIAL PRIMARY KEY,
  kind                    TEXT NOT NULL CHECK (kind IN ('one_time','subscription')),

  -- buyer
  email                   TEXT NOT NULL,
  full_name               TEXT,
  company                 TEXT,
  phone                   TEXT,

  -- product spec — both filter dimensions persist on the row so we can
  -- regenerate the CSV later (snapshot count) or audit what was sold.
  cities                  TEXT[] NOT NULL DEFAULT '{}'::TEXT[],
  specialties             TEXT[] NOT NULL DEFAULT '{}'::TEXT[],
  doctor_count            INT,
  amount_cents            INT NOT NULL,

  -- status
  status                  TEXT NOT NULL DEFAULT 'pending_payment'
                          CHECK (status IN ('pending_payment','paid','refunded','cancelled','sub_cancelled')),

  -- stripe
  stripe_session_id       TEXT,
  stripe_customer_id      TEXT,
  stripe_subscription_id  TEXT,
  payment_link            TEXT,

  -- delivery
  download_token          TEXT NOT NULL UNIQUE,           -- random 32-byte hex
  downloads_count         INT  NOT NULL DEFAULT 0,
  last_downloaded_at      TIMESTAMPTZ,

  -- timestamps
  created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  paid_at                 TIMESTAMPTZ,
  cancelled_at            TIMESTAMPTZ,

  -- audit
  ip                      TEXT,
  user_agent              TEXT
);

CREATE INDEX IF NOT EXISTS idx_data_orders_email   ON data_orders (LOWER(email));
CREATE INDEX IF NOT EXISTS idx_data_orders_status  ON data_orders (status);
CREATE INDEX IF NOT EXISTS idx_data_orders_created ON data_orders (created_at DESC);

INSERT INTO schema_migrations(filename) VALUES ('002_data_orders.sql') ON CONFLICT DO NOTHING;