Database Scaffolding

---
name: database-scaffolding
description: Scaffolds PostgreSQL database component with migrations, seeds, and management scripts.
user-invocable: false
---

# Database Scaffolding Skill

Creates database component structure for PostgreSQL-based projects.

## What It Creates

The directory path depends on the component name as defined in `.sdd/sdd-settings.yaml` (refer to the `project-settings` skill for directory mappings). Database components support multiple instances (e.g., `database-app-db/`, `database-analytics-db/`).

```text
components/database[-<name>]/
├── package.json              # npm scripts (call sdd-system CLI)
├── README.md                 # Component documentation
├── migrations/
│   └── 001_initial_schema.sql
└── seeds/
    └── 001_seed_data.sql
```

## Template Variables

| Variable | Description |
|----------|-------------|
| `{{PROJECT_NAME}}` | Project name for naming and comments |

## When to Use

Use when your project needs:
- PostgreSQL database with version-controlled schema
- Migration-based schema management
- Seed data for development/testing
- Scripts for database lifecycle management

## Usage

After scaffolding, the database component provides npm scripts that call the sdd-system CLI:

```bash
# From components/database/ (path depends on component name)
npm run setup        # Deploy PostgreSQL to k8s
npm run teardown     # Remove PostgreSQL from k8s
npm run migrate      # Run all migrations in order
npm run seed         # Run all seed files in order
npm run reset        # Full reset: teardown + setup + migrate + seed
npm run port-forward # Port forward to local
npm run psql         # Open psql shell
```

Or use the CLI directly:

```bash
sdd-system database setup <component-name>
sdd-system database migrate <component-name>
sdd-system database seed <component-name>
sdd-system database reset <component-name>
```

## Prerequisites

- `sdd-system` CLI available in PATH (installed via the SDD plugin's npm package)

The CLI commands require:
- PostgreSQL 14+ (client tools: `psql`, `createdb`, `dropdb`)
- Environment variables set:
  - `PGHOST` - Database host
  - `PGPORT` - Database port
  - `PGUSER` - Database user
  - `PGPASSWORD` - Database password
  - `PGDATABASE` - Database name

## Migration Conventions

Create numbered SQL files for sequential execution:

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

Each migration should:
- Be wrapped in `BEGIN`/`COMMIT` for transactional safety
- Be idempotent where possible (use `IF NOT EXISTS`)
- Delegate to the `postgresql` skill for SQL syntax patterns, index strategies, and constraint conventions

## Seed Conventions

Create numbered SQL files for seed data:

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

Each seed file should:
- Use `ON CONFLICT DO NOTHING` for idempotency
- Be wrapped in transactions
- Delegate to the `postgresql` skill for seed data patterns and idempotent insert conventions

## Integration with Server Component

The database component works alongside the server component:

```text
components/
├── database/           # Schema, migrations, seeds
│   └── migrations/
└── server/
    └── src/dal/        # Data access layer queries
```

The server's DAL layer imports types and executes queries against the schema defined in the database component.

## Config Schema

When scaffolding a database component, the following config section is added to `components/config/`:

### Minimal Config (envs/default/config.yaml)

```yaml
database-{name}:
  host: localhost
  port: 5432
  name: appdb
  user: app
  passwordSecret: db-credentials
  pool: 10
```

### TypeScript Type (types/database.ts)

```typescript
export type DatabaseConfig = Readonly<{
  host: string;
  port: number;
  name: string;
  user: string;
  passwordSecret: string;
  pool: number;
}>;
```

### JSON Schema (schemas/config.schema.json)

```json
{
  "database-{name}": {
    "type": "object",
    "properties": {
      "host": { "type": "string" },
      "port": { "type": "number", "default": 5432 },
      "name": { "type": "string" },
      "user": { "type": "string" },
      "passwordSecret": { "type": "string" },
      "pool": { "type": "number", "default": 10 }
    },
    "required": ["host", "port", "name", "user", "passwordSecret"]
  }
}
```

---

## Input

Schema: [`schemas/input.schema.json`](./schemas/input.schema.json)

Accepts database name and optional project name for migration and seed template generation.

## Root Package.json Update

After scaffolding, update the root `package.json`:

1. If root `package.json` doesn't exist, create it from the `project-scaffolding` skill template (`templates/project/package.json`)
2. Add component scripts:
   - `"<name>:setup": "npm run setup -w components/databases/<name>"`
   - `"<name>:migrate": "npm run migrate -w components/databases/<name>"`
   - `"<name>:seed": "npm run seed -w components/databases/<name>"`
   - `"<name>:reset": "npm run reset -w components/databases/<name>"`

## Related Skills

- `project-settings` — Authoritative source for database component settings schema and directory mappings.
- `postgresql` — Delegate to this for SQL patterns, Docker/K8s deployment, schema management, and performance tuning. Provides migration SQL templates, seed data patterns, and introspection queries.
- `backend-scaffolding` — Generates the server component that contains the DAL layer querying this database. The server's repository layer imports database connection config and executes queries against the schema defined here.