Database Schema

The kit uses 31 Supabase tables organized into seven domains: workspace, projects, tasks, planning, collaboration, settings, and analytics. All tables have row-level security (RLS) enabled with a single-workspace model.

Schema Overview

Workspace:      profiles, workspaces

Projects:       projects, project_members, labels

Tasks:          tasks, task_labels, subtasks, task_comments

Planning:       cycles, cycle_tasks, modules, module_tasks,
                goals, key_results, milestones

Collaboration:  pages, activities, notifications,
                notification_preferences, saved_views

Settings:       automation_rules, api_keys, webhooks,
                integration_configs, project_templates

Analytics:      daily_metrics, burndown_points, velocity_points,
                cumulative_flow_points, contribution_data

Enum Types

The schema defines 14 enum types for strict value constraints:

Enum	Values
workspace_plan	Free, Pro, Business, Enterprise
project_status	Active, Paused, Archived
view_layout	board, list, timeline, calendar
task_status	Backlog, Todo, In Progress, In Review, Done, Cancelled
task_priority	Urgent, High, Medium, Low, None
cycle_status	Active, Upcoming, Completed
module_status	Planned, Active, Completed
goal_status	On Track, At Risk, Off Track, Completed
member_role	Owner, Admin, Member, Guest
member_status	Active, Invited, Deactivated
activity_type	created, status_change, assigned, commented, completed, moved_to_cycle, priority_change, label_added, deleted
activity_entity_type	task, project, cycle, module, goal
notification_type	assigned, mentioned, status_change, comment, due_date, sprint_started, sprint_ended, goal_milestone
notification_entity	task, project, cycle, goal
label_group	Type, Area, Status
webhook_status	Active, Failed

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, cascade delete
full_name	text	Display name (not null)
email	text	User email (not null)
avatar_url	text	Profile image URL
role	member_role	Owner, Admin, Member, or Guest (default Member)
title	text	Job title
status	member_status	Active, Invited, or Deactivated (default Active)
joined_at	timestamptz	When the user joined
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

Indexes: idx_profiles_email, idx_profiles_status

workspaces

Organization-level settings. The seed data creates a single "Acme Inc" workspace.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Workspace name (not null)
slug	text (unique)	URL-safe identifier (not null)
description	text	Workspace description
timezone	text	e.g., America/New_York (default UTC)
date_format	text	e.g., MM/DD/YYYY
first_day_of_week	int	0 (Sunday) to 6 (Saturday), default 0
plan	workspace_plan	Free, Pro, Business, or Enterprise (default Free)
member_count	int	Number of workspace members
project_count	int	Number of projects
created_at	timestamptz	Auto-set

Indexes: idx_workspaces_slug

projects

Individual projects within the workspace. Each project has feature toggles and count tracking.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Project name (not null)
prefix	text	Short code for task IDs, e.g., WEB (not null)
emoji	text	Project icon emoji
color	text	Hex color for project branding
description	text	Project description
status	project_status	Active, Paused, or Archived (default Active)
default_view	view_layout	board, list, timeline, or calendar (default board)
lead_id	uuid (FK)	References profiles.id, set null on delete
feat_cycles	boolean	Enable sprint cycles (default false)
feat_modules	boolean	Enable modules (default false)
feat_pages	boolean	Enable wiki pages (default false)
feat_automations	boolean	Enable automation rules (default false)
feat_estimates	boolean	Enable story point estimates (default false)
issue_count	int	Total task count (auto-maintained by trigger)
completed_count	int	Completed/cancelled task count (auto-maintained by trigger)
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

Indexes: idx_projects_status, idx_projects_lead_id, idx_projects_prefix

project_members

Join table linking team members to projects.

Column	Type	Notes
project_id	uuid (FK)	References projects.id, cascade delete
member_id	uuid (FK)	References profiles.id, cascade delete
Primary key	composite	(project_id, member_id)

Indexes: idx_project_members_member_id

labels

Reusable labels for categorizing tasks. Grouped by Type, Area, or Status.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Label name (not null)
color	text	Display color (not null)
label_group	label_group	Type, Area, or Status (default Type)
description	text	Label description
usage_count	int	Times used (default 0)

Indexes: idx_labels_group

tasks

