Database Setup

8Space uses PostgreSQL via Supabase with a comprehensive schema for multi-tenant project management, including projects, tasks, workflows, and team collaboration features.

Quick Start

Start Supabase

Initialize the local Supabase instance:

cd packages/app
supabase start

Apply Migrations

Reset the database and apply all migrations:

supabase db reset

This applies all migrations in order and runs the seed data.

Verify Setup

Check the Supabase Studio:

# Studio URL is shown in supabase start output
# Default: http://127.0.0.1:54323

Database Schema

Core Tables

The database schema supports multi-tenant project management with role-based access control.

Profiles

User profiles are automatically created when users sign up:

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text not null default 'User',
  avatar_url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Tenants

Multi-tenant workspaces for team isolation:

create table public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null check (char_length(name) between 1 and 120),
  slug text not null unique check (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
  created_by uuid not null references public.profiles (id) on delete restrict,
  created_at timestamptz not null default now(),
  archived_at timestamptz
);

Each tenant has a unique slug used for URL routing: /app/t/{tenant-slug}

Projects

Projects belong to tenants and contain tasks:

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null check (char_length(name) between 1 and 120),
  description text,
  created_by uuid not null references public.profiles (id) on delete restrict,
  created_at timestamptz not null default now(),
  archived_at timestamptz
);

Tasks

Core entity for work items:

create table public.tasks (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  title text not null check (char_length(title) between 1 and 240),
  status_column_id uuid not null references public.workflow_columns (id),
  start_date date,
  due_date date,
  priority public.task_priority not null default 'p1',
  order_rank numeric(18, 6) not null default 1000,
  description text,
  estimate numeric(10, 2),
  is_milestone boolean not null default false,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint task_dates_valid check (
    due_date is null or start_date is null or due_date >= start_date
  )
);

Workflow Columns

Kanban board columns for task status:

