Supabase Setup

The editable SQL lives in domain modules under database/schema/ and native integrations under database/overlays/. database/manifest.json defines exact assembly order. Run pnpm run db:schema:check after source edits. Init, local start/reset, bootstrap and audits assemble the complete core and CMS batches in memory; exported files are optional.

The same common sources assemble for Supabase or Neon and for Supabase Auth or Better Auth. Provider-native behavior is added only by explicit overlays. This fresh-installation workflow does not support upgrades of populated deployments. See SQL Sources & Installation for composition selection, domain ownership, manual installation and error mapping.

Create a Supabase project
Go to supabase.com and create a new project.
Get your credentials
Navigate to Settings > API and copy:
- Project URL
- Publishable key
- Secret key
Initialize the database
Run pnpm run init for a fresh installation. For the manual SQL step, first run pnpm run db:schema:build and pnpm run db:schema:check-export, then execute the complete generated core file followed by the CMS file. Follow SQL Sources & Installation; source fragments must not be run individually.

Schema Overview

The database schema is organized into several functional areas. Click on each table to see detailed field information.

RLS follows the account-centric model: server-side Prisma transactions set the verified appUserId in app.actor_id, and policies scope reads and attributed writes through private.current_actor_id() plus Account memberships. This prevents one workspace member from forging another member's chat message or document ownership without coupling business SQL to a provider-native subject. Runtime, worker, maintenance and platform-administration credentials are distinct and least-privileged.

Data API Grants

Business persistence is not exposed through the Supabase Data API. The Supabase database overlay explicitly fences anon, authenticated, and service_role from common business tables and functions; the server uses the same shared Prisma capabilities as a Neon composition. Supabase-native Auth, Storage, and Realtime grants remain isolated in their own overlays.

RLS is not a substitute for Data API grants

When you add a new public object, keep common capabilities in its owning source and update the Supabase Data API deny overlay if required. Native client access must be an explicit, reviewed exception in a provider overlay. Fresh projects and resets must not depend on dashboard defaults.

Role	Expected surface
`anon`	No common business-table or business-function privileges.
`authenticated`	No common business-table or business-function privileges; authenticated product traffic uses the verified application actor through shared Prisma.
`service_role`	No portable business-runtime bypass. It remains confined to explicit Supabase-native Auth or Storage administration.

For every new function, revoke default EXECUTE and grant only the owning portable capability. Do not add browser Data API access as a shortcut around a shared Prisma repository.

Users & Auth

Multi-Tenancy

Billing & Credits

AI & Chat

CMS

Jobs System

profiles User Profiles

Application-owned profile data keyed by the canonical private.app_users identity. It is created or revalidated by the explicit server-side application bootstrap after provider verification, never as an auth.users business trigger side effect.

Field	Type	Description
`id`	uuid (PK)	References `private.app_users(id)`
`email`	text	Application profile email synchronized after provider verification
`full_name`	text	Full display name
`first_name`	text	First name (from onboarding)
`last_name`	text	Last name (from onboarding)
`birthday`	date	Optional birthday
`phone`	text	Optional phone number
`avatar_url`	text	Profile picture URL (OAuth or upload)
`is_admin`	boolean	Platform super-admin flag
`is_disabled`	boolean	Account disabled by admin
`onboarding_completed`	boolean	Has completed onboarding flow
`newsletter_subscribed`	boolean	Newsletter subscription status
`scheduled_deletion_at`	timestamptz	GDPR deletion scheduled date
`created_at`	timestamptz	Profile creation timestamp
`updated_at`	timestamptz	Last update timestamp

Auto-Admin Assignment

The shared administrator check promotes an active, enabled user when their provider-verified email matches both the application identity and app_settings.admin_email. Promotion and audit commit together.

app_settings Application Settings

Global key-value settings for the application.

Field	Type	Description
`key`	text (PK)	Setting key (e.g., 'admin_email')
`value`	text	Setting value
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update timestamp

accounts Accounts (Core)

The central entity of the system. All resources are attached to accounts, not users directly.

