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_idforeign keys - Permissions are managed through workspace roles and granular permissions
Key Design Principles
- Security First: All tables have RLS policies enforcing workspace isolation
- Audit Trails: Many tables include
created_atandupdated_attimestamps - Soft Deletes: Critical tables support soft deletion patterns
- Hierarchical Structures: Support for nested organizational structures (tasks, calendars, etc.)
Private Schema Operations
Private tables and RPCs used byapps/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:
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)
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
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: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)
task_project_initiatives- links projects to initiativestask_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)
task_cycles- sprint/iteration groupingtask_labels- categorization tagstask_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 inprovider/modelform (e.g.google/gemini-2.5-flash). No longer a foreign key — the legacyai_chats.model → private.ai_modelsconstraint was dropped, andprivate.ai_gateway_modelsis 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, systemcontent(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 legacyai_modelsreference for historical executions; the active model catalog now lives inprivate.ai_gateway_models, and pricing helpers map identifiers accordinglyinput_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 synccreator_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 tokenlast_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_successerror_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 amountcurrency(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, transfercategory_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 accesswallet_id(uuid, FK → private.workspace_wallets) - the wallet being accessedviewing_window(text) - allowed values:1_day,3_days,7_days,2_weeks,1_month,1_quarter,1_year,customcustom_days(integer) - required whenviewing_window = 'custom'; must be > 0; must be NULL otherwisecreated_at(timestamptz)
- 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 whenviewing_window = 'custom'and NULL otherwise
- RLS policies restrict access to users with
manage_workspace_rolespermission - 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) - minutesposition(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-generatedrecording_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, hardcreated_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, originalissued_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) - secondsdate(date) - for daily hierarchy
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, expiredstarted_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, supportsubject(text)description(text)status(text) - open, in-progress, resolvedcreated_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:
- 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', returnsp_custom_daysvalue (defaults to 30 if invalid) - Defaults to 30 days for unknown
viewing_windowvalues - Function is
IMMUTABLE- safe for indexing and optimization
user_has_wallet_access_via_role
Checks if a user has wallet access through role whitelisting and returns viewing window details.
Signature:
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 forp_ws_id- the workspace context
has_access-TRUEif user has access via any role whitelist,FALSEotherwiseviewing_window- the viewing window setting from the whitelist (e.g.,1_month,custom)custom_days- the custom day count (NULL if not usingcustomwindow)window_start_date- calculated start of the viewing window (rolling from current timestamp)
- Function is
SECURITY DEFINER- runs with elevated privileges - Authorization gate: Callers can only query their own
user_idOR must be a workspace admin (ADMIN/OWNER role) - Raises exception
Permission deniedif caller attempts to query another user without admin privileges
- When multiple roles grant access to the same wallet, selects the widest access window (most days)
- Tie-breaking: uses highest
custom_daysvalue, thenidfor deterministic ordering - Calculates rolling window start date by subtracting window days from current timestamp
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:
Related Documentation
- RLS Policies - Security model and policy patterns
- Migration Guide - Migration workflow
Type Generation
After schema changes, regenerate TypeScript types:@tuturuuu/types: