← back to Bankrupt Leads
[overnight] pre-debate baseline
b8ccf1054b5f0464548f23fec8424b78bc23ff4b · 2026-05-03 23:52:25 -0700 · SteveStudio2
Files touched
A .env.exampleA CHANGES.mdA README.mdA docs/dealer_outreach.mdA requirements.txtA src/classify.pyA src/digest.pyA src/extract.pyA src/llm.pyA src/poller.pyA src/schema.sql
Diff
commit b8ccf1054b5f0464548f23fec8424b78bc23ff4b
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Sun May 3 23:52:25 2026 -0700
[overnight] pre-debate baseline
---
.env.example | 10 +++++
CHANGES.md | 14 ++++++
README.md | 38 ++++++++++++++++
docs/dealer_outreach.md | 40 +++++++++++++++++
requirements.txt | 2 +
src/classify.py | 84 ++++++++++++++++++++++++++++++++++++
src/digest.py | 106 +++++++++++++++++++++++++++++++++++++++++++++
src/extract.py | 112 ++++++++++++++++++++++++++++++++++++++++++++++++
src/llm.py | 50 +++++++++++++++++++++
src/poller.py | 86 +++++++++++++++++++++++++++++++++++++
src/schema.sql | 70 ++++++++++++++++++++++++++++++
11 files changed, 612 insertions(+)
diff --git a/.env.example b/.env.example
new file mode 100644
index 0000000..2bbc780
--- /dev/null
+++ b/.env.example
@@ -0,0 +1,10 @@
+# Copy to .env and fill in
+COURTLISTENER_TOKEN=
+PACER_USERNAME=
+PACER_PASSWORD=
+BANKRUPT_LEADS_PG=postgresql:///bankrupt_leads
+
+# Local LLM (Ollama). Override if running Prism or a remote Ollama host.
+OLLAMA_HOST=http://localhost:11434
+CLASSIFY_MODEL=gemma3:4b
+EXTRACT_MODEL=qwen3:14b
diff --git a/CHANGES.md b/CHANGES.md
new file mode 100644
index 0000000..a5ad086
--- /dev/null
+++ b/CHANGES.md
@@ -0,0 +1,14 @@
+## Applied
+
+- None — CODEX_REVIEW.md did not list P0/P1 findings matching the approved safe-fix categories.
+
+## Deferred (needs Steve)
+
+- P0 — src/poller.py:37 — poller only requests the first 100 CourtListener dockets and does not follow pagination — deferred because pagination changes pipeline behavior and is not one of the approved autonomous safe fixes.
+- P0 — src/poller.py:45 — poller inserts cases only; no filing/document ingestion, PDF download, or text extraction path populates downstream inputs — deferred because implementing ingestion is a product/workflow change, not a safe surgical fix.
+- P0 — src/extract.py:77 — extraction is not idempotent and reruns can duplicate assets — deferred because the reviewed fix requires schema/uniqueness or transactional delete/upsert semantics, and database schema/migration work is explicitly out of scope.
+- P1 — src/llm.py:34 — malformed or empty Ollama JSON output can crash the job without filing-level error state — deferred because durable retry/error-state handling changes workflow semantics and was not in the approved safe-fix list.
+- P1 — src/classify.py:70 — one missing or unreadable text_path aborts the whole classification batch after prior commits — deferred because per-filing error handling/state semantics were not in the approved safe-fix list.
+- P1 — src/extract.py:91 — arbitrary LLM asset keys and invalid dates/numerics can fail DB writes mid-run — deferred because validation/coercion policy is domain-specific and not one of the approved safe fixes.
+- P1 — src/poller.py:76 — one court request failure skips all remaining courts — deferred because court-level retry/logging behavior was not in the approved safe-fix list.
+- P1 — src/schema.sql:27 — filings.case_id allows null and lacks ON DELETE behavior — deferred because SQL/schema changes are explicitly prohibited.
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..6a6107d
--- /dev/null
+++ b/README.md
@@ -0,0 +1,38 @@
+# bankrupt-leads
+
+MVP: scrape bankruptcy court filings, extract asset schedules + 363 sale motions, resell as vertical lead digests.
+
+## Pipeline (local-LLM only — no cloud APIs)
+1. **Poll** CourtListener RECAP (free) + PACER RSS for new Ch. 7/11/Subchapter V cases
+2. **Classify** docs with Ollama `gemma3:4b`: Schedule A/B, 363 motion, auctioneer retention, APA, abandonment
+3. **Extract** assets with Ollama `qwen3:14b` (JSON mode) → Postgres. Fallback `gemma3:27b` on tough filings.
+4. **Enrich** trustee/auctioneer contact; cross-ref UCC-1 filings for secured creditors
+5. **Digest** weekly email per vertical (start: restaurant equipment)
+
+## Status
+- [x] Scaffold (`~/Projects/bankrupt-leads/`)
+- [x] Postgres schema (`src/schema.sql`)
+- [x] RECAP poller (`src/poller.py`)
+- [x] Local-LLM client (`src/llm.py` — Ollama)
+- [x] Doc classifier (`src/classify.py` — gemma3:4b)
+- [x] Asset extractor (`src/extract.py` — qwen3:14b JSON mode)
+- [x] Weekly digest builder (`src/digest.py` — vertical filters: restaurant_equipment, machine_tools, medical_equipment)
+- [x] Cold-email template + target-list plan (`docs/dealer_outreach.md`)
+- [x] Registered in CNCP `cncp-projects.json`
+- [ ] `createdb bankrupt_leads && psql -f src/schema.sql` on Local
+- [ ] CourtListener token + first poll run
+- [ ] Seed 50 dealers into `docs/dealers.csv`
+
+## First vertical: restaurant equipment
+Rationale: frequent Ch. 7 filings, active resale market (Silver State, RestaurantEquipment.net, local dealers), dealers already cold-call trustees — so proving value is just "we found it first."
+
+## Cost envelope (month 1)
+- PACER: ~$200 (only for gaps RECAP misses)
+- LLM: $0 (all inference on local Ollama — gemma3:4b / qwen3:14b / gemma3:27b)
+- Postgres + hosting: existing Kamatera
+- Target: 10 paid dealers × $149/mo = $1,490 MRR by week 8
+
+## Non-goals (for now)
+- Competing with Reorg/BankruptcyData on breadth
+- General-public UI
+- Real-time alerts (weekly cadence matches sale timelines)
diff --git a/docs/dealer_outreach.md b/docs/dealer_outreach.md
new file mode 100644
index 0000000..52a1586
--- /dev/null
+++ b/docs/dealer_outreach.md
@@ -0,0 +1,40 @@
+# Dealer outreach — restaurant equipment
+
+## Target list (seed 50)
+Pull from:
+- NAFEM member directory (north american assn of food equipment mfrs)
+- Facebook Marketplace "commercial kitchen" top sellers by region
+- Restaurant-equipment auction houses: Silver State, ProRestaurantEquipment, RestaurantEquipment.net, WebstaurantStore remarketing
+- Google Maps "used restaurant equipment" in top-25 metros
+- Industry trade groups: NRA (National Restaurant Association) supplier list
+
+Store in `docs/dealers.csv` (cols: company, contact_name, email, region, priority).
+
+## Cold-email template v1
+
+Subject: 3 bankrupt restaurants with kitchens for sale this week — free digest
+
+Hey {{first_name}},
+
+I'm building a free weekly digest of commercial kitchens being liquidated out of
+US bankruptcy filings — hoods, walk-ins, Hobart/Vulcan/True lineups, the full
+setup — with the trustee or auctioneer's direct contact for each one.
+
+Last week there were 14 filings in the Northeast alone. Most dealers find these
+too late (after the auctioneer's already picked a buyer).
+
+Want me to add you to this week's list? Free, no pitch, unsubscribe any time.
+
+Steve Abrams
+steve@designerwallcoverings.com
+
+## Conversion path
+
+1. Week 1–2: free digest, measure open + click rate
+2. Week 3: soft paywall — "premium" version includes direct contact + pre-auction access ($149/mo)
+3. Week 4+: tiered — free (case list only) / paid (full asset detail + contacts)
+
+## KPIs
+- Digest open rate > 35% = vertical has signal
+- Paid conversion > 5% of free list = pricing is right
+- <1% = either wrong vertical or value is weak — iterate before scaling
diff --git a/requirements.txt b/requirements.txt
new file mode 100644
index 0000000..fa472d7
--- /dev/null
+++ b/requirements.txt
@@ -0,0 +1,2 @@
+httpx>=0.27
+psycopg[binary]>=3.2
diff --git a/src/classify.py b/src/classify.py
new file mode 100644
index 0000000..84bc2e1
--- /dev/null
+++ b/src/classify.py
@@ -0,0 +1,84 @@
+"""
+Doc classifier — local Ollama (default gemma3:4b).
+
+Runs before extract.py so the expensive model only sees filings that actually
+contain assets (Schedule A/B, 363 motion, auctioneer retention, APA, abandonment).
+"""
+from __future__ import annotations
+
+import argparse
+import os
+from pathlib import Path
+
+import psycopg
+
+from llm import generate_text
+
+PG_DSN = os.environ.get("BANKRUPT_LEADS_PG", "postgresql:///bankrupt_leads")
+MODEL = os.environ.get("CLASSIFY_MODEL", "gemma3:4b")
+
+CATEGORIES = {
+ "schedule_ab",
+ "363_motion",
+ "auctioneer_retention",
+ "apa",
+ "abandonment",
+ "other",
+}
+
+PROMPT_TEMPLATE = """Classify this US bankruptcy court filing into exactly ONE category:
+
+- schedule_ab Schedule A/B (real & personal property inventory)
+- 363_motion Motion to sell assets under 11 U.S.C. section 363
+- auctioneer_retention Application to employ auctioneer or liquidator
+- apa Asset Purchase Agreement (or notice attaching one)
+- abandonment Notice of abandonment of property under section 554
+- other everything else (procedural, claims, plan, etc.)
+
+Respond with ONLY the category string. No prose, no punctuation.
+
+FILING TEXT:
+{text}
+"""
+
+
+def classify(text: str) -> str:
+ prompt = PROMPT_TEMPLATE.format(text=text[:20_000])
+ raw = generate_text(MODEL, prompt, num_ctx=8192).strip().lower()
+ # Model sometimes wraps in quotes or adds a period — strip.
+ raw = raw.strip("`\"' .\n")
+ return raw if raw in CATEGORIES else "other"
+
+
+def main() -> None:
+ ap = argparse.ArgumentParser()
+ ap.add_argument("--limit", type=int, default=50)
+ args = ap.parse_args()
+
+ with psycopg.connect(PG_DSN) as conn:
+ with conn.cursor() as cur:
+ cur.execute(
+ """
+ SELECT filing_id, text_path FROM bankrupt_leads.filings
+ WHERE classified_at IS NULL AND text_path IS NOT NULL
+ LIMIT %s
+ """,
+ (args.limit,),
+ )
+ rows = cur.fetchall()
+
+ for filing_id, text_path in rows:
+ text = Path(text_path).read_text()
+ doc_type = classify(text)
+ with conn.cursor() as cur:
+ cur.execute(
+ "UPDATE bankrupt_leads.filings SET doc_type = %s, classified_at = now() "
+ "WHERE filing_id = %s",
+ (doc_type, filing_id),
+ )
+ conn.commit()
+ print(f"{filing_id}: {doc_type}")
+
+
+if __name__ == "__main__":
+ main()
diff --git a/src/digest.py b/src/digest.py
new file mode 100644
index 0000000..e76d1db
--- /dev/null
+++ b/src/digest.py
@@ -0,0 +1,106 @@
+"""
+Weekly vertical digest builder.
+
+Pulls new assets in a vertical over the last 7 days and renders a plain-text
+digest suitable for emailing to dealer subscribers. Prints to stdout; caller
+decides how to send (George Gmail agent, Resend, manual, etc.).
+
+Usage:
+ python digest.py --vertical restaurant_equipment --days 7
+"""
+from __future__ import annotations
+
+import argparse
+import os
+from datetime import date, timedelta
+
+import psycopg
+
+PG_DSN = os.environ.get("BANKRUPT_LEADS_PG", "postgresql:///bankrupt_leads")
+
+VERTICAL_FILTERS = {
+ "restaurant_equipment": {
+ "categories": ("equipment", "inventory"),
+ "keywords": (
+ "kitchen", "oven", "fryer", "hood", "walk-in", "walkin", "refrigerator",
+ "freezer", "restaurant", "cafe", "bar", "pizza", "grill", "bakery",
+ "dish", "prep", "hobart", "vulcan", "true", "traulsen",
+ ),
+ },
+ "machine_tools": {
+ "categories": ("equipment",),
+ "keywords": (
+ "cnc", "lathe", "mill", "milling", "grinder", "press", "haas", "mazak",
+ "okuma", "doosan", "fanuc", "bridgeport", "machine shop",
+ ),
+ },
+ "medical_equipment": {
+ "categories": ("equipment",),
+ "keywords": (
+ "mri", "ct scan", "ultrasound", "x-ray", "xray", "surgical", "dental",
+ "exam table", "sterilizer", "autoclave", "ge healthcare", "siemens",
+ ),
+ },
+}
+
+
+def fetch_assets(vertical: str, since: date) -> list[dict]:
+ cfg = VERTICAL_FILTERS[vertical]
+ like_clause = " OR ".join(["description ILIKE %s"] * len(cfg["keywords"]))
+ sql = f"""
+ SELECT a.category, a.subcategory, a.description, a.location,
+ a.book_value_usd, a.sale_date, a.sale_venue,
+ a.contact_name, a.contact_email, a.contact_phone,
+ c.debtor_name, c.court, c.docket_number
+ FROM bankrupt_leads.assets a
+ JOIN bankrupt_leads.cases c ON c.case_id = a.case_id
+ WHERE a.extracted_at >= %s
+ AND a.category = ANY(%s)
+ AND ({like_clause})
+ ORDER BY a.sale_date NULLS LAST, a.book_value_usd DESC NULLS LAST
+ """
+ params = [since, list(cfg["categories"])] + [f"%{kw}%" for kw in cfg["keywords"]]
+ with psycopg.connect(PG_DSN) as conn, conn.cursor() as cur:
+ cur.execute(sql, params)
+ cols = [d[0] for d in cur.description]
+ return [dict(zip(cols, row)) for row in cur.fetchall()]
+
+
+def render(vertical: str, assets: list[dict], since: date) -> str:
+ title = vertical.replace("_", " ").title()
+ lines = [
+ f"{title} — Bankruptcy Asset Digest",
+ f"New leads filed {since.isoformat()} → {date.today().isoformat()}",
+ f"{len(assets)} asset{'s' if len(assets) != 1 else ''}",
+ "=" * 60,
+ "",
+ ]
+ for a in assets:
+ val = f"${a['book_value_usd']:,.0f}" if a["book_value_usd"] else "value n/d"
+ sale = f" — sale {a['sale_date']}" if a["sale_date"] else ""
+ lines.append(f"• {a['debtor_name']} ({a['court']} {a['docket_number']})")
+ lines.append(f" {a['subcategory'] or a['category']}: {a['description'][:200]}")
+ lines.append(f" {a['location'] or 'location n/d'} — {val}{sale}")
+ if a["contact_name"]:
+ lines.append(
+ f" contact: {a['contact_name']}"
+ + (f" <{a['contact_email']}>" if a["contact_email"] else "")
+ + (f" {a['contact_phone']}" if a["contact_phone"] else "")
+ )
+ lines.append("")
+ return "\n".join(lines)
+
+
+def main() -> None:
+ ap = argparse.ArgumentParser()
+ ap.add_argument("--vertical", required=True, choices=sorted(VERTICAL_FILTERS))
+ ap.add_argument("--days", type=int, default=7)
+ args = ap.parse_args()
+
+ since = date.today() - timedelta(days=args.days)
+ assets = fetch_assets(args.vertical, since)
+ print(render(args.vertical, assets, since))
+
+
+if __name__ == "__main__":
+ main()
diff --git a/src/extract.py b/src/extract.py
new file mode 100644
index 0000000..6e0cbe8
--- /dev/null
+++ b/src/extract.py
@@ -0,0 +1,112 @@
+"""
+Asset extractor — local Ollama (default qwen3:14b) with JSON-mode.
+
+Uses /api/chat format="json" so the model returns parsable output.
+For tougher filings, swap EXTRACT_MODEL to gemma3:27b.
+"""
+from __future__ import annotations
+
+import argparse
+import os
+from pathlib import Path
+
+import psycopg
+
+from llm import chat_json
+
+PG_DSN = os.environ.get("BANKRUPT_LEADS_PG", "postgresql:///bankrupt_leads")
+MODEL = os.environ.get("EXTRACT_MODEL", "qwen3:14b")
+
+EXTRACT_SCHEMA = """
+You extract saleable assets from US bankruptcy court filings (Ch. 7 / 11 / Subchapter V).
+
+Input: the raw text of ONE filing -- typically a Schedule A/B, a 363 sale motion, an
+auctioneer retention application, an asset purchase agreement (APA), or an abandonment
+notice.
+
+Output: a JSON object with a single key "assets" whose value is an array. Each array
+element has this shape:
+
+{
+ "category": "real_estate" | "equipment" | "vehicles" | "inventory" | "ip" | "receivables" | "other",
+ "subcategory": short free-text (e.g. "commercial kitchen", "CNC machine", "domain portfolio"),
+ "description": full description as written in the filing (preserve make/model/year),
+ "location": city/state or street address if present, else null,
+ "book_value_usd": number or null,
+ "secured_party": name of secured creditor if disclosed, else null,
+ "sale_date": ISO date if a hearing / auction / bid deadline is set, else null,
+ "sale_venue": "auction" | "private_sale" | "stalking_horse" | null,
+ "contact_name": trustee, auctioneer, or debtor counsel (best point of contact),
+ "contact_email": email if present, else null,
+ "contact_phone": phone if present, else null,
+ "raw_excerpt": verbatim excerpt (<=500 chars) the extraction was based on
+}
+
+Rules:
+- If the filing contains NO saleable assets, return {"assets": []}.
+- Never invent values. If a field is not in the text, use null.
+- Collapse duplicate line items into one entry.
+- Ignore exempt property (homestead, retirement, tools-of-trade exemptions).
+- Return ONLY the JSON object.
+"""
+
+
+def load_filing_text(filing_id: str) -> tuple[str, str]:
+ with psycopg.connect(PG_DSN) as conn, conn.cursor() as cur:
+ cur.execute(
+ "SELECT case_id, text_path FROM bankrupt_leads.filings WHERE filing_id = %s",
+ (filing_id,),
+ )
+ row = cur.fetchone()
+ if not row:
+ raise SystemExit(f"filing {filing_id} not found")
+ case_id, text_path = row
+ return case_id, Path(text_path).read_text()
+
+
+def extract(filing_text: str) -> list[dict]:
+ result = chat_json(
+ MODEL,
+ system=EXTRACT_SCHEMA,
+ user=filing_text[:100_000],
+ num_ctx=32768,
+ )
+ return result.get("assets", []) if isinstance(result, dict) else []
+
+
+def persist(case_id: str, filing_id: str, assets: list[dict]) -> None:
+ with psycopg.connect(PG_DSN) as conn, conn.cursor() as cur:
+ for a in assets:
+ cur.execute(
+ """
+ INSERT INTO bankrupt_leads.assets
+ (case_id, filing_id, category, subcategory, description, location,
+ book_value_usd, secured_party, sale_date, sale_venue,
+ contact_name, contact_email, contact_phone, raw_excerpt)
+ VALUES (%(case_id)s, %(filing_id)s, %(category)s, %(subcategory)s,
+ %(description)s, %(location)s, %(book_value_usd)s, %(secured_party)s,
+ %(sale_date)s, %(sale_venue)s, %(contact_name)s, %(contact_email)s,
+ %(contact_phone)s, %(raw_excerpt)s)
+ """,
+ {**a, "case_id": case_id, "filing_id": filing_id},
+ )
+ cur.execute(
+ "UPDATE bankrupt_leads.filings SET extracted_at = now() WHERE filing_id = %s",
+ (filing_id,),
+ )
+ conn.commit()
+
+
+def main() -> None:
+ ap = argparse.ArgumentParser()
+ ap.add_argument("--filing-id", required=True)
+ args = ap.parse_args()
+
+ case_id, text = load_filing_text(args.filing_id)
+ assets = extract(text)
+ print(f"extracted {len(assets)} asset(s) from {args.filing_id}")
+ persist(case_id, args.filing_id, assets)
+
+
+if __name__ == "__main__":
+ main()
diff --git a/src/llm.py b/src/llm.py
new file mode 100644
index 0000000..03496d3
--- /dev/null
+++ b/src/llm.py
@@ -0,0 +1,50 @@
+"""
+Thin Ollama client. No cloud APIs.
+
+Uses /api/chat with format="json" so the model returns parsable JSON.
+Falls back to /api/generate for classify (string label) calls.
+"""
+from __future__ import annotations
+
+import json
+import os
+
+import httpx
+
+OLLAMA_HOST = os.environ.get("OLLAMA_HOST", "http://localhost:11434")
+
+
+def chat_json(model: str, system: str, user: str, *, num_ctx: int = 32768) -> dict:
+ """Chat call constrained to JSON output. Returns parsed dict."""
+ with httpx.Client(timeout=600) as c:
+ r = c.post(
+ f"{OLLAMA_HOST}/api/chat",
+ json={
+ "model": model,
+ "format": "json",
+ "stream": False,
+ "options": {"temperature": 0, "num_ctx": num_ctx},
+ "messages": [
+ {"role": "system", "content": system},
+ {"role": "user", "content": user},
+ ],
+ },
+ )
+ r.raise_for_status()
+ body = r.json()["message"]["content"]
+ return json.loads(body)
+
+
+def generate_text(model: str, prompt: str, *, num_ctx: int = 8192) -> str:
+ with httpx.Client(timeout=600) as c:
+ r = c.post(
+ f"{OLLAMA_HOST}/api/generate",
+ json={
+ "model": model,
+ "prompt": prompt,
+ "stream": False,
+ "options": {"temperature": 0, "num_ctx": num_ctx},
+ },
+ )
+ r.raise_for_status()
+ return r.json()["response"].strip()
diff --git a/src/poller.py b/src/poller.py
new file mode 100644
index 0000000..0b852ce
--- /dev/null
+++ b/src/poller.py
@@ -0,0 +1,86 @@
+"""
+CourtListener RECAP poller.
+
+Pulls recent Ch. 7 / 11 / Subchapter V filings from the free RECAP archive.
+Docs: https://www.courtlistener.com/help/api/rest/recap/
+
+Usage:
+ python poller.py --since 2026-04-01 --courts nysb,cacb,txsb
+"""
+from __future__ import annotations
+
+import argparse
+import os
+from datetime import date, datetime, timedelta
+
+import httpx
+import psycopg
+
+COURTLISTENER_TOKEN = os.environ.get("COURTLISTENER_TOKEN")
+PG_DSN = os.environ.get("BANKRUPT_LEADS_PG", "postgresql:///bankrupt_leads")
+
+BASE = "https://www.courtlistener.com/api/rest/v4"
+HEADERS = {"Authorization": f"Token {COURTLISTENER_TOKEN}"} if COURTLISTENER_TOKEN else {}
+
+# Chapter codes appear in the docket nature_of_suit / cause fields inconsistently
+# across courts — we filter downstream once we have the filing text.
+BANKRUPTCY_NATURE = "bankruptcy"
+
+
+def fetch_new_dockets(court: str, since: date) -> list[dict]:
+ """List dockets filed >= `since` in a given bankruptcy court."""
+ params = {
+ "court": court,
+ "date_filed__gte": since.isoformat(),
+ "nature_of_suit__icontains": BANKRUPTCY_NATURE,
+ "order_by": "-date_filed",
+ "page_size": 100,
+ }
+ with httpx.Client(headers=HEADERS, timeout=30) as client:
+ r = client.get(f"{BASE}/dockets/", params=params)
+ r.raise_for_status()
+ return r.json().get("results", [])
+
+
+def upsert_case(conn, docket: dict, court: str) -> None:
+ case_id = f"{court}-{docket['docket_number']}"
+ with conn.cursor() as cur:
+ cur.execute(
+ """
+ INSERT INTO bankrupt_leads.cases
+ (case_id, court, docket_number, chapter, debtor_name, filing_date)
+ VALUES (%s, %s, %s, %s, %s, %s)
+ ON CONFLICT (case_id) DO UPDATE SET last_checked_at = now()
+ """,
+ (
+ case_id,
+ court,
+ docket["docket_number"],
+ # Chapter is inferred later from filing text; placeholder for now.
+ "unknown",
+ docket.get("case_name", "")[:500],
+ docket["date_filed"],
+ ),
+ )
+
+
+def main() -> None:
+ ap = argparse.ArgumentParser()
+ ap.add_argument("--since", default=(date.today() - timedelta(days=1)).isoformat())
+ ap.add_argument("--courts", default="nysb,cacb,txsb,deb,njb")
+ args = ap.parse_args()
+
+ since = datetime.fromisoformat(args.since).date()
+ courts = [c.strip() for c in args.courts.split(",") if c.strip()]
+
+ with psycopg.connect(PG_DSN) as conn:
+ for court in courts:
+ dockets = fetch_new_dockets(court, since)
+ print(f"{court}: {len(dockets)} new dockets since {since}")
+ for d in dockets:
+ upsert_case(conn, d, court)
+ conn.commit()
+
+
+if __name__ == "__main__":
+ main()
diff --git a/src/schema.sql b/src/schema.sql
new file mode 100644
index 0000000..1a4acd8
--- /dev/null
+++ b/src/schema.sql
@@ -0,0 +1,70 @@
+-- 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()
+);
(oldest)
·
back to Bankrupt Leads
·
add canonical .gitignore per standing rule 924a861 →