database-standards

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:

| Pattern | Why | |---------|-----| | BEGIN/COMMIT | Atomic transaction | | IF NOT EXISTS | Idempotent (safe to re-run) | | TIMESTAMPTZ | Timezone-aware timestamps | | gen_random_uuid() | PostgreSQL native UUIDs |

Migration Types

| Type | When | Example | |------|------|---------| | Schema creation | New tables | CREATE TABLE IF NOT EXISTS | | Schema modification | Add/modify columns | ALTER TABLE ... ADD COLUMN IF NOT EXISTS | | Index creation | Performance | CREATE INDEX IF NOT EXISTS | | Data migration | Transform existing data | UPDATE ... WHERE ... | | Constraint addition | Add validation | ALTER TABLE ... ADD CONSTRAINT |

Column Conventions

| Type | PostgreSQL Type | Notes | |------|-----------------|-------| | Primary key | UUID | Use gen_random_uuid() | | Timestamps | TIMESTAMPTZ | Always timezone-aware | | Money | NUMERIC(19,4) | Never FLOAT or MONEY | | Enums | VARCHAR + CHECK | Or PostgreSQL ENUM type | | JSON | JSONB | Never 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:

| Pattern | Why | |---------|-----| | BEGIN/COMMIT | Atomic transaction | | ON CONFLICT DO NOTHING | Idempotent | | ON CONFLICT DO UPDATE | Upsert when updates needed |

Seed Categories

| Category | Purpose | Example | |----------|---------|---------| | Lookup data | Reference tables | Status codes, countries | | Admin data | Initial admin users | System accounts | | Test data | Development/testing | Sample 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.