Field	Type	Description
`id`	uuid (PK)	Unique account identifier
`type`	enum	`'personal'` \| `'workspace'`
`name`	text	Account display name
`slug`	text (unique)	URL-friendly identifier (workspaces only)
`owner_user_id`	uuid (FK)	References `private.app_users(id)`
`credits_balance`	integer	Current credit balance
`max_members`	integer	Additional administrative member ceiling (null = commercial limit only)
`billing_email`	text	Provider-owned billing contact
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update timestamp

billing_customers Provider Customer Registry

Service-only mapping between an Account and each payment provider customer, isolated by test/live environment.

Field	Type	Description
`id`	uuid (PK)	Unique registry identifier
`account_id`	uuid (FK)	References `accounts(id)`; cascades on deletion
`provider`	enum	Stripe, Lemon Squeezy, or Paddle
`livemode`	boolean	Separates test and production identities
`external_customer_id`	text	Provider-owned customer identifier

roles Dynamic Roles

Role definitions with permissions. System roles (owner, admin, member) are protected.

Field	Type	Description
`id`	uuid (PK)	Unique role identifier
`name`	text	Display name (e.g., "Administrator")
`slug`	text (unique)	Identifier slug (e.g., "admin")
`description`	text	Role description
`permissions`	jsonb	JSONB array of permission strings (default `[]`)
`is_system`	boolean	Protected system role (cannot delete)
`color`	text	Badge color (e.g., "blue", "amber")
`icon`	text	Icon name (e.g., "crown", "shield")
`display_order`	integer	Sort order for display
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update timestamp

Available Permissions: account:update, account:delete, billing:view, billing:manage, members:view, members:invite, members:remove, members:update_role, api_keys:view, api_keys:create, api_keys:delete, ai:use. Roles store these as a JSONB array of strings; is_system roles (owner/admin/member) cannot be deleted.

memberships User-Account Relationships

Links users to accounts with their assigned role. Unique constraint: (account_id, user_id)

Field	Type	Description
`id`	uuid (PK)	Unique membership identifier
`account_id`	uuid (FK)	References `accounts(id)`
`user_id`	uuid (FK)	References `profiles(id)`
`role`	enum	Legacy: `'owner'` \| `'admin'` \| `'member'`
`role_slug`	text (FK)	References `roles(slug)` - dynamic roles
`created_at`	timestamptz	Membership creation
`updated_at`	timestamptz	Last update

invitations Workspace Invitations

Pending invitations to join a workspace. Expire after 7 days by default.

Field	Type	Description
`id`	uuid (PK)	Unique invitation identifier
`account_id`	uuid (FK)	References `accounts(id)`
`email`	text	Invitee email address
`role_slug`	text (FK)	Role to assign on acceptance
`invited_by`	uuid (FK)	References `profiles(id)`
`token`	text (unique)	Secure invitation token
`status`	enum	`'pending'` \| `'accepted'` \| `'expired'` \| `'revoked'`
`expires_at`	timestamptz	Expiration (default: 7 days)
`created_at`	timestamptz	Creation timestamp

subscriptions Provider-neutral Subscriptions

Stores the normalized current and historical subscription state for Stripe, Lemon Squeezy, Paddle, and internal free plans.

Field	Type	Description
`id`	uuid (PK)	Local subscription ID
`account_id`	uuid (FK)	References `accounts(id)`
`billing_customer_id`	uuid (FK, nullable)	Matching provider customer identity; null only for internal plans
`provider`	`billing_provider`	`internal`, `stripe`, `lemon_squeezy`, or `paddle`
`livemode`	boolean	Separates sandbox/test identities from production identities
`external_subscription_id`	text (nullable)	Provider subscription identifier; unique per provider and environment
`external_price_id`	text (nullable)	Validated provider catalogue binding snapshot
`plan_id`	text	Plan ID from config/pricing.ts
`status`	`billing_subscription_status`	Normalized lifecycle state shared by every provider
`provider_status`	text (nullable)	Sanitized provider-native state for service diagnostics
`currency`	text	Validated ISO 4217 currency
`billing_interval`	text	`monthly` or `yearly`
`current_period_start`	timestamptz	Billing period start
`current_period_end`	timestamptz	Billing period end
`trial_start` / `trial_end`	timestamptz	Normalized trial bounds
`cancel_at_period_end`	boolean	Will cancel at period end
`cancel_at`	timestamptz	Scheduled cancellation timestamp
`canceled_at`	timestamptz	Cancellation timestamp
`paused_at` / `resumed_at`	timestamptz	Pause/resume audit timestamps
`billing_hold`	boolean	Internal risk or debt hold, paired with a stable reason code
`superseded_at`	timestamptz	Null only for the account's unique current subscription
`last_provider_occurred_at`	timestamptz	Provider event ordering guard
`last_provider_event_id`	text	Deterministic event tie-breaker, service-only
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update timestamp

Plans are in Code, Not Database

The plan_id references plans defined in config/pricing.ts. Plan details (price, features, credits) are NOT stored in the database.

payments One-Time Payments

Provider-neutral financial proofs for subscriptions, credit packs, and licenses. Every row belongs to an Account; provider identifiers and financial details are service-only while members receive a restricted safe projection.

Field	Type	Description
`id`	uuid (PK)	Payment identifier
`account_id`	uuid (FK)	References `accounts(id)`
`subscription_id`	uuid (FK, nullable)	Required for `purchase_kind='subscription'`; references `subscriptions(id)`
`checkout_attempt_id`	uuid (FK, nullable)	Optional link to the initiating checkout attempt
`provider`	enum	`'internal'` \| `'stripe'` \| `'lemon_squeezy'` \| `'paddle'`
`livemode`	boolean	Separates test and live financial identities
`purchase_kind`	enum	`'subscription'` \| `'credit_pack'` \| `'license'`
`offer_id`	text	Stable configured plan, credit-pack, or license identifier
`status`	enum	`'pending'` \| `'paid'` \| `'failed'` \| `'partially_refunded'` \| `'refunded'` \| `'disputed'` \| `'reversed'`
`amount_cents`	bigint	Gross amount in minor currency units
`tax_amount_cents`	bigint	Tax included in the provider proof
`discount_amount_cents`	bigint	Discount reported by the provider
`refunded_amount_cents`	bigint	Cumulative refunded amount
`provider_fee_cents`	bigint (nullable)	Provider fee when reconciliation supplies it
`merchant_net_cents`	bigint (nullable)	Merchant net amount when reconciliation supplies it
`currency`	text	Uppercase ISO currency code
`external_checkout_id`	text (nullable)	Provider checkout identifier; unique per provider and mode
`external_transaction_id`	text (nullable)	Provider transaction or payment-intent identifier; unique per provider and mode
`external_invoice_id`	text (nullable)	Subscription invoice identifier; unique per provider and mode
`external_payment_id`	text (nullable)	Additional provider payment identifier; unique per provider and mode
`payment_proof_key`	text (unique)	Canonical test/live-scoped financial idempotency proof
`provider_period_start`	timestamptz (nullable)	Provider entitlement-period start
`provider_period_end`	timestamptz (nullable)	Provider entitlement-period end
`paid_at`	timestamptz (nullable)	Required for paid or post-payment states
`metadata`	jsonb	Bounded internal reconciliation and entitlement pointers
`created_at` / `updated_at`	timestamptz	Record lifecycle timestamps

licenses One-Time Licenses

Records of license purchases for one-time payment access (alternative to subscriptions).

