-
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
npm run initand provide your PostgreSQL connection URL, or manually run the SQL files in the SQL Editor.
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: reads are scoped through memberships, and writes that include a user attribution column also require user_id = auth.uid() unless the write is service-role-only. This prevents one workspace member from forging another member's chat message or document ownership. Sensitive profile identity/display fields are persisted through server-side routes with the service role rather than direct browser writes.
Data API Grants
Supabase Data API access is grant-explicit in this boilerplate. RLS decides which rows a role can see or mutate, but Postgres grants decide whether anon, authenticated, or service_role can reach a table, view, sequence, or RPC at all. The migration 20260527225001_explicit_data_api_grants.sql resets legacy/default privileges, revokes broad access, and then grants only the intended Data API surface.
RLS is not a substitute for Data API grants
When you add a new exposed public object, include the matching GRANT or REVOKE statements in the migration and mirror them into supabase/schema.sql or supabase/cms-schema.sql. Fresh Supabase projects and database resets should not depend on dashboard defaults.
| Role | Expected surface |
|---|---|
anon | Public read-only objects such as roles, affiliate tiers, changelog entries, CMS pages/blocks, and blog taxonomy. |
authenticated | User/admin Data API paths that are still bounded by RLS policies, including account-scoped dashboard, chat, documents, jobs, invitations, consent, CMS admin, and related reads/writes. |
service_role | Backend-only full table access, service queues, audit/log tables, document chunks, and privileged RPC paths. The key stays server-only. |
For new RPCs, revoke EXECUTE from public, anon, and authenticated unless browser clients are explicitly meant to call the function. For Data API inserts that use identity or serial columns, grant sequence privileges intentionally rather than relying on defaults.
Users & Auth
Multi-Tenancy
Billing & Credits
AI & Chat
CMS
Jobs System
profiles User Profiles
Extends auth.users with additional profile data. Auto-created on signup via trigger.
| Field | Type | Description |
|---|---|---|
id | uuid (PK) | References auth.users(id) |
email | text | User email (synced from auth.users) |
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
When a user signs up with an email matching app_settings.admin_email, the is_admin flag is automatically set to true.
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 auth.users(id) |
credits_balance | integer | Current credit balance |
max_members | integer | Member limit (null = unlimited) |
stripe_customer_id | text (unique) | Stripe customer ID |
created_at | timestamptz | Creation timestamp |
updated_at | timestamptz | Last update timestamp |
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 Stripe Subscriptions
Mirrors Stripe subscription data. Synced via webhooks.
| Field | Type | Description |
|---|---|---|
id | uuid (PK) | Local subscription ID |
account_id | uuid (FK) | References accounts(id) |
stripe_subscription_id | text (unique) | Stripe subscription ID (sub_xxx) |
stripe_price_id | text | Stripe price ID (price_xxx) |
plan_id | text | Plan ID from config/pricing.ts |
status | enum | 'active' | 'canceled' | 'past_due' | etc. |
current_period_start | timestamptz | Billing period start |
current_period_end | timestamptz | Billing period end |
cancel_at_period_end | boolean | Will cancel at period end |
canceled_at | timestamptz | Cancellation timestamp |
trial_end | timestamptz | Stripe-mirrored trial end timestamp; partial index subscriptions_trial_end_idx covers the trialing-status hot path |
paused_at | timestamptz | Set on customer.subscription.paused by applyPaused(). Audit only. Required by the migration shipped 2026-05-06 — without these columns the pause/resume webhook handlers fail silently |
resumed_at | timestamptz | Set on customer.subscription.resumed by applyResumed() |
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
Records of one-time purchases (credit packs, products, etc.).
| Field | Type | Description |
|---|---|---|
id | uuid (PK) | Payment identifier |
account_id | uuid (FK) | References accounts(id) |
user_id | uuid (FK) | User who made the payment |
amount | integer | Amount in cents |
currency | text | Currency code (EUR, USD, GBP, CAD, CHF) |
type | enum | 'credit_pack' | 'product' | 'service' | 'donation' | 'other' (default) | 'license' |
status | enum | 'pending' | 'completed' | 'failed' | 'refunded' |
description | text | Payment description |
stripe_session_id | text (unique) | Stripe checkout session ID |
stripe_payment_intent_id | text | Stripe payment intent ID |
stripe_invoice_id | text | Stripe invoice ID |
metadata | jsonb | Additional payment data |
created_at | timestamptz | Payment timestamp |
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' |
stripe_session_id | text (unique) | Stripe checkout session id — idempotency gate on webhook replay |
stripe_payment_intent_id | text | Stripe payment intent reference |
amount_paid | integer | Amount in cents |
currency | text | Currency code (default 'EUR') |
purchased_by | uuid (FK) | References auth.users(id) — user 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' | 'license' | '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 | LLM model used (e.g., 'gpt-4o') |
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 | LLM model (e.g., 'gpt-4o-mini') |
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 service-role writes stay out of browser code.
| Field | Type | Description |
|---|---|---|
id | uuid (PK) | Consent identifier |
user_id | uuid (FK) | References profiles(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 profiles(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 deletes every member's auth.users row. Set only by /my-account when a workspace request is part of the owner's personal-account cascade in B2B mode — see "B2B Member Cascade" below. A BEFORE UPDATE column-lock trigger makes this column (and every other field except status) immutable to non-service_role callers, and the owner-cancel UPDATE policy carries a matching WITH CHECK; a member or owner cannot flip cascade_members via the Data API to force member-account deletion. |
processor_status | jsonb | Per-processor deletion proof for Stripe, newsletter/email, Storage, Supabase Auth, and DB cascade steps. |
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. |
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 | Supabase Storage path (UUID-based) |
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
| Function | Trigger | Description |
|---|---|---|
handle_new_user() |
on_auth_user_created |
Creates profile on signup. Sets is_admin=true if email matches admin setting. |
handle_new_user_account() |
on_auth_user_created_account |
Creates personal account and owner membership on signup. |
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. service-role only (REVOKE EXECUTE FROM authenticated, anon). |
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. service-role only. |
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) |
sync_missing_profiles() |
void | Creates profiles for auth.users without one |
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. service-role only. |
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. service-role only. |
apply_referral_code(...) |
uuid | Attribution entry point — runs self-referral / same-owner / one-per-lifetime / IP rate-limit guards; inserts the referrals row. service-role only. See .claude/rules/referrals.md. |
qualify_referral(p_referral_id, p_trigger, p_referrer_credits, p_referred_credits) |
void | Calls add_credits on both sides with source='referral'. service-role only. |
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). service-role only. |
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. service-role only. |
| + 12 affiliate RPCs | — | Application workflow, link creation, click recording, attribution, conversion ledger, reversal, hold-period maturation, and aggregate stats. All service-role only. See .claude/rules/affiliates.md for the full registry. |
Entity Relationships
auth.users (Supabase Auth)
│
│ 1:1 (on signup trigger)
▼
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(service-role only),affiliate_attributions,affiliate_conversions,affiliate_payouts. All account-scoped through the affiliate row.