Skip to main content
The Tuturuuu platform uses Supabase (PostgreSQL) with a comprehensive multi-tenant architecture. This document provides an overview of the database schema, organized by domain.

​
Core Architecture

​
Multi-Tenancy Model

The database implements workspace-based multi-tenancy where:
  • Each workspace is isolated via Row-Level Security (RLS) policies
  • Users can belong to multiple workspaces
  • Resources are scoped to workspaces using ws_id foreign keys
  • Permissions are managed through workspace roles and granular permissions

​
Key Design Principles

  1. Security First: All tables have RLS policies enforcing workspace isolation
  2. Audit Trails: Many tables include created_at and updated_at timestamps
  3. Soft Deletes: Critical tables support soft deletion patterns
  4. Hierarchical Structures: Support for nested organizational structures (tasks, calendars, etc.)

​
Schema Domains

​
User & Workspace Management

​
workspace_users

Primary user table with workspace association. Key Fields:
  • id (uuid, PK)
  • display_name (text)
  • email (text)
  • phone (text)
  • avatar_url (text)

​
workspaces

Workspace container for multi-tenant organization. Key Fields:
  • id (text, PK)
  • name (text)
  • logo_url (text)
  • created_at (timestamptz)

​
workspace_members

Links users to workspaces with roles. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • user_id (uuid, FK → workspace_users)
  • role (text)
  • pending (boolean) - invitation status

​
workspace_role_permissions

Granular permission system for workspace roles. Key Fields:
  • ws_id (text, FK → workspaces)
  • role_id (text)
  • permission (workspace_role_permission enum)
  • created_at (timestamptz)
Available Permissions:
  • Infrastructure: manage_infrastructure_settings
  • Workspace: manage_workspace_settings, manage_workspace_security
  • Users: manage_users, manage_user_groups, manage_user_roles
  • Finance: manage_finance, ai_lab_assistant
  • Calendar: manage_calendar, manage_external_users
  • Documents: manage_documents
  • Inventory: manage_inventory
  • Disabled Users: view_disabled_users, disable_user

​
Task Management Hierarchy

The platform implements a 6-level hierarchical task management system: 
Workspaces (top-level)
└── Task Initiatives (strategic grouping)
    └── Task Projects (cross-board coordination)
        └── Workspace Boards (kanban boards)
            └── Task Lists (columns)
                └── Workspace Tasks (work items)

​
notes

Unstructured notes that can be converted to tasks or projects (Bucket Dump feature). Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • content (text)
  • created_by (uuid, FK → workspace_users)

​
task_initiatives

Strategic initiatives grouping multiple projects. Key Fields:
  • id (text, PK)
  • ws_id (text, FK → workspaces)
  • name (text)
  • description (text)
  • start_date (date)
  • end_date (date)

​
task_projects

Cross-functional projects coordinating tasks across boards. Key Fields:
  • id (text, PK)
  • ws_id (text, FK → workspaces)
  • name (text)
  • description (text)
Relationships:
  • task_project_initiatives - links projects to initiatives
  • task_project_tasks - links tasks to projects

​
workspace_boards

Kanban-style task boards. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • name (text)
  • description (text)
  • created_at (timestamptz)

​
task_lists

Columns within boards (e.g., “To Do”, “In Progress”, “Done”). Key Fields:
  • id (text, PK)
  • board_id (uuid, FK → workspace_boards)
  • name (text)
  • position (integer) - display order

​
workspace_tasks

Individual work items. Key Fields:
  • id (text, PK)
  • ws_id (text, FK → workspaces)
  • list_id (text, FK → task_lists)
  • name (text)
  • description (text)
  • priority (integer)
  • completed (boolean)
  • start_date (date)
  • due_date (date)
  • created_by (uuid, FK → workspace_users)
Related Tables:
  • task_cycles - sprint/iteration grouping
  • task_labels - categorization tags
  • task_estimates - effort estimation (fibonacci, exponential, linear, t-shirt)
  • task_statuses - custom status definitions per board

​
AI Features

​
ai_providers

Supported AI providers (OpenAI, Anthropic, Google Vertex AI, Gemini, etc.). Key Fields:
  • id (text, PK)
  • name (text)
  • value (text) - provider identifier

​
ai_models

Available AI models per provider. Key Fields:
  • id (text, PK)
  • provider (text, FK → ai_providers)
  • name (text)
  • model (text) - model identifier
  • input_price (numeric) - per million tokens
  • output_price (numeric) - per million tokens

​
ai_chats