The core entity. Tasks belong to a project and can be assigned to cycles, modules, and team members.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
number	int	Auto-incremented per project via trigger (not null)
title	text	Task title (not null)
description	text	Task details (Markdown)
status	task_status	Backlog, Todo, In Progress, In Review, Done, Cancelled (default Backlog)
priority	task_priority	Urgent, High, Medium, Low, None (default None)
assignee_id	uuid (FK)	References profiles.id, set null on delete
due_date	timestamptz	Task deadline
start_date	timestamptz	Task start date
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger
cycle_id	uuid (FK)	References cycles.id, set null on delete
module_id	uuid (FK)	References modules.id, set null on delete
estimate	numeric	Story point estimate
parent_id	uuid (FK)	Self-referencing for parent tasks, set null on delete

Unique constraint: uq_task_number_per_project (project_id, number)

Indexes: idx_tasks_project_id, idx_tasks_status, idx_tasks_priority, idx_tasks_assignee_id, idx_tasks_cycle_id, idx_tasks_module_id, idx_tasks_parent_id, idx_tasks_due_date, idx_tasks_created_at (desc), idx_tasks_project_status (composite)

task_labels

Junction table linking tasks to labels.

Column	Type	Notes
task_id	uuid (FK)	References tasks.id, cascade delete
label_id	uuid (FK)	References labels.id, cascade delete
Primary key	composite	(task_id, label_id)

Indexes: idx_task_labels_label_id

subtasks

Checklist items within a task.

Column	Type	Notes
id	uuid (PK)	Auto-generated
task_id	uuid (FK)	References tasks.id, cascade delete (not null)
title	text	Subtask title (not null)
completed	boolean	Completion status (default false)
assignee_id	uuid (FK)	References profiles.id, set null on delete
due_date	timestamptz	Subtask deadline

Indexes: idx_subtasks_task_id, idx_subtasks_assignee_id

task_comments

Threaded comments on tasks.

Column	Type	Notes
id	uuid (PK)	Auto-generated
task_id	uuid (FK)	References tasks.id, cascade delete (not null)
author_id	uuid (FK)	References profiles.id, cascade delete (not null)
content	text	Comment body (not null)
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

Indexes: idx_task_comments_task_id, idx_task_comments_author_id, idx_task_comments_created_at (desc)

cycles

Time-boxed sprint iterations within a project.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
name	text	Sprint name (not null)
description	text	Sprint description
start_date	timestamptz	Sprint start (not null)
end_date	timestamptz	Sprint end (not null, must be after start_date)
status	cycle_status	Active, Upcoming, or Completed (default Upcoming)
velocity	numeric	Points completed (default 0)
planned_points	numeric	Points planned (default 0)
completed_points	numeric	Points completed (default 0)

Check constraint: chk_cycle_dates ensures end_date > start_date

Indexes: idx_cycles_project_id, idx_cycles_status

cycle_tasks

Junction table linking tasks to cycles.

Column	Type	Notes
cycle_id	uuid (FK)	References cycles.id, cascade delete
task_id	uuid (FK)	References tasks.id, cascade delete
Primary key	composite	(cycle_id, task_id)

Indexes: idx_cycle_tasks_task_id

modules

Feature-scoped groupings of tasks within a project.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
name	text	Module name (not null)
description	text	Module description
status	module_status	Planned, Active, or Completed (default Planned)
lead_id	uuid (FK)	References profiles.id, set null on delete
start_date	timestamptz	Module start date
end_date	timestamptz	Module end date

Indexes: idx_modules_project_id, idx_modules_lead_id, idx_modules_status

module_tasks

Junction table linking tasks to modules.

Column	Type	Notes
module_id	uuid (FK)	References modules.id, cascade delete
task_id	uuid (FK)	References tasks.id, cascade delete
Primary key	composite	(module_id, task_id)

Indexes: idx_module_tasks_task_id

goals

OKR-style goals with progress tracking.

Column	Type	Notes
id	uuid (PK)	Auto-generated
title	text	Goal title (not null)
description	text	Goal description
owner_id	uuid (FK)	References profiles.id, cascade delete (not null)
status	goal_status	On Track, At Risk, Off Track, or Completed (default On Track)
progress	int	0-100 percentage (default 0, check constraint)
due_date	timestamptz	Goal deadline
created_at	timestamptz	Auto-set

Indexes: idx_goals_owner_id, idx_goals_status

key_results

Measurable outcomes linked to goals.

Column	Type	Notes
id	uuid (PK)	Auto-generated
goal_id	uuid (FK)	References goals.id, cascade delete (not null)
title	text	Key result title (not null)
target	numeric	Target value (not null)
current	numeric	Current value (default 0)
unit	text	Unit of measurement, e.g., %, users (default empty)

Indexes: idx_key_results_goal_id