Field	Type	Description
`id`	uuid (PK)	License identifier
`account_id`	uuid (FK)	References `accounts(id)` (ON DELETE CASCADE)
`product_id`	text	Product ID from `config/pricing.ts`
`license_type`	enum	`'lifetime'` \| `'yearly'` \| `'monthly'` \| `'custom'`
`status`	enum	`'active'` \| `'expired'` \| `'revoked'`
`payment_id`	uuid (unique FK)	Provider-neutral normalized payment. The composite `(payment_id, account_id)` foreign key keeps payment and license ownership aligned; amount, currency, provider, environment, and external transaction identity live on `payments`.
`purchased_by`	uuid (FK)	References `private.app_users(id)` — application actor who initiated the purchase
`starts_at`	timestamptz	License start date (default `now()`)
`expires_at`	timestamptz	Expiration date (NULL for lifetime)
`features`	jsonb	Feature flags snapshot at purchase (e.g., `{"api_access": true}`)
`credits_included`	integer	One-time credits granted on purchase
`credits_granted`	boolean	Whether the one-time credits have been added
`limits`	jsonb	Resource limits snapshot (e.g., `{"projects": 10}`)
`metadata`	jsonb	Additional license data
`created_at`	timestamptz	Purchase timestamp
`updated_at`	timestamptz	Last update timestamp

Billing Models

Configure via billingModel in config/app.ts: 'subscription' | 'one_time' | 'hybrid'. Hybrid mode allows both subscriptions and licenses.

credit_transactions Credit Ledger

Audit trail of all credit changes. Immutable.

Field	Type	Description
`id`	uuid (PK)	Transaction identifier
`account_id`	uuid (FK)	References `accounts(id)`
`amount`	integer	Change amount (+ = add, - = deduct)
`balance_after`	integer	Balance after transaction
`reason`	text	Human-readable reason
`source`	enum (`credit_source`)	`'subscription_refill'` \| `'one_time_purchase'` \| `'admin_adjustment'` \| `'ai_usage'` \| `'refund'` \| `'bonus'` \| `'license_purchase'` \| `'referral'`. Plus webhook audit-trail values used for zero-amount ledger rows: `'payment_method_added'`, `'payment_method_removed'`, `'payment_failed'`, `'dispute_created'`, `'dispute_closed'`, `'checkout_expired'`, `'async_payment_failed'`, `'trial_ending'`, `'payment_action_required'`.
`metadata`	jsonb	Additional transaction data
`created_at`	timestamptz	Transaction timestamp

chat_sessions Chat Sessions

Conversation sessions with optional agent assignment.

Field	Type	Description
`id`	uuid (PK)	Session identifier
`account_id`	uuid (FK)	References `accounts(id)`
`user_id`	uuid (FK)	References `profiles(id)`
`title`	text	Session title (auto-generated or user-defined)
`agent_id`	text	Default agent for this session (e.g., 'chat')
`created_at`	timestamptz	Session creation
`updated_at`	timestamptz	Last activity timestamp

chat_messages Chat Messages

Individual messages within a session.

Field	Type	Description
`id`	uuid (PK)	Message identifier
`session_id`	uuid (FK)	References `chat_sessions(id)`
`account_id`	uuid (FK)	References `accounts(id)`
`role`	enum	`'user'` \| `'assistant'` \| `'system'`
`content`	text	Message content
`tokens_used`	integer	Tokens consumed by this message
`agent_id`	text	Agent that generated response
`model_id`	text	Resolved LLM model used (e.g., 'gpt-5.6-terra')
`created_at`	timestamptz	Message timestamp

ai_requests AI Request Logs

Detailed logging of all AI API calls for observability and cost tracking.

Field	Type	Description
`id`	uuid (PK)	Request identifier
`account_id`	uuid (FK)	References `accounts(id)`
`user_id`	uuid (FK)	References `profiles(id)` (who made the request)
`agent_type`	text	Agent used (e.g., 'chat', 'code-assistant')
`model`	text	Resolved LLM model (e.g., 'gpt-5.6-luna')
`input_tokens`	integer	Input tokens consumed
`output_tokens`	integer	Output tokens generated
`total_tokens`	integer	Computed: input + output (generated)
`cost_cents`	numeric	Calculated cost in cents
`latency_ms`	integer	Response time in milliseconds
`status`	text	`'success'` \| `'error'`
`error_message`	text	Error details (if status is 'error')
`metadata`	jsonb	Additional request metadata
`created_at`	timestamptz	Request timestamp

cms_pages CMS Pages

