25+ tables, Row-Level Security on every one, pgvector for embeddings — organized by domain
Database Architecture
25+ tables, Row-Level Security on every one, pgvector for embeddings. Organized by domain.
Core / Auth Tables
| Table |
Key Columns |
Purpose |
| organizations |
id, name, plan, branding, stripe_customer_id |
Tenant root — every other table references this |
| profiles |
id, email, full_name, onboarding_complete |
User identity, linked to Supabase Auth |
| org_members |
org_id (FK), user_id (FK), role |
Maps users to orgs with role hierarchy |
| clients |
id, org_id (FK), name, email, status |
Client records scoped to org |
Content Tables
| Table |
Key Columns |
Purpose |
| proposals |
id, org_id (FK), client_id (FK), title, status, brain_status, deal_outcome |
Core document with AI generation status |
| contracts |
id, org_id (FK), client_name, title, scope, terms |
Service agreements with legal sections |
| templates |
id, org_id (FK), name, type, content (JSONB), is_system |
Reusable document templates |
| services |
id, org_id (FK), name, description |
Service catalog for proposal generation |
Intelligence Tables
| Table |
Key Columns |
Purpose |
| client_intelligence |
org_id (FK), client_name (UNIQUE), psychographics (JSONB), deal_history (JSONB[]), embedding (vector 1536) |
Per-client memory with vector search |
| org_brain |
id, org_id (FK), content_type, content_text, embedding (vector 1536) |
Org knowledge base for AI generation |
| agent_runs |
id, proposal_id (FK), status, current_step, cached_writer_output, resume_from |
Brain pipeline execution state |
| prospect_intelligence |
org_id (FK), prospect_name (UNIQUE), website_url, profile (JSONB), cached_at |
Cached web research on prospects |
System Tables
| Table |
Key Columns |
Purpose |
| jobs |
id, org_id (FK), job_type, status, payload (JSONB), result (JSONB), actual_tokens |
Async job queue — the worker polls this |
| audit_log |
user_id (FK), org_id (FK), action, resource_type, ip, user_agent |
Security event log |
| system_settings |
key (PK), value (JSONB), description |
Global config (kill switches, feature flags) |
Billing Tables
| Table |
Key Columns |
Purpose |
| invoices |
id, org_id (FK), stripe_invoice_id, amount, status |
Synced from Stripe webhooks |
Row-Level Security (RLS): Every table has RLS policies that filter by org_id. When a user queries proposals, they only see their organization's proposals — enforced at the database layer, not application code. Even a bug in the API route can't leak data across tenants.