db-preflight

Files (1)

• SKILL.md

SKILL.md (first lines)

# DB Preflight Skill

## Purpose
Catch unapplied database migrations before they cause runtime 500 errors. Scans the project's `DBScripts/` folder, compares against a list of known-applied scripts, and reports which ones are missing. Maps runtime DB errors back to the specific DBScript that fixes them.

## Activation
- **Session start**: SessionStart hook surfaces unapplied scripts
- **Before build**: build-check runs this as a pre-flight step
- **On DB error**: When a runtime error mentions "Invalid object name", "Invalid column name", or similar schema errors

## How It Works

### State (per project)
| File | Purpose |
|------|---------|
| `.claude/idev/db-preflight/applied.json` | List of scripts the user has confirmed are applied |
| `.claude/idev/db-preflight/known-errors.json` | Maps DB error patterns to their fixing DBScript |

### Scan Process
```
1. Glob for DBScripts/**/*.sql (or DBScripts/**/*.ps1, DBScripts/**/*.sh)
2. Extract script names (sort by date prefix if present: YYYY-MM-DD-*.sql)
3. Compare against applied.json
4. Report unapplied scripts with their date and description
```

### Detection Patterns
The skill detects DBScript folders using these conventions:
```
DBScripts/
DBScripts/YYYY-MM-DD/
db-scripts/
migrations/
```

### Script Naming Convention
Most DBScript folders follow: `YYYY-MM-DD-description.sql`
The date prefix determines execution order.

## Phase 1: Scan for DBScripts

```bash
# Find all SQL scripts in DBScripts folders
find . -path "*/DBScripts/*.sql" -not -path "*/node_modules/*" -not -path "*/bin/*" -not -path "*/obj/*" | sort
```

Or use Glob: `**/DBScripts/**/*.sql`

## Phase 2: Compare Against Applied

Read `.claude/idev/db-preflight/applied.json`:
```json
{
  "applied": [
    "2026-01-15-add-orders-table.sql",
    "2026-01-20-add-customer-column.sql"
  ],
  "lastChecked": "2026-07-29"
}
```

Scripts in DBScripts/ NOT in the `applied` list are unapplied.

## Phase 3: Report

### At session start (brief):
```
[db-preflight] 2 unapplied DBScripts:
  - 2026-07-25-add-role-alias-table.sql
  - 2026-07-28-add-support-portal-mailbox.sql
Run these before building or expect "Invalid object name" errors.
```

### Before build (detailed):
```
DB Preflight Check:
  Total DBScripts: 12
  Applied: 10
  Unapplied: 2
  
  ⚠ UNAPPLIED:
  1. 2026-07-25-add-role-alias-table.sql
     Creates: RoleAlias table
     Affects: Any code referencing RoleAlias entity
     
  2. 2026-07-28-add-support-portal-mailbox.sql
     Creates: SupportPortalMailbox table, Adds: MailboxPassword column
     Affects: Support Portal feature
     
  Apply these scripts to the dev database before building.
```

## Phase 4: Error Mapping

When a runtime DB error occurs, map it to the fixing script:

### Error Pattern Matching
```
"Invalid object name 'RoleAlias'" 
  → Grep DBScripts for "CREATE TABLE.*RoleAlias" or "RoleAlias"
  → Found: 2026-07-25-add-role-alias-table.sql
  → Report: "This error is fixed by applying 2026-07-25-add-role-alias-table.sql"

"Invalid column name 'MailboxPassword'"
  → Grep DBScripts for "MailboxPassword" or "ALTER TABLE.*ADD.*MailboxPassword"
  → Found: 2026-07-28-add-support-portal-mailbox.sql
  → Report: "This error is fixed by applying 2026-07-28-add-support-portal-mailbox.sql"
```

### Building the Error Map
On first run (or when DBScripts change), scan all scripts and extract:
- Tables created (`CREATE TABLE`)
- Columns added (`ALTER TABLE.*ADD`)
- Views created (`CREATE VIEW`)
- Stored procedures (`CREATE PROCEDURE`)

Write to `.claude/idev/db-preflight/known-errors.json`:
```json
{

From TheophilusChinomona/idev, MIT.

More from TheophilusChinomona/idev