Multi-locale content pages. All locales stored in JSONB fields per row.

Canonical CMS locale keys are the BCP 47 ids from i18n/config.ts (fr-FR, en-US, en-CA, fr-CH). The app still reads legacy language-only keys (fr, en) through localized helpers for backwards compatibility, while admin writes and schema seeds normalize content back to the configured BCP 47 keys.

Field	Type	Description
`id`	uuid (PK)	Page identifier
`slug`	text (unique)	URL slug (e.g., 'about', 'blog/my-post')
`title`	jsonb	`{"fr-FR": "...", "fr-CH": "...", "en-US": "...", "en-CA": "..."}`
`content`	jsonb	HTML/Markdown content per locale
`excerpt`	jsonb	Short description (for blog posts)
`featured_image`	text	Featured image URL
`category_id`	uuid (FK)	Blog category reference (ON DELETE SET NULL)
`published`	jsonb	Published status per locale
`seo_title`	jsonb	SEO title override
`seo_description`	jsonb	Meta description
`seo_noindex`	jsonb	Noindex flag per locale
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update

cms_blocks CMS Blocks

Reusable content snippets (promo bars, banners, etc.).

Field	Type	Description
`id`	uuid (PK)	Block identifier
`key`	text (unique)	Block key (e.g., 'promo_bar')
`content`	jsonb	Block content per locale
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update

blog_categories Blog Categories

Multi-locale blog categories with color coding and display ordering.

Field	Type	Description
`id`	uuid (PK)	Category identifier
`slug`	text (unique)	URL-safe identifier
`name`	jsonb	Multi-locale name `{"fr-FR": "...", "fr-CH": "...", "en-US": "...", "en-CA": "..."}`
`description`	jsonb	Multi-locale description
`color`	text	Badge color (slate, amber, blue, green, red, purple, pink, indigo, teal, orange)
`display_order`	integer	Sort order for display
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update

blog_tags Blog Tags

Multi-locale blog tags for content labeling.

Field	Type	Description
`id`	uuid (PK)	Tag identifier
`slug`	text (unique)	URL-safe identifier
`name`	jsonb	Multi-locale name `{"fr-FR": "...", "fr-CH": "...", "en-US": "...", "en-CA": "..."}`
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update

blog_post_tags Blog Post Tags (Join Table)

Many-to-many relationship between blog posts and tags.

Field	Type	Description
`id`	uuid (PK)	Row identifier
`page_id`	uuid (FK)	References cms_pages (ON DELETE CASCADE)
`tag_id`	uuid (FK)	References blog_tags (ON DELETE CASCADE)
`created_at`	timestamptz	Creation timestamp

jobs Job Definitions

Background job configurations for scheduled and manual tasks.

Field	Type	Description
`id`	uuid (PK)	Job identifier
`name`	text (unique)	Job name
`description`	text	Job description
`executor`	enum	`'edge'` \| `'internal'`
`function_name`	text	Handler function name
`cron_expression`	text	Cron schedule (e.g., '0 3 * * *')
`is_enabled`	boolean	Job enabled status
`notify_on_failure`	boolean	Email admin when job fails
`timeout_seconds`	integer	Execution timeout
`max_retries`	integer	Max retry attempts
`last_run_at`	timestamptz	Last execution time
`last_run_status`	enum	Last run result
`run_count`	integer	Total run count
`success_count`	integer	Successful runs
`failure_count`	integer	Failed runs
`config`	jsonb	Job-specific configuration

job_runs Job Execution History

Records of job executions with output and error logging.

Field	Type	Description
`id`	uuid (PK)	Run identifier
`job_id`	uuid (FK)	References `jobs(id)`
`status`	enum	`'pending'` \| `'running'` \| `'success'` \| `'failed'`
`triggered_by`	enum	`'cron'` \| `'manual'` \| `'webhook'` \| `'api'`
`started_at`	timestamptz	Execution start
`completed_at`	timestamptz	Execution end
`duration_ms`	integer	Execution duration
`output`	jsonb	Job output/result
`error`	text	Error message if failed

