Multi-tenant lead intelligence with restricted-role DB access

Dedicated Postgres schema plus restricted DB role. The Supabase project is shared. The prospector schema is not. Migration 002_prospector_app_role.sql creates a prospector_app role that is NOLOGIN, has usage grants only on the prospector schema, and no access to anything else in the project. The app signs NextAuth JWTs with role: prospector_app; PostgREST enforces the boundary. Anonymous and authenticated Supabase roles have zero grants – the "public API" of the shared project cannot see a Prospector row.

Deterministic ICP scoring, LLM-free. scoring.ts supports three criterion types: numeric_range (ideal min/max with linear falloff, hard bounds), enum_map (normalised string lookup), and manual (user-entered 0–1). Missing inputs keep a company unscored (never defaulted to 0), with a partial breakdown showing which criteria are missing. A SHA-256 hash of the active criteria plus scoring version invalidates stale scores automatically; a rescore touches score columns only in chunks of 250. No LLM ever influences the score. That decision closes off a whole class of "why is this account WARM this week and MONITOR next?" that no LLM path can honestly answer.

Tenant scoping in the app layer, verified by tests. A tenantScoped() helper wraps every read/write; a middleware test verifies every API route rejects unauthenticated traffic. The database prevents cross-schema leaks; the app prevents cross-tenant leaks within the schema. Belt-and-suspenders is deliberate here – one of the two layers alone would be brittle.

Enrichment eligibility gates. Companies must be scored to enter enrichment; unscored companies are held in flight (not force-purged) so a later discovery run can fill missing inputs. Rows scored unscored with no financials and unreachable sites move to disqualified after a judge pass – never deleted, so discovery's known-orgnr check never re-adds them.

Alternatives considered and rejected:

• Separate Supabase project per tool. Would have doubled ops for a single-user internal tool. The restricted-role approach carries its weight instead.
• LLM-assisted scoring for edge cases. Ships fast, feels smart, becomes an unauditable disagreement engine within a quarter. Not a fit for the "why is this WARM" conversation.
• App-layer auth as the sole boundary. One accidental service-role query and the boundary is gone. The restricted role forces PostgREST to enforce it structurally.

• Excel export with a formula-injection sanitizer. ExcelJS workbook with two sheets (Prospects, Activity Log). Every string cell passes through sanitizeCell(), which strips leading =, +, @, and – characters. Estimators paste rows into other workbooks; a mistyped material name should not exfiltrate one.
• GDPR default: personal emails excluded. Norwegian markedsføringsloven §15 requires opt-in for personal (non-role) email marketing. Personal addresses are flagged and excluded from export by default; a ?includePersonalEmails=1 query parameter overrides with an audit-trail row.
• Session revocation without logout. Every user has a session_version column, bumped when a password is reset. Live JWTs become invalid the next time PostgREST checks the row, without needing a logout endpoint or a session store.
• SSRF guard on any user-supplied URL. Internal IP ranges blocked, redirect count capped at 3, probes issued as GET Range: bytes=0-0 expecting 200 or 206. Lookalike-URL detection runs before any external fetch.
• Mobile-native "Today" view. Three surfaces stacked on page.tsx: overdue plus due-today actions as row cards with owner and score chips, funnel strip by stage (linking to filtered rows), full sortable-filterable-exportable prospect table. Desktop uses the table; mobile uses stacked cards with a filter drawer – separate renderings, not responsive shrinking.

• 80 test files, ~11,000 lines of test code. Golden tests for scoring config-hash mismatch, formula-injection defence, SSRF range validation, middleware-wide 401-if-unauthenticated sweep, stage-transition rules, idempotent contact-merging on enrichment.
• Every paid API call tagged and logged to Langfuse. Hunter, Firecrawl, EXA, Claude – spend visible per tenant per day in the fleet dashboard.
• Migrations are idempotent. Sixteen migration files, safe to re-apply, small enough to review one-shot.

1. Postgres multi-tenancy without multi-project overhead – schema isolation, restricted role with NOLOGIN and scoped grants, JWT-signed role assignment, PostgREST as the structural boundary.
2. Security-first data plumbing – RLS by default, formula-injection sanitisation on export, SSRF guard on user-supplied URLs, session revocation via row-versioning.
3. Deterministic scoring at production quality – hashed-config staleness detection, missing-input handling as first-class state (not zero-defaulted), pure functions with golden tests.
4. Trigger.dev v4 orchestration – enrichment, demo-build, template generation, outreach drafts as chained tasks with cost tags per hop.
5. Mobile-first productivity UX – separate mobile and desktop renderings for a workbench used standing up, not a responsive-shrink of a desktop table.

1. A restricted DB role is a one-migration pattern that closes a whole class of shared-project blast radius. Every new tool inside a shared Supabase project gets one; app-layer auth alone is not enough.
2. Missing inputs must be first-class state. Defaulting to zero is a lie the model doesn't know it's telling; unscored with a partial breakdown is the honest surface.
3. Sanitise export cells, always. Formula injection via CSV/XLSX is one of the oldest exfiltration paths and one of the easiest to close – never ship an export without it.