create table public.workflow_columns (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  name text not null check (char_length(name) between 1 and 80),
  kind public.workflow_column_kind not null default 'custom',
  position integer not null,
  wip_limit integer,
  definition_of_done text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Custom Types

The schema defines several enums for type safety:

create type public.project_role as enum ('owner', 'editor', 'viewer');
create type public.tenant_role as enum ('owner', 'admin', 'member');
create type public.task_priority as enum ('p0', 'p1', 'p2');
create type public.workflow_column_kind as enum (
  'backlog', 'todo', 'in_progress', 'done', 'custom'
);
create type public.dependency_type as enum ('FS');

Supporting Tables

Task Relationships

task_assignees: Many-to-many task assignments
task_labels: Project-scoped labels
task_label_links: Task-to-label associations
task_checklist_items: Subtasks within tasks
task_attachments: File references
task_dependencies: Task predecessor/successor links
task_activity: Audit log for task changes

Team Management

tenant_members: User membership in tenants
project_members: User membership in projects

Both support role-based access control.

Migrations

Migrations are located in packages/app/supabase/migrations/ and applied in chronological order.

Migration History

Migration	Description
`20260212220000_init_gantt_mvp.sql`	Initial schema: projects, tasks, workflow, RLS policies
`20260213220000_auto_join_new_users.sql`	Single-tenant mode auto-join triggers
`20260213220100_handle_new_user_google_compat.sql`	Google OAuth profile handling
`20260216220000_fix_create_project_trigger_conflict.sql`	Fix duplicate project member insertion
`20260217143000_multi_tenant_onboarding.sql`	Multi-tenant support with tenant isolation

View the full migration content in packages/app/supabase/migrations/ to understand the schema evolution.

Database Functions

Project Management

create or replace function public.create_tenant_with_owner(
  p_name text,
  p_slug text default null
)
returns public.tenants

create or replace function public.create_project_with_defaults(
  p_tenant_slug text,
  p_name text,
  p_description text
)
returns uuid

create or replace function public.move_task(
  p_task_id uuid,
  p_to_column_id uuid,
  p_new_rank numeric
)
returns public.tasks

Permission Helpers

These functions are used in Row Level Security policies:

-- Tenant permissions
select public.is_tenant_member(tenant_id);
select public.is_tenant_owner(tenant_id);
select public.current_tenant_role(tenant_id);

-- Project permissions
select public.is_project_member(project_id);
select public.can_edit_project(project_id);
select public.is_project_owner(project_id);
select public.current_project_role(project_id);

Analytics

-- Get project metrics for dashboard
select public.dashboard_metrics(
  p_project_id := '44444444-4444-4444-4444-444444444444',
  p_days_window := 14
);

Returns JSON with:

tasksByStatus: Task counts by workflow column
overdueCount: Number of overdue tasks
dueThisWeek: Tasks due in next 7 days
workloadByAssignee: Active tasks per team member
completionTrend: Daily completion counts

Row Level Security

All tables have RLS enabled with policies based on tenant and project membership:

-- Example: Projects table policies
create policy "projects_select_members"
on public.projects for select to authenticated
using (public.is_project_member(id));

create policy "projects_insert_owner"
on public.projects for insert to authenticated
with check (
  created_by = auth.uid()
  and public.is_tenant_member(tenant_id)
);

create policy "projects_update_owner"
on public.projects for update to authenticated
using (public.is_project_owner(id));

RLS ensures users can only access data within their tenants and projects, with appropriate permissions.

Seed Data

The seed file (packages/app/supabase/seed.sql) creates demo data:

Test Users

All users have password: password123

owner@gantt.local - Olivia Owner (tenant owner)
editor@gantt.local - Ethan Editor (tenant admin)
viewer@gantt.local - Vera Viewer (tenant member)

Demo Content

1 tenant: “8Space Demo” (demo-space)
1 project: “Product Launch Q2”
4 workflow columns: Backlog, To Do, In Progress, Done
5 tasks with various states
3 labels: Frontend, Backend, Urgent
Task assignments, checklist items, and dependencies

Triggers

Auto Profile Creation

When a user signs up, a profile is automatically created:

create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();

The trigger extracts name and avatar from OAuth metadata (supports Google OAuth).

Updated At Timestamps

Automatic timestamp updates on record changes:

create trigger touch_tasks_updated_at
before update on public.tasks
for each row execute procedure public.touch_updated_at();

Database Backup

Local Development
Production

Supabase local data is ephemeral. To preserve data:

# Export schema
supabase db dump -f schema.sql --schema public

# Export data
supabase db dump -f data.sql --data-only

Indexes

Optimized indexes for common queries:

-- Task queries
create index tasks_project_column_rank_idx
  on public.tasks (project_id, status_column_id, order_rank);

create index tasks_project_due_date_idx
  on public.tasks (project_id, due_date);

-- Lookups
create index task_assignees_user_idx
  on public.task_assignees (user_id);

create index project_members_user_idx
  on public.project_members (user_id);

create index tenant_members_user_idx
  on public.tenant_members (user_id);

Schema Reference

View the complete schema in the migration files:

Initial schema: packages/app/supabase/migrations/20260212220000_init_gantt_mvp.sql
Multi-tenant: packages/app/supabase/migrations/20260217143000_multi_tenant_onboarding.sql

Get Started

Core Features

Configuration

Architecture

Database Setup

Quick Start

Database Schema

Core Tables

Profiles

Tenants

Projects

Tasks

Workflow Columns

Custom Types

Supporting Tables

Migrations

Migration History

Database Functions

Project Management

Permission Helpers

Analytics

Row Level Security

Seed Data

Test Users

Demo Content

Triggers

Auto Profile Creation

Updated At Timestamps

Database Backup

Indexes

Schema Reference

Next Steps

Authentication

Environment Variables

​Quick Start

​Database Schema

​Core Tables

​Profiles

​Tenants

​Projects

​Tasks

​Workflow Columns

​Custom Types

​Supporting Tables

​Migrations

​Migration History

​Database Functions

​Project Management

​Permission Helpers

​Analytics

​Row Level Security

​Seed Data

​Test Users

​Demo Content

​Triggers

​Auto Profile Creation

​Updated At Timestamps

​Database Backup

​Indexes

​Schema Reference

​Next Steps

Authentication

Environment Variables

Quick Start

Database Schema

Core Tables

Profiles

Tenants

Projects

Tasks

Workflow Columns

Custom Types

Supporting Tables

Migrations

Migration History

Database Functions

Project Management

Permission Helpers

Analytics

Row Level Security

Seed Data

Test Users

Demo Content

Triggers

Auto Profile Creation

Updated At Timestamps

Database Backup

Indexes

Schema Reference

Next Steps