api_keys B2B API Keys

Hashed API keys for programmatic access.

Field	Type	Description
`id`	uuid (PK)	Key identifier
`account_id`	uuid (FK)	References `accounts(id)`
`name`	text	Key display name
`key_prefix`	text	Visible prefix (e.g., 'sk_live_abc...')
`key_hash`	text	Hashed key value
`scopes`	text[]	Permitted API scopes
`last_used_at`	timestamptz	Last usage timestamp
`expires_at`	timestamptz	Expiration date (optional)
`is_active`	boolean	Key active status

user_consents GDPR Consents

User consent records for GDPR compliance. Logged-in consent changes are written server-side via POST /api/user/consents so IP/user-agent capture, CSRF checks, rate limiting, and the append-only evidence capability stay out of browser code.

Field	Type	Description
`id`	uuid (PK)	Consent identifier
`user_id`	uuid (FK)	References `private.app_users(id)`
`type`	enum	`'marketing'` \| `'analytics'` \| `'necessary'`
`accepted`	boolean	Consent status
`ip_address`	inet	IP when consent given
`accepted_at`	timestamptz	Consent timestamp

account_deletion_requests GDPR Deletion Queue

Pending account deletion requests with 30-day grace period.

Field	Type	Description
`id`	uuid (PK)	Request identifier
`account_id`	uuid (FK)	References `accounts(id)`
`requested_by`	uuid (FK)	References `private.app_users(id)`
`status`	enum	`'pending'` \| `'processing'` \| `'completed'` \| `'cancelled'` \| `'failed'`
`scheduled_for`	timestamptz	Deletion date (default: now + 30 days)
`completed_at`	timestamptz	Actual deletion timestamp
`cascade_members`	boolean	When `true`, the deletion job also erases every member's application identity and asks the selected Auth adapter to delete each provider subject. It is set only by the personal-account deletion workflow for the owner's B2B cascade. A column-lock trigger keeps it immutable outside the dedicated deletion capability; a member or ordinary runtime request cannot force another member's deletion.
`processor_status`	jsonb	Per-processor and per-payment-resource deletion proof, including `mutation_started`, `provider_unknown`, and `verified` checkpoints.
`processor_errors`	jsonb	Redacted processor failures that blocked completion.
`error_message`	text	Short redacted summary when the request is marked `failed`.
`retry_count`	integer	Number of failed processor attempts.
`processing_lease_token`	uuid	Current worker fencing token; populated only while `status='processing'`.
`processing_lease_expires_at`	timestamptz	Lease deadline renewed by fenced checkpoints; an expired request may be reclaimed with a new token.

admin_logs Admin Audit Logs

Admin action audit trail for compliance and debugging.

Field	Type	Description
`id`	uuid (PK)	Log entry identifier
`admin_user_id`	uuid (FK)	Admin who performed action
`action`	text	Action performed (e.g., 'user.disable')
`target_type`	text	Entity type (e.g., 'user', 'subscription')
`target_id`	uuid	Affected entity ID
`details`	jsonb	Action details and changes
`ip_address`	inet	Admin IP address
`created_at`	timestamptz	Action timestamp

in_app_notifications In-App Notifications

User notifications with read status tracking. Column-lock trigger restricts user updates to the read column only.

Field	Type	Description
`id`	uuid (PK)	Notification identifier
`user_id`	uuid (FK)	References `profiles(id)`
`account_id`	uuid (FK)	NOT NULL — References `accounts(id)` (ON DELETE CASCADE)
`type`	enum	`'info'` \| `'success'` \| `'warning'` \| `'error'` \| `'system'`
`title`	text	Notification title (max 200 chars)
`message`	text	Notification body (max 1000 chars)
`link`	text	Optional relative URL for navigation
`read`	boolean	Read status (default: false)
`created_at`	timestamptz	Creation timestamp

changelog_entries Changelog Entries

Public changelog with multi-locale JSONB content. Supports versioning and type badges.

