database-schema-designer

Database Schema Designer Tier: POWERFUL Category: Engineering / Data Architecture Maintainer: Claude Skills Team Overview Design normalized relational database schemas from requirements and generate migrations, TypeScript/Python types, seed data, Row-Level Security policies, index strategies, and ERD diagrams. Handles multi-tenancy, soft deletes, audit trails, optimistic locking, polymorphic associations, and temporal data patterns. Supports PostgreSQL, MySQL, and SQLite with Drizzle, Prisma, TypeORM, and Alembic. Keywords database schema, schema design, normalization, migration, ERD, row-lev…

,\n part, re.IGNORECASE\n )\n if col_match:\n cname = col_match.group(1)\n ctype = col_match.group(2).strip()\n\n is_pk = bool(re.search(r'PRIMARY\\s+KEY', part, re.IGNORECASE))\n nullable = not bool(re.search(r'NOT\\s+NULL', part, re.IGNORECASE)) and not is_pk\n default_match = re.search(r'DEFAULT\\s+(\\S+)', part, re.IGNORECASE)\n ref_match = re.search(r'REFERENCES\\s+\"?(\\w+)\"?', part, re.IGNORECASE)\n\n col = Column(\n name=cname,\n data_type=ctype,\n nullable=nullable,\n is_pk=is_pk,\n default=default_match.group(1) if default_match else None,\n references=ref_match.group(1) if ref_match else None,\n )\n table.columns[cname] = col\n if is_pk:\n table.primary_key = [cname]\n\n tables.append(table)\n table_map[tname] = table\n\n # Extract CREATE INDEX statements\n idx_pattern = re.compile(\n r'CREATE\\s+(UNIQUE\\s+)?INDEX\\s+(?:CONCURRENTLY\\s+)?(?:IF\\s+NOT\\s+EXISTS\\s+)?'\n r'\"?(\\w+)\"?\\s+ON\\s+\"?(\\w+)\"?\\s*(?:USING\\s+\\w+\\s*)?\$([^)]+)\$'\n r'(?:\\s+WHERE\\s+(.+?))?;',\n re.IGNORECASE | re.DOTALL\n )\n for match in idx_pattern.finditer(sql):\n unique = bool(match.group(1))\n idx_name = match.group(2)\n tname = match.group(3)\n cols = [c.strip().strip('\"') for c in match.group(4).split(',')]\n where = match.group(5).strip() if match.group(5) else None\n\n idx = Index(name=idx_name, columns=cols, unique=unique, where_clause=where)\n if tname in table_map:\n table_map[tname].indexes.append(idx)\n\n return tables\n\n\ndef _split_comma_top_level(s: str) -> List[str]:\n \"\"\"Split on commas that are not inside parentheses.\"\"\"\n parts = []\n depth = 0\n current = []\n for ch in s:\n if ch == '(':\n depth += 1\n elif ch == ')':\n depth -= 1\n elif ch == ',' and depth == 0:\n parts.append(''.join(current))\n current = []\n continue\n current.append(ch)\n if current:\n parts.append(''.join(current))\n return parts\n\n\ndef validate(tables: List[Table]) -> List[Finding]:\n \"\"\"Run all validation rules against parsed tables.\"\"\"\n findings: List[Finding] = []\n\n for table in tables:\n _check_naming(table, findings)\n _check_primary_key(table, findings)\n _check_timestamps(table, findings)\n _check_fk_indexes(table, findings)\n _check_fk_nullable(table, findings)\n _check_soft_delete_index(table, findings)\n _check_sequential_pk(table, findings)\n _check_no_indexes(table, findings)\n\n return findings\n\n\ndef _check_naming(table: Table, findings: List[Finding]):\n \"\"\"Enforce snake_case naming for tables and columns.\"\"\"\n snake = re.compile(r'^[a-z][a-z0-9]*(_[a-z0-9]+)*

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

)\n if not snake.match(table.name):\n findings.append(Finding(\n table=table.name, severity=Severity.WARNING, rule=\"naming_convention\",\n message=f\"Table name '{table.name}' is not snake_case.\",\n suggestion=f\"Rename to '{_to_snake(table.name)}'.\"\n ))\n for cname in table.columns:\n if not snake.match(cname):\n findings.append(Finding(\n table=table.name, severity=Severity.WARNING, rule=\"naming_convention\",\n message=f\"Column '{cname}' is not snake_case.\",\n suggestion=f\"Rename to '{_to_snake(cname)}'.\"\n ))\n\n\ndef _to_snake(name: str) -> str:\n s = re.sub(r'([A-Z]+)([A-Z][a-z])', r'\\1_\\2', name)\n s = re.sub(r'([a-z0-9])([A-Z])', r'\\1_\\2', s)\n return s.lower().replace(' ', '_').replace('-', '_')\n\n\ndef _check_primary_key(table: Table, findings: List[Finding]):\n if not table.primary_key:\n findings.append(Finding(\n table=table.name, severity=Severity.ERROR, rule=\"missing_primary_key\",\n message=f\"Table '{table.name}' has no PRIMARY KEY defined.\",\n suggestion=\"Add a PRIMARY KEY column (e.g., id TEXT PRIMARY KEY or id UUID PRIMARY KEY).\"\n ))\n\n\ndef _check_timestamps(table: Table, findings: List[Finding]):\n cols = set(table.columns.keys())\n if 'created_at' not in cols:\n findings.append(Finding(\n table=table.name, severity=Severity.WARNING, rule=\"missing_created_at\",\n message=f\"Table '{table.name}' is missing a 'created_at' column.\",\n suggestion=\"Add: created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\"\n ))\n if 'updated_at' not in cols:\n findings.append(Finding(\n table=table.name, severity=Severity.WARNING, rule=\"missing_updated_at\",\n message=f\"Table '{table.name}' is missing an 'updated_at' column.\",\n suggestion=\"Add: updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\"\n ))\n\n\ndef _check_fk_indexes(table: Table, findings: List[Finding]):\n indexed_cols = set()\n for idx in table.indexes:\n if idx.columns:\n indexed_cols.add(idx.columns[0])\n\n for cname, col in table.columns.items():\n if col.references and cname not in indexed_cols and not col.is_pk:\n findings.append(Finding(\n table=table.name, severity=Severity.ERROR, rule=\"missing_fk_index\",\n message=f\"Foreign key column '{cname}' has no index.\",\n suggestion=f\"CREATE INDEX idx_{table.name}_{cname} ON {table.name} ({cname});\"\n ))\n\n\ndef _check_fk_nullable(table: Table, findings: List[Finding]):\n for cname, col in table.columns.items():\n if col.references and col.nullable:\n findings.append(Finding(\n table=table.name, severity=Severity.INFO, rule=\"nullable_fk\",\n message=f\"Foreign key column '{cname}' is nullable.\",\n suggestion=\"Consider adding NOT NULL if the relationship is mandatory.\"\n ))\n\n\ndef _check_soft_delete_index(table: Table, findings: List[Finding]):\n if 'deleted_at' not in table.columns:\n return\n has_partial = any(\n idx.where_clause and 'deleted_at' in idx.where_clause.lower()\n for idx in table.indexes\n )\n if not has_partial:\n findings.append(Finding(\n table=table.name, severity=Severity.WARNING, rule=\"soft_delete_no_partial_index\",\n message=f\"Table '{table.name}' has soft deletes but no partial index filtering deleted rows.\",\n suggestion=\"Add a partial index: WHERE deleted_at IS NULL on frequently queried columns.\"\n ))\n\n\ndef _check_sequential_pk(table: Table, findings: List[Finding]):\n if not table.primary_key:\n return\n for pk_col_name in table.primary_key:\n col = table.columns.get(pk_col_name)\n if col and col.data_type.upper() in ('SERIAL', 'BIGSERIAL', 'INT', 'INTEGER', 'BIGINT'):\n if col.is_pk:\n findings.append(Finding(\n table=table.name, severity=Severity.INFO, rule=\"sequential_pk\",\n message=f\"Primary key '{pk_col_name}' uses sequential integer type '{col.data_type}'.\",\n suggestion=\"Consider CUID2 or UUIDv7 for non-guessable, sortable IDs (especially if exposed in URLs).\"\n ))\n\n\ndef _check_no_indexes(table: Table, findings: List[Finding]):\n if not table.indexes and len(table.columns) > 2:\n findings.append(Finding(\n table=table.name, severity=Severity.WARNING, rule=\"no_indexes\",\n message=f\"Table '{table.name}' has no indexes besides the primary key.\",\n suggestion=\"Add indexes on columns used in WHERE clauses, JOINs, and ORDER BY.\"\n ))\n\n\ndef format_human(findings: List[Finding], tables: List[Table]) -> str:\n \"\"\"Format findings for human-readable output.\"\"\"\n lines = []\n lines.append(f\"Schema Validation Report\")\n lines.append(f\"{'=' * 50}\")\n lines.append(f\"Tables analyzed: {len(tables)}\")\n\n error_count = sum(1 for f in findings if f.severity == Severity.ERROR)\n warn_count = sum(1 for f in findings if f.severity == Severity.WARNING)\n info_count = sum(1 for f in findings if f.severity == Severity.INFO)\n\n lines.append(f\"Findings: {error_count} errors, {warn_count} warnings, {info_count} info\")\n lines.append(\"\")\n\n if not findings:\n lines.append(\"No issues found. Schema looks good!\")\n return '\\n'.join(lines)\n\n severity_order = {Severity.ERROR: 0, Severity.WARNING: 1, Severity.INFO: 2}\n sorted_findings = sorted(findings, key=lambda f: (severity_order.get(f.severity, 3), f.table))\n\n icons = {Severity.ERROR: \"[ERROR]\", Severity.WARNING: \"[WARN] \", Severity.INFO: \"[INFO] \"}\n\n for f in sorted_findings:\n icon = icons.get(f.severity, \" \")\n lines.append(f\"{icon} {f.table}: {f.message}\")\n lines.append(f\" -> {f.suggestion}\")\n lines.append(\"\")\n\n return '\\n'.join(lines)\n\n\ndef format_json(findings: List[Finding], tables: List[Table]) -> str:\n \"\"\"Format findings as JSON.\"\"\"\n return json.dumps({\n \"tables_analyzed\": len(tables),\n \"summary\": {\n \"errors\": sum(1 for f in findings if f.severity == Severity.ERROR),\n \"warnings\": sum(1 for f in findings if f.severity == Severity.WARNING),\n \"info\": sum(1 for f in findings if f.severity == Severity.INFO),\n },\n \"findings\": [\n {\n \"table\": f.table,\n \"severity\": f.severity,\n \"rule\": f.rule,\n \"message\": f.message,\n \"suggestion\": f.suggestion,\n }\n for f in findings\n ]\n }, indent=2)\n\n\ndef main():\n parser = argparse.ArgumentParser(\n description=\"Validate SQL DDL schemas for normalization violations, missing indexes, and naming conventions.\"\n )\n parser.add_argument(\"file\", help=\"Path to SQL DDL file to validate\")\n parser.add_argument(\"--json\", action=\"store_true\", dest=\"json_output\",\n help=\"Output results as JSON\")\n parser.add_argument(\"--strict\", action=\"store_true\",\n help=\"Exit with code 1 on any warning (not just errors)\")\n args = parser.parse_args()\n\n try:\n with open(args.file, 'r') as f:\n sql = f.read()\n except FileNotFoundError:\n print(f\"Error: File not found: {args.file}\", file=sys.stderr)\n sys.exit(2)\n except IOError as e:\n print(f\"Error reading file: {e}\", file=sys.stderr)\n sys.exit(2)\n\n tables = parse_ddl(sql)\n if not tables:\n print(\"Warning: No CREATE TABLE statements found in the file.\", file=sys.stderr)\n sys.exit(0)\n\n findings = validate(tables)\n\n if args.json_output:\n print(format_json(findings, tables))\n else:\n print(format_human(findings, tables))\n\n error_count = sum(1 for f in findings if f.severity == Severity.ERROR)\n warn_count = sum(1 for f in findings if f.severity == Severity.WARNING)\n\n if error_count > 0:\n sys.exit(1)\n if args.strict and warn_count > 0:\n sys.exit(1)\n sys.exit(0)\n\n\nif __name__ == '__main__':\n main()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":14138,"content_sha256":"b15d121d6f9242af6fe729a70c7be9bc6c0f773e9fbfc66cc6fd2d1b27aa6bf1"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Database Schema Designer","type":"text"}]},{"type":"paragraph","content":[{"text":"Tier:","type":"text","marks":[{"type":"strong"}]},{"text":" POWERFUL ","type":"text"},{"text":"Category:","type":"text","marks":[{"type":"strong"}]},{"text":" Engineering / Data Architecture ","type":"text"},{"text":"Maintainer:","type":"text","marks":[{"type":"strong"}]},{"text":" Claude Skills Team","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"Design normalized relational database schemas from requirements and generate migrations, TypeScript/Python types, seed data, Row-Level Security policies, index strategies, and ERD diagrams. Handles multi-tenancy, soft deletes, audit trails, optimistic locking, polymorphic associations, and temporal data patterns. Supports PostgreSQL, MySQL, and SQLite with Drizzle, Prisma, TypeORM, and Alembic.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Keywords","type":"text"}]},{"type":"paragraph","content":[{"text":"database schema, schema design, normalization, migration, ERD, row-level security, indexing, multi-tenancy, soft deletes, audit trail, Drizzle, Prisma, PostgreSQL","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Core Capabilities","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. Schema Design from Requirements","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Extract entities and relationships from natural language requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Apply normalization rules (1NF through 3NF with denormalization guidance)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add cross-cutting concerns: timestamps, soft deletes, audit, versioning","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Generate complete DDL with constraints, defaults, and comments","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Migration Planning","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Generate forward and rollback migrations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Plan zero-downtime migrations for large tables","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Handle column additions, type changes, and data backfills","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Support Drizzle, Prisma, TypeORM, Alembic, and raw SQL","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Index Strategy","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Composite indexes for common query patterns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partial indexes for filtered queries (e.g., active records only)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Covering indexes to eliminate table lookups","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GIN/GiST indexes for full-text search and JSONB","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index bloat detection and maintenance","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"4. Type Generation","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"TypeScript interfaces and Zod schemas from DB schema","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Python dataclasses and Pydantic models","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Enums as string unions (not database enums for migration safety)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"5. Security","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Row-Level Security policies for multi-tenant isolation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Column-level encryption for PII","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Audit logging with before/after JSON snapshots","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When to Use","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Designing tables for a new feature","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Reviewing an existing schema for normalization or performance issues","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Adding multi-tenancy to a single-tenant schema","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Planning a breaking schema migration","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Generating ERD documentation for a service","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Schema Design Process","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 1: Requirements to Entities","type":"text"}]},{"type":"paragraph","content":[{"text":"Given requirements like:","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"\"Users can create workspaces. Each workspace has projects. Projects contain tasks with assignees, labels, and due dates. We need audit trails and multi-tenant isolation.\"","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Extract entities:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"User, Workspace, WorkspaceMember, Project, Task, TaskAssignment,\nLabel, TaskLabel (junction), AuditLog","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 2: Identify Relationships","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"User 1──* WorkspaceMember *──1 Workspace\nWorkspace 1──* Project\nProject 1──* Task\nTask *──* User (via TaskAssignment)\nTask *──* Label (via TaskLabel)\nUser 1──* AuditLog","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 3: Add Cross-Cutting Concerns","type":"text"}]},{"type":"paragraph","content":[{"text":"Every table gets:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"id","type":"text","marks":[{"type":"code_inline"}]},{"text":" — CUID2 or UUIDv7 (sortable, non-sequential)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"created_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" — TIMESTAMPTZ, server-side default","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"updated_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" — TIMESTAMPTZ, updated on every write","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Tenant-scoped tables additionally get:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"workspace_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" — FK to workspaces, included in every query","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"RLS policy enforcing workspace isolation","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Auditable tables additionally get:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"created_by_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" — FK to users","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"updated_by_id","type":"text","marks":[{"type":"code_inline"}]},{"text":" — FK to users","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"deleted_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" — TIMESTAMPTZ for soft deletes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"version","type":"text","marks":[{"type":"code_inline"}]},{"text":" — INTEGER for optimistic locking","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 4: Full Schema (Drizzle ORM)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"import {\n pgTable, text, timestamp, integer, boolean, uniqueIndex, index, pgEnum\n} from 'drizzle-orm/pg-core'\nimport { createId } from '@paralleldrive/cuid2'\n\n// Enums as pgEnum for type safety, but string columns also acceptable\nexport const taskStatusEnum = pgEnum('task_status', ['todo', 'in_progress', 'in_review', 'done'])\nexport const taskPriorityEnum = pgEnum('task_priority', ['low', 'medium', 'high', 'urgent'])\nexport const memberRoleEnum = pgEnum('member_role', ['owner', 'admin', 'member', 'viewer'])\n\n// ──── WORKSPACES ────\nexport const workspaces = pgTable('workspaces', {\n id: text('id').primaryKey().$defaultFn(createId),\n name: text('name').notNull(),\n slug: text('slug').notNull(),\n plan: text('plan').notNull().default('free'),\n createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),\n updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),\n}, (t) => [\n uniqueIndex('workspaces_slug_idx').on(t.slug),\n])\n\n// ──── USERS ────\nexport const users = pgTable('users', {\n id: text('id').primaryKey().$defaultFn(createId),\n email: text('email').notNull(),\n name: text('name'),\n avatarUrl: text('avatar_url'),\n passwordHash: text('password_hash'),\n createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),\n updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),\n}, (t) => [\n uniqueIndex('users_email_idx').on(t.email),\n])\n\n// ──── WORKSPACE MEMBERS ────\nexport const workspaceMembers = pgTable('workspace_members', {\n id: text('id').primaryKey().$defaultFn(createId),\n workspaceId: text('workspace_id').notNull().references(() => workspaces.id, { onDelete: 'cascade' }),\n userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),\n role: memberRoleEnum('role').notNull().default('member'),\n joinedAt: timestamp('joined_at', { withTimezone: true }).defaultNow().notNull(),\n}, (t) => [\n uniqueIndex('workspace_members_unique').on(t.workspaceId, t.userId),\n index('workspace_members_workspace_idx').on(t.workspaceId),\n index('workspace_members_user_idx').on(t.userId),\n])\n\n// ──── PROJECTS ────\nexport const projects = pgTable('projects', {\n id: text('id').primaryKey().$defaultFn(createId),\n workspaceId: text('workspace_id').notNull().references(() => workspaces.id, { onDelete: 'cascade' }),\n name: text('name').notNull(),\n description: text('description'),\n status: text('status').notNull().default('active'),\n ownerId: text('owner_id').notNull().references(() => users.id),\n createdById: text('created_by_id').references(() => users.id),\n updatedById: text('updated_by_id').references(() => users.id),\n createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),\n updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),\n deletedAt: timestamp('deleted_at', { withTimezone: true }),\n}, (t) => [\n index('projects_workspace_idx').on(t.workspaceId),\n index('projects_workspace_status_idx').on(t.workspaceId, t.status),\n])\n\n// ──── TASKS ────\nexport const tasks = pgTable('tasks', {\n id: text('id').primaryKey().$defaultFn(createId),\n projectId: text('project_id').notNull().references(() => projects.id, { onDelete: 'cascade' }),\n title: text('title').notNull(),\n description: text('description'),\n status: taskStatusEnum('status').notNull().default('todo'),\n priority: taskPriorityEnum('priority').notNull().default('medium'),\n position: integer('position').notNull().default(0),\n dueDate: timestamp('due_date', { withTimezone: true }),\n version: integer('version').notNull().default(1),\n createdById: text('created_by_id').notNull().references(() => users.id),\n updatedById: text('updated_by_id').references(() => users.id),\n createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),\n updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),\n deletedAt: timestamp('deleted_at', { withTimezone: true }),\n}, (t) => [\n index('tasks_project_idx').on(t.projectId),\n index('tasks_project_status_idx').on(t.projectId, t.status),\n index('tasks_due_date_idx').on(t.dueDate).where(sql`deleted_at IS NULL`),\n])\n\n// ──── AUDIT LOG ────\nexport const auditLog = pgTable('audit_log', {\n id: text('id').primaryKey().$defaultFn(createId),\n workspaceId: text('workspace_id').notNull().references(() => workspaces.id),\n userId: text('user_id').notNull().references(() => users.id),\n action: text('action').notNull(), // 'create' | 'update' | 'delete'\n entityType: text('entity_type').notNull(), // 'task' | 'project' | etc.\n entityId: text('entity_id').notNull(),\n before: text('before'), // JSON snapshot\n after: text('after'), // JSON snapshot\n ipAddress: text('ip_address'),\n createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),\n}, (t) => [\n index('audit_log_workspace_idx').on(t.workspaceId),\n index('audit_log_entity_idx').on(t.entityType, t.entityId),\n index('audit_log_user_idx').on(t.userId),\n index('audit_log_created_idx').on(t.createdAt),\n])","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Row-Level Security (PostgreSQL)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Enable RLS on tenant-scoped tables\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\nALTER TABLE tasks ENABLE ROW LEVEL SECURITY;\n\n-- Create application role\nCREATE ROLE app_user;\n\n-- Projects: users can only see projects in their workspace\nCREATE POLICY projects_workspace_isolation ON projects\n FOR ALL TO app_user\n USING (\n workspace_id IN (\n SELECT wm.workspace_id FROM workspace_members wm\n WHERE wm.user_id = current_setting('app.current_user_id')::text\n )\n );\n\n-- Tasks: access through project's workspace membership\nCREATE POLICY tasks_workspace_isolation ON tasks\n FOR ALL TO app_user\n USING (\n project_id IN (\n SELECT p.id FROM projects p\n JOIN workspace_members wm ON wm.workspace_id = p.workspace_id\n WHERE wm.user_id = current_setting('app.current_user_id')::text\n )\n );\n\n-- Soft delete filter: never show deleted records to app users\nCREATE POLICY tasks_hide_deleted ON tasks\n FOR SELECT TO app_user\n USING (deleted_at IS NULL);\n\n-- Set user context at request start (in middleware)\n-- SELECT set_config('app.current_user_id', $1, true);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Index Strategy Decision Framework","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"Query Pattern → Index Type\n─────────────────────────────────────────────────────\nWHERE col = value → B-tree (default)\nWHERE col1 = v1 AND col2 = v2 → Composite B-tree (col1, col2)\nWHERE col = value AND deleted_at IS NULL → Partial index with WHERE clause\nWHERE col IN (v1, v2, v3) → B-tree (handles IN efficiently)\nWHERE col LIKE 'prefix%' → B-tree (prefix match only)\nWHERE col LIKE '%substring%' → GIN with pg_trgm extension\nWHERE jsonb_col @> '{\"key\": \"val\"}' → GIN on JSONB column\nWHERE to_tsvector(col) @@ query → GIN on tsvector\nORDER BY col DESC LIMIT N → B-tree DESC\nSELECT a, b WHERE a = v → Covering index INCLUDE(b)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Index Anti-Patterns","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Anti-Pattern","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Why It Hurts","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fix","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index on every column","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Write overhead, storage bloat","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index only queried columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"No index on foreign keys","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Slow JOINs and CASCADE deletes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Always index FK columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Missing partial index for soft deletes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Full table scan on ","type":"text"},{"text":"WHERE deleted_at IS NULL","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add ","type":"text"},{"text":"WHERE deleted_at IS NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" to index","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Composite index in wrong order","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index unused for prefix queries","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Put most selective / equality column first","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"No index maintenance","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Bloated indexes, slow queries","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Schedule REINDEX CONCURRENTLY","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Zero-Downtime Migration Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Adding a NOT NULL Column to a Large Table","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- WRONG: locks table for duration of ALTER\nALTER TABLE tasks ADD COLUMN assignee_id TEXT NOT NULL;\n\n-- RIGHT: three-phase migration\n-- Phase 1: Add nullable column (instant, no lock)\nALTER TABLE tasks ADD COLUMN assignee_id TEXT;\n\n-- Phase 2: Backfill in batches (no lock)\nUPDATE tasks SET assignee_id = created_by_id\nWHERE assignee_id IS NULL AND id > $last_processed_id\nLIMIT 10000;\n-- Repeat until all rows backfilled\n\n-- Phase 3: Add NOT NULL constraint (brief lock, but validates existing data)\nALTER TABLE tasks ALTER COLUMN assignee_id SET NOT NULL;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Renaming a Column Safely","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- WRONG: ALTER TABLE RENAME COLUMN breaks all running code instantly\n\n-- RIGHT: expand-contract pattern\n-- Phase 1: Add new column, write to both\nALTER TABLE tasks ADD COLUMN assignee_user_id TEXT;\n-- Deploy code that writes to BOTH old_name and new_name\n\n-- Phase 2: Backfill\nUPDATE tasks SET assignee_user_id = old_assignee WHERE assignee_user_id IS NULL;\n\n-- Phase 3: Switch reads to new column\n-- Deploy code that reads from new_name only\n\n-- Phase 4: Drop old column (after all deployments use new name)\nALTER TABLE tasks DROP COLUMN old_assignee;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"ERD Generation (Mermaid)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"mermaid"},"content":[{"text":"erDiagram\n Workspace ||--o{ WorkspaceMember : has\n Workspace ||--o{ Project : contains\n User ||--o{ WorkspaceMember : joins\n User ||--o{ Task : creates\n Project ||--o{ Task : contains\n Task ||--o{ TaskAssignment : has\n Task ||--o{ TaskLabel : has\n Label ||--o{ TaskLabel : tags\n User ||--o{ TaskAssignment : assigned\n\n Workspace {\n text id PK\n text name\n text slug UK\n text plan\n }\n User {\n text id PK\n text email UK\n text name\n }\n Project {\n text id PK\n text workspace_id FK\n text name\n text status\n }\n Task {\n text id PK\n text project_id FK\n text title\n enum status\n enum priority\n int version\n timestamp deleted_at\n }","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Pitfalls","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No index on foreign keys","type":"text","marks":[{"type":"strong"}]},{"text":" — every FK column needs an index for JOIN and CASCADE performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Soft deletes without partial index","type":"text","marks":[{"type":"strong"}]},{"text":" — ","type":"text"},{"text":"WHERE deleted_at IS NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" without index causes full table scans","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Sequential integer IDs exposed in URLs","type":"text","marks":[{"type":"strong"}]},{"text":" — reveals entity count; use CUID2 or UUIDv7 instead","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Adding NOT NULL to a large table","type":"text","marks":[{"type":"strong"}]},{"text":" — locks the table; use the three-phase pattern above","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Database enums for status fields","type":"text","marks":[{"type":"strong"}]},{"text":" — altering enums requires migration; use text with CHECK constraint","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No optimistic locking","type":"text","marks":[{"type":"strong"}]},{"text":" — concurrent updates silently overwrite each other; add a ","type":"text"},{"text":"version","type":"text","marks":[{"type":"code_inline"}]},{"text":" column","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"RLS not tested","type":"text","marks":[{"type":"strong"}]},{"text":" — always test RLS policies with a non-superuser role in staging","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Missing updated_at trigger","type":"text","marks":[{"type":"strong"}]},{"text":" — without a trigger, updated_at only updates when application code remembers to set it","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Timestamps on every table","type":"text","marks":[{"type":"strong"}]},{"text":" — ","type":"text"},{"text":"created_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"updated_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" as TIMESTAMPTZ with server defaults","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Soft deletes for user-facing data","type":"text","marks":[{"type":"strong"}]},{"text":" — ","type":"text"},{"text":"deleted_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead of hard DELETE for audit and recovery","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CUID2 or UUIDv7 as primary keys","type":"text","marks":[{"type":"strong"}]},{"text":" — sortable, non-sequential, globally unique","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index every foreign key column","type":"text","marks":[{"type":"strong"}]},{"text":" — required for JOIN performance and CASCADE operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partial indexes for filtered queries","type":"text","marks":[{"type":"strong"}]},{"text":" — ","type":"text"},{"text":"WHERE deleted_at IS NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" saves significant scan time","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"RLS over application-level filtering","type":"text","marks":[{"type":"strong"}]},{"text":" — the database enforces tenancy, not just application code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Version column for optimistic locking","type":"text","marks":[{"type":"strong"}]},{"text":" — ","type":"text"},{"text":"WHERE version = $expected_version","type":"text","marks":[{"type":"code_inline"}]},{"text":" prevents lost updates","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Audit log with JSON snapshots","type":"text","marks":[{"type":"strong"}]},{"text":" — store before/after state for compliance and debugging","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Troubleshooting","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Problem","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Cause","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Solution","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Migration locks table for minutes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Adding NOT NULL column or index on large table without batching","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use the three-phase migration pattern: add nullable, backfill in batches, then set NOT NULL","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"RLS policies silently return empty results","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"current_setting('app.current_user_id')","type":"text","marks":[{"type":"code_inline"}]},{"text":" not set before query","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Verify middleware calls ","type":"text"},{"text":"set_config","type":"text","marks":[{"type":"code_inline"}]},{"text":" at the start of every request; add a test that queries as a non-superuser role","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Composite index not used by query planner","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Columns in the WHERE clause do not match the index prefix order","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Reorder index columns so equality predicates come first, then range predicates; run ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" to confirm","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Soft-deleted records appear in API responses","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Application queries missing ","type":"text"},{"text":"WHERE deleted_at IS NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" filter","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add a default scope or database view that excludes soft-deleted rows; prefer RLS policy for enforcement","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Optimistic locking conflicts spike after deploy","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"New code path writes without incrementing the ","type":"text"},{"text":"version","type":"text","marks":[{"type":"code_inline"}]},{"text":" column","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Audit all UPDATE statements to include ","type":"text"},{"text":"SET version = version + 1","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"WHERE version = $expected","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Foreign key CASCADE deletes are slow","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Missing index on the child table's FK column","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add a B-tree index on every FK column; verify with ","type":"text"},{"text":"EXPLAIN","type":"text","marks":[{"type":"code_inline"}]},{"text":" on a DELETE of the parent row","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"CUID2/UUIDv7 IDs cause index bloat over time","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Text-based IDs are wider than integers, increasing B-tree page splits","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Schedule ","type":"text"},{"text":"REINDEX CONCURRENTLY","type":"text","marks":[{"type":"code_inline"}]},{"text":" during low-traffic windows; monitor ","type":"text"},{"text":"pg_stat_user_indexes","type":"text","marks":[{"type":"code_inline"}]},{"text":" for bloat ratio","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Success Criteria","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema passes 3NF validation","type":"text","marks":[{"type":"strong"}]},{"text":" — no transitive dependencies remain unless documented as intentional denormalization for read performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"All foreign key columns are indexed","type":"text","marks":[{"type":"strong"}]},{"text":" — zero FK columns without a corresponding B-tree index, verified via ","type":"text"},{"text":"pg_indexes","type":"text","marks":[{"type":"code_inline"}]},{"text":" query","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Zero-downtime migrations verified","type":"text","marks":[{"type":"strong"}]},{"text":" — every migration executes without ","type":"text"},{"text":"ACCESS EXCLUSIVE","type":"text","marks":[{"type":"code_inline"}]},{"text":" locks exceeding 5 seconds on tables with 100K+ rows","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"RLS policies tested with non-superuser role","type":"text","marks":[{"type":"strong"}]},{"text":" — at least one integration test per tenant-scoped table confirms cross-tenant data isolation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Type generation matches schema","type":"text","marks":[{"type":"strong"}]},{"text":" — generated TypeScript interfaces or Pydantic models have zero drift from the current DDL, validated in CI","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Query performance meets SLA","type":"text","marks":[{"type":"strong"}]},{"text":" — 95th percentile query latency under 50ms for indexed queries on tables up to 10M rows","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Audit log captures all mutations","type":"text","marks":[{"type":"strong"}]},{"text":" — every INSERT, UPDATE, and DELETE on auditable tables produces a corresponding audit_log entry with before/after snapshots","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scope & Limitations","type":"text"}]},{"type":"paragraph","content":[{"text":"This skill covers:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Relational schema design for PostgreSQL, MySQL, and SQLite including normalization through 3NF","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Migration generation and zero-downtime migration planning for Drizzle, Prisma, TypeORM, and Alembic","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Row-Level Security policies, index strategy, and type generation (TypeScript and Python)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Cross-cutting patterns: multi-tenancy, soft deletes, audit trails, optimistic locking, and temporal data","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"This skill does NOT cover:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"NoSQL or document database design (MongoDB, DynamoDB, Cassandra) — see ","type":"text"},{"text":"senior-data-engineer","type":"text","marks":[{"type":"code_inline"}]},{"text":" for broader data store guidance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Query optimization and execution plan analysis beyond index recommendations — see ","type":"text"},{"text":"performance-profiler","type":"text","marks":[{"type":"code_inline"}]},{"text":" for runtime profiling","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Database infrastructure provisioning, replication, or failover configuration — see ","type":"text"},{"text":"senior-cloud-architect","type":"text","marks":[{"type":"code_inline"}]},{"text":" for cloud database setup","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Application-layer ORM patterns, connection pooling, or caching strategies — see ","type":"text"},{"text":"senior-backend","type":"text","marks":[{"type":"code_inline"}]},{"text":" for backend architecture decisions","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Integration Points","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Skill","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Integration","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Data Flow","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"migration-architect","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Hands off generated DDL and migration files for sequencing across services","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Schema Designer produces migrations, Migration Architect orchestrates cross-service rollout order","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"api-design-reviewer","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Schema entities map directly to API resource models and endpoint structure","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Schema entities and relationships feed into REST/GraphQL resource definitions and validation rules","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"senior-backend","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Generated types and ORM schemas plug into repository and service layers","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"TypeScript interfaces and Pydantic models from schema become the backend's data access contracts","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"performance-profiler","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index strategy recommendations are validated against real query execution plans","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Schema Designer proposes indexes, Performance Profiler confirms effectiveness with ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" data","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"senior-secops","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"RLS policies and column encryption align with security compliance requirements","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Security requirements flow in, RLS policies and encryption specifications flow out for audit verification","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"observability-designer","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Audit log schema provides the foundation for operational dashboards and alerting","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Audit log table structure feeds into observability pipelines for change tracking and anomaly detection","type":"text"}]}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"database-schema-designer","author":"@skillopedia","source":{"stars":209,"repo_name":"claude-skills","origin_url":"https://github.com/borghei/claude-skills/blob/HEAD/engineering/database-schema-designer/SKILL.md","repo_owner":"borghei","body_sha256":"88b01eef20ee4177cbd0766fd7d237b37c6fafdb135b2344b16c8eb40164ec7d","cluster_key":"de9d161139b33e8974c0594a61f4c7038db066575d5fff9ce082b29c0d527701","clean_bundle":{"format":"clean-skill-bundle-v1","source":"borghei/claude-skills/engineering/database-schema-designer/SKILL.md","attachments":[{"id":"c3728d73-93de-5321-b294-9c0f5ab95259","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/c3728d73-93de-5321-b294-9c0f5ab95259/attachment.py","path":"scripts/erd_generator.py","size":12158,"sha256":"5f9d7c83bca7d98c57cb9f25b9c2899cbad5747a45d6f3535f00b858c9e36d77","contentType":"text/x-python; charset=utf-8"},{"id":"0a10e5d9-f3f0-506c-84d5-8d3ec8597c1f","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/0a10e5d9-f3f0-506c-84d5-8d3ec8597c1f/attachment.py","path":"scripts/migration_diffr.py","size":17776,"sha256":"abf6aaa8be915528ef9600b00d444fee8b84bba08479338a6315d19cf89fd234","contentType":"text/x-python; charset=utf-8"},{"id":"0c0048c1-d176-5f18-9414-cfcfa3a81401","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/0c0048c1-d176-5f18-9414-cfcfa3a81401/attachment.py","path":"scripts/schema_validator.py","size":14138,"sha256":"b15d121d6f9242af6fe729a70c7be9bc6c0f773e9fbfc66cc6fd2d1b27aa6bf1","contentType":"text/x-python; charset=utf-8"}],"bundle_sha256":"ef1602bb592db7cfe7eed3aed9406ea61a3ad690e5d152e76b7ce64add92c1ab","attachment_count":3,"text_attachments":3,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"engineering/database-schema-designer/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"web-development","category_label":"Web"},"exact_dupes_collapsed_into_this":0},"license":"MIT + Commons Clause","version":"v1","category":"web-development","metadata":{"tier":"POWERFUL","author":"borghei","domain":"data-architecture","updated":"2026-03-09T00:00:00.000Z","version":"1.0.0","category":"engineering","frameworks":"drizzle, prisma, alembic, typeorm"},"import_tag":"clean-skills-v1","description":"Design relational database schemas from requirements with normalization, migration planning, ERD generation, RLS policies, index strategies, and type generation. Supports PostgreSQL, MySQL, and SQLite with Drizzle, Prisma, and Alembic migrations. Use when designing new features, reviewing schemas, or adding multi-tenancy.\n"}},"renderedAt":1782987139126}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.