pages

Wiki-style documentation pages within projects. Supports nested hierarchy.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
title	text	Page title (not null)
content	text	Page content (Markdown)
author_id	uuid (FK)	References profiles.id, cascade delete (not null)
parent_id	uuid (FK)	Self-referencing for nested pages, set null on delete
created_at	timestamptz	Auto-set
updated_at	timestamptz	Auto-updated by trigger

Indexes: idx_pages_project_id, idx_pages_author_id, idx_pages_parent_id

activities

Chronological log of workspace actions.

Column	Type	Notes
id	uuid (PK)	Auto-generated
user_id	uuid (FK)	References profiles.id, cascade delete (not null)
type	activity_type	created, status_change, assigned, commented, etc. (not null)
entity_type	activity_entity_type	task, project, cycle, module, goal (not null)
entity_id	uuid	ID of the referenced entity (not null)
entity_title	text	Display title of the entity (not null)
project_id	uuid (FK)	References projects.id, set null on delete
metadata	jsonb	Extra data (e.g., old/new status for status_change), default {}
created_at	timestamptz	Auto-set

Indexes: idx_activities_user_id, idx_activities_project_id, idx_activities_entity (entity_type, entity_id), idx_activities_created_at (desc), idx_activities_type

notifications

In-app notifications for team members.

Column	Type	Notes
id	uuid (PK)	Auto-generated
type	notification_type	assigned, mentioned, status_change, comment, due_date, sprint_started, sprint_ended, goal_milestone (not null)
user_id	uuid (FK)	References profiles.id, cascade delete (not null)
actor_id	uuid (FK)	References profiles.id, cascade delete (not null)
entity_type	notification_entity	task, project, cycle, goal (not null)
entity_id	uuid	ID of the referenced entity (not null)
entity_title	text	Display title (not null)
project_id	uuid (FK)	References projects.id, set null on delete
message	text	Notification body (not null)
read	boolean	Read status (default false)
created_at	timestamptz	Auto-set

Indexes: idx_notifications_user_id, idx_notifications_user_read (user_id, read), idx_notifications_created_at (desc), idx_notifications_actor_id, idx_notifications_project_id

saved_views

User-created custom views with saved filters, grouping, and layout.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	View name (not null)
description	text	View description
icon	text	View icon identifier
layout	view_layout	board, list, timeline, or calendar (default board)
filters	jsonb	Saved filter criteria (default {})
group_by	text	Grouping field
sort_by	text	Sorting field
creator_id	uuid (FK)	References profiles.id, cascade delete (not null)
is_public	boolean	Visible to all workspace members (default false)
is_pinned	boolean	Pinned to sidebar (default false)
last_used	timestamptz	Last time the view was accessed

Indexes: idx_saved_views_creator_id, idx_saved_views_public (partial, where is_public = true)

automation_rules

Project-level automation rules for task workflow.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
name	text	Rule name (not null)
trigger	text	Trigger event (not null)
condition	text	Optional condition
action	text	Action to perform (not null)
enabled	boolean	Active status (default true)
last_triggered	timestamptz	Last execution time
created_at	timestamptz	Auto-set

Indexes: idx_automation_rules_project_id, idx_automation_rules_enabled (partial, where enabled = true)

milestones

Project milestones displayed on timeline views.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
name	text	Milestone name (not null)
date	timestamptz	Milestone date (not null)
completed	boolean	Completion status (default false)

Indexes: idx_milestones_project_id, idx_milestones_date

api_keys

Developer API keys for external integrations.

Column	Type	Notes
id	uuid (PK)	Auto-generated
user_id	uuid (FK)	References profiles.id, cascade delete (not null)
name	text	Key name (not null)
prefix	text	Visible key prefix (not null)
created_at	timestamptz	Auto-set
last_used	timestamptz	Last usage time

Indexes: idx_api_keys_user_id, idx_api_keys_prefix

webhooks

Outbound webhooks for external event delivery.

Column	Type	Notes
id	uuid (PK)	Auto-generated
user_id	uuid (FK)	References profiles.id, cascade delete (not null)
url	text	Webhook endpoint URL (not null)
events	text[]	Array of subscribed event types (default {})
status	webhook_status	Active or Failed (default Active)
last_triggered	timestamptz	Last delivery time
created_at	timestamptz	Auto-set

Indexes: idx_webhooks_user_id, idx_webhooks_status

integration_configs

