القائمة
Implements database migrations using checkpoint-driven approach with verification at each step to ensure safe schema changes and data integrity
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
استنادا إلى تصنيف SOC المهني
Organize, update, and enrich project documentation through checkpoint-driven audits and post-implementation updates ensuring consistency and completeness
Data-driven sprint planning and retrospective analysis for Gastrobrain using GitHub Project #3 issues, historical velocity patterns, and sprint estimation diary insights. Generates realistic sprint plans with capacity analysis, sequencing strategy, and risk assessment. Conducts structured sprint retrospectives with developer interviews, estimation accuracy analysis, and diary entry generation.
Execute Phase 1 (Analysis & Understanding) of issue roadmaps through systematic 5-checkpoint technical analysis
Generates actionable, phase-based implementation roadmaps for GitHub issues. Creates comprehensive markdown documents with checkbox lists for analysis, implementation, testing, and documentation phases. Applies Gastrobrain-specific testing, localization, and database conventions automatically.
Strategic partner for roadmap planning, priority setting, and project health management. Provides 'wide picture' perspective balancing feature development with code quality, technical debt, and long-term sustainability. Use for: 'Review the roadmap', 'Help me prioritize', 'Is my milestone balanced?', 'Should I focus on features or quality?', 'Strategic review of 0.X.Y'.
User experience design specialist for Gastrobrain. Analyzes user goals, maps flows, designs information architecture, creates wireframes, and ensures accessibility before implementation. Reinforces 'Cultured & Flavorful' design identity. Use when: 'Design the UX for #XXX', 'Help me redesign [screen]', 'What should the user flow be?', 'I need wireframes for #XXX', 'Think through the information architecture'.
| name | gastrobrain-database-migration |
| description | Implements database migrations using checkpoint-driven approach with verification at each step to ensure safe schema changes and data integrity |
| version | 1.0.0 |
Implements database migrations from issue roadmaps using a checkpoint-based approach that ensures safety, verifiability, and data integrity through careful step-by-step progression.
Core Philosophy: Plan → Checkpoint → Verify → Next Checkpoint
Use this skill when:
DO NOT use this skill for:
The Problem with All-At-Once:
❌ BAD: Write entire migration + model + tests → Test → Find issues → Debug everything
Risk: High (data loss, broken rollback, model mismatch)
Clarity: Low (too many moving parts)
Debugging: Hard (which part failed?)
The Checkpoint Advantage:
✅ GOOD: CP1 (file) → verify → CP2 (schema) → verify → CP3 (rollback) → verify...
Risk: Low (each step verified before next)
Clarity: High (one concern at a time)
Debugging: Easy (issue isolated to current checkpoint)
1. Detect current branch: feature/XXX-description
2. Extract issue number: XXX
3. Load roadmap: docs/planning/0.1.X/ISSUE-XXX-ROADMAP.md
4. Locate Phase 2 (Implementation - Database section)
5. Scan lib/core/database/migrations/ for latest version
6. Determine new version: latest + 1
7. Identify impacted models from roadmap
Database Migration for Issue #XXX
Branch detected: feature/XXX-description
Roadmap: docs/planning/0.1.X/ISSUE-XXX-ROADMAP.md
Phase 2 (Database) Requirements:
[Summary of database changes needed]
Current State:
- Latest migration: v15 (migration_v15.dart)
- New migration: v16
- Impacted tables: [list]
- Impacted models: [list]
- Backward compatibility: [analysis]
Migration Checkpoint Plan:
1. Migration file creation
2. Schema changes (up method)
3. Rollback implementation (down method)
4. Model class updates
5. Seed data updates (if needed)
6. Migration tests
Total: 6 checkpoints
Ready to start Checkpoint 1/6? (y/n)
Checkpoint 1: Migration File Creation
Checkpoint 2: Schema Changes (up method)
Checkpoint 3: Rollback Implementation (down method) ⚠️ CRITICAL
Checkpoint 4: Model Class Updates
Checkpoint 5: Seed Data Updates
Checkpoint 6: Migration Tests ⚠️ MANDATORY
Not all migrations need all 6 checkpoints:
Simple column addition: 4-5 checkpoints
New table creation: 5-6 checkpoints
Complex data migration: 6+ checkpoints
==================
CHECKPOINT X/Y: [Clear checkpoint name]
Goal: [One sentence describing what this accomplishes]
[If X > 1, show context:]
Progress from previous checkpoints:
✓ Checkpoint 1: [what was accomplished]
✓ Checkpoint 2: [what was accomplished]
[... previous checkpoints ...]
Tasks for this checkpoint:
- [ ] Task 1
- [ ] Task 2
- [ ] Task 3
[Generate complete code for this checkpoint]
✓ Changes complete
Files modified:
- [file path]: [what changed]
Verification Steps:
1. [Specific verification step 1]
2. [Specific verification step 2]
3. [Specific verification step 3]
Commands to verify:
```bash
[Exact commands to run]
Database State After Checkpoint X: [Description of expected database state]
Ready to proceed to Checkpoint [X+1]/Y? (y/n)
[STOP - WAIT for user response]
### Response Handling
**If user responds "y" (checkpoint verified):**
✅ CHECKPOINT X/Y complete
Progress: X/Y checkpoints complete [progress bar]
Checkpoints Status: ✓ Checkpoint 1: [name] ✓ Checkpoint 2: [name] ✓ Checkpoint X: [name] [JUST COMPLETED] ○ Checkpoint X+1: [name] [... remaining checkpoints ...]
Ready for CHECKPOINT [X+1]/Y? (y/n)
**If user responds "n" (checkpoint failed verification):**
❌ CHECKPOINT X/Y verification failed
Let's debug before proceeding. It's critical to fix this checkpoint before continuing to ensure database integrity.
Common issues for [checkpoint type]:
What issue are you seeing? [Options: "doesn't compile", "migration fails", "rollback fails", "other"]
[WAIT for user input]
[After receiving issue:]
Analysis: [Diagnosis of the issue]
Fix: [Corrected code or specific fix instructions]
Try the fix: [Commands to verify]
Does it work now? (y/n)
[Continue debugging loop until checkpoint passes]
## Safety and Rollback Verification
### Checkpoint 3: Rollback Testing (CRITICAL)
This checkpoint is **mandatory** and **cannot be skipped**.
================== CHECKPOINT 3/6: Rollback Implementation Goal: Implement and verify down() method completely reverts schema changes
⚠️ CRITICAL CHECKPOINT - ROLLBACK SAFETY
Progress from previous checkpoints: ✓ Checkpoint 1: Migration file created (v16) ✓ Checkpoint 2: Schema changes implemented (up method works)
Tasks for this checkpoint:
[Generates complete down() method]
✓ Rollback implementation complete
Files modified:
Verification Steps - MANDATORY TESTING:
Apply migration (up):
# Run app to apply migration
flutter run
# Or run test that applies migration
Verify column/table exists:
sqlite3 path/to/gastrobrain.db ".schema table_name"
# Should show new column/table
Rollback (down):
# Run test that rolls back migration
flutter test test/core/database/migrations/migration_vX_test.dart --name "rollback"
Verify column/table removed:
sqlite3 path/to/gastrobrain.db ".schema table_name"
# Should NOT show new column/table
Re-apply migration (up):
flutter run
# Should work without errors
Verify column/table exists again:
sqlite3 path/to/gastrobrain.db ".schema table_name"
# Should show new column/table again
App still functional at each step:
Database State After Checkpoint 3:
⚠️ DO NOT PROCEED if rollback doesn't work perfectly!
Ready to proceed to Checkpoint 4/6? (y/n)
### Rollback Patterns by Change Type
**Adding Column:**
```dart
// up
await db.execute('ALTER TABLE table_name ADD COLUMN col_name TYPE NULL');
// down
await db.execute('ALTER TABLE table_name DROP COLUMN col_name');
Creating Table:
// up
await db.execute('CREATE TABLE IF NOT EXISTS table_name (...)');
// down
await db.execute('DROP TABLE IF EXISTS table_name');
Adding Index:
// up
await db.execute('CREATE INDEX IF NOT EXISTS idx_name ON table(col)');
// down
await db.execute('DROP INDEX IF EXISTS idx_name');
New columns MUST be nullable
// ✅ CORRECT - nullable column
ALTER TABLE meals ADD COLUMN meal_type TEXT NULL
// ❌ WRONG - not null without default breaks existing data
ALTER TABLE meals ADD COLUMN meal_type TEXT NOT NULL
Use NOT NULL only with DEFAULT
// ✅ CORRECT - not null with default value
ALTER TABLE meals ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0
Test with existing data
Consider data migration
CHECKPOINT 2/6: Schema Changes
[... implementation ...]
Backward Compatibility Verification:
Before applying migration:
1. App has existing data in [table]
2. Existing records: [count]
After applying migration:
1. Existing records still present: [verify count]
2. Existing records still queryable: [test query]
3. New column is nullable: ✓
4. No data loss: ✓
If adding NOT NULL column:
- Must have DEFAULT value
- Verify: sqlite3 db ".schema table" shows DEFAULT
Consumer Audit (MANDATORY for every schema change):
1. List every table this migration touches — including tables it creates
2. Grep each table name (and affected column names) across lib/ and test/
3. Audit the feature's table cluster, not just the altered table
(e.g. touching recipe_tags → also check tags, tag_types consumers)
4. For each consumer found, state: unaffected / updated / follow-up issue
5. Pay special attention to bulk-write paths that bypass migrations:
backup/restore (database_backup_service), import/export services
(sqflite is stringly-typed — drift compiles clean and passes analyze;
see #399/#400 where backup referenced columns that never shipped)
Ready to proceed to Checkpoint 3/6? (y/n)
==================
CHECKPOINT 4/6: Model Class Updates
Goal: Update Dart model to match new schema exactly
Progress from previous checkpoints:
✓ Checkpoint 1: Migration file created
✓ Checkpoint 2: Schema changes (up)
✓ Checkpoint 3: Rollback verified (down)
Tasks for this checkpoint:
- [ ] Add/modify field in model class
- [ ] Update toMap() method to include new field
- [ ] Update fromMap() method to parse new field
- [ ] Update copyWith() method if exists
- [ ] Ensure field type matches schema
- [ ] Make field nullable if column is nullable
[For adding a field:]
Model: lib/core/models/[model_name].dart
Changes needed:
1. Add field to class
2. Update toMap()
3. Update fromMap()
4. Update copyWith()
[Generates complete model updates]
✓ Model updated
Files modified:
- lib/core/models/[model_name].dart
Verification Steps:
1. Code compiles:
```bash
flutter analyze lib/core/models/[model_name].dart
Field type matches schema:
Field nullability matches schema:
Test CRUD operations:
flutter test test/core/models/[model_name]_test.dart
Test with database:
Model State After Checkpoint 4:
Ready to proceed to Checkpoint 5/6? (y/n)
### Standard Model Pattern
**Adding nullable field:**
```dart
class MyModel {
final String id;
final String? newField; // Nullable if column is NULL
MyModel({
required this.id,
this.newField, // Optional parameter
});
Map<String, dynamic> toMap() {
return {
'id': id,
'new_field': newField, // Can be null
};
}
factory MyModel.fromMap(Map<String, dynamic> map) {
return MyModel(
id: map['id'] as String,
newField: map['new_field'] as String?, // Nullable
);
}
MyModel copyWith({
String? id,
String? newField, // Nullable
}) {
return MyModel(
id: id ?? this.id,
newField: newField ?? this.newField,
);
}
}
Adding non-null field with default:
class MyModel {
final String id;
final bool isFavorite; // Non-null with default
MyModel({
required this.id,
this.isFavorite = false, // Default value
});
Map<String, dynamic> toMap() {
return {
'id': id,
'is_favorite': isFavorite ? 1 : 0,
};
}
factory MyModel.fromMap(Map<String, dynamic> map) {
return MyModel(
id: map['id'] as String,
isFavorite: (map['is_favorite'] as int?) == 1,
);
}
}
==================
CHECKPOINT 5/6: Seed Data Updates
Goal: Update seed data if needed for new schema
Progress from previous checkpoints:
✓ Checkpoint 1: Migration file created
✓ Checkpoint 2: Schema changes (up)
✓ Checkpoint 3: Rollback verified (down)
✓ Checkpoint 4: Model updated
Analysis: Should seed data be updated?
Question: Do seed data records need values for new field?
- If YES → Update seed data
- If NO → Leave as null (skip to Checkpoint 6)
[Based on issue requirements, provide guidance]
Decision: [UPDATE / SKIP]
[If UPDATE:]
Tasks for this checkpoint:
- [ ] Update lib/core/database/seed_data.dart
- [ ] Add values for new field in seed records
- [ ] Verify seed data works with new schema
- [ ] Test app initialization with seed data
[Generates seed data updates]
✓ Seed data updated
Files modified:
- lib/core/database/seed_data.dart
Verification Steps:
1. Clear app data:
```bash
# iOS Simulator
xcrun simctl uninstall booted com.example.gastrobrain
# Android Emulator
adb uninstall com.example.gastrobrain
Run app (will seed database):
flutter run
Verify seed data loaded:
Seed Data State After Checkpoint 5:
Ready to proceed to Checkpoint 6/6? (y/n)
[If SKIP:]
Seed data doesn't require updates because:
Skipping to Checkpoint 6/6 (Migration Tests).
Ready to proceed to Checkpoint 6/6? (y/n)
## Migration Testing Requirements
### Checkpoint 6: Migration Tests (MANDATORY)
================== CHECKPOINT 6/6: Migration Tests Goal: Create comprehensive tests verifying migration works correctly
⚠️ MANDATORY CHECKPOINT - Tests are required for all migrations
Progress from previous checkpoints: ✓ Checkpoint 1: Migration file created (vX) ✓ Checkpoint 2: Schema changes implemented (up) ✓ Checkpoint 3: Rollback verified (down) ✓ Checkpoint 4: Model updated ✓ Checkpoint 5: Seed data updated [or skipped]
Tasks for this checkpoint:
[Generates complete migration test file]
Test file: test/core/database/migrations/migration_vX_test.dart
Test coverage:
✓ Migration tests created
Files created:
Verification Steps:
Run migration tests:
flutter test test/core/database/migrations/migration_vX_test.dart
Expected output:
If tests fail:
Database State After Checkpoint 6:
🎉 MIGRATION COMPLETE!
═══════════════════════════════════════════ MIGRATION IMPLEMENTATION SUMMARY ═══════════════════════════════════════════
Migration: v[X] Issue: #[XXX] Changes: [Summary of schema changes]
Checkpoints Completed: ✓ Checkpoint 1: Migration file created ✓ Checkpoint 2: Schema changes (up) ✓ Checkpoint 3: Rollback verified (down) ✓ Checkpoint 4: Model updated ✓ Checkpoint 5: Seed data [updated/skipped] ✓ Checkpoint 6: Migration tests (all passing)
Files Modified:
Safety Verification: ✓ Rollback works (up → down → up tested) ✓ Backward compatible (existing data preserved) ✓ Model matches schema exactly ✓ All tests passing
Next Steps:
═══════════════════════════════════════════
Ready to commit migration? (y/n)
### Standard Test Structure
```dart
import 'package:flutter_test/flutter_test.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:gastrobrain/core/database/migrations/migration_vX.dart';
void main() {
late Database db;
late MigrationVX migration;
setUp(() async {
sqfliteFfiInit();
databaseFactory = databaseFactoryFfi;
db = await openDatabase(inMemoryDatabasePath, version: 1);
migration = MigrationVX();
});
tearDown(() async {
await db.close();
});
group('Migration vX', () {
test('applies schema changes (up)', () async {
// Apply migration
await migration.up(db);
// Verify schema changes
final result = await db.rawQuery(
"SELECT name FROM sqlite_master WHERE type='table' AND name='table_name'"
);
expect(result, isNotEmpty);
// Verify column exists
final columns = await db.rawQuery('PRAGMA table_info(table_name)');
expect(columns.any((col) => col['name'] == 'new_column'), isTrue);
});
test('reverts schema changes (down)', () async {
// Apply migration
await migration.up(db);
// Verify it was applied
var columns = await db.rawQuery('PRAGMA table_info(table_name)');
expect(columns.any((col) => col['name'] == 'new_column'), isTrue);
// Rollback
await migration.down(db);
// Verify it was reverted
columns = await db.rawQuery('PRAGMA table_info(table_name)');
expect(columns.any((col) => col['name'] == 'new_column'), isFalse);
});
test('preserves existing data', () async {
// Create table with old schema
await db.execute('''
CREATE TABLE table_name (
id TEXT PRIMARY KEY,
existing_field TEXT
)
''');
// Insert test data
await db.insert('table_name', {'id': '1', 'existing_field': 'test'});
// Apply migration
await migration.up(db);
// Verify data still exists
final result = await db.query('table_name');
expect(result.length, equals(1));
expect(result.first['id'], equals('1'));
expect(result.first['existing_field'], equals('test'));
});
test('supports up → down → up cycle', () async {
// First up
await migration.up(db);
var columns = await db.rawQuery('PRAGMA table_info(table_name)');
expect(columns.any((col) => col['name'] == 'new_column'), isTrue);
// Down
await migration.down(db);
columns = await db.rawQuery('PRAGMA table_info(table_name)');
expect(columns.any((col) => col['name'] == 'new_column'), isFalse);
// Second up
await migration.up(db);
columns = await db.rawQuery('PRAGMA table_info(table_name)');
expect(columns.any((col) => col['name'] == 'new_column'), isTrue);
});
});
}
Show clear progress after each checkpoint:
Migration Progress: 3/6 checkpoints complete ████░░ 50%
✓ Checkpoint 1: Migration file created [COMPLETE]
✓ Checkpoint 2: Schema changes (up) [COMPLETE]
✓ Checkpoint 3: Rollback verified (down) [COMPLETE]
⧗ Checkpoint 4: Model updates [CURRENT]
○ Checkpoint 5: Seed data updates
○ Checkpoint 6: Migration tests
Checkpoints: 5 (skip seed data)
CP1: Create migration file
CP2: ALTER TABLE table ADD COLUMN col TYPE NULL
CP3: ALTER TABLE table DROP COLUMN col
CP4: Add field to model (nullable)
CP5: Skip (nullable field, no seed update needed)
CP6: Tests
Checkpoints: 6
CP1: Create migration file
CP2: ALTER TABLE table ADD COLUMN col TYPE NOT NULL DEFAULT value
CP3: ALTER TABLE table DROP COLUMN col
CP4: Add field to model (with default)
CP5: Update seed data with default values
CP6: Tests
Checkpoints: 6
CP1: Create migration file
CP2: CREATE TABLE IF NOT EXISTS table (...)
CP3: DROP TABLE IF EXISTS table
CP4: Create new model class
CP5: Add seed data for new table
CP6: Tests
Checkpoints: 4 (no model/seed changes)
CP1: Create migration file
CP2: CREATE INDEX IF NOT EXISTS idx ON table(col)
CP3: DROP INDEX IF EXISTS idx
CP4: No model changes (skip)
CP5: No seed changes (skip)
CP6: Tests
Checkpoints: 7 (add data migration)
CP1: Create migration file
CP2: ALTER TABLE (add new column)
CP3: Data migration (transform existing data)
CP4: Rollback (revert both schema and data)
CP5: Update model
CP6: Update seed data
CP7: Tests (including data migration)
Checkpoint 1 (File Creation):
Checkpoint 2 (Schema up):
Checkpoint 3 (Rollback down):
Checkpoint 4 (Model):
Checkpoint 5 (Seed Data):
Checkpoint 6 (Tests):
This skill succeeds when:
lib/core/database/migrations/: Existing migration patternslib/core/models/: Model structure and patternslib/core/database/seed_data.dart: Seed data formattest/core/database/migrations/: Migration test patternsRemember: Database migrations are permanent. Each checkpoint verification prevents data loss and broken deployments. Never skip checkpoints, especially rollback testing.