Field	Type	Description
`id`	uuid (PK)	Entry identifier
`version`	text	Version label (e.g., '1.2.0')
`type`	enum	`'feature'` \| `'improvement'` \| `'fix'` \| `'breaking'` \| `'security'`
`title`	jsonb	Multi-locale title (`{ "fr-FR": "...", "fr-CH": "...", "en-US": "...", "en-CA": "..." }`)
`content`	jsonb	Multi-locale markdown content
`published`	boolean	Publish status (global, not per-locale)
`published_at`	timestamptz	Publication date (for ordering)
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update timestamp

documents RAG Documents

Uploaded documents for RAG chat. Tracks processing status, file metadata, and chunk count.

Field	Type	Description
`id`	uuid (PK)	Document identifier
`account_id`	uuid (FK)	References `accounts(id)`
`user_id`	uuid (FK)	References `profiles(id)` (uploader)
`name`	text	Original filename
`storage_path`	text	Opaque UUID-based object key for the selected Storage adapter
`file_type`	text	File type
`file_size`	integer	File size in bytes
`status`	enum	`'pending'` \| `'processing'` \| `'ready'` \| `'error'`
`chunk_count`	integer	Number of generated chunks
`error_message`	text	Processing error details
`created_at`	timestamptz	Upload timestamp

document_chunks Document Chunks (Vector)

Text chunks with vector embeddings for similarity search. Uses pgvector vector(1536) with HNSW index. Embedding model is configurable in config/ai.ts (default: text-embedding-3-small).

Field	Type	Description
`id`	uuid (PK)	Chunk identifier
`document_id`	uuid (FK)	References `documents(id)` (CASCADE delete)
`content`	text	Chunk text content
`embedding`	vector(1536)	Embedding vector (model configurable via `embeddingConfig.defaultModel`)
`chunk_index`	integer	Position within document
`metadata`	jsonb	Chunk metadata (source, chunk index, total, `embedding_model`)
`created_at`	timestamptz	Creation timestamp

Database Functions

The schema includes several PostgreSQL functions for common operations.

Trigger Functions

Supabase Auth does not install an auth.users bootstrap trigger. Verified callbacks explicitly and idempotently finalize the provider principal into the canonical application identity before profile, Account, membership or consent work.

Function	Trigger	Description
`update_job_stats()`	`job_runs_update_stats`	Updates job statistics after each run completes.

Credit Functions

Function	Parameters	Description
`add_credits()`	`p_account_id uuid, p_amount integer, p_source credit_source, p_reason text default null, p_metadata jsonb default '{}'::jsonb`	Atomically adds credits to an account, writes the matching `credit_transactions` ledger row, and returns the new balance. Executable only by the bounded portable ledger capabilities; Data API roles have no access.
`decrement_credits()`	`p_account_id uuid, p_amount integer, p_reason text default null, p_metadata jsonb default '{}'::jsonb, p_source credit_source default 'ai_usage'`	Atomically deducts credits, writes the ledger row, and returns the new balance. Raises a `CHECK (credits_balance >= 0)` violation when the result would go negative — wrap calls in try/catch. Executable only by the bounded portable ledger capabilities.

These functions ensure atomic credit operations. Never update the credits_balance column directly; always use these RPC functions to maintain data integrity and the audit trail.

Utility Functions

