Database Schema

8Space uses PostgreSQL via Supabase with a comprehensive schema supporting multi-tenancy, projects, tasks, and collaboration features.

Entity Relationship Diagram

Core Tables

Multi-Tenancy Layer

tenants
tenant_members
profiles

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
);

create index projects_tenant_idx on public.projects (tenant_id);

Purpose: Workspace/organization container for projectsKey Features:

URL-safe slug for routing (e.g., /app/:tenantSlug/projects)
Soft delete via archived_at
Unique constraint on slug

create type public.tenant_role as enum ('owner', 'admin', 'member');

create table public.tenant_members (
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  user_id uuid not null references public.profiles (id) on delete cascade,
  role public.tenant_role not null,
  joined_at timestamptz not null default now(),
  primary key (tenant_id, user_id)
);

create index tenant_members_user_idx on public.tenant_members (user_id);

Roles:

owner: Full control, can delete tenant
admin: Manage members and projects
member: Access tenant projects

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()
);

Purpose: User profile informationTriggers:

handle_new_user(): Auto-creates profile on signup
Extracts display_name from OAuth metadata or email
Supports Google OAuth (full_name, avatar_url)

Project Management

projects
project_members
workflow_columns

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
);

Key Relationships:

Belongs to exactly one tenant
Has many tasks, workflow columns, labels
Soft delete via archived_at

create type public.project_role as enum ('owner', 'editor', 'viewer');

create table public.project_members (
  project_id uuid not null references public.projects (id) on delete cascade,
  user_id uuid not null references public.profiles (id) on delete cascade,
  role public.project_role not null,
  joined_at timestamptz not null default now(),
  primary key (project_id, user_id)
);

create index project_members_user_idx on public.project_members (user_id);

Roles:

owner: Full project control
editor: Create/edit tasks and settings
viewer: Read-only access

create type public.workflow_column_kind as enum (
  'backlog', 'todo', 'in_progress', 'done', 'custom'
);

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(),
  unique (project_id, position)
);

Default Columns:

Backlog (position 100)
To Do (position 200)
In Progress (position 300)
Done (position 400)

Task Management

create type public.task_priority as enum ('p0', 'p1', 'p2');

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) on delete restrict,
  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
  )
);

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);

Key Features:

Lexicographic order_rank for drag-and-drop ordering
Date validation constraint
Milestone support
Effort estimation

