`migrations/010_data_orders.sql`

52 lines
-- Data marketplace: customers buy lists of California-licensed attorneys filtered
-- by city. Two purchase kinds:
--   'one_time'    — pay once, single CSV download
--   'subscription'— recurring monthly fee, unlimited downloads while active
--
-- Distinct from upgrade_orders (lawyer-facing site rebuild). Different kind of
-- buyer, different SKU, different reporting.

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
  cities                  TEXT[] NOT NULL DEFAULT '{}'::TEXT[],
  attorney_count          INT,                            -- snapshot at order time
  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);