Persistent chat sessions. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • model (text, FK → ai_models)
  • creator_id (uuid, FK → workspace_users)

​
ai_chat_messages

Individual messages within chat sessions. Key Fields:
  • id (uuid, PK)
  • chat_id (uuid, FK → ai_chats)
  • role (text) - user, assistant, system
  • content (text)
  • created_at (timestamptz)

​
workspace_ai_executions

Token usage tracking for billing. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • model (text, FK → ai_models)
  • input_tokens (integer)
  • output_tokens (integer)
  • total_cost (numeric)

​
ai_whitelisted_emails

Email whitelist for AI feature access.

​
Calendar System

​
workspace_calendar_events

Calendar events with Google Calendar sync support. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • description (text)
  • start_at (timestamptz)
  • end_at (timestamptz)
  • location (text)
  • google_event_id (text) - for sync
  • creator_id (uuid, FK → workspace_users)

​
calendar_event_participants

Event attendees. Key Fields:
  • event_id (uuid, FK → workspace_calendar_events)
  • user_id (uuid, FK → workspace_users)
  • attending (boolean)

​
calendar_sync_states

Tracks synchronization state with Google Calendar. Key Fields:
  • ws_id (text, PK, FK → workspaces)
  • sync_token (text) - Google sync token
  • last_synced_at (timestamptz)
  • is_syncing (boolean) - prevents concurrent syncs

​
calendar_auth_tokens

OAuth tokens for Google Calendar integration. Key Fields:
  • user_id (uuid, PK, FK → workspace_users)
  • access_token (text, encrypted)
  • refresh_token (text, encrypted)
  • expires_at (timestamptz)

​
workspace_calendar_sync_log

Audit trail for sync operations. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • sync_type (text) - full, incremental, batched
  • started_at (timestamptz)
  • completed_at (timestamptz)
  • error (text)

​
Finance Management

​
workspace_wallets

Primary wallet for workspace financial tracking. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • name (text)
  • balance (numeric)
  • currency (text)

​
credit_wallets

Credit-based wallet system. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • balance (numeric) - credit amount
  • currency (text)

​
wallet_transactions

Financial transaction history. Key Fields:
  • id (uuid, PK)
  • wallet_id (uuid, FK → workspace_wallets)
  • amount (numeric)
  • description (text)
  • type (text) - income, expense, transfer
  • category_id (uuid, FK → transaction_categories)
  • created_at (timestamptz)

​
workspace_role_wallet_whitelist

Role-based wallet access control with time-bounded viewing windows. Purpose: Controls which workspace roles can access specific wallets and defines the historical data viewing window for each role-wallet pairing. Key Fields:
  • id (uuid, PK)
  • role_id (uuid, FK → workspace_roles) - the role granted access
  • wallet_id (uuid, FK → workspace_wallets) - the wallet being accessed
  • viewing_window (text) - allowed values: 1_day, 3_days, 7_days, 2_weeks, 1_month, 1_quarter, 1_year, custom
  • custom_days (integer) - required when viewing_window = 'custom'; must be > 0; must be NULL otherwise
  • created_at (timestamptz)
Constraints:
  • Unique constraint on (role_id, wallet_id) - one whitelist entry per role-wallet pair
  • CHECK constraint on viewing_window - enforces allowed literal values
  • CHECK constraint on custom_days - ensures presence/positivity when viewing_window = 'custom' and NULL otherwise
Access Control:
  • RLS policies restrict access to users with manage_workspace_roles permission
  • INSERT policy validates wallet belongs to the same workspace as the role
  • UPDATE policy enforces wallet-workspace consistency via WITH CHECK clause

​
Meeting Management

​
meeting_plans

Scheduled meetings with metadata. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • description (text)
  • start_time (timestamptz)
  • end_time (timestamptz)

​
meeting_plan_timeblocks

Time allocations within meetings. Key Fields:
  • id (uuid, PK)
  • meeting_id (uuid, FK → meeting_plans)
  • title (text)
  • duration (integer) - minutes
  • position (integer)

​
meeting_guest_accounts

External participant management. Key Fields:
  • id (uuid, PK)
  • email (text)
  • display_name (text)
  • ws_id (text, FK → workspaces)

​
meeting_recordings

Meeting transcription and recording storage. Key Fields:
  • id (uuid, PK)
  • meeting_id (uuid, FK → meeting_plans)
  • transcription (text)
  • summary (text) - AI-generated
  • recording_url (text)

​
Nova Platform (Prompt Engineering)

​
nova_problems