create table public.task_assignees (
  task_id uuid not null references public.tasks (id) on delete cascade,
  user_id uuid not null references public.profiles (id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (task_id, user_id)
);

create index task_assignees_user_idx on public.task_assignees (user_id);

Purpose: Many-to-many relationship for task assignments

create table public.task_labels (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  name text not null,
  color text not null,
  created_at timestamptz not null default now(),
  unique (project_id, name)
);

create table public.task_label_links (
  task_id uuid not null references public.tasks (id) on delete cascade,
  label_id uuid not null references public.task_labels (id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (task_id, label_id)
);

Purpose: Project-scoped tags for task categorization

create table public.task_checklist_items (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references public.tasks (id) on delete cascade,
  title text not null check (char_length(title) between 1 and 240),
  is_done boolean not null default false,
  position integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Purpose: Sub-tasks or action items within a task

create table public.task_attachments (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references public.tasks (id) on delete cascade,
  url text not null,
  title text,
  created_at timestamptz not null default now()
);

Purpose: File uploads or external links

create type public.dependency_type as enum ('FS');

create table public.task_dependencies (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  predecessor_task_id uuid not null references public.tasks (id) on delete cascade,
  successor_task_id uuid not null references public.tasks (id) on delete cascade,
  type public.dependency_type not null default 'FS',
  created_at timestamptz not null default now(),
  constraint task_dependency_no_self check (predecessor_task_id <> successor_task_id),
  unique (project_id, predecessor_task_id, successor_task_id, type)
);

create index task_dependencies_project_link_idx 
  on public.task_dependencies (project_id, predecessor_task_id, successor_task_id);

Dependency Types:

FS (Finish-to-Start): Predecessor must finish before successor starts

Future support planned for:

SS (Start-to-Start)
FF (Finish-to-Finish)
SF (Start-to-Finish)

create table public.task_activity (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  task_id uuid not null references public.tasks (id) on delete cascade,
  actor_id uuid not null references public.profiles (id) on delete restrict,
  event_type text not null,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

Event Types:

task_created
task_moved
task_updated
assignee_added / assignee_removed
dependency_added / dependency_removed

Database Functions

Tenant Management

create_tenant_with_owner

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

Purpose: Create new tenant workspace with auto-generated slugBehavior:

Normalizes slug from name if not provided
Handles slug conflicts with numeric suffixes
Automatically adds creator as owner
Returns created tenant record

Example:

select * from create_tenant_with_owner('Acme Corp', 'acme');
-- Returns: { id: uuid, name: 'Acme Corp', slug: 'acme', ... }

normalize_tenant_slug

create or replace function public.normalize_tenant_slug(p_value text)
returns text

Purpose: Convert arbitrary text to URL-safe slugTransformations:

Lowercase conversion
Replace non-alphanumeric with hyphens
Remove leading/trailing hyphens
Fallback to ‘space’ if empty

Example:

select normalize_tenant_slug('Acme Corp LLC!');
-- Returns: 'acme-corp-llc'

Project Management

create_project_with_defaults

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

Purpose: Create project with default workflow columnsBehavior:

Validates tenant membership
Creates project record
Adds all tenant members as project members
Creates default workflow columns (Backlog, To Do, In Progress, Done)
Returns project ID

Role Mapping:

Creator → owner
Tenant owners/admins → editor
Tenant members → viewer

dashboard_metrics

create or replace function public.dashboard_metrics(
  p_project_id uuid,
  p_days_window int default 14
)
returns jsonb

Purpose: Aggregate project analyticsReturns:

{
  "tasksByStatus": { "backlog": 5, "todo": 12, "in_progress": 8, "done": 43 },
  "overdueCount": 3,
  "dueThisWeek": 7,
  "workloadByAssignee": [
    { "userId": "...", "displayName": "Alice", "activeCount": 5 },
    { "userId": "...", "displayName": "Bob", "activeCount": 3 }
  ],
  "completionTrend": [
    { "date": "2026-02-20", "doneCount": 4 },
    { "date": "2026-02-21", "doneCount": 2 }
  ]
}

Task Operations

move_task

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

Purpose: Move task between workflow columnsBehavior:

Validates task and column existence
Checks user permissions (can_edit_project)
Updates status_column_id and order_rank
Logs activity event
Returns updated task

Used by: Drag-and-drop in Kanban board

Permission Helpers

-- Tenant-level permissions
create function public.current_tenant_role(p_tenant_id uuid) returns tenant_role;
create function public.is_tenant_member(p_tenant_id uuid) returns boolean;
create function public.is_tenant_owner(p_tenant_id uuid) returns boolean;

-- Project-level permissions
create function public.current_project_role(p_project_id uuid) returns project_role;
create function public.is_project_member(p_project_id uuid) returns boolean;
create function public.can_edit_project(p_project_id uuid) returns boolean;
create function public.is_project_owner(p_project_id uuid) returns boolean;

All permission functions are marked security definer and set search_path = public to prevent SQL injection and privilege escalation.

Row Level Security (RLS)

Tenant Policies

alter table public.tenants enable row level security;
alter table public.tenant_members enable row level security;

-- Users can only see tenants they belong to
create policy "tenants_select_members"
on public.tenants for select to authenticated
using (public.is_tenant_member(id));

-- Only owners can update/delete tenants
create policy "tenants_update_owner"
on public.tenants for update to authenticated
using (public.is_tenant_owner(id))
with check (public.is_tenant_owner(id));

Project Policies

alter table public.projects enable row level security;

-- Users can only see projects they're members of
create policy "projects_select_members"
on public.projects for select to authenticated
using (public.is_project_member(id));

-- Only editors can modify tasks
create policy "tasks_update_editors"
on public.tasks for update to authenticated
using (public.can_edit_project(project_id))
with check (public.can_edit_project(project_id));

RLS policies are automatically enforced on all queries, even from backend functions. This provides defense-in-depth security.

Task Policies

alter table public.tasks enable row level security;

-- Members can view all project tasks
create policy "tasks_select_members"
on public.tasks for select to authenticated
using (public.is_project_member(project_id));

-- Editors can create/update/delete tasks
create policy "tasks_insert_editors"
on public.tasks for insert to authenticated
with check (public.can_edit_project(project_id));

Triggers

Updated Timestamps
User Onboarding

create or replace function public.touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

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

Applied to: profiles, workflow_columns, tasks, task_checklist_items

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, display_name, avatar_url)
  values (
    new.id,
    coalesce(
      new.raw_user_meta_data ->> 'full_name',
      new.raw_user_meta_data ->> 'name',
      split_part(new.email, '@', 1)
    ),
    coalesce(
      new.raw_user_meta_data ->> 'avatar_url',
      new.raw_user_meta_data ->> 'picture'
    )
  )
  on conflict (id) do update set
    display_name = coalesce(excluded.display_name, profiles.display_name),
    avatar_url = coalesce(excluded.avatar_url, profiles.avatar_url);
  
  return new;
end;
$$;

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

Purpose: Auto-create profile when user signs upOAuth Support:

Google: full_name, picture
GitHub: name, avatar_url
Email: Uses email prefix as fallback

Indexes

create index tasks_project_column_rank_idx 
  on tasks (project_id, status_column_id, order_rank);

create index tasks_project_due_date_idx 
  on tasks (project_id, due_date);

create index project_members_user_idx 
  on project_members (user_id);

create index tenant_members_user_idx 
  on tenant_members (user_id);

create index task_assignees_user_idx 
  on task_assignees (user_id);

create index task_dependencies_project_link_idx 
  on task_dependencies (project_id, predecessor_task_id, successor_task_id);

create index projects_tenant_idx 
  on projects (tenant_id);

Migration History

20260212220000_init_gantt_mvp.sql

Initial schema with projects, tasks, workflow columns, and basic RLS

20260213220000_auto_join_new_users.sql

Single-tenant mode: auto-add users to all projects

20260213220100_handle_new_user_google_compat.sql

Google OAuth metadata support for profile creation

20260216220000_fix_create_project_trigger_conflict.sql

Fixed duplicate project member insertions

20260217143000_multi_tenant_onboarding.sql

Multi-tenant architecture with tenant tables and updated functions

Next Steps

Multi-Tenancy Design

Deep dive into tenant isolation and permission model

Get Started

Core Features

Configuration

Architecture

Database Schema

Entity Relationship Diagram

Core Tables

Multi-Tenancy Layer

Project Management

Task Management

Database Functions

Tenant Management

Project Management

Task Operations

Permission Helpers

Row Level Security (RLS)

Tenant Policies

Project Policies

Task Policies

Triggers

Indexes

Migration History

Next Steps

Multi-Tenancy Design

​Entity Relationship Diagram

​Core Tables

​Multi-Tenancy Layer

​Project Management

​Task Management

​Database Functions

​Tenant Management

​Project Management

​Task Operations

​Permission Helpers

​Row Level Security (RLS)

​Tenant Policies

​Project Policies

​Task Policies

​Triggers

​Indexes

​Migration History

​Next Steps

Multi-Tenancy Design

Entity Relationship Diagram

Core Tables

Multi-Tenancy Layer

Project Management

Task Management

Database Functions

Tenant Management

Project Management

Task Operations

Permission Helpers

Row Level Security (RLS)

Tenant Policies

Project Policies

Task Policies

Triggers

Indexes

Migration History

Next Steps