cwinvestments/memstack-development-database-architect

🗄️ Database Architect — Designing schema, relationships, and policies...

Produces production-ready Supabase/Postgres schemas with proper naming, relationships, RLS policies, indexes, and migration SQL.

Activation

When this skill activates, output:

🗄️ Database Architect — Analyzing requirements and designing schema...

Then execute the protocol below.

| Context | Status | |---------|--------| | User says "design database" or "create tables" or "add table" | ACTIVE | | User says "database schema" or "ERD" or "data model" | ACTIVE | | Building a new feature that needs persistent storage | ACTIVE | | Writing migration SQL for Supabase | ACTIVE | | Querying existing data or debugging SQL | DORMANT | | Discussing database concepts generally | DORMANT |

Anti-patterns

| Trap | Reality Check | |------|---------------| | "I'll add RLS later" | RLS is the auth layer in Supabase. No RLS = public data. Design it with the schema. | | "JSONB for everything" | JSONB skips type safety, indexing, and foreign keys. Use it for truly flexible data, not as a schema-avoidance crutch. | | "I'll index when it's slow" | Known query patterns should get indexes from day one. Adding indexes to production tables locks writes. | | "Free text for status" | status TEXT invites typos and inconsistency. Use an enum: CREATE TYPE order_status AS ENUM (...). | | "One big table is simpler" | Wide tables with nullable columns signal missing normalization. Split into focused tables with proper FKs. | | "snake_case is just preference" | Supabase auto-generates TypeScript types from your schema. Inconsistent naming produces inconsistent types. Pick snake_case and enforce it. |

Protocol

Step 1: Gather Requirements

Before designing, understand what the data needs to represent:

1. What entities exist? (users, orders, products, organizations, etc.)
2. What are the relationships? (one-to-many, many-to-many, self-referential)
3. Who accesses what? (public data, org-scoped, user-private, admin-only)
4. What are the query patterns? (list by org, search by name, filter by date range)
5. What status flows exist? (draft → published, pending → approved → rejected)

If the user hasn't specified, ask:

What entities and relationships does this feature need? Who should be able to read/write each table?

Step 2: Design Table Structure

Apply naming conventions strictly:

| Convention | Rule | Example | |------------|------|---------| | Table names | snake_case, plural | order_items, user_profiles | | Column names | snake_case | first_name, created_at | | Foreign keys | {singular_table}_id | user_id, organization_id | | Primary keys | id (uuid) | id UUID DEFAULT gen_random_uuid() | | Junction tables | {table1}_{table2} alphabetical | organizations_users | | Enums | {table}_{column} or descriptive | order_status, subscription_tier | | Indexes | idx_{table}_{columns} | idx_orders_org_id_created_at |

Standard columns on every table:

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

Auto-update updated_at:

-- Create this function once per database
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to each table
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON table_name
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Step 3: Define Relationships

Design foreign keys with explicit constraint naming and cascading behavior:

-- One-to-many: organization has many projects
ALTER TABLE projects
  ADD CONSTRAINT fk_projects_organization
  FOREIGN KEY (organization_id) REFERENCES organizations(id)
  ON DELETE CASCADE;

-- Many-to-many: users belong to many organizations
CREATE TABLE organizations_users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (organization_id, user_id)
);

Cascade rules:

| Relationship | ON DELETE | Rationale | |-------------|-----------|-----------| | Org → Projects | CASCADE | Deleting org removes its projects | | User → Profile | CASCADE | Deleting user removes profile | | Order → Items | CASCADE | Deleting order removes line items | | User → Orders | RESTRICT | Don't delete user if they have orders | | Category → Products | SET NULL | Keep products, clear category |

Step 4: Design RLS Policies

Every table needs RLS enabled with explicit policies. No exceptions.

-- Enable RLS (do this for EVERY table)
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Common RLS patterns:

-- Pattern 1: User owns their own rows
CREATE POLICY "Users read own data"
  ON user_profiles FOR SELECT
  USING (auth.uid() = user_id);

-- Pattern 2: Org-scoped access (most common in multi-tenant apps)
CREATE POLICY "Org members read projects"
  ON projects FOR SELECT
  USING (
    organization_id IN (
      SELECT organization_id FROM organizations_users
      WHERE user_id = auth.uid()
    )
  );

-- Pattern 3: Role-based write access
CREATE POLICY "Org admins write projects"
  ON projects FOR INSERT
  WITH CHECK (
    organization_id IN (
      SELECT organization_id FROM organizations_users
      WHERE user_id = auth.uid()
      AND role IN ('owner', 'admin')
    )
  );

-- Pattern 4: Public read, authenticated write
CREATE POLICY "Public read"
  ON articles FOR SELECT
  USING (published = true);

CREATE POLICY "Authors write own articles"
  ON articles FOR UPDATE
  USING (author_id = auth.uid());

-- Pattern 5: Service role bypass (for server-side operations)
-- Use supabase.rpc() or service_role key — RLS is bypassed with service role

RLS checklist per table:

| Operation | Policy | Who Can? | |-----------|--------|----------| | SELECT | Read own / org-scoped / public | Define per table | | INSERT | Org members / authenticated / admin-only | Define per table | | UPDATE | Owner / admin / role-based | Define per table | | DELETE | Owner / admin / restrict | Define per table |

Step 5: Create Indexes

Design indexes based on known query patterns:

-- Foreign keys used in JOINs (always index these)
CREATE INDEX idx_projects_organization_id ON projects(organization_id);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index for common filter + sort
CREATE INDEX idx_orders_org_created ON orders(organization_id, created_at DESC);

-- Partial index for active records (skip soft-deleted)
CREATE INDEX idx_projects_active ON projects(organization_id)
  WHERE deleted_at IS NULL;

