Supabase — New Table Checklist

Contents

1. Before You Create the Table

Requirements for this table are clear and documented
Confirmed this table does not already exist under a different name
Confirmed this is not better handled by extending an existing table
Multi-tenant scope confirmed: is this workspace-scoped, user-scoped, or global?
Soft delete or hard delete decision made and documented
Who can access this table and in what ways: SELECT / INSERT / UPDATE / DELETE?

2. Table Schema

Required Columns — Every Table

Workspace-Scoped Tables — Multi-Tenant

User-Owned Records

Soft Delete — If Applicable

3. Naming Standards

Table name is plural snake_case: projects, task_items, workspace_members
Column names are singular snake_case: project_id, created_at, user_email
Foreign keys named: referenced_table_singular + _id: workspace_id, project_id, user_id
Boolean columns start with is_ or has_: is_active, is_verified, has_access
Timestamp columns end with _at: created_at, updated_at, deleted_at, verified_at
No reserved SQL keywords as column names

4. Constraints

Primary key set: id uuid PRIMARY KEY
NOT NULL on every required column
FOREIGN KEY constraints with appropriate ON DELETE behavior
UNIQUE constraints where needed: workspace_id + slug, user_id + workspace_id
CHECK constraints for enums or restricted values
CHECK constraints for numeric ranges
CHECK constraint: updated_at >= created_at if relevant
No nullable foreign keys unless intentionally optional

Example CHECK Constraint

CHECK (status IN ('active', 'inactive', 'archived'))
CHECK (percentage >= 0 AND percentage <= 100)

ON DELETE Decision Guide

5. Indexes

Primary key already indexed automatically
Foreign key columns indexed: workspace_id, project_id, user_id
Columns used in WHERE clauses indexed
Columns used in ORDER BY indexed
Soft delete: index on deleted_at if filtering by it
Composite index if filtering on multiple columns together
No over-indexing: only index what will be queried

Composite Index Example

CREATE INDEX ON projects (workspace_id, status);

6. RLS Setup

RLS enabled on table: ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
Default deny confirmed — no access unless explicitly granted by a policy
SELECT policy added
INSERT policy added
UPDATE policy added
DELETE policy added or intentionally blocked with no policy
Each policy uses auth.uid() — not a hardcoded value
Workspace-scoped policies verify workspace membership, not just workspace_id match
Policies tested as owner, admin, member, viewer, non-member, anonymous
Service role not relied on for normal operations

RLS Policy Patterns

-- SELECT: workspace members only
CREATE POLICY "workspace_members_select"
ON projects
FOR SELECT
USING (
  workspace_id IN (
    SELECT workspace_id
    FROM workspace_members
    WHERE user_id = auth.uid()
  )
);

-- INSERT: workspace members only, sets created_by automatically
CREATE POLICY "workspace_members_insert"
ON projects
FOR INSERT
WITH CHECK (
  workspace_id IN (
    SELECT workspace_id
    FROM workspace_members
    WHERE user_id = auth.uid()
  )
  AND created_by = auth.uid()
);

-- UPDATE: only creator or admin
CREATE POLICY "owner_or_admin_update"
ON projects
FOR UPDATE
USING (
  created_by = auth.uid()
  OR EXISTS (
    SELECT 1
    FROM workspace_members
    WHERE workspace_id = projects.workspace_id
      AND user_id = auth.uid()
      AND role = 'admin'
  )
);

-- DELETE: only admin
CREATE POLICY "admin_delete"
ON projects
FOR DELETE
USING (
  EXISTS (
    SELECT 1
    FROM workspace_members
    WHERE workspace_id = projects.workspace_id
      AND user_id = auth.uid()
      AND role = 'admin'
  )
);

7. Audit Fields

created_at is set automatically with DEFAULT now()
updated_at is updated automatically via trigger — not manually in application code
created_by is set from auth.uid() in RLS INSERT policy — not from request payload
If audit log table exists: insert log entry on significant changes

Auto-Update Trigger for updated_at

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

8. Migration File

Migration created in Supabase migrations folder or via Supabase CLI
Migration is reversible: includes both UP and DOWN migrations if needed
Migration name is descriptive: 20240115_create_projects_table.sql
Migration tested on local/staging before applying to production
No data mutations in schema migrations — keep schema and data migrations separate
Migration does not depend on data that may not exist

9. Documentation

Table purpose documented — what it stores, what entity it represents
Column list documented — all columns, types, nullable, purpose
Foreign key relationships documented
RLS access rules documented — who can do what
Frontend team notified of new table and how to access it
API documentation written if a new API endpoint was created for this table

Before Marking Done

10. New Table Checklist