Function	Returns	Description
`get_user_accounts(user_uuid)`	setof accounts	Returns all accounts a user belongs to
`is_account_member(account_uuid, user_uuid)`	boolean	Check if user belongs to an account
`get_user_role_slug(account_uuid, user_uuid)`	text	Get user's role in an account
`user_has_permission(account_id, permission)`	boolean	Check if current user has a specific permission
`user_belongs_to_account(account_id)`	boolean	RLS helper - check membership (security definer)
`user_is_account_admin(account_id)`	boolean	RLS helper - check if owner/admin (security definer)
`has_valid_license(p_account_id)`	boolean	True if the account has any active, non-expired license. `SECURITY INVOKER`, callable from authenticated context.
`get_active_license(p_account_id)`	setof licenses	Returns the current active license row (if any) for the account.
`mark_expired_licenses()`	integer	Batch-marks `expires_at < now()` licenses as `'expired'`. Called by the `check-license-expiration` daily job. Returns row count.
`grant_license_credits(p_license_id)`	integer	Grants the license's `credits_included` via `add_credits` (source `'license_purchase'`) and flips `credits_granted = true`. Idempotent on the boolean flag and restricted to the license worker capability.
`generate_referral_code(p_account_id, p_length, p_alphabet)`	text	Idempotent: returns the existing active code for the account, or generates a new one with collision-retry through the actor-scoped referral runtime.
`apply_referral_code(...)`	uuid	Attribution entry point — runs self-referral / same-owner / one-per-lifetime / IP rate-limit guards and inserts the `referrals` row. Its optional historical-payment reconciliation flag is enabled only by trusted cookie attribution, never by manual code application. Actor and worker entry points use separate portable capabilities.
`qualify_referral(p_referral_id, p_trigger, p_referrer_credits, p_referred_credits, p_qualifying_payment_id)`	void	Calls `add_credits` on both sides with `source='referral'`. Paid triggers require a positive, non-fully-refunded normalized payment belonging to the referred Account; subscription rewards additionally require `purchase_kind='subscription'`. Restricted to the referral worker capability.
`qualify_referral_for_account(p_referred_account_id, p_trigger, p_referrer_credits, p_referred_credits, p_qualifying_payment_id)`	uuid / null	Account-locked webhook entry point. Qualifies a pending referral with the exact payment and returns the referrer Account id for targeted cache invalidation. Restricted to the billing/referral worker capability.
`reverse_referral_for_payment(p_payment_id, p_reason)`	uuid / null	Reverses only the reward whose `qualifying_payment_id` matches the supplied payment, then returns the referrer Account id. Restricted to the billing/referral worker capability.
`reverse_referral(p_referral_id, p_reason)`	void	Per-side guarded `decrement_credits` — an insufficient balance on one side doesn't abort the other (details written to `metadata`). Restricted to the referral worker capability.
`purge_error_logs(p_retention_days)`	integer	Deletes `error_logs` rows older than the supplied retention window. Called by the daily `purge-error-logs` job through the isolated maintenance credential.
+ 12 affiliate RPCs	—	Application workflow, link creation, click recording, attribution, conversion ledger, reversal, hold-period maturation, and aggregate stats. Actor, public-attribution, worker and platform-admin operations use separate portable capabilities. See `.claude/rules/affiliates.md` for the full registry.

Entity Relationships

Provider principal (provider + issuer + subject) │ explicit identity resolution ▼ private.app_users ◄── 1:N ── private.auth_identities │ explicit application bootstrap ▼ profiles │ │ 1:N ▼ memberships ◄─── N:1 ───► accounts │ │ │ N:1 ├── 1:N ──► subscriptions ▼ ├── 1:N ──► payments roles ├── 1:N ──► licenses ├── 1:N ──► credit_transactions ├── 1:N ──► chat_sessions ──► chat_messages ├── 1:N ──► ai_requests ├── 1:N ──► documents ──► document_chunks ├── 1:N ──► api_keys ├── 1:N ──► invitations ├── 1:N ──► in_app_notifications ├── 1:N ──► referral_codes / referrals └── 1:N ──► affiliate_* (8 tables, see below)

Other domain tables grouped by ownership:

Standalone (global): app_settings, admin_logs, error_logs, pending_emails, jobs / job_runs / job_handlers.
User-scoped: user_consents, user_access_logs, push_subscriptions, notification_preferences, notification_log, account_deletion_requests.
CMS & content: cms_pages, cms_blocks, blog_categories, blog_tags, blog_post_tags, changelog_entries.
Referrals: referral_codes, referrals (account-scoped via the referrer).
Affiliates (8 tables): affiliate_tiers, affiliate_applications, affiliates, affiliate_links, affiliate_clicks (write-only attribution capability), affiliate_attributions, affiliate_conversions, affiliate_payouts. All account-scoped through the affiliate row.