Database Schema

The kit uses 19 Supabase tables organized into five domains: people, deals, productivity, communication, and system. All tables have row-level security (RLS) enabled with a shared workspace model.

Schema Overview

People:         profiles, companies, contacts, tags, contact_tags, company_tags

Deals:          pipelines, pipeline_stages, deals, deal_products, deal_tags

Productivity:   tasks, activities

Communication:  emails, email_templates, email_sequences, email_sequence_steps

System:         notifications, crm_settings

Tables

profiles

Auto-created on user signup via database trigger. Stores team member information.

Column	Type	Notes
id	uuid (PK, FK)	References auth.users.id
full_name	text	Display name
avatar_url	text	Profile image URL
email	text	User email
phone	text	Phone number
role	text	member or admin
job_title	text	Role in the organization
quota	numeric	Sales quota amount
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

companies

Organizations tracked in the CRM.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Company name
industry	text	Industry category
website	text	Company URL
phone	text	Company phone
email	text	General contact email
description	text	Company description
logo_url	text	Logo from crm-assets bucket
employee_count	integer	Number of employees
annual_revenue	numeric	Annual revenue
founded_year	integer	Year founded
address	jsonb	{street, city, state, zip, country}
social	jsonb	{linkedin, twitter}
owner_id	uuid (FK)	References profiles.id
search_vector	tsvector	Full-text search index
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

contacts

Individual people associated with companies.

Column	Type	Notes
id	uuid (PK)	Auto-generated
first_name	text	First name
last_name	text	Last name
email	text	Contact email
phone	text	Phone number
job_title	text	Job title
company_id	uuid (FK)	References companies.id
owner_id	uuid (FK)	References profiles.id
lead_source	text	website, referral, linkedin, cold_call, event, other
address	jsonb	{street, city, state, zip, country}
social	jsonb	{linkedin, twitter, website}
notes	text	Freeform notes
avatar_url	text	Contact photo
last_contacted	timestamptz	Auto-updated by trigger
search_vector	tsvector	Full-text search index
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

tags

Reusable labels for contacts, companies, and deals.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text (unique)	Tag label
color	text	Display color

contact_tags / company_tags / deal_tags

Junction tables linking tags to entities.

Column	Type	Notes
entity_id	uuid (FK)	References the entity table
tag_id	uuid (FK)	References tags.id
Primary key	composite	(entity_id, tag_id)

pipelines

Named sales pipelines (e.g., "Sales Pipeline", "Enterprise Pipeline").

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Pipeline name
description	text	Pipeline description
is_default	boolean	Default pipeline for new deals
created_at	timestamptz	Auto-set

pipeline_stages

Ordered stages within each pipeline.

Column	Type	Notes
id	uuid (PK)	Auto-generated
pipeline_id	uuid (FK)	References pipelines.id
name	text	Stage name (e.g., "Lead", "Qualified")
color	text	Stage color for Kanban column
position	integer	Display order
default_probability	integer	0-100 probability when deal enters stage
description	text	Stage description

deals

Individual deals tracked through pipeline stages.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Deal name
description	text	Deal description
value	numeric	Deal monetary value
probability	integer	Win probability (0-100)
company_id	uuid (FK)	References companies.id
contact_id	uuid (FK)	References contacts.id
pipeline_id	uuid (FK)	References pipelines.id
stage_id	uuid (FK)	References pipeline_stages.id
owner_id	uuid (FK)	References profiles.id
priority	text	high, medium, or low
close_date	date	Expected close date
source	text	Lead source
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

deal_products

Line items / products associated with a deal.

Column	Type	Notes
id	uuid (PK)	Auto-generated
deal_id	uuid (FK)	References deals.id
name	text	Product/service name
quantity	integer	Quantity
unit_price	numeric	Price per unit

tasks

To-do items linked to contacts, deals, or standalone.

Column	Type	Notes
id	uuid (PK)	Auto-generated
title	text	Task title
description	text	Task details
priority	text	high, medium, or low
status	text	pending, in_progress, or completed
due_date	date	Due date
assignee_id	uuid (FK)	References profiles.id
contact_id	uuid (FK)	References contacts.id (nullable)
deal_id	uuid (FK)	References deals.id (nullable)
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

activities

Chronological log of CRM actions (calls, emails, meetings, notes, stage changes).

Column	Type	Notes
id	uuid (PK)	Auto-generated
type	text	call, email, meeting, note, stage_change
description	text	Activity description
user_id	uuid (FK)	References profiles.id
contact_id	uuid (FK)	References contacts.id (nullable)
deal_id	uuid (FK)	References deals.id (nullable)
company_id	uuid (FK)	References companies.id (nullable)
metadata	jsonb	Extra data (e.g., old/new stage for stage_change)
created_at	timestamptz	Auto-set

emails

Email messages sent and received within the CRM.

Column	Type	Notes
id	uuid (PK)	Auto-generated
subject	text	Email subject
body	text	Email body content
from_email	text	Sender email
to_email	text	Recipient email
folder	text	inbox, sent, or drafts
is_read	boolean	Read status
is_starred	boolean	Starred flag
contact_id	uuid (FK)	Linked CRM contact (nullable)
deal_id	uuid (FK)	Linked deal (nullable)
created_at	timestamptz	Auto-set

email_templates

Reusable email templates with merge field support.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Template name
subject	text	Subject line with merge fields
body	text	Body with merge fields
category	text	Sales, Follow-up, Onboarding, Support
usage_count	integer	Times used
created_by	uuid (FK)	References profiles.id
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

email_sequences

