`src/schema.sql`

71 lines
-- bankrupt-leads Postgres schema
-- Run on Local first (per feedback_pg_alter_table_order), then publish to Kamatera.

CREATE SCHEMA IF NOT EXISTS bankrupt_leads;

CREATE TABLE IF NOT EXISTS bankrupt_leads.cases (
    case_id          TEXT PRIMARY KEY,              -- "nysb-24-12345"
    court            TEXT NOT NULL,                 -- "nysb", "cacb", etc.
    docket_number    TEXT NOT NULL,
    chapter          TEXT NOT NULL,                 -- "7", "11", "subV"
    debtor_name      TEXT NOT NULL,
    debtor_naics     TEXT,
    filing_date      DATE NOT NULL,
    trustee_name     TEXT,
    trustee_email    TEXT,
    auctioneer_name  TEXT,
    status           TEXT DEFAULT 'open',           -- open | closed | dismissed
    first_seen_at    TIMESTAMPTZ DEFAULT now(),
    last_checked_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS cases_naics_idx ON bankrupt_leads.cases(debtor_naics);
CREATE INDEX IF NOT EXISTS cases_filing_date_idx ON bankrupt_leads.cases(filing_date DESC);

CREATE TABLE IF NOT EXISTS bankrupt_leads.filings (
    filing_id        TEXT PRIMARY KEY,              -- recap doc id or pacer seq
    case_id          TEXT REFERENCES bankrupt_leads.cases(case_id),
    docket_entry     INT,
    filed_at         TIMESTAMPTZ,
    doc_type         TEXT,                          -- schedule_ab | 363_motion | auctioneer_retention | apa | abandonment | other
    source           TEXT,                          -- recap | pacer
    pdf_path         TEXT,
    text_path        TEXT,
    classified_at    TIMESTAMPTZ,
    extracted_at     TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS filings_doctype_idx ON bankrupt_leads.filings(doc_type) WHERE doc_type IS NOT NULL;

CREATE TABLE IF NOT EXISTS bankrupt_leads.assets (
    asset_id         BIGSERIAL PRIMARY KEY,
    case_id          TEXT REFERENCES bankrupt_leads.cases(case_id),
    filing_id        TEXT REFERENCES bankrupt_leads.filings(filing_id),
    category         TEXT,                          -- real_estate | equipment | vehicles | inventory | ip | receivables | other
    subcategory      TEXT,                          -- "commercial kitchen", "cnc machine", "domain name"
    description      TEXT NOT NULL,
    location         TEXT,
    book_value_usd   NUMERIC(14,2),
    secured_party    TEXT,
    sale_date        DATE,
    sale_venue       TEXT,                          -- auction house / private sale / stalking horse
    contact_name     TEXT,
    contact_email    TEXT,
    contact_phone    TEXT,
    raw_excerpt      TEXT,                          -- source text the extractor pulled from
    extracted_at     TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS assets_category_idx ON bankrupt_leads.assets(category);
CREATE INDEX IF NOT EXISTS assets_sale_date_idx ON bankrupt_leads.assets(sale_date) WHERE sale_date IS NOT NULL;

CREATE TABLE IF NOT EXISTS bankrupt_leads.subscribers (
    sub_id           BIGSERIAL PRIMARY KEY,
    email            TEXT UNIQUE NOT NULL,
    company          TEXT,
    vertical         TEXT NOT NULL,                 -- "restaurant_equipment", "machine_tools", etc.
    plan             TEXT DEFAULT 'free',           -- free | paid
    stripe_customer  TEXT,
    created_at       TIMESTAMPTZ DEFAULT now()
);