Database Schema & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• GEMINI.md
• packages/ai/src/chat/google/route.ts
• packages/ai/src/chat/google-vertex/route.ts
• apps/backend/README.md
• packages/supabase/PUBLISHING.md
• packages/supabase/QUICKSTART.md

The database layer of the Tuturuuu platform is primarily built on Supabase (PostgreSQL), utilizing Row-Level Security (RLS) for multi-tenant data isolation. The architecture is designed to support a monorepo ecosystem where shared packages and individual applications interact with a centralized schema that manages workspaces, users, AI interactions, and platform-specific data models.

The system handles both structured relational data via PostgreSQL and unstructured file storage through Supabase Storage. Migrations are managed through the Supabase CLI, ensuring that schema changes are versioned and consistently applied across local development and production environments.

Sources: README.md:126-133, GEMINI.md:13-17, GEMINI.md:214-220

Database Architecture & Client Integration

The project employs a tiered client approach for database interaction, separating standard user-context operations from administrative tasks. This is implemented through the @tuturuuu/supabase package, which provides specific clients for different execution environments.

Supabase Clients

Client Type	Purpose	Security Context
createClient	Standard user operations	Respects Row-Level Security (RLS) and auth.uid()
createAdminClient	Service-level tasks	Bypasses RLS for maintenance or system triggers
createDynamicClient	Dynamic server-side operations	Used for server-side resource management

Sources: packages/ai/src/chat/google/route.ts:13-17, packages/ai/src/chat/google/route.ts:40-42, GEMINI.md:204-206, packages/supabase/QUICKSTART.md:52-64

Data Access Flow

The following diagram illustrates how different platform components interact with the database and storage layers.

Migration Workflow

Database schema evolution is strictly controlled through a migration-based workflow. Agents and developers are prohibited from manual schema edits, relying instead on the Supabase CLI for version control.

1. Creation: New migrations are initialized using bun sb:new, which generates a timestamped SQL file in the database/supabase/migrations/ directory.
2. Application: Migrations are applied locally using bun sb:push or bun sb:up.
3. Type Generation: Following a successful migration, TypeScript types are regenerated via bun sb:typegen to ensure the @tuturuuu/types package reflects the latest schema.
4. Inference: Developers import types from packages/types/src/db.ts to access type aliases for entities like Workspace, Task, or AI_Chat.

Sources: GEMINI.md:183-195, README.md:196-203

Key Data Models

AI & Chat System

The platform includes specific tables and RPCs (Remote Procedure Calls) to manage AI interactions and credit consumption.

Table/RPC	Type	Description
ai_chats	Table	Stores session metadata for AI conversations, linked to creator_id.
ai_chat_messages	Table	Stores individual prompts and assistant responses, including token usage.
insert_ai_chat_message	RPC	Database function for atomic insertion of user messages with metadata.

Sources: packages/ai/src/chat/google/route.ts:241-260, packages/ai/src/chat/google-vertex/route.ts:117-124, packages/ai/src/chat/google-vertex/route.ts:182-191

Storage Integration

The platform uses a workspace-prefixed storage hierarchy. Files are stored within the workspaces bucket, following the path structure: {wsId}/chats/ai/resources/{chatId}/{fileName}.

Privacy & Security Considerations

A critical design choice in the Tuturuuu schema is the separation of public and private user data to enhance security and comply with privacy standards.

• Public User Profiles: The public.users table is used for general identity but does not contain email addresses.
• Private Details: Sensitive information, specifically user email addresses, is stored in a separate public.user_private_details table.
• RLS Bypassing: While createAdminClient can bypass RLS, it is strictly noted that triggers checking auth.uid() can be bypassed, requiring careful validation in the application logic before using administrative privileges.

Sources: GEMINI.md:214-220, GEMINI.md:204-206

Backend Rust Integration

While the primary platform uses Supabase, a secondary Rust/Axum backend exists for educational and specific microservice tasks. It manages its own PostgreSQL schema using SQLx migrations, supporting features such as:

• Users & Profiles: Managed via 001_create_users_table.sql.
• Content Management: Blog posts and metadata in 002_create_posts_table.sql.
• Session Tracking: JWT-based session management in 003_create_sessions_table.sql.

Sources: apps/backend/README.md:255-275

Summary

The Database Schema & Migrations system provides a robust, multi-tenant foundation for the platform. By centralizing logic in the @tuturuuu/supabase package and enforcing a strict migration-typegen cycle, the project maintains type safety across diverse applications (web, mobile, and backend) while ensuring sensitive user data remains protected via separated tables and Row-Level Security.