-- GIN index for JSONB columns (if used)
CREATE INDEX idx_settings_metadata ON settings USING GIN(metadata);

-- Full-text search index
CREATE INDEX idx_articles_search ON articles USING GIN(
  to_tsvector('english', title || ' ' || content)
);

Index decision matrix:

| Query Pattern | Index Type | Example | |--------------|-----------|---------| | WHERE fk_id = ? | B-tree (single column) | idx_orders_user_id | | WHERE fk_id = ? ORDER BY created_at | B-tree (composite) | idx_orders_user_created | | WHERE status = 'active' | Partial index | WHERE status = 'active' | | WHERE metadata @> '{}' | GIN | JSONB containment queries | | WHERE to_tsvector(...) @@ ? | GIN | Full-text search | | WHERE col LIKE 'prefix%' | B-tree (text_pattern_ops) | Prefix search |

Don't index:

• Boolean columns with low cardinality (Postgres won't use the index)
• Columns that are rarely queried
• Tables with < 1000 rows (sequential scan is faster)

Step 6: Use JSONB Appropriately

JSONB is powerful but should be used intentionally:

✅ Good use cases:

• User preferences/settings (schema varies per user)
• External API response caching (shape is unstable)
• Metadata that varies by record type (e.g., metadata on polymorphic tables)
• Audit log payloads (recording what changed)

❌ Bad use cases:

• Core business fields (use typed columns instead)
• Fields you query frequently (use dedicated columns + indexes)
• Replacing relationships (use junction tables)
• Fields that need foreign key constraints (JSONB can't reference other tables)

-- Good: flexible metadata alongside typed columns
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  category_id UUID REFERENCES categories(id),
  metadata JSONB DEFAULT '{}'::jsonb,    -- flexible extras
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Step 7: Define Enums for Status Fields

-- Create enum types for finite status sets
CREATE TYPE order_status AS ENUM (
  'draft', 'pending', 'confirmed', 'processing',
  'shipped', 'delivered', 'cancelled', 'refunded'
);

CREATE TYPE subscription_tier AS ENUM (
  'free', 'starter', 'professional', 'enterprise'
);

-- Use in table definition
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status order_status NOT NULL DEFAULT 'draft',
  -- ...
);

Enum vs CHECK constraint vs lookup table:

| Approach | When to Use | Trade-off | |----------|------------|-----------| | CREATE TYPE ... AS ENUM | Fixed set, rarely changes | Adding values requires ALTER TYPE | | CHECK (col IN (...)) | Very short lists (2-3 values) | Inline, no separate type | | Lookup table | Values change often or have metadata | More flexible, extra JOIN |

Adding values to an existing enum:

ALTER TYPE order_status ADD VALUE 'on_hold' AFTER 'processing';
-- Note: you cannot remove or reorder enum values without recreating the type

Step 8: Generate Migration SQL

Produce a complete, ordered migration file:

-- Migration: 001_create_[feature]_tables.sql
-- Description: [what this migration creates]
-- Date: YYYY-MM-DD

BEGIN;

-- 1. Create enum types
CREATE TYPE ...;

-- 2. Create tables (parent tables first, then children)
CREATE TABLE ...;

-- 3. Add foreign key constraints
ALTER TABLE ...;

-- 4. Create indexes
CREATE INDEX ...;

-- 5. Enable RLS on all tables
ALTER TABLE ... ENABLE ROW LEVEL SECURITY;

-- 6. Create RLS policies
CREATE POLICY ...;

-- 7. Create triggers (updated_at)
CREATE TRIGGER ...;

-- 8. Insert seed data (if needed)
INSERT INTO ...;

COMMIT;

Migration rules:

• Wrap in BEGIN/COMMIT for atomicity
• Create parent tables before children (FK dependency order)
• Always include a rollback script:

-- Rollback: 001_create_[feature]_tables.sql
BEGIN;
DROP TABLE IF EXISTS child_table CASCADE;
DROP TABLE IF EXISTS parent_table CASCADE;
DROP TYPE IF EXISTS custom_enum;
COMMIT;

Step 9: Document Environment Variables

If new connection strings or configuration are needed, output .env.example entries:

# Database
DATABASE_URL=postgresql://user:pass@host:5432/dbname
DIRECT_URL=postgresql://user:pass@host:5432/dbname  # For migrations (bypasses pooler)

# Supabase
NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_ROLE_KEY=eyJ...    # Server-side only, never expose to client

Remind user: Add to .env.local with real values. Never commit secrets.

Step 10: Output Summary

Present the complete design:

🗄️ Database Architect — Schema Complete

Feature: [name]
Tables: [count] new, [count] modified
Enums: [list]
Relationships: [count] foreign keys, [count] junction tables

ERD Summary:
  organizations ──< projects ──< tasks
       │                          │
       └──< organizations_users   └──< task_comments
                  │
              auth.users

RLS Policies: [count] policies across [count] tables
Indexes: [count] new indexes
Migration: [filename] ([line count] lines)

Files generated:
  - supabase/migrations/001_create_[feature].sql
  - supabase/migrations/001_create_[feature]_rollback.sql

Next steps:
  1. Review migration SQL
  2. Run: supabase db push (or supabase migration up)
  3. Verify RLS policies in Supabase dashboard → Auth → Policies
  4. Test queries with different user roles

Level History

• Lv.1 — Base: Requirements gathering, naming conventions, standard columns, relationship design, RLS policies, index strategy, JSONB guidance, enum patterns, migration generation, environment documentation. Based on Supabase patterns across AdminStack (40+ tables), EpsteinScan, DeedStack, and GreenAcres. (Origin: MemStack Pro v3.2, Mar 2026)