add-database-table

Access: /template add-database-table or /t add-database-table

Creates database tables with proper structure, indexes, and Row Level Security (RLS) policies following MakerKit's multi-tenant architecture patterns.

What It Does

The add-database-table command helps you create:

• Database tables with appropriate columns and constraints
• Row Level Security policies using MakerKit's helper functions
• Foreign key relationships to existing tables
• Performance indexes
• Database migrations
• TypeScript type generation

Usage

/template add-database-table "Description"
# or
/t add-database-table "Description"

When prompted, provide:

• Table name and purpose
• Column definitions
• Relationships to other tables
• Access control requirements
• Index requirements

Prerequisites

• A MakerKit project with Supabase configured
• Understanding of your data model
• Commercial MakerKit license from MakerKit

What Gets Created

1. Migration File

Located in apps/web/supabase/migrations/:

• Table creation SQL
• RLS policy definitions
• Index creation
• Foreign key constraints

2. RLS Policies

Using MakerKit's helper functions:

• has_role_on_account() - Check user's role in an account
• has_permission() - Check specific permissions
• is_account_owner() - Verify account ownership
• is_team_member() - Check team membership

3. TypeScript Types

Automatically generated after migration

Example

Creating a "documents" table for team collaboration:

/template add-database-table "Description"
# or
/t add-database-table "Description"

This creates:

-- Create table
create table public.documents (
  id uuid default gen_random_uuid() primary key,
  account_id uuid not null references public.accounts(id) on delete cascade,
  title text not null,
  content text,
  status text not null default 'draft',
  created_by uuid references auth.users(id),
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Enable RLS
alter table public.documents enable row level security;

-- Create policies
create policy "Team members can view documents"
  on public.documents for select
  to authenticated
  using (
    public.has_role_on_account(account_id)
  );

create policy "Can create documents with permission"
  on public.documents for insert
  to authenticated
  with check (
    public.has_permission(auth.uid(), account_id, 'documents.write'::app_permissions)
  );

create policy "Can update own documents"
  on public.documents for update
  to authenticated
  using (
    created_by = auth.uid() OR
    public.has_permission(auth.uid(), account_id, 'documents.write'::app_permissions)
  );

-- Create indexes
create index idx_documents_account_id on public.documents(account_id);
create index idx_documents_created_at on public.documents(created_at desc);
create index idx_documents_status on public.documents(status) where status != 'archived';

MakerKit Table Patterns

Common Columns

Most tables include:

id uuid default gen_random_uuid() primary key,
account_id uuid not null references public.accounts(id) on delete cascade,
created_at timestamptz default now(),
updated_at timestamptz default now()

Team-Scoped Tables

For features scoped to teams:

account_id uuid not null references public.accounts(id) on delete cascade

User-Scoped Tables

For personal features:

user_id uuid not null references auth.users(id) on delete cascade

Audit Columns

For tracking changes:

created_by uuid references auth.users(id),
updated_by uuid references auth.users(id),
deleted_at timestamptz -- for soft deletes

RLS Policy Patterns

Read Access

-- All team members can read
using (public.has_role_on_account(account_id))

-- Only specific role
using (public.has_role_on_account(account_id, 'admin'::account_role))

-- With specific permission
using (public.has_permission(auth.uid(), account_id, 'resource.read'::app_permissions))

Write Access

-- Insert with permission check
with check (
  public.has_permission(auth.uid(), account_id, 'resource.write'::app_permissions)
)

-- Update own records
using (
  created_by = auth.uid() OR
  public.has_role_on_account(account_id, 'admin'::account_role)
)

Delete Access

-- Soft delete pattern
using (
  public.has_permission(auth.uid(), account_id, 'resource.delete'::app_permissions)
)

Index Strategies

Common Indexes

-- Foreign key index
create index idx_table_account_id on table(account_id);

-- Timestamp for sorting
create index idx_table_created_at on table(created_at desc);

-- Status filtering
create index idx_table_status on table(status) where status = 'active';

-- Composite for common queries
create index idx_table_account_status on table(account_id, status);

Post-Creation Steps

1. Run the migration:

   pnpm db:migrate

2. Generate TypeScript types:

   pnpm db:typegen

3. Update seed data (if needed):

   pnpm db:seed

4. Test RLS policies:

   • Create test users with different roles
   • Verify access controls work as expected

Advanced Patterns

Hierarchical Data

-- Self-referencing for tree structures
parent_id uuid references public.table(id) on delete cascade,
path ltree, -- PostgreSQL extension for hierarchies

Full-Text Search

-- Add search vector
search_vector tsvector generated always as (
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
) stored;

-- Create GIN index
create index idx_table_search on table using gin(search_vector);

JSON Data

-- JSONB column with constraints
metadata jsonb default '{}' not null,
settings jsonb default '{}' check (jsonb_typeof(settings) = 'object'),

-- Index specific JSON fields
create index idx_table_metadata_type on table((metadata->>'type'));

Common Permissions

Add new permissions to the enum:

alter type app_permissions add value 'documents.read';
alter type app_permissions add value 'documents.write';
alter type app_permissions add value 'documents.delete';

Then grant to roles:

insert into public.role_permissions (role, permission) values
  ('owner', 'documents.write'),
  ('owner', 'documents.delete'),
  ('admin', 'documents.write'),
  ('member', 'documents.read');

License Requirement

Important: This command requires a commercial MakerKit license from https://makerkit.dev?atp=MqaGgc MakerKit is a premium SaaS starter kit and requires proper licensing for commercial use.