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

# Database Schema Overview

> Complete reference for the Tuturuuu platform database schema

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

### Private Schema Operations

Private tables and RPCs used by `apps/web` are exposed to server-side Supabase
clients through PostgREST's configured private schema. After production
migrations add private tables, RPC signatures, or columns that the app reads
through `.schema('private')`, reload the PostgREST schema cache:

```sql theme={null}
notify pgrst, 'reload schema';
```

Local Supabase restarts usually refresh this cache automatically, so stale-cache
failures can appear only in production after otherwise successful migrations.

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

<Warning>
  The `workspace_role_permission` enum is large (100+ values) and evolves
  frequently as new product surfaces ship. The generated TypeScript union is the
  single source of truth — do **not** hand-copy permission names from this page
  into RLS policies, role seeds, or app code. Read the live union instead:

  ```typescript theme={null}
  import type { Database } from '@tuturuuu/types';

  type WorkspacePermission =
    Database['public']['Enums']['workspace_role_permission'];
  ```

  In SQL, the canonical definition lives in the `workspace_role_permission` enum
  type (defined by the migrations under `apps/database/supabase/migrations/`).
</Warning>

The values below are a representative snapshot grouped by domain (accurate as of
this page's `updatedAt`, but treat the generated type as authoritative):

* **Workspace administration**: `manage_workspace_settings`,
  `manage_workspace_roles`, `manage_workspace_members`,
  `manage_workspace_security`, `manage_workspace_integrations`,
  `manage_workspace_billing`, `manage_workspace_audit_logs`,
  `manage_workspace_secrets`, `manage_subscription`, `manage_api_keys`,
  `manage_e2ee`, `admin`
* **Infrastructure**: `view_infrastructure`, `manage_external_migrations`,
  `manage_infrastructure_stress_tests`, `manage_mobile_deployment_vault`
* **Users & groups**: `manage_users`, `create_users`, `update_users`,
  `delete_users`, `export_users_data`, `view_users_public_info`,
  `view_users_private_info`, `view_user_groups`, `create_user_groups`,
  `update_user_groups`, `delete_user_groups`, `send_user_group_post_emails`,
  `manage_user_report_templates`, `manage_user_profile_links`
* **Finance**: `manage_finance`, `export_finance_data`, `view_finance_stats`,
  `view_transactions`, `create_transactions`, `update_transactions`,
  `delete_transactions`, `view_invoices`, `view_expenses`, `view_incomes`,
  `create_wallets`, `update_wallets`, `delete_wallets`, `change_finance_wallets`,
  `view_confidential_amount`, `view_confidential_description`
* **Inventory**: `manage_inventory`, `view_inventory`, `create_inventory`,
  `update_inventory`, `delete_inventory`, `view_inventory_catalog`,
  `manage_inventory_catalog`, `view_inventory_stock`, `adjust_inventory_stock`,
  `view_inventory_sales`, `view_inventory_analytics`
* **AI**: `ai_chat`, `ai_lab`
* **Chat**: `view_chat`, `create_chat`, `manage_chat`, `moderate_chat`
* **Projects, docs & drive**: `manage_projects`, `manage_documents`,
  `manage_drive`, `view_drive`, `manage_drive_tasks_directory`,
  `manage_external_projects`, `publish_external_projects`
* **Calendar & time tracking**: `manage_calendar`,
  `manage_time_tracking_requests`, `bypass_time_tracking_request_approval`
* **Workforce & payroll**: `manage_workforce`, `view_workforce`,
  `manage_payroll`, `view_payroll`, `check_user_attendance`,
  `update_user_attendance`
* **Forms & changelog**: `manage_forms`, `view_form_analytics`,
  `manage_changelog`

The previously documented permissions `manage_infrastructure_settings`,
`ai_lab_assistant`, `manage_user_groups`, `manage_user_roles`,
`manage_external_users`, `view_disabled_users`, and `disable_user` have been
**removed or renamed** in the current enum — do not reference them in new policies.

### Authentication & Credential Handoffs

#### `private.cross_app_tokens`

Private short-lived tokens for cross-app authentication handoffs. Client and
satellite apps should use the existing public RPC helpers or central
`apps/web` verification route; do not read or write the table through public
Supabase REST access.

**Key Fields:**

* `id` (uuid, PK)
* `user_id` (uuid, FK → users)
* `token` (text, unique)
* `origin_app` (text)
* `target_app` (text)
* `expires_at` (timestamptz)
* `used_at` (timestamptz)
* `session_data` (jsonb)

#### `private.internal_email_api_keys`

Private API credentials for the internal mail send route. The
`apps/web` route reads these keys with the server-side admin client and keeps
credential validation behind the application proxy.

**Key Fields:**

* `id` (uuid, PK)
* `creator_id` (uuid, FK → users)
* `user_id` (uuid, FK → users)
* `value` (text)
* `allowed_emails` (text\[])
* `created_at` (timestamptz)

### Notification Delivery Internals

#### `private.notification_batches`

Private operational queue rows for email and push notification delivery.
Server-owned `apps/web` cron and immediate-send routes read and update these
rows through the service-role client; browser, mobile, and satellite apps should
not query them directly.

**Key Fields:**

* `id` (uuid, PK)
* `ws_id` (uuid, FK → workspaces, nullable for pending-email flows)
* `user_id` (uuid, FK → users, nullable for pending-email flows)
* `email` (text)
* `channel` (text)
* `delivery_mode` (notification\_delivery\_mode)
* `status` (text)
* `window_start` / `window_end` (timestamptz)

#### `private.notification_delivery_log`

Private delivery-attempt records linked to persisted notifications and optional
batches. The table is intentionally excluded from public Data API and Realtime
access; inspect or mutate it through centralized notification APIs only.

**Key Fields:**

* `id` (uuid, PK)
* `notification_id` (uuid, FK → notifications)
* `batch_id` (uuid, FK → private.notification\_batches)
* `channel` (text)
* `status` (text)
* `retry_count` (integer)
* `sent_at` (timestamptz)

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

#### `private.ai_providers`

Legacy supported AI providers retained for historical foreign-key compatibility.
Active model catalog APIs should use `private.ai_gateway_models` through centralized
`apps/web` routes instead of Supabase REST/PostgREST.

**Key Fields:**

* `id` (text, PK)
* `name` (text)
* `created_at` (timestamptz)

#### `private.ai_models`

Legacy AI models per provider retained for historical foreign-key compatibility.
Active model catalog APIs should use `private.ai_gateway_models` through centralized
`apps/web` routes instead of Supabase REST/PostgREST.

**Key Fields:**

* `id` (text, PK)
* `provider` (text, FK → private.ai\_providers)
* `name` (text)
* `enabled` (boolean)

#### `ai_chats`

Persistent chat sessions.

**Key Fields:**

* `id` (uuid, PK)
* `title` (text)
* `model` (text) - free-text AI Gateway model id in `provider/model` form
  (e.g. `google/gemini-2.5-flash`). No longer a foreign key — the legacy
  `ai_chats.model → private.ai_models` constraint was dropped, and
  `private.ai_gateway_models` is now the single source of truth for the active
  model catalog.
* `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_id` (text, FK → private.ai\_models) - retains the legacy `ai_models`
  reference for historical executions; the active model catalog now lives in
  `private.ai_gateway_models`, and pricing helpers map identifiers accordingly
* `input_tokens` (integer)
* `output_tokens` (integer)
* `total_tokens` (integer)

#### `private.ai_whitelisted_emails`

Server-owned email whitelist for AI feature access. This table is kept outside
the Supabase REST-exposed `public` schema; apps should check or manage access
through centralized `apps/web` APIs.

### Private Reference Tables

#### `private.currencies`

Finance reference data retained for foreign-key compatibility with
`private.workspace_wallets` and `currency_exchange_rates`. Runtime consumers should use
centralized app APIs or package currency constants instead of Supabase REST.

#### `private.field_types`

Workspace user-field reference data retained for historical foreign keys.
Management should go through centralized app APIs instead of direct public table
access.

#### `private.healthcare_diagnoses`

Legacy healthcare diagnosis records retained for historical checkup foreign keys.
Server-side helpers own access; the public count RPC is restricted to
`service_role` and reads from the private schema.

#### `private.personal_notes`

Legacy per-user notes retained for historical compatibility. Active personal
task-note behavior should stay behind centralized app flows instead of Supabase
REST table access.

#### `private.team_members`

Legacy team membership data retained for historical foreign-key compatibility.
Active membership flows should use workspace or Nova team tables.

#### `private.timezones`

Timezone synchronization records for infrastructure management. The dashboard
continues to source canonical timezone data from package constants and reaches
stored records only through `apps/web` APIs.

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

#### `private.workspace_calendar_sync_log`

Private operational audit trail for calendar sync jobs. Access this table
through service-role infrastructure routes instead of Supabase REST table
access.

**Key Fields:**

* `id` (uuid, PK)
* `ws_id` (uuid, FK → workspaces)
* `google_account_email` (text)
* `sync_started_at` (timestamptz)
* `sync_ended_at` (timestamptz)
* `status` (text) - success, failed, in\_progress, cancelled, partial\_success
* `error_message` (text)
* `event_snapshot_before` (jsonb)

### Finance Management

#### `private.workspace_wallets`

Primary wallet for workspace financial tracking. Wallet rows are owned by
server-side finance APIs that verify workspace permissions before using
service-role private schema access.

**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 → private.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 → private.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

#### `shortened_links`

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)