Third-party integration configuration (Slack, GitHub, etc.).

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Integration name (not null)
icon	text	Integration icon identifier
description	text	Integration description
connected	boolean	Connection status (default false)
last_synced	timestamptz	Last sync time

project_templates

Pre-configured project templates for quick setup.

Column	Type	Notes
id	uuid (PK)	Auto-generated
name	text	Template name (not null)
description	text	Template description
category	text	Template category (not null)
icon	text	Template icon
statuses	text[]	Default status columns (default {})
labels	text[]	Default labels (default {})
sample_tasks	text[]	Sample task titles (default {})

Indexes: idx_project_templates_category

notification_preferences

Per-user notification delivery settings.

Column	Type	Notes
id	uuid (PK)	Auto-generated
user_id	uuid (FK)	References profiles.id, cascade delete (not null)
event	text	Event type (not null)
in_app	boolean	In-app notification (default true)
email	boolean	Email notification (default true)
slack	boolean	Slack notification (default false)

Unique constraint: uq_notification_pref_user_event (user_id, event)

Indexes: idx_notification_preferences_user_id

daily_metrics

Per-project daily task creation and completion counts for analytics.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete
date	date	Metric date (not null)
created	int	Tasks created (default 0)
completed	int	Tasks completed (default 0)

Unique constraint: uq_daily_metrics_project_date (project_id, date)

Indexes: idx_daily_metrics_project_id, idx_daily_metrics_date

burndown_points

Sprint burndown chart data points.

Column	Type	Notes
id	uuid (PK)	Auto-generated
cycle_id	uuid (FK)	References cycles.id, cascade delete (not null)
date	date	Data point date (not null)
ideal	numeric	Ideal remaining points (default 0)
actual	numeric	Actual remaining points (default 0)
scope	numeric	Total scope (default 0)

Unique constraint: uq_burndown_cycle_date (cycle_id, date)

Indexes: idx_burndown_points_cycle_id

velocity_points

Sprint-over-sprint velocity tracking.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
sprint	text	Sprint name/identifier (not null)
planned	int	Points planned (default 0)
completed	int	Points completed (default 0)

Indexes: idx_velocity_points_project_id

cumulative_flow_points

Daily status distribution for cumulative flow diagrams.

Column	Type	Notes
id	uuid (PK)	Auto-generated
project_id	uuid (FK)	References projects.id, cascade delete (not null)
date	date	Data point date (not null)
backlog	int	Tasks in Backlog (default 0)
todo	int	Tasks in Todo (default 0)
in_progress	int	Tasks In Progress (default 0)
in_review	int	Tasks In Review (default 0)
done	int	Tasks Done (default 0)

Unique constraint: uq_cumulative_flow_project_date (project_id, date)

Indexes: idx_cumulative_flow_project_id

contribution_data

Per-member contribution metrics for team analytics.

Column	Type	Notes
id	uuid (PK)	Auto-generated
member_id	uuid (FK)	References profiles.id, cascade delete (not null)
project_id	uuid (FK)	References projects.id, cascade delete
created	int	Tasks created (default 0)
completed	int	Tasks completed (default 0)
avg_cycle_time	numeric	Average days to complete tasks (default 0)

Indexes: idx_contribution_data_member_id, idx_contribution_data_project_id

Row-Level Security

RLS is enabled on every table. The kit uses a single-workspace model -- all authenticated team members can read all workspace data, while write operations are scoped based on role and ownership.

Helper Functions

-- Check if the current user is a workspace member (has a profile)
CREATE OR REPLACE FUNCTION public.is_workspace_member()
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid()
  );
$$;

-- Check if the current user is a workspace admin (Owner or Admin)
CREATE OR REPLACE FUNCTION public.is_workspace_admin()
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
AS $$
  SELECT auth.uid() IS NOT NULL;
$$;

Note: In this starter kit, is_workspace_admin() treats all authenticated users as admins for ease of use. For production multi-tenant use, restore the role-based check: SELECT EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role IN ('Owner','Admin')).

Policy Summary

