Database Schema Designer

What This Skill Does

• The Database Schema Designer skill designs relational database schemas from requirements and generates migrations and types.
• It handles multi-tenancy, soft deletes, audit trails, versioning, and polymorphic associations.
• This skill is useful for backend engineers and data architects.

When to Use

• Designing a new feature that needs database tables.
• Reviewing a schema for performance issues.
• Adding multi-tenancy to an existing schema.
• Generating Type.
• Script types from a Prisma schema.
• Planning a schema migration for a breaking change.
• Generating seed data.

Key Features

• Normalizes requirements into tables and relationships.
• Generates migrations for Drizzle, Prisma, Type.
• ORM, Alembic.
• Generates Type.
• Script interfaces and Python dataclasses.
• Creates Row-Level Security (RLS) policies.
• Suggests composite, partial, and covering indexes.
• Generates Mermaid diagram from schema.

Manual Installation

Database Schema Designer

Tier: POWERFUL
Category: Engineering
Domain: Data Architecture / Backend

Overview

• Design relational database schemas from requirements and generate migrations, TypeScript/Python types, seed data, RLS policies, and indexes.
• Handles multi-tenancy, soft deletes, audit trails, versioning, and polymorphic associations.

Core Capabilities

Schema design — normalize requirements into tables, relationships, constraints Migration generation — Drizzle, Prisma, TypeORM, Alembic Type generation — TypeScript interfaces, Python dataclasses/Pydantic models RLS policies — Row-Level Security for multi-tenant apps Index strategy — composite indexes, partial indexes, covering indexes Seed data — realistic test data generation ERD generation — Mermaid diagram from schema

Designing a new feature that needs database tables Reviewing a schema for performance or normalization issues Adding multi-tenancy to an existing schema Generating TypeScript types from a Prisma schema Planning a schema migration for a breaking change

Schema Design Process

Step 1: Requirements → Entities

Given requirements:

• "Users can create projects.

• Each project has tasks.
• Tasks can have labels.
• Tasks can be assigned to users.
• We need a full audit trail.
• ".

Extract entities:

• User, Project, Task, Label, Task.
• Label (junction), Task.
• Assignment, Audit.
• Log.

Step 2: Identify Relationships

User 1──* Project (owner) Project 1──* Task Task ── Label (via TaskLabel) Task ── User (via TaskAssignment) User 1──* AuditLog

Step 3: Add Cross-cutting Concerns

Multi-tenancy: add organization_id to all tenant-scoped tables Soft deletes: add deleted_at TIMESTAMPTZ instead of hard deletes Audit trail: add created_by, updated_by, created_at, updated_at Versioning: add version INTEGER for optimistic locking

Full Schema Example (Task Management SaaS)

→ See references/full-schema-examples.md for details

Row-Level Security (RLS) Policies

-- Enable RLS ALTER TABLE tasks ENABLE ROW LEVEL SECURITY; ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create app role CREATE ROLE app_user;

-- Users can only see tasks in their organization's projects CREATE POLICY tasks_org_isolation ON tasks FOR ALL TO app_user USING ( project_id IN ( SELECT p.id FROM projects p JOIN organization_members om ON om.organization_id = p.organization_id WHERE om.user_id = current_setting('app.current_user_id')::text ) );

-- Soft delete: never show deleted records CREATE POLICY tasks_no_deleted ON tasks FOR SELECT TO app_user USING (deleted_at IS NULL);

-- Only task creator or admin can delete CREATE POLICY tasks_delete_policy ON tasks FOR DELETE TO app_user USING ( created_by_id = current_setting('app.current_user_id')::text OR EXISTS ( SELECT 1 FROM organization_members om JOIN projects p ON p.organization_id = om.organization_id WHERE p.id = tasks.project_id AND om.user_id = current_setting('app.current_user_id')::text AND om.role IN ('owner', 'admin') ) );

-- Set user context (call at start of each request) SELECT set_config('app.current_user_id', $1, true);

Seed Data Generation

// db/seed.ts import { faker } from '@faker-js/faker' import { db } from './client' import { organizations, users, projects, tasks } from './schema' import { createId } from '@paralleldrive/cuid2' import { hashPassword } from '../src/lib/auth'

async function seed() { console.log('Seeding database...')

// Create org const [org] = await db.insert(organizations).values({ id: createId(), name: "acme-corp", slug: 'acme', plan: 'growth', }).returning()

// Create users const adminUser = await db.insert(users).values({ id: createId(), email: 'admin@acme.com', name: "alice-admin", passwordHash: await hashPassword('password123'), }).returning().then(r => r[0])

// Create projects const projectsData = Array.from({ length: 3 }, () => ({ id: createId(), organizationId: org.id, ownerId: adminUser.id, name: "fakercompanycatchphrase" description: faker.lorem.paragraph(), status: 'active' as const, }))

• const createdProjects = await db.

• insert(projects).

• values(projectsData).

• returning().

  // Create tasks for each project for (const project of createdProjects) { const tasksData = Array.from({ length: faker.number.int({ min: 5, max: 20 }) }, (_, i) => ({ id: createId(), projectId: project.id, title: faker.hacker.phrase(), description: faker.lorem.sentences(2), status: faker.helpers.arrayElement(['todo', 'in_progress', 'done'] as const), priority: faker.helpers.arrayElement(['low', 'medium', 'high'] as const), position: i * 1000, createdById: adminUser.id, updatedById: adminUser.id, }))

  await db.insert(tasks).values(tasksData) }

  console.log(✅ Seeded: 1 org, ${projectsData.length} projects, tasks) }

• seed().

• catch(console.

• error).

• finally(() => process.

• exit(0)).

ERD Generation (Mermaid)

erDiagram Organization ||--o{ OrganizationMember : has Organization ||--o{ Project : owns User ||--o{ OrganizationMember : joins User ||--o{ Task : "created by" Project ||--o{ Task : contains Task ||--o{ TaskAssignment : has Task ||--o{ TaskLabel : has Task ||--o{ Comment : has Task ||--o{ Attachment : has Label ||--o{ TaskLabel : "applied to" User ||--o{ TaskAssignment : assigned

Organization {
    string id PK
    string name
    string slug
    string plan
}

Task {
    string id PK
    string project_id FK
    string title
    string status
    string priority
    timestamp due_date
    timestamp deleted_at
    int version
}

Generate from Prisma: npx prisma-erd-generator or: npx @dbml/cli prisma2dbml -i schema.prisma | npx dbml-to-mermaid

Common Pitfalls

Soft delete without index — WHERE deleted_at IS NULL without index = full scan Missing composite indexes — WHERE org_id = ? AND status = ? needs a composite index Mutable surrogate keys — never use email or slug as PK; use UUID/CUID Non-nullable without default — adding a NOT NULL column to existing table requires default or migration plan No optimistic locking — concurrent updates overwrite each other; add version column RLS not tested — always test RLS with a non-superuser role

Best Practices

Timestamps everywhere — created_at, updated_at on every table Soft deletes for auditable data — deleted_at instead of DELETE Audit log for compliance — log before/after JSON for regulated domains UUIDs or CUIDs as PKs — avoid sequential integer leakage Index foreign keys — every FK column should have an index Partial indexes — use WHERE deleted_at IS NULL for active-only queries RLS over application-level filtering — database enforces tenancy, not just app codeDatabase Schema Designer

Tier: POWERFUL
Category