AGENT SKILL

database-schema-designer

Use when the user asks to create ERD diagrams, normalize database schemas, design table relationships, or plan schema migrations.

License 许可
MIT
最近更新
2026-04-01
Stars
★ 3
Forks
2

01 前言 Front Matter

name
database-schema-designer
description
Use when the user asks to create ERD diagrams, normalize database schemas, design table relationships, or plan schema migrations.

02 资源文件 1

03 SKILL.md 正文

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

When to Use

  • 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, TaskLabel (junction), TaskAssignment, AuditLog

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

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

04 同仓库更多 Skills

afaizalam2003
afaizalam2003/Master-claude-skill

soc2-compliance

Use when the user asks to prepare for SOC 2 audits, map Trust Service Criteria, build control matrices, collect audit evidence, perform gap analysis, or assess SOC 2 Type I vs Type II readiness.

律师与法务
★ 3
afaizalam2003
afaizalam2003/Master-claude-skill

risk-management-specialist

Medical device risk management specialist implementing ISO 14971 throughout product lifecycle. Provides risk analysis, risk evaluation, risk control, and post-production information analysis. Use when user mentions risk management, ISO 14971, risk analysis, FMEA, fault tree analysis, hazard identification, risk control, risk matrix, benefit-risk analysis, residual risk, risk acceptability, or post-market risk.

★ 3
afaizalam2003
afaizalam2003/Master-claude-skill

regulatory-affairs-head

Senior Regulatory Affairs Manager for HealthTech and MedTech companies. Prepares FDA 510(k), De Novo, and PMA submission packages; analyzes regulatory pathways for new medical devices; drafts responses to FDA deficiency letters and Notified Body queries; develops CE marking technical documentation under EU MDR 2017/745; coordinates multi-market approval strategies across FDA, EU, Health Canada, PMDA, and NMPA; and maintains regulatory intelligence on evolving standards. Use when users need to plan or execute FDA submissions, navigate 510(k) or PMA approval processes, achieve CE marking, prepare pre-submission meeting materials, write regulatory strategy documents, respond to agency queries, or manage compliance documentation for medical device market access.

★ 3
afaizalam2003
afaizalam2003/Master-claude-skill

quality-manager-qms-iso13485

ISO 13485 Quality Management System implementation and maintenance for medical device organizations. Provides QMS design, documentation control, internal auditing, CAPA management, and certification support. Use when working with medical device quality systems, preparing for ISO 13485 audits, managing regulatory compliance documentation, setting up corrective actions, or building audit preparation programs. Useful for quality management, audit preparation, regulatory compliance, medical device documentation, and corrective action workflows.

设计师视觉设计
★ 3
afaizalam2003
afaizalam2003/Master-claude-skill

quality-manager-qmr

Senior Quality Manager Responsible Person (QMR) for HealthTech and MedTech companies. Provides quality system governance, management review leadership, regulatory compliance oversight, and quality performance monitoring per ISO 13485 Clause 5.5.2.

律师与法务
★ 3
afaizalam2003
afaizalam2003/Master-claude-skill

quality-documentation-manager

Document control system management for medical device QMS. Covers document numbering, version control, change management, and 21 CFR Part 11 compliance. Use for document control procedures, change control workflow, document numbering, version management, electronic signature compliance, or regulatory documentation review.

律师与法务文档处理Skill与智能体开发
★ 3