database-scaffolding

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. Delegate to the techpack-settings skill for directory path resolution — it maps component type (database) + name to a filesystem path (e.g., type=database, name=app-db → components/databases/app-db/). Database components support multiple instances (e.g., database-app-db/, database-analytics-db/).

components/database[-<name>]/
├── package.json              # Component package metadata
├── 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, database operations are performed via the system CLI:

<plugin-root>/fullstack-typescript/system/system-run.sh database setup <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database teardown <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database migrate <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database seed <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database reset <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database port-forward <component-name>
<plugin-root>/fullstack-typescript/system/system-run.sh database psql <component-name>

Prerequisites

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:

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:

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:

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)

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

TypeScript Type (types/database.ts)

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

JSON Schema (schemas/config.schema.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"]
  }
}

Scaffold Spec

To scaffold a database component, build a spec and invoke the engine:

<plugin-root>/fullstack-typescript/system/system-run.sh scaffolding apply --spec spec.json

Variables

| Variable | Source | |----------|--------| | PROJECT_NAME | From sdd-settings.yaml project name |

Operations

{
  "target_dir": "<project-root>",
  "base_dir": "<plugin-root>/skills",
  "variables": { "PROJECT_NAME": "<project-name>" },
  "operations": [
    {
      "type": "template_dir",
      "source": "components/database/database-scaffolding/templates",
      "dest": "components/databases/<database-name>"
    }
  ]
}

No conditions needed.

Input

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

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

Output

Schema: [schemas/output.schema.json](./schemas/output.schema.json)

Returns the scaffolding engine result: created files, directories, and scripts; skipped paths; errors; and a human-readable summary.

Related Skills

• techpack-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.