AgentSkillsCN

supabase-schema-design

当您需要设计数据库表、列（包括固定值列）、关联关系，或向实时事件系统中添加数据表时，可使用此技能。

SKILL.md
--- frontmatter
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):

sql
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
filesname, sizefile_name, file_size
projectsnameproject_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.

sql
-- ✅ 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)

sql
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

sql
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

sql
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

AspectENUMTEXT + CHECK
TypeScript typesAuto-generated unionJust string
Frontend optionsSupabase_Enums<"...">Must hardcode
Type safetyFull compile-timeNone
sql
-- ✅ 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
sql
-- 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

typescript
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:

typescript
// 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
<!-- Last compacted: 2025-12-18 -->