Table	Team Read	Team Write	Scoped Write
profiles	All	Own profile only	-
projects	All	Admin create/update/delete	-
project_members	All	Admin create/update/delete	-
tasks	All	Members create; assignee or admin update	Members delete
subtasks	All	Members create/update/delete	-
task_comments	All	Members create	Author update/delete
task_labels	All	Members create/delete	-
cycles	All	Admin create/update/delete	-
cycle_tasks	All	Members create/delete	-
modules	All	Admin create/update/delete	-
module_tasks	All	Members create/delete	-
goals	All	Owner or admin create/update	Admin delete
key_results	All	Goal owner or admin create/update/delete	-
pages	All	Members create	Author or admin update/delete
activities	All (read only)	System insert only	-
notifications	Own only	Own (update read status)	-
saved_views	Public or own	Members create	Creator update/delete
labels	All	Admin create/update/delete	-
automation_rules	All	Admin create/update/delete	-
milestones	All	Admin create/update/delete	-
api_keys	Own or admin	Own or admin create/delete	-
webhooks	All	Admin create/update/delete	-
integration_configs	All	Admin create/update/delete	-
project_templates	All	Admin create/update/delete	-
notification_preferences	Own only	Own create/update	-
daily_metrics	All (read only)	System insert only	-
burndown_points	All (read only)	System insert only	-
velocity_points	All (read only)	System insert only	-
cumulative_flow_points	All (read only)	System insert only	-
contribution_data	All (read only)	System insert only	-

Triggers

Profile Auto-Creation

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

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, email, avatar_url)
  VALUES (
    new.id,
    COALESCE(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)),
    new.email,
    COALESCE(new.raw_user_meta_data->>'avatar_url', '')
  );
  RETURN new;
END;
$$;

The trigger fires AFTER INSERT ON auth.users. If no full_name is provided in the sign-up metadata, it uses the email prefix as a fallback.

Auto-Update updated_at

All tables with an updated_at column have a trigger that sets it to now() on every update. Applied to: tasks, projects, profiles, pages, and task_comments.

Auto-Increment Task Number

Each project has its own task counter. When a task is inserted, a trigger assigns max(number) + 1 within that project. An advisory lock keyed on the project_id prevents race conditions:

CREATE OR REPLACE FUNCTION public.assign_task_number()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  next_number integer;
BEGIN
  PERFORM pg_advisory_xact_lock(hashtext(NEW.project_id::text));

  SELECT COALESCE(MAX(number), 0) + 1
    INTO next_number
    FROM public.tasks
   WHERE project_id = NEW.project_id;

  NEW.number = next_number;
  RETURN NEW;
END;
$$;

The trigger only fires when NEW.number IS NULL, allowing explicit number assignment when needed.

Recalculate Project Counts

When a task is inserted, updated (status change), or deleted, a trigger recalculates the parent project's issue_count and completed_count fields. This keeps the project cards and dashboard stats accurate without application-level count queries:

-- Counts all tasks in the project
SET issue_count = (SELECT count(*) FROM tasks WHERE project_id = target_project_id)
-- Counts only Done and Cancelled tasks
SET completed_count = (SELECT count(*) FROM tasks
                       WHERE project_id = target_project_id
                       AND status IN ('Done', 'Cancelled'))

If a task moves between projects (rare), both the old and new project are recalculated.

Seed Data

The supabase/seed.sql file includes:

Entity	Count	Notes
Auth users / Profiles	10	Including 1 Owner (sarah@acme.io / password123) and 1 Admin (marcus@acme.io / password123). All passwords: password123
Workspace	1	"Acme Inc" on Pro plan
Projects	6	Web App, Mobile App, API Platform, Design System, QA Automation, Infrastructure
Project members	~30	Various team members per project
Labels	15	Type (Bug, Feature, Improvement, etc.), Area (Frontend, Backend, etc.), Status (Critical, Blocked)
Tasks	64	Distributed across projects with varied statuses and priorities
Subtasks	26	Checklist items on various tasks
Task comments	31	Discussion threads on tasks
Cycles	8	Active, Upcoming, and Completed sprints
Modules	8	Feature modules across projects
Goals	5	OKRs with varied statuses
Key results	13	Measurable outcomes linked to goals
Pages	12	Wiki pages across projects with nested hierarchy
Saved views	6	Custom views with saved filters
Activities	25	Status changes, assignments, comments, completions
Notifications	27	Various notification types
Milestones	6	Project milestones on timelines
Automation rules	8	Workflow automation rules
API keys	3	Sample developer API keys
Webhooks	3	Sample webhook configurations
Integration configs	4	Slack, GitHub, Figma, Linear
Project templates	6	Agile, Scrum, Kanban, Bug Tracking, Feature Planning, Marketing
Notification preferences	Per user	Default notification settings
Daily metrics	30 days	Created/completed task counts per day
Burndown points	Per cycle	Ideal, actual, and scope per day
Velocity points	Per sprint	Planned vs completed per sprint
Cumulative flow points	30 days	Status distribution per day
Contribution data	Per member	Created, completed, avg cycle time

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.