Database & Supabase Operations

Relevant source files

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

• lib/supabase/serverClient.ts
• dev/API-AGENT.md
• types/database.types.ts
• CLAUDE.md
• AGENTS.md

Database operations in this project are powered by Supabase, providing a PostgreSQL backend with integrated authentication and real-time capabilities. The architecture emphasizes a strict separation of concerns, ensuring that database logic is encapsulated within specific domain directories and never called directly from API routes or high-level business logic.

The system uses a "Sandbox-First" approach for data-heavy tasks, utilizing a persistent environment for complex data analysis, report generation, and release management. All data entities are referred to using "account" terminology (e.g., account_id) to maintain consistency across individuals, artists, and organizations.

Sources: CLAUDE.md:55-58, AGENTS.md:55-58, lib/chat/const.ts:10-15

Architectural Standards

The project follows a rigid directory structure and naming convention to maintain a clean codebase. A critical rule is that @/lib/supabase/serverClient must never be imported outside of the lib/supabase/ directory.

Directory Structure

Database logic is organized by table name within the lib/supabase/ directory:

• lib/supabase/serverClient.ts: Contains the singleton Supabase client instance.
• lib/supabase/[table_name]/: Subdirectories for each database table containing specific operation files.

Sources: CLAUDE.md:61-64, CLAUDE.md:82-93, AGENTS.md:82-93

Function Naming Conventions

To ensure predictability, developers must use the following prefixes for database functions:

Prefix	Description	Example
select	Basic SELECT queries with optional filters	selectAccounts.ts
insert	INSERT queries for adding new records	insertApiKey.ts
update	UPDATE queries for modifying existing records	updateAccount.ts
delete	DELETE queries for removing records	deleteTask.ts
get	Complex queries involving multiple joins	getAccountOrganizations.ts

Sources: CLAUDE.md:95-101, AGENTS.md:95-101, dev/API-AGENT.md:195-204

Data Flow Pattern

The following diagram illustrates the standard flow for a database request, originating from an API route and resolving through the domain-specific Supabase lib.

The "Handler Pattern" ensures that routes are thin wrappers, delegating validation and business logic to dedicated modules before reaching the database layer.

Sources: CLAUDE.md:120-130, dev/API-AGENT.md:65-75

Database Schema (Selected Entities)

The database consists of numerous tables handling accounts, social data, messaging, and financial operations. Below are key entities and their relationships.

Core Account Entities

The system treats all entities as "accounts". Relationships are managed through junction tables like account_organization_ids.

Table	Field	Type	Description
accounts	id	uuid	Unique identifier for the account
accounts	name	text	Display name of the account
account_emails	email	text	Associated email address
account_api_keys	key_hash	text	Hashed API key for authentication
account_info	knowledges	jsonb	Knowledge base files and metadata

Sources: types/database.types.ts:114-123, types/database.types.ts:77-85, types/database.types.ts:25-35

Schema Relationships

The following Entity Relationship Diagram (ERD) showcases the connections between core account tables and associated resources.

Operational Implementation

Supabase Client Instance

The central client is configured to use environment variables for connection and is the sole point of contact with the Supabase API.

// lib/supabase/serverClient.ts
import { createClient } from "@supabase/supabase-js";
import { Database } from "@/types/database.types";

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY!;

const supabase = createClient<Database>(supabaseUrl, supabaseKey);
export default supabase;

Note: Direct usage of this client is strictly restricted to the lib/supabase/ directory.

Sources: CLAUDE.md:61-64, dev/API-AGENT.md:38-45

Credits and Usage Tracking

The database includes specific functions for managing resource usage, such as deduct_credits.

This is often used in conjunction with AI chat operations where handleChatCredits calculates usage based on token counts and model types before calling the database.

Sources: types/database.types.ts:1709-1712, lib/chat/tests/integration/chatEndToEnd.test.ts:316-330

Stored Procedures and Functions

The PostgreSQL backend utilizes several stored functions for complex operations:

• accept_invitation: Handles the logic for joining an account via a token.
• deduct_credits: Atomically reduces an account's credit balance.
• has_permission: Checks if a specific user_id has a required app_permissions on an account_id.
• upsert_subscription: Manages the lifecycle of billing subscriptions across different providers.

Sources: types/database.types.ts:1680-1685, types/database.types.ts:1709-1712, types/database.types.ts:1754-1761

Database and Supabase operations in this repository are defined by a strict domain-driven architecture. By enforcing isolation between the database client and the rest of the application, the project ensures type safety (via database.types.ts) and maintainability. All interactions are centered around the "account" entity, providing a unified data model for artists, organizations, and team members.