> ## Documentation Index
> Fetch the complete documentation index at: https://docs.8space.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> PostgreSQL schema, tables, relationships, and RLS policies

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

## Entity Relationship Diagram

```mermaid theme={null}
erDiagram
    auth_users ||--o{ profiles : "has"
    profiles ||--o{ tenants : "creates"
    profiles ||--o{ tenant_members : "belongs to"
    tenants ||--o{ tenant_members : "has"
    tenants ||--o{ projects : "contains"
    profiles ||--o{ projects : "creates"
    profiles ||--o{ project_members : "belongs to"
    projects ||--o{ project_members : "has"
    projects ||--o{ workflow_columns : "has"
    projects ||--o{ tasks : "contains"
    projects ||--o{ task_labels : "has"
    projects ||--o{ task_dependencies : "tracks"
    projects ||--o{ task_activity : "logs"
    workflow_columns ||--o{ tasks : "organizes"
    tasks ||--o{ task_assignees : "assigned to"
    tasks ||--o{ task_label_links : "tagged with"
    tasks ||--o{ task_checklist_items : "has"
    tasks ||--o{ task_attachments : "has"
    tasks ||--o{ task_dependencies : "predecessor"
    tasks ||--o{ task_dependencies : "successor"
    tasks ||--o{ task_activity : "logs"
    profiles ||--o{ task_assignees : "assigned"
    task_labels ||--o{ task_label_links : "links"
```

## Core Tables

### Multi-Tenancy Layer

<Tabs>
  <Tab title="tenants">
    ```sql theme={null}
    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 projects

    **Key Features:**

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

  <Tab title="tenant_members">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="profiles">
    ```sql theme={null}
    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 information

    **Triggers:**

    * `handle_new_user()`: Auto-creates profile on signup
    * Extracts `display_name` from OAuth metadata or email
    * Supports Google OAuth (`full_name`, `avatar_url`)
  </Tab>
</Tabs>

### Project Management

<Tabs>
  <Tab title="projects">
    ```sql theme={null}
    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`
  </Tab>

  <Tab title="project_members">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="workflow_columns">
    ```sql theme={null}
    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:**

    1. Backlog (position 100)
    2. To Do (position 200)
    3. In Progress (position 300)
    4. Done (position 400)
  </Tab>
</Tabs>

### Task Management

<Tabs>
  <Tab title="tasks">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="task_assignees">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="task_labels">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="task_checklist_items">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="task_attachments">
    ```sql theme={null}
    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
  </Tab>

  <Tab title="task_dependencies">
    ```sql theme={null}
    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)
  </Tab>

  <Tab title="task_activity">
    ```sql theme={null}
    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`
  </Tab>
</Tabs>

## Database Functions

### Tenant Management

<AccordionGroup>
  <Accordion title="create_tenant_with_owner">
    ```sql theme={null}
    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 slug

    **Behavior:**

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

    **Example:**

    ```sql theme={null}
    select * from create_tenant_with_owner('Acme Corp', 'acme');
    -- Returns: { id: uuid, name: 'Acme Corp', slug: 'acme', ... }
    ```
  </Accordion>

  <Accordion title="normalize_tenant_slug">
    ```sql theme={null}
    create or replace function public.normalize_tenant_slug(p_value text)
    returns text
    ```

    **Purpose:** Convert arbitrary text to URL-safe slug

    **Transformations:**

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

    **Example:**

    ```sql theme={null}
    select normalize_tenant_slug('Acme Corp LLC!');
    -- Returns: 'acme-corp-llc'
    ```
  </Accordion>
</AccordionGroup>

### Project Management

<AccordionGroup>
  <Accordion title="create_project_with_defaults">
    ```sql theme={null}
    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 columns

    **Behavior:**

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

    **Role Mapping:**

    * Creator → `owner`
    * Tenant owners/admins → `editor`
    * Tenant members → `viewer`
  </Accordion>

  <Accordion title="dashboard_metrics">
    ```sql theme={null}
    create or replace function public.dashboard_metrics(
      p_project_id uuid,
      p_days_window int default 14
    )
    returns jsonb
    ```

    **Purpose:** Aggregate project analytics

    **Returns:**

    ```json theme={null}
    {
      "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 }
      ]
    }
    ```
  </Accordion>
</AccordionGroup>

### Task Operations

<AccordionGroup>
  <Accordion title="move_task">
    ```sql theme={null}
    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 columns

    **Behavior:**

    1. Validates task and column existence
    2. Checks user permissions (`can_edit_project`)
    3. Updates `status_column_id` and `order_rank`
    4. Logs activity event
    5. Returns updated task

    **Used by:** Drag-and-drop in Kanban board
  </Accordion>
</AccordionGroup>

## Permission Helpers

```sql theme={null}
-- 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;
```

<Note>
  All permission functions are marked `security definer` and set `search_path = public` to prevent SQL injection and privilege escalation.
</Note>

## Row Level Security (RLS)

### Tenant Policies

```sql theme={null}
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

```sql theme={null}
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));
```

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

### Task Policies

```sql theme={null}
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

<Tabs>
  <Tab title="Updated Timestamps">
    ```sql theme={null}
    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`
  </Tab>

  <Tab title="User Onboarding">
    ```sql theme={null}
    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 up

    **OAuth Support:**

    * Google: `full_name`, `picture`
    * GitHub: `name`, `avatar_url`
    * Email: Uses email prefix as fallback
  </Tab>
</Tabs>

## Indexes

<CodeGroup>
  ```sql Tasks theme={null}
  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);
  ```

  ```sql Memberships theme={null}
  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);
  ```

  ```sql Dependencies theme={null}
  create index task_dependencies_project_link_idx 
    on task_dependencies (project_id, predecessor_task_id, successor_task_id);
  ```

  ```sql Tenants theme={null}
  create index projects_tenant_idx 
    on projects (tenant_id);
  ```
</CodeGroup>

## Migration History

<Steps>
  <Step title="20260212220000_init_gantt_mvp.sql">
    Initial schema with projects, tasks, workflow columns, and basic RLS
  </Step>

  <Step title="20260213220000_auto_join_new_users.sql">
    Single-tenant mode: auto-add users to all projects
  </Step>

  <Step title="20260213220100_handle_new_user_google_compat.sql">
    Google OAuth metadata support for profile creation
  </Step>

  <Step title="20260216220000_fix_create_project_trigger_conflict.sql">
    Fixed duplicate project member insertions
  </Step>

  <Step title="20260217143000_multi_tenant_onboarding.sql">
    Multi-tenant architecture with tenant tables and updated functions
  </Step>
</Steps>

## Next Steps

<Card title="Multi-Tenancy Design" icon="users" href="/architecture/multi-tenancy">
  Deep dive into tenant isolation and permission model
</Card>