#### `shortened_link_analytics`

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 → private.workspace\_subscription\_products)
* `status` (text) - active, canceled, expired
* `started_at` (timestamptz)
* `expires_at` (timestamptz)

#### `private.workspace_subscription_products`

Private subscription tiers managed through billing and Polar server helpers.

**Key Fields:**

* `id` (text, PK)
* `name` (text)
* `price` (numeric)
* `currency` (text)
* `features` (jsonb)

#### `private.workspace_subscription_errors`

Private subscription failure state used by billing overview RPCs and
service-role repair helpers.

**Key Fields:**

* `id` (uuid, PK)
* `ws_id` (uuid, FK → workspaces)
* `error_message` (text)
* `error_source` (text)
* `created_at` (timestamptz)
* `resolved_at` (timestamptz)

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

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

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

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

```sql theme={null}
-- 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](/reference/database/migrations-guide) for workflow details.

## Related Documentation

* [RLS Policies](/reference/database/rls-policies) - Security model and policy patterns
* [Migration Guide](/reference/database/migrations-guide) - Migration workflow

## Type Generation

After schema changes, regenerate TypeScript types:

```bash theme={null}
bun sb:typegen
```

Generated types are exported from `@tuturuuu/types`:

```typescript theme={null}
import type { Database } from '@tuturuuu/types';

type Task = Database['public']['Tables']['workspace_tasks']['Row'];
type PrivateConfig = Database['private']['Tables']['notification_email_config']['Row'];
type StorageObject = Database['storage']['Tables']['objects']['Row'];
```