Prompt engineering challenge problems. Key Fields:
  • id (uuid, PK)
  • title (text)
  • description (text)
  • difficulty (text) - easy, medium, hard
  • created_by (uuid, FK → workspace_users)

​
nova_challenges

Specific challenge instances. Key Fields:
  • id (uuid, PK)
  • problem_id (uuid, FK → nova_problems)
  • title (text)
  • start_time (timestamptz)
  • end_time (timestamptz)

​
nova_test_cases

Test cases for validating prompt submissions. Key Fields:
  • id (uuid, PK)
  • problem_id (uuid, FK → nova_problems)
  • input (jsonb)
  • expected_output (jsonb)
  • is_hidden (boolean) - public vs private tests

​
nova_submissions

User prompt submissions. Key Fields:
  • id (uuid, PK)
  • challenge_id (uuid, FK → nova_challenges)
  • user_id (uuid, FK → workspace_users)
  • prompt (text)
  • score (numeric)
  • submitted_at (timestamptz)

​
nova_sessions

Active problem-solving sessions. Key Fields:
  • id (uuid, PK)
  • user_id (uuid, FK → workspace_users)
  • problem_id (uuid, FK → nova_problems)
  • started_at (timestamptz)
  • completed_at (timestamptz)

​
Education System

​
courses

Educational course catalog. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • description (text)
  • instructor_id (uuid, FK → workspace_users)

​
course_modules

Course content structure. Key Fields:
  • id (uuid, PK)
  • course_id (uuid, FK → courses)
  • title (text)
  • content (text)
  • position (integer)

​
course_module_completion_status

Student progress tracking. Key Fields:
  • user_id (uuid, FK → workspace_users)
  • module_id (uuid, FK → course_modules)
  • completed_at (timestamptz)

​
course_certificates

Achievement certificates. Key Fields:
  • id (uuid, PK)
  • user_id (uuid, FK → workspace_users)
  • course_id (uuid, FK → courses)
  • template (text) - elegant, modern, original
  • issued_at (timestamptz)

​
quiz_sets

Quiz collections. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • questions (jsonb)

​
quiz_attempts

User quiz submissions. Key Fields:
  • id (uuid, PK)
  • quiz_id (uuid, FK → quiz_sets)
  • user_id (uuid, FK → workspace_users)
  • score (numeric)
  • answers (jsonb)
  • completed_at (timestamptz)

​
flashcard_sets

Flashcard study materials. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • cards (jsonb) - array of {front, back}

​
Time Tracking

​
time_tracking_sessions

Hierarchical time tracking with daily structure. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • user_id (uuid, FK → workspace_users)
  • task_id (text, FK → workspace_tasks)
  • start_time (timestamptz)
  • end_time (timestamptz)
  • duration (integer) - seconds
  • date (date) - for daily hierarchy
Related Tables:
  • workspace_calendar_hour_settings - categorizes hours (work, meeting, personal)

​
URL Shortener

Short URL mappings. Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • short_code (text, unique)
  • original_url (text)
  • created_by (uuid, FK → workspace_users)
  • expires_at (timestamptz)
Click tracking. Key Fields:
  • id (uuid, PK)
  • link_id (uuid, FK → shortened_links)
  • clicked_at (timestamptz)
  • ip_address (inet)
  • user_agent (text)
  • referrer (text)

​
Subscriptions & Billing

​
workspace_subscriptions

Workspace subscription status. Key Fields:
  • ws_id (text, PK, FK → workspaces)
  • product_id (text, FK → workspace_subscription_products)
  • status (text) - active, canceled, expired
  • started_at (timestamptz)
  • expires_at (timestamptz)

​
workspace_subscription_products

Available subscription tiers. Key Fields:
  • id (text, PK)
  • name (text)
  • price (numeric)
  • currency (text)
  • features (jsonb)

​
Additional Features

​
user_referrals

Referral tracking system (added September 2025). Key Fields:
  • id (uuid, PK)
  • referrer_id (uuid, FK → workspace_users)
  • referred_id (uuid, FK → workspace_users)
  • created_at (timestamptz)

​
whiteboards

Real-time collaborative whiteboard (added June 2025). Key Fields:
  • id (uuid, PK)
  • ws_id (text, FK → workspaces)
  • title (text)
  • content (jsonb) - whiteboard state

​
support_inquiries

Support ticket system. Key Fields:
  • id (uuid, PK)
  • type (text) - bug, feature-request, job-application, support
  • subject (text)
  • description (text)
  • status (text) - open, in-progress, resolved
  • created_by (uuid, FK → workspace_users)

​
discord_guild_integrations

