Database schema reference

The schema is defined in supabase/migrations/0001_initial_schema.sql. That file is the source of truth; this page is a navigable summary.

Tables

tenants

The workspace itself. One row per client.

Column	Type	Notes
id	uuid PK	gen_random_uuid() default
slug	text UNIQUE	subdomain; matches ^[a-z0-9][a-z0-9-]{0,62}$
name	text	display name
logo_url	text NULL	Supabase Storage URL once Phase 7 lands
primary_color	text NULL	hex like #18181b
tagline	text NULL	small descriptor on splash
api_key_hash	text	SHA-256 of the plaintext key
settings	jsonb	open-ended per-tenant settings
created_at, updated_at	timestamptz	updated_at via trigger

Indexes: slug.

tenant_members

Bridge table between auth.users and tenants. A single user can belong to multiple tenants.

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK → tenants ON DELETE CASCADE
user_id	uuid FK → auth.users ON DELETE CASCADE
role	text DEFAULT 'member'	enum-checked: 'member' | 'admin' | 'manager'
invited_by	uuid FK → auth.users ON DELETE SET NULL
joined_at	timestamptz

Constraints: UNIQUE (tenant_id, user_id). Indexes: tenant_id, user_id.

tenant_invites

Outstanding invitations. Once accepted, the row stays for audit (with accepted_at set).

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK
email	text	lowercased on insert
token	text UNIQUE	32 random bytes, base64url
role	text DEFAULT 'member'	same enum as tenant_members.role
invited_by	uuid FK → auth.users NULL
expires_at	timestamptz	now + 7 days at provisioning time
accepted_at	timestamptz NULL	set on acceptance
created_at	timestamptz

Indexes: tenant_id, (tenant_id, email), token.

pipeline_stages

Per-tenant kanban columns.

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK
name	text	"New", "Contacted", etc.
color	text DEFAULT '#71717a'	hex
sort_order	integer	1, 2, 3, ...
stage_type	text DEFAULT 'active'	enum: 'active' | 'won' | 'lost'
created_at	timestamptz

Indexes: (tenant_id, sort_order).

Default stages are seeded by seed_default_pipeline_stages(tenant_id) (see Helper functions below).

leads

The core record. One row per lead, regardless of source.

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK
first_name, last_name	text NULL
email, phone	text NULL	at least one of the two is required
street, city, state, zip	text NULL	optional address fields
source	text NULL	"website-contact-form", "phone-walk-in", etc.
utm_source ... utm_content	text NULL	full UTM set
metadata	jsonb DEFAULT '{}'	open-ended per-lead extras (loan amount, case type, anything the form sends)
status	text DEFAULT 'new'	enum: 'new' | 'open' | 'won' | 'lost'
pipeline_stage_id	uuid FK NULL	which kanban column
assigned_to	uuid FK → auth.users NULL	v2 hook, hidden in v1 UI
created_at, updated_at	timestamptz	trigger updates updated_at

Constraints: email IS NOT NULL OR phone IS NOT NULL. Indexes: (tenant_id, created_at desc), (tenant_id, email), (tenant_id, phone), (tenant_id, pipeline_stage_id), (tenant_id, assigned_to).

lead_notes

Free-text notes attached to a lead.

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK
lead_id	uuid FK ON DELETE CASCADE
author_id	uuid FK → auth.users NULL	who wrote it
body	text
created_at	timestamptz

Indexes: (lead_id, created_at desc), tenant_id.

lead_activities

Auto-generated audit log for a lead. v1 does not allow manual activity insertion; only system code writes here.

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK
lead_id	uuid FK ON DELETE CASCADE
type	text	enum below
data	jsonb DEFAULT '{}'	event-specific payload
actor_id	uuid FK → auth.users NULL	null for API-created
created_at	timestamptz

Activity types (type enum):

