> ## 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 Setup

> Set up and configure the PostgreSQL database schema with Supabase migrations

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

## Quick Start

<Steps>
  <Step title="Start Supabase">
    Initialize the local Supabase instance:

    ```bash theme={null}
    cd packages/app
    supabase start
    ```
  </Step>

  <Step title="Apply Migrations">
    Reset the database and apply all migrations:

    ```bash theme={null}
    supabase db reset
    ```

    This applies all migrations in order and runs the seed data.
  </Step>

  <Step title="Verify Setup">
    Check the Supabase Studio:

    ```bash theme={null}
    # Studio URL is shown in supabase start output
    # Default: http://127.0.0.1:54323
    ```
  </Step>
</Steps>

## 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:

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

#### Tenants

Multi-tenant workspaces for team isolation:

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

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

#### Projects

Projects belong to tenants and contain tasks:

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

#### Tasks

Core entity for work items:

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

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

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

<AccordionGroup>
  <Accordion title="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
  </Accordion>

  <Accordion title="Team Management">
    * **tenant\_members**: User membership in tenants
    * **project\_members**: User membership in projects

    Both support role-based access control.
  </Accordion>
</AccordionGroup>

## 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              |

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

## Database Functions

### Project Management

<CodeGroup>
  ```sql create_tenant_with_owner theme={null}
  create or replace function public.create_tenant_with_owner(
    p_name text,
    p_slug text default null
  )
  returns public.tenants
  ```

  ```sql create_project_with_defaults theme={null}
  create or replace function public.create_project_with_defaults(
    p_tenant_slug text,
    p_name text,
    p_description text
  )
  returns uuid
  ```

  ```sql move_task 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
  ```
</CodeGroup>

### Permission Helpers

These functions are used in Row Level Security policies:

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

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

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

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

## Seed Data

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

### Test Users

All users have password: `password123`

* **[owner@gantt.local](mailto:owner@gantt.local)** - Olivia Owner (tenant owner)
* **[editor@gantt.local](mailto:editor@gantt.local)** - Ethan Editor (tenant admin)
* **[viewer@gantt.local](mailto: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:

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

```sql theme={null}
create trigger touch_tasks_updated_at
before update on public.tasks
for each row execute procedure public.touch_updated_at();
```

## Database Backup

<Tabs>
  <Tab title="Local Development">
    Supabase local data is ephemeral. To preserve data:

    ```bash theme={null}
    # Export schema
    supabase db dump -f schema.sql --schema public

    # Export data
    supabase db dump -f data.sql --data-only
    ```
  </Tab>

  <Tab title="Production">
    Supabase provides automatic backups:

    * Point-in-time recovery (PITR) on Pro plan
    * Daily backups retained for 7 days
    * Manual backups via Supabase Dashboard
  </Tab>
</Tabs>

## Indexes

Optimized indexes for common queries:

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

## Next Steps

<CardGroup cols={2}>
  <Card title="Authentication" icon="shield-halved" href="/configuration/authentication">
    Configure Supabase Auth providers
  </Card>

  <Card title="Environment Variables" icon="key" href="/configuration/environment-variables">
    Set up required environment variables
  </Card>
</CardGroup>