LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/database-scaffolding
database-scaffolding
VerifiedScaffolds PostgreSQL database component with migrations, seeds, and management scripts.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 6.0 KB |
| schemas/input.schema.json | config | 519 B |
| schemas/output.schema.json | config | 1.4 KB |
| templates/README.md | markdown | 2.6 KB |
| templates/migrations/001_initial_schema.sql | other | 910 B |
| templates/package.json | manifest | 62 B |
| templates/seeds/001_seed_data.sql | other | 711 B |
SKILL.md
View on GitHub---
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`. 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/`).
```text
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:
```bash
<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:
```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"]
}
}
```
---
## Scaffold Spec
To scaffold a database component, build a spec and invoke the engine:
```bash
<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
```json
{
"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.
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: 119k | 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: 119k | 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: 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: 118k | Updated | Claude CodeCodex | Verified | Install |