• created — lead row was created
• stage_changed — pipeline stage updated; data: { from_stage_id, to_stage_id }
• status_changed — status updated; data: { from, to }
• note_added — new lead_notes row; data: { note_id }
• task_added — new tasks row linked to this lead; data: { task_id }
• task_completed — task linked to this lead was completed; data: { task_id }
• lead_assigned — assigned_to updated (v2); data: { from_user_id, to_user_id }

Indexes: (lead_id, created_at desc), tenant_id.

tasks

Follow-ups, optionally linked to a lead.

Column	Type	Notes
id	uuid PK
tenant_id	uuid FK
lead_id	uuid FK ON DELETE CASCADE NULL	optional
assigned_to	uuid FK → auth.users NULL	v2 hook, ignored in v1 UI
title	text
due_at	timestamptz NULL
completed_at	timestamptz NULL	non-null = task is done
created_by	uuid FK → auth.users NULL
created_at, updated_at	timestamptz

Indexes: (tenant_id, due_at) WHERE completed_at IS NULL (open-tasks index), lead_id, (tenant_id, assigned_to).

Row-Level Security

Every table above (except auth.users, which Supabase manages) has RLS enabled. The pattern is consistent: gate by is_tenant_member(tenant_id).

Helper function

CREATE OR REPLACE FUNCTION is_tenant_member(target UUID)
RETURNS BOOLEAN
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM tenant_members
    WHERE tenant_id = target AND user_id = auth.uid()
  );
$$;

SECURITY DEFINER and the explicit search_path are intentional: the function runs with the schema owner's privileges and a locked-down search path so it cannot be hijacked by callers manipulating their session.

Policy summary

Table	SELECT	INSERT	UPDATE	DELETE
tenants	members of self	(service-role only)	(service-role only)	(service-role only)
tenant_members	members of same tenant	(service-role only)	(service-role only)	(service-role only)
tenant_invites	members of same tenant	(service-role only)	(service-role only)	(service-role only)
pipeline_stages	members	members	members	members
leads	members	members	members	members
lead_notes	members	members & author=self	members & author=self	members & author=self
lead_activities	members	(service-role only)	(service-role only)	(service-role only)
tasks	members	members	members	members

"Service-role only" means the policy returns false for any normal authenticated request, and we only mutate via the admin Supabase client (which bypasses RLS entirely). This applies to provisioning, intake API, invite acceptance, etc.

Triggers

set_updated_at()       -- BEFORE UPDATE on tenants, leads, tasks
                       -- bumps updated_at to now()

Helper functions

seed_default_pipeline_stages(target_tenant UUID) RETURNS VOID

Inserts the five default stages (New / Contacted / Qualified / Won / Lost) for a freshly created tenant. Called by the provisioning script.

Realtime publication

ALTER PUBLICATION supabase_realtime ADD TABLE
  leads,
  lead_activities,
  lead_notes,
  tasks;

Authenticated browser clients subscribe to tenant:<id> channels. RLS still applies to the publication, so a client only sees broadcasts for tenants they're a member of.

Migrations

All schema changes go through new SQL files in supabase/migrations/000N_*.sql. Apply locally:

docker exec -i supabase-db psql -U postgres -d postgres < supabase/migrations/000N_*.sql

We do not (yet) use the Supabase CLI's migration runner because the self-hosted stack at localhost:8000 is not registered as a "linked" Supabase project. Direct psql is fine for our scale and gives us full control.

Sample queries

A tenant's open leads (as the tenant's own user, RLS-gated):

SELECT id, first_name, last_name, email, source, status, pipeline_stage_id, created_at
FROM leads
WHERE status IN ('new', 'open')
ORDER BY created_at DESC
LIMIT 50;

Activity timeline for a single lead:

SELECT type, data, actor_id, created_at
FROM lead_activities
WHERE lead_id = $1
ORDER BY created_at DESC;

Cross-tenant query (run as the service-role to verify RLS isolation):

-- as a normal authed user this returns ZERO rows for any tenant the user
-- is not a member of. as service-role, it returns everything.
SELECT tenant_id, COUNT(*) FROM leads GROUP BY tenant_id ORDER BY 2 DESC;