Discord server integration metadata. Key Fields:
  • guild_id (text, PK)
  • ws_id (text, FK → workspaces)
  • enabled (boolean)

​
Database Functions

The schema includes several PostgreSQL functions for complex operations:

​
atomic_sync_token_operation

Atomically updates calendar sync tokens to prevent race conditions. Usage: Calendar synchronization coordination

​
get_wallet_viewing_window_days

Converts viewing window literals to day counts for wallet access control. Signature: 
public.get_wallet_viewing_window_days(
  p_viewing_window TEXT,
  p_custom_days INTEGER DEFAULT NULL
) RETURNS INTEGER
Return Type: INTEGER - number of days in the viewing window Semantics:
  • Maps viewing window literals (1_day, 3_days, 7_days, 2_weeks, 1_month, 1_quarter, 1_year) to day counts
  • For viewing_window = 'custom', returns p_custom_days value (defaults to 30 if invalid)
  • Defaults to 30 days for unknown viewing_window values
  • Function is IMMUTABLE - safe for indexing and optimization
Example: 
SELECT public.get_wallet_viewing_window_days('1_month', NULL); -- Returns 30
SELECT public.get_wallet_viewing_window_days('custom', 45);    -- Returns 45
SELECT public.get_wallet_viewing_window_days('1_year', NULL);  -- Returns 365

​
user_has_wallet_access_via_role

Checks if a user has wallet access through role whitelisting and returns viewing window details. Signature: 
public.user_has_wallet_access_via_role(
  p_user_id UUID,
  p_wallet_id UUID,
  p_ws_id UUID
) RETURNS TABLE (
  has_access BOOLEAN,
  viewing_window TEXT,
  custom_days INTEGER,
  window_start_date TIMESTAMPTZ
)
Parameters:
  • p_user_id - the user to check (caller can only query their own ID unless they are a workspace admin)
  • p_wallet_id - the wallet to check access for
  • p_ws_id - the workspace context
Return Columns:
  • has_access - TRUE if user has access via any role whitelist, FALSE otherwise
  • viewing_window - the viewing window setting from the whitelist (e.g., 1_month, custom)
  • custom_days - the custom day count (NULL if not using custom window)
  • window_start_date - calculated start of the viewing window (rolling from current timestamp)
Security:
  • Function is SECURITY DEFINER - runs with elevated privileges
  • Authorization gate: Callers can only query their own user_id OR must be a workspace admin (ADMIN/OWNER role)
  • Raises exception Permission denied if caller attempts to query another user without admin privileges
Access Logic:
  • When multiple roles grant access to the same wallet, selects the widest access window (most days)
  • Tie-breaking: uses highest custom_days value, then id for deterministic ordering
  • Calculates rolling window start date by subtracting window days from current timestamp
Example Usage: 
-- Check if current user has access to a specific wallet
SELECT * FROM public.user_has_wallet_access_via_role(
  auth.uid(),
  '550e8400-e29b-41d4-a716-446655440000'::uuid,
  'workspace-123'::text
);

-- Admin checking access for another user
SELECT * FROM public.user_has_wallet_access_via_role(
  '123e4567-e89b-12d3-a456-426614174000'::uuid,  -- another user
  '550e8400-e29b-41d4-a716-446655440000'::uuid,  -- wallet
  'workspace-123'::text
);

-- Filter transactions by viewing window
WITH access AS (
  SELECT * FROM public.user_has_wallet_access_via_role(
    auth.uid(),
    '550e8400-e29b-41d4-a716-446655440000'::uuid,
    'workspace-123'::text
  )
)
SELECT t.*
FROM wallet_transactions t
CROSS JOIN access a
WHERE t.wallet_id = '550e8400-e29b-41d4-a716-446655440000'::uuid
  AND a.has_access = TRUE
  AND t.created_at >= a.window_start_date;

​
User Creation Triggers

Automatically create related records when new users are added.

​
Task Board Member Management

Helper functions for managing board access permissions.

​
AI Pricing Helpers

Calculate AI model costs based on token usage.

​
Migration Management

Migrations are stored in /apps/database/supabase/migrations/ with timestamp prefixes: 
20250929042000_add_notes.sql
20250929060000_add_task_cycles.sql
20250911083732_add_task_labels_and_estimates.sql
...
See Migration Guide for workflow details.

​
Type Generation

After schema changes, regenerate TypeScript types: 
bun sb:typegen
Generated types are exported from @tuturuuu/types: 
import type { Database } from '@tuturuuu/types';

type Task = Database['public']['Tables']['workspace_tasks']['Row'];