supabase-schema-design

name	supabase-schema-design
description	Use when designing database tables, columns (including fixed-value columns), relationships, or adding tables to the realtime events system

Supabase: Schema Design

Guidelines for database tables, columns, and relationships with custom ID generation, naming conventions, and multi-tenancy patterns.

Custom ID Generation

Always use generate_id('prefix') for primary keys (not UUID):

id TEXT PRIMARY KEY DEFAULT generate_id('prj')  -- ✅ Custom ID
id UUID PRIMARY KEY DEFAULT gen_random_uuid()   -- ❌ Never UUID

Common prefixes: org, prj, mem, wel, rp, pfl

Naming Conventions

All identifiers: snake_case

Avoid redundant prefixes: Table name already describes context

Table	✅ Good	❌ Bad
files	name, size	file_name, file_size
projects	name	project_name

Exception: Foreign keys keep full context: organization_id, project_id

Index naming: idx_{table}_{column} (full names, supabase db lint to verify)

Foreign Key Relationships

Use inline REFERENCES with ON DELETE CASCADE - PostgreSQL auto-generates constraint names.

-- ✅ Correct - inline (auto-generates {table}_{column}_fkey)
organization_id TEXT NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE

-- ❌ Wrong - duplicate constraints
organization_id TEXT NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
CONSTRAINT fk_projects_org FOREIGN KEY (organization_id)
    REFERENCES public.organizations(id) ON DELETE CASCADE  -- Creates duplicate!

DELETE behaviors: CASCADE (most common), SET NULL (optional refs), RESTRICT (rarely)

Junction Tables (Many-to-Many)

Naming: rel__[table1]__[table2] (double underscore delimiters, alphabetical order)

CREATE TABLE public.rel__files__tags (
    file_id TEXT NOT NULL REFERENCES public.files(id) ON DELETE CASCADE,
    tag_id TEXT NOT NULL REFERENCES public.file_tags(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (file_id, tag_id)
);

CREATE INDEX idx_rel__files__tags_file_id ON public.rel__files__tags(file_id);
CREATE INDEX idx_rel__files__tags_tag_id ON public.rel__files__tags(tag_id);

Requirements: Composite PK, CASCADE on both FKs, index each FK, include created_at

Standard Columns Pattern

CREATE TABLE public.projects (
    id TEXT PRIMARY KEY DEFAULT generate_id('prj'),
    organization_id TEXT NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_projects_organization_id ON public.projects(organization_id);

Required: id, organization_id (multi-tenant), created_at, updated_at

Enum Naming Convention

Pattern: [table]_[column]_enum

CREATE TYPE files_category_enum AS ENUM ('character', 'location', 'camera');
ALTER TABLE public.files ADD COLUMN category files_category_enum;

Fixed Value Columns: ALWAYS Use ENUM

Aspect	ENUM	TEXT + CHECK
TypeScript types	Auto-generated union	Just string
Frontend options	Supabase_Enums<"...">	Must hardcode
Type safety	Full compile-time	None

-- ✅ Correct: ENUM
CREATE TYPE scene_file_keyframes_interpolation_mode_enum AS ENUM ('hold', 'linear');
ALTER TABLE public.scene_file_keyframes
ADD COLUMN interpolation_mode scene_file_keyframes_interpolation_mode_enum NOT NULL DEFAULT 'linear';

-- ❌ Wrong: TEXT + CHECK (no TypeScript safety)
ADD COLUMN interpolation_mode TEXT CHECK (interpolation_mode IN ('hold', 'linear'));

After creating ENUM: Create options file per frontend-typed-enum-options skill.

Realtime Events for Org-Scoped Tables

1. Create your table (standard migration)
2. Update get_organization_id_for_change() function to handle your table
3. Attach trigger

-- In get_organization_id_for_change() CASE statement:
WHEN 'my_new_table' THEN
    org_id := record_data->>'organization_id';
    -- Or lookup via parent: SELECT p.organization_id INTO org_id FROM parent p WHERE p.id = ...

-- Attach trigger
CREATE TRIGGER trigger_notify_org_change
    AFTER INSERT OR UPDATE OR DELETE ON public.my_new_table
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_organization_of_table_change();

Frontend: Automatic query invalidation - no changes needed.

Type Generation Reference

type Project = Supabase_Tables<"projects">["Row"];
type ProjectInsert = Supabase_Tables<"projects">["Insert"];
type ProjectUpdate = Supabase_Tables<"projects">["Update"];

Custom Types for JSONB Columns

JSONB columns generate as Json type. Use MergeDeep from type-fest for type safety:

// src/types/scene-files.types.ts
export type SceneFiles_Data = {
    transform: { position: [number, number, number]; rotation: [...]; scale: [...] };
};

// src/types/database.override.types.ts
import type { MergeDeep } from "type-fest";
import type { Database } from "./database.types";
import type { SceneFiles_Data } from "./scene-files.types";

type DatabaseOverrides = {
    public: { Tables: { scene_files: {
        Row: { data: SceneFiles_Data };
        Insert: { data?: SceneFiles_Data };
        Update: { data?: SceneFiles_Data };
    }}}
};

export type DatabaseWithCustomTypes = MergeDeep<Database, DatabaseOverrides>;

// src/configs/supabase/config.ts
export const supabase = createClient<DatabaseWithCustomTypes>(...);

Requirements: pnpm add -D type-fest, strictNullChecks: true

See Also

• supabase-common-workflows - Workflow recipes
• frontend-realtime-sync - Comprehensive realtime patterns
• frontend-typed-enum-options - Frontend enum options