Reference

Database schema

OpsDash uses Supabase (PostgreSQL 15). All tables have Row-Level Security enabled. The schema is managed by 57 additive migrations in supabase/migrations/ — migrations are never breaking.

Key convention: All domain tables include org_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE and an RLS policy checking is_org_member(org_id).

`organizations`

Workspaces — the root of all data scoping.

Column	Type	Notes
id	uuid PK	gen_random_uuid()
name	text	Display name
slug	text UNIQUE	URL identifier — /org/[slug]/
type	text	'personal' \| 'team'
billing_owner_user_id	uuid FK → users	Billing anchor for team orgs
onboarding_completed	boolean	False until setup wizard completes
settings	jsonb	Org-level preferences
deleted_at	timestamptz	Soft-delete timestamp; NULL = active
created_at	timestamptz	Row creation timestamp

`users`

Public profile for auth.users. Created by handle_new_user() trigger.

Column	Type	Notes
id	uuid PK FK → auth.users	Same UUID as Supabase auth
full_name	text	Display name
avatar_url	text	Profile image URL
current_org_id	uuid FK → organizations	Last active org — used for login redirect
preferences	jsonb	Notification, theme preferences
created_at	timestamptz

`org_members`

Join table for users ↔ organizations with role.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users
org_id	uuid FK → organizations
role	org_role ENUM	'owner' \| 'admin' \| 'member' \| 'viewer'
created_at	timestamptz

`subscriptions`

One row per org. Plan drives feature access.

Column	Type	Notes
id	uuid PK
org_id	uuid FK → organizations	One subscription per org
plan	subscription_plan ENUM	'free' \| 'pro' \| 'enterprise'
status	subscription_status ENUM	'active' \| 'trialing' \| 'past_due' \| 'canceled'
stripe_subscription_id	text	Stripe subscription reference
stripe_customer_id	text	Stripe customer reference
current_period_end	timestamptz	When current billing period ends

`feature_flags`

Per-org feature toggles with role restrictions.

Column	Type	Notes
id	uuid PK
org_id	uuid FK → organizations
feature_key	text	e.g. 'crm:contacts', 'projects:milestones'
enabled	boolean	Feature on/off for this org
allowed_roles	text[]	NULL = all roles; array = restrict to listed roles
created_at	timestamptz

`contacts`

CRM contacts — people the agency works with.

Column	Type	Notes
id	uuid PK
org_id	uuid FK → organizations	RLS scope
full_name	text
email	text
phone	text
company_id	uuid FK → companies	Optional link
lead_source	text
tags	text[]	Max 30 per contact — enforced by trigger
assigned_to	uuid FK → users
metadata	jsonb	Custom field values
deleted_at	timestamptz	Soft-delete
created_at	timestamptz

`deals`

Sales pipeline deals.

Column	Type	Notes
id	uuid PK
org_id	uuid FK → organizations
title	text
value	numeric	Deal value in org currency
probability	integer	0–100
stage_id	uuid FK → deal_stages
contact_id	uuid FK → contacts
company_id	uuid FK → companies
expected_close_date	date
status	text	'open' \| 'won' \| 'lost'
metadata	jsonb	Custom field values
deleted_at	timestamptz

`projects`

Delivery projects — linked to deals.

Column	Type	Notes
id	uuid PK
org_id	uuid FK → organizations
name	text
status	text	'planning' \| 'active' \| 'on_hold' \| 'completed'
deal_id	uuid FK → deals	Optional — links delivery to sale
budget	numeric	Project budget
start_date	date
end_date	date
client	text	Client name (denormalized for display)
metadata	jsonb	Custom field values
deleted_at	timestamptz

`tasks`

Atomic units of work within projects.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects
org_id	uuid FK → organizations
title	text
status	text	'todo' \| 'in_progress' \| 'in_review' \| 'done'
priority	text	'low' \| 'medium' \| 'high' \| 'urgent'
assignee_id	uuid FK → users
due_date	date
story_points	integer	Estimation (Pro)
parent_task_id	uuid FK → tasks	For subtasks (Pro)
deal_id	uuid FK → deals	Optional deal link
deleted_at	timestamptz	Soft-delete

`task_time_entries`

Time logged against tasks — drives profitability calculation.

Column	Type	Notes
id	uuid PK
task_id	uuid FK → tasks
user_id	uuid FK → users
org_id	uuid FK → organizations
duration_minutes	integer
description	text
logged_at	date

`audit_logs`

Immutable log of every mutation.

Column	Type	Notes
id	uuid PK
org_id	uuid FK → organizations
user_id	uuid FK → users
action	text	e.g. contact.created, deal.updated
metadata	jsonb	Entity IDs and field values
created_at	timestamptz