Automated multi-step email campaigns.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Sequence name
status	text	active, paused, or draft
enrolled_count	integer	Contacts enrolled
open_rate	numeric	Aggregate open rate
reply_rate	numeric	Aggregate reply rate
created_by	uuid (FK)	References profiles.id
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

email_sequence_steps

Individual steps within an email sequence.

Column	Type	Notes
id	uuid (PK)	Auto-generated
sequence_id	uuid (FK)	References email_sequences.id
step_type	text	email, wait, or condition
position	integer	Step order
template_id	uuid (FK)	References email_templates.id (nullable)
delay_days	integer	Wait duration for wait steps
condition_type	text	For condition steps: opened, clicked, replied
content	text	Custom email content if no template

notifications

In-app notifications for team members.

Column	Type	Notes
id	uuid (PK)	Auto-generated
user_id	uuid (FK)	References profiles.id
title	text	Notification title
message	text	Notification body
type	text	deal, task, contact, system
is_read	boolean	Read status
link	text	URL to navigate to
created_at	timestamptz	Auto-set

crm_settings

Global CRM configuration (single row).

Column	Type	Notes
id	uuid (PK)	Auto-generated
company_name	text	Organization name
company_logo	text	Logo URL
default_currency	text	Currency code (e.g., "USD")
fiscal_year_start	integer	Month number (1-12)
date_format	text	e.g., "MM/DD/YYYY"
timezone	text	e.g., "America/New_York"
updated_at	timestamptz	Auto-updated by trigger

Row-Level Security

RLS is enabled on every table. The kit uses a shared workspace model: every active team member can read and edit all CRM data. Team management and CRM settings are limited to admins.

Helper Functions

-- Check if the current user is an active team member
CREATE OR REPLACE FUNCTION is_team_member()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid()
    AND status = 'active'
  );
$$ LANGUAGE sql SECURITY DEFINER SET search_path = '';

-- Check if the current user is an active CRM admin
CREATE OR REPLACE FUNCTION is_crm_admin()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid()
    AND role = 'admin'
    AND status = 'active'
  );
$$ LANGUAGE sql SECURITY DEFINER SET search_path = '';

All helper and trigger functions use SECURITY DEFINER with SET search_path = '', so every table name inside them is schema-qualified (public.profiles).

Security Hardening

• New accounts start as invited: the handle_new_user() trigger creates every profile with role = 'rep' and status = 'invited'. Invited users can't read CRM data until an admin sets them to active, so a public sign-up page doesn't expose your customer records.
• Column-level grants on profiles: users can only update full_name and avatar_url on their own profile. role, status, quota and the performance columns can't be changed from the browser. Admin changes to those columns go through the admin (service role) client.
• Role never read from sign-up data: the trigger hardcodes the default role instead of reading raw_user_meta_data, which the person signing up controls.

Policy Summary

"Team" means an active team member (is_team_member()).

Table	Team Read	Team Write	Admin-Only Write
profiles	All profiles	Own full_name and avatar_url	Update any profile
companies	All	Insert, update, delete	-
contacts	All	Insert, update, delete	-
deals, deal_products	All	Insert, update, delete	-
tasks	All	Insert, update, delete	-
activities	All	Insert	-
emails, email_templates	All	Insert, update, delete	-
email_sequences, email_sequence_steps	All	Insert, update, delete	-
tags and tag join tables	All	Insert, update, delete	-
pipelines, pipeline_stages	All	Insert, update, delete	-
notifications	Own only	Update own; team inserts	-
crm_settings	All	-	Insert, update

Note: Deleting records and managing pipelines are open to every active team member, both in RLS and in the Server Actions. If only admins should do these in your CRM, change those policies to is_crm_admin() and add an admin check to the matching actions.

Triggers

Profile Auto-Creation

When a new user signs up, a trigger automatically creates their profile:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, avatar_url, role, status)
  VALUES (
    new.id,
    coalesce(new.raw_user_meta_data ->> 'full_name', new.raw_user_meta_data ->> 'name', ''),
    coalesce(new.raw_user_meta_data ->> 'avatar_url', new.raw_user_meta_data ->> 'picture', ''),
    'rep',
    'invited'
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = '';

Every new account starts as an invited rep. The role and status are hardcoded, never taken from sign-up data.

Auto-Update updated_at

All tables with an updated_at column have a trigger that sets it to now() on every update.

Deal Stage Change Activity

When a deal's stage_id changes, a trigger automatically logs an activity:

-- Inserts into activities with type = 'stage_change'
-- Stores old and new stage names in metadata

Contact Last Contacted

When a new activity is created with a contact_id, a trigger updates that contact's last_contacted timestamp.

Seed Data

The supabase/seed.sql file includes:

Entity	Count	Notes
Team members (profiles)	6	Including 1 admin: admin@example.com / password123
Companies	10	Various industries, sizes, and revenues
Contacts	20	Distributed across companies
Tags	8	Sales, Enterprise, Partner, etc.
Pipelines	2	Sales Pipeline (6 stages), Enterprise Pipeline (6 stages)
Deals	16	Various stages, values, and priorities
Tasks	12	Mix of pending, in-progress, and completed
Activities	20	Calls, emails, meetings, notes
Emails	12	Inbox, sent, and draft messages
Email templates	6	Sales, follow-up, and onboarding
Email sequences	3	Active, paused, and draft
Notifications	10	Various types
CRM settings	1	Default configuration

Regenerating Types

After modifying the schema, regenerate TypeScript types:

npx supabase gen types typescript --project-id your-project-id > types/database.ts

This updates the typed Supabase client used throughout the application.