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.