Skip to content

LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/database-standards

database-standards

Verified

PostgreSQL database standards for migrations, seeds, and schema management.

Database Standards Skill

Standards for PostgreSQL database components with migrations, seeds, and schema management.


Purpose

Database components manage schema evolution and seed data:

  1. Version-controlled schema via numbered migrations
  2. Repeatable seed data for development and testing
  3. Idempotent operations for safe re-runs
  4. Transactional safety for atomic changes

Directory Structure

components/database[-{name}]/
├── package.json              # Component package metadata
├── migrations/               # Schema migrations (numbered)
│   ├── 001_initial_schema.sql
│   ├── 002_add_users_table.sql
│   └── 003_add_indexes.sql
└── seeds/                    # Seed data (numbered)
    ├── 001_lookup_data.sql
    └── 002_test_users.sql

Config Schema

Database components require connection configuration from components/config/. The database-scaffolding skill generates the initial database structure including a minimal config schema with host, port, database, user, and password fields.


Migration Standards

File Naming

migrations/
├── 001_initial_schema.sql
├── 002_add_users_table.sql
├── 003_add_orders_table.sql
└── 004_add_indexes.sql

Rules:

  • Three-digit prefix: 001_, 002_, etc.
  • Descriptive name in snake_case
  • .sql extension
  • Never rename or reorder existing migrations

Migration Structure

-- migrations/002_add_users_table.sql
BEGIN;

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

COMMIT;

Required Patterns:

PatternWhy
BEGIN/COMMITAtomic transaction
IF NOT EXISTSIdempotent (safe to re-run)
TIMESTAMPTZTimezone-aware timestamps
gen_random_uuid()PostgreSQL native UUIDs

Migration Types

TypeWhenExample
Schema creationNew tablesCREATE TABLE IF NOT EXISTS
Schema modificationAdd/modify columnsALTER TABLE ... ADD COLUMN IF NOT EXISTS
Index creationPerformanceCREATE INDEX IF NOT EXISTS
Data migrationTransform existing dataUPDATE ... WHERE ...
Constraint additionAdd validationALTER TABLE ... ADD CONSTRAINT

Column Conventions

TypePostgreSQL TypeNotes
Primary keyUUIDUse gen_random_uuid()
TimestampsTIMESTAMPTZAlways timezone-aware
MoneyNUMERIC(19,4)Never FLOAT or MONEY
EnumsVARCHAR + CHECKOr PostgreSQL ENUM type
JSONJSONBNever JSON

Rollback Strategy

Migrations are forward-only. If you need to undo:

  1. Create a new migration that reverses the change
  2. Never modify or delete existing migrations
  3. Never use DROP TABLE without careful consideration
-- migrations/005_remove_legacy_column.sql
BEGIN;

ALTER TABLE users DROP COLUMN IF EXISTS legacy_field;

COMMIT;

Seed Standards

File Naming

seeds/
├── 001_lookup_data.sql
├── 002_admin_users.sql
└── 003_sample_data.sql

Rules:

  • Three-digit prefix matching execution order
  • Descriptive name in snake_case
  • Seeds run AFTER migrations

Seed Structure

-- seeds/001_lookup_data.sql
BEGIN;

INSERT INTO status_types (code, label) VALUES
    ('pending', 'Pending'),
    ('active', 'Active'),
    ('completed', 'Completed')
ON CONFLICT (code) DO NOTHING;

COMMIT;

Required Patterns:

PatternWhy
BEGIN/COMMITAtomic transaction
ON CONFLICT DO NOTHINGIdempotent
ON CONFLICT DO UPDATEUpsert when updates needed

Seed Categories

CategoryPurposeExample
Lookup dataReference tablesStatus codes, countries
Admin dataInitial admin usersSystem accounts
Test dataDevelopment/testingSample users, orders

Environment-Specific Seeds

Seeds run in all environments. For test-only data:

-- seeds/003_test_data.sql
-- Only populate if specific flag table exists
BEGIN;

DO $$
BEGIN
    -- Check if we should seed test data
    -- This is controlled by a flag in the environment's config
    INSERT INTO users (email, name)
    SELECT 'test@example.com', 'Test User'
    WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'test@example.com');
END $$;

COMMIT;

Implementation Order

When adding database changes:

Step 1: Design Schema

  1. Define tables and relationships
  2. Choose appropriate data types
  3. Plan indexes for query patterns

Step 2: Create Migration

  1. Create new numbered migration file
  2. Wrap in BEGIN/COMMIT
  3. Use IF NOT EXISTS for idempotency

Step 3: Test Migration

<plugin-root>/fullstack-typescript/system/system-run.sh database migrate <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database psql <component-name>  # Verify schema

Step 4: Add Seeds (if needed)

  1. Create seed file for initial data
  2. Use ON CONFLICT for idempotency

Step 5: Update Server DAL

The backend DAL layer must follow backend-standards — it defines CMDO architecture with strict layer separation, including repository patterns for database queries, connection pooling rules, and typed result mapping.


Database Commands

<plugin-root>/fullstack-typescript/system/system-run.sh database setup <component-name>        # Deploy PostgreSQL to k8s
<plugin-root>/fullstack-typescript/system/system-run.sh database teardown <component-name>     # Remove PostgreSQL from k8s
<plugin-root>/fullstack-typescript/system/system-run.sh database migrate <component-name>      # Run all migrations
<plugin-root>/fullstack-typescript/system/system-run.sh database seed <component-name>         # Run all seeds
<plugin-root>/fullstack-typescript/system/system-run.sh database reset <component-name>        # Full reset: teardown + setup + migrate + seed
<plugin-root>/fullstack-typescript/system/system-run.sh database port-forward <component-name> # Port forward to local
<plugin-root>/fullstack-typescript/system/system-run.sh database psql <component-name>         # Open psql shell

Multi-Database Projects

When a project has multiple databases:

components/
├── database-orders/      # Orders domain
│   └── migrations/
└── database-analytics/   # Analytics domain
    └── migrations/

Each database:

  • Has its own config section (database-orders, database-analytics)
  • Runs migrations independently
  • Has separate connection pools in server

Summary Checklist

Before committing database changes:

  • Migration file uses three-digit prefix
  • Migration wrapped in BEGIN/COMMIT
  • Uses IF NOT EXISTS or IF EXISTS for idempotency
  • Uses TIMESTAMPTZ for all timestamps
  • Uses UUID for primary keys
  • Seeds use ON CONFLICT for idempotency
  • No DROP TABLE without explicit approval
  • Migration tested locally

Input / Output

This skill defines no input parameters or structured output.


Related Skills

  • backend-standards — Delegate to this for DAL implementation patterns. Defines CMDO repository layer for database queries, connection pooling, and typed result mapping.
  • config-standards — Delegate to this for database connection configuration. Defines how host, port, database, user, and password fields are structured in the config component.
  • helm-standards — Delegate to this for Kubernetes deployment of database components. Defines how database secrets and connection strings are injected via Helm values.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall