LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/database-standards
database-standards
VerifiedPostgreSQL 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:
- Version-controlled schema via numbered migrations
- Repeatable seed data for development and testing
- Idempotent operations for safe re-runs
- 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
.sqlextension- 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:
- Create a new migration that reverses the change
- Never modify or delete existing migrations
- Never use
DROP TABLEwithout 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
- Define tables and relationships
- Choose appropriate data types
- Plan indexes for query patterns
Step 2: Create Migration
- Create new numbered migration file
- Wrap in
BEGIN/COMMIT - Use
IF NOT EXISTSfor 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)
- Create seed file for initial data
- Use
ON CONFLICTfor 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 EXISTSorIF EXISTSfor idempotency - Uses
TIMESTAMPTZfor all timestamps - Uses
UUIDfor primary keys - Seeds use
ON CONFLICTfor idempotency - No
DROP TABLEwithout 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 howhost,port,database,user, andpasswordfields 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.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 117k | Updated | Claude CodeCodex | Verified | Install |
| 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: 117k | Updated | Claude CodeCodex | Verified | Install |