🔬 STORED PROCS ANALYZED → 🧠 BUSINESS RULES EXTRACTED → 📐 SCHEMA MIGRATED!

╔═══════════════════════════════════════════════════════════════════╗
║                                                                   ║
║   ███████╗██████╗ ███████╗ ██████╗                               ║
║   ██╔════╝██╔══██╗██╔════╝██╔════╝                               ║
║   ███████╗██████╔╝█████╗  ██║                                    ║
║   ╚════██║██╔═══╝ ██╔══╝  ██║                                    ║
║   ███████║██║     ███████╗╚██████╗                               ║
║   ╚══════╝╚═╝     ╚══════╝ ╚═════╝                               ║
║                                                                   ║
║         🗃️  SPEC-DRIVEN DB MIGRATION 🗃️                          ║
║                                                                   ║
╚═══════════════════════════════════════════════════════════════════╝

🔬 STORED PROCS ANALYZED → 🧠 BUSINESS RULES EXTRACTED → 📐 SCHEMA MIGRATED!

MISSION: Reverse-engineer a LEGACY DATABASE BEHEMOTH with Spec2Cloud! Extract 80+ stored procedures, decode 30+ triggers, and migrate to modern cloud — WITHOUT LOSING A SINGLE BUSINESS RULE! 🚀🗄️

🎮 LEVEL SELECT

🌾 BUSINESS DOMAIN: GreenHarvest Agricultural Cooperative
📂 CATEGORY: Spec-Driven Development
⚡ PRIORITY: P3
🛠️ TECH STACK: SQL Server • PostgreSQL • Python • Spec2Cloud
🏢 ORG: EmeaAppGbb

🕹️ PLAYER STATS

┌─────────────────────────────────────────────┐
│  BEFORE (SQL Server 2012) 🗄️                │
├─────────────────────────────────────────────┤
│  📊 120+ tables (decades of schema evolution)│
│  🔮 80+ stored procedures (hidden logic)    │
│  ⚡ 30+ triggers (implicit dependencies)    │
│  🧮 Computed columns (complex formulas)     │
│  🔗 Cross-DB references (commodity pricing) │
│  📅 SQL Agent jobs for batch sync           │
│  🤷 Business rules? Only the DB knows...    │
└─────────────────────────────────────────────┘

┌─────────────────────────────────────────────┐
│  AFTER (Cloud PostgreSQL) ☁️✨              │
├─────────────────────────────────────────────┤
│  📋 Complete spec: every table, proc, trigger│
│  🧠 Business rules extracted & documented   │
│  🐘 Modern PostgreSQL schema                │
│  🐍 Python services (from stored procs)     │
│  ✅ Validated migration (100% coverage)     │
│  📖 Living documentation (Spec2Cloud spec)  │
│  🎯 Zero business logic lost!               │
└─────────────────────────────────────────────┘

💥 BOSS BATTLES

Defeat these database migration demons:

👾 BOSS	💀 ATTACK PATTERN	⚔️ YOUR WEAPON
The Stored Proc Fortress	80+ procedures hiding business logic	Spec2Cloud extraction
The Trigger Tangle	Implicit execution order dependencies	Specification mapping
The Computed Column Chaos	Complex formulas everywhere	Formula documentation
The Cross-DB Dragon	Linked servers and remote queries	Spec-driven refactor
The Lost Knowledge	"Only Bob knows how this works"	Automated reverse engineering
The Validation Void	Did we migrate everything? 🤷	Spec-generated test suite

🎯 QUEST OBJECTIVES

🏆 Explore Legacy DB — Review schema, run stored procs
🏆 Schema Analysis — Spec2Cloud extracts tables & relationships
🏆 Business Rule Extraction — Analyze procs, triggers, UDFs
🏆 Generate Migration Spec — Complete specification with targets
🏆 Create Target Schema — PostgreSQL DDL from spec
🏆 Extract Logic — Python services from stored procedures
🏆 Migrate Data — Execute data migration scripts
🏆 Replace Triggers — Event handlers or PostgreSQL triggers
🏆 Validate — Spec-generated validation queries

🎨 ARCADE THEMES

╔════════════════════════════════════════╗
║  🔊 SOUND EFFECTS:                     ║
║                                        ║
║  🔬 "STORED PROCS ANALYZED!"           ║
║  🧠 "BUSINESS RULES EXTRACTED!"        ║
║  📐 "SCHEMA MIGRATED!"                 ║
║  🔍 "TRIGGER DECODED!"                 ║
║  🐘 "POSTGRESQL SCHEMA LIVE!"          ║
║  ✅ "VALIDATION PASSED!"               ║
║  🎯 "ZERO DATA LOSS!"                  ║
╚════════════════════════════════════════╝

🚀 POWER-UPS UNLOCKED

Complete this migration and gain:

🔬 Database Reverse Engineering — Spec2Cloud mastery
🧠 Business Rule Extraction — From stored procedures
📋 Spec-Driven Migration — Spec = plan + acceptance criteria
🐘 PostgreSQL Migration — Modern cloud database
🐍 Stored Proc → Python — Extract logic to services
⚡ Trigger Replacement — Event-driven architecture
✅ Migration Validation — Automated completeness checks

⏱️ SPEEDRUN TIME

Estimated Duration: 5–7 hours ⏳

┌──────────────────────────────────────┐
│  🥉 BRONZE:  Schema migrated         │
│  🥈 SILVER:  +All business rules extracted│
│  🥇 GOLD:    +100% validation passing│
└──────────────────────────────────────┘

🛠️ STARTER PACK (Prerequisites)

Gear up for migration:

✅ SQL Server experience (T-SQL, stored procedures, triggers)
✅ Basic PostgreSQL knowledge
✅ Python experience (for extracted logic)
✅ Docker Desktop (for SQL Server + PostgreSQL)

📂 LOOT TABLE (Key Files)

greenharvest-db/
├── Schema/
│   ├── Tables/
│   │   ├── CropManagement/        # 🌾 30+ crop tables
│   │   ├── Inventory/             # 📦 25+ inventory tables
│   │   ├── Members/               # 👥 15+ member tables
│   │   ├── Trading/               # 💰 20+ trading tables
│   │   └── Reference/            # 📚 30+ reference tables
│   ├── StoredProcedures/
│   │   ├── 🌱 CropPlanning/       # Rotation & planning
│   │   ├── 📊 Inventory/          # Stock management
│   │   ├── 💵 Pricing/            # Price calculations
│   │   ├── 🧮 Settlement/         # Coop settlements
│   │   └── 📈 Reporting/          # Report data
│   ├── Triggers/
│   │   ├── 📝 AuditTriggers/      # Change tracking
│   │   ├── 🛡️  IntegrityTriggers/  # Business rules
│   │   └── 🔄 SyncTriggers/       # Data sync
│   └── Functions/
│       ├── 🧮 Scalar/             # Calculations
│       └── 📊 TableValued/        # Complex filtering
├── Specs/                         # ← SPEC2CLOUD OUTPUT!
│   ├── 📋 schema-spec/
│   ├── 🧠 business-rules/
│   ├── 🗺️  migration-plan/
│   └── ✅ validation/
└── Migration/
    ├── 🐘 PostgreSQL/             # ← YOUR TARGET!
    │   ├── schema.sql
    │   └── data-migration.sql
    └── 🐍 Python/
        └── services/              # Extracted logic

🌊 LEVEL PROGRESSION

🎮 BRANCH FLOW

            main (🏁 Complete lab)
              │
    ┌─────────┴──────┬────────┬────────┬────────┐
 legacy     step-1   step-2   step-3   step-4   step-5
  (🗄️)        ↓        ↓        ↓        ↓        ↓
          Schema   Business  Migration  Target   Validate
          Analysis  Rules    Spec       Impl

Migration Phases:

🔍 Schema Analysis — Reverse-engineer 120+ tables
🧠 Business Rule Extraction — 80+ stored procs decoded
📋 Migration Spec — Complete specification generated
🐘 Target Implementation — PostgreSQL + Python services
✅ Validation — Prove migration completeness

🗃️ DATABASE COMPLEXITY

By The Numbers 📊

┌────────────────────────────────────┐
│  120+ tables                       │
│   80+ stored procedures            │
│   30+ triggers                     │
│   20+ user-defined functions       │
│   40+ views (some indexed)         │
│  dozens of computed columns        │
│  decades of business knowledge 🧠  │
└────────────────────────────────────┘

Sample Business Rules 🌾

🔄 Crop rotation calculated by soil type + previous crops
🌡️ Planting dates enforced by growing season constraints
📏 Yield per acre computed from harvest data
⚖️ Unit conversions (bushels ↔ tonnes ↔ hundredweight)
💰 Settlement calculations based on grade/quality matrices

🎯 ACHIEVEMENT UNLOCKED CONDITIONS

✅ SQL Server DB created with 120+ tables
✅ Stored procedures execute with sample data
✅ Triggers fire and enforce business rules
✅ Spec2Cloud generates complete schema spec
✅ Business rules extracted from stored procs
✅ PostgreSQL schema created from spec
✅ Python services replicate stored proc logic
✅ Data migration completes successfully
✅ Validation queries pass (100% coverage)
✅ APPMODLAB.md complete with frontmatter

🌟 FINAL BOSS: THE VALIDATION SUITE

╔══════════════════════════════════════════════╗
║                                              ║
║   Run spec-generated validation tests:      ║
║                                              ║
║   ✅ Table count matches                    ║
║   ✅ Row counts match                       ║
║   ✅ Computed values match                  ║
║   ✅ Business logic produces same results   ║
║   ✅ Triggers enforce same rules            ║
║                                              ║
║   100% PASS = MIGRATION COMPLETE! 🏆        ║
║                                              ║
╚══════════════════════════════════════════════╝

🎊 VICTORY SCREEN

YOU DEFEATED:

🗄️ Legacy SQL Server 2012
🔮 Hidden business logic in stored procs
⚡ Implicit trigger dependencies
🧮 Undocumented computed columns
🤷 "Tribal knowledge" syndrome

YOU UNLOCKED:

📋 Complete database specification
🧠 Documented business rules
🐘 Modern PostgreSQL schema
🐍 Extracted Python services
✅ Validated, lossless migration
📖 Living documentation!

┌─────────────────────────────────────────┐
│                                         │
│   🎮 DATABASE MIGRATION MASTERED 🎮     │
│                                         │
│   From chaos to clarity! 🌟             │
│                                         │
└─────────────────────────────────────────┘

🎵 Now playing: Data Dreams by Spec Master 💿
🔬 Status: 120 TABLES ANALYZED — ZERO BUSINESS RULES LOST!

📖 Full lab guide: See APPMODLAB.md for spec-driven migration walkthrough
🏆 Credits: Built with 🗃️ by EmeaAppGbb

📋 SOLUTION WALKTHROUGH

Complete step-by-step execution using GitHub Copilot CLI (gh copilot).
All outputs are on the solution-final branch under assets/outputs/.

Setup

cd C:\code\gbb\labs\appmodlab-spec-driven-database-migration
git checkout main && git checkout -b solution-final
New-Item -ItemType Directory -Force -Path assets/outputs

Step 01 — Explore Legacy Database

Tag: step-01-explore-legacy-db · Output: assets/outputs/step-01-explore-legacy-db.md

gh copilot -- -p "Analyze the GreenHarvest Agricultural Co-op legacy SQL Server database in this repository. Explore Schema/ directory (Tables, StoredProcedures, Functions, Triggers, Views), Data/ (SeedData, SampleData), Specs/, and Migration/ folders. Produce a comprehensive analysis document at assets/outputs/step-01-explore-legacy-db.md covering: 1) Complete inventory of all database objects (6 tables across CropManagement, Members, Inventory, Trading schemas), 2) Column details with data types, constraints, defaults, indexes, 3) Foreign key relationships and referential integrity map, 4) Stored procedures with business rule extraction, 5) Scalar function fn_CalculateYieldBushels with conversion factors, 6) Trigger tr_AuditHarvestChanges behavior and audit log structure, 7) View vw_FieldProductivity definition and joins, 8) Data patterns from seed/sample data, 9) SQL Server-specific features (GEOGRAPHY, IDENTITY, MONEY, computed columns, PERSISTED, GETDATE, SUSER_SNAME, FOR JSON AUTO)." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-01.txt"
git add assets/outputs/step-01* && git commit -m "Step 01: Explore Legacy Database" && git tag step-01-explore-legacy-db

Step 02 — Generate Database Specification

Tag: step-02-generate-db-spec · Output: assets/outputs/step-02-database-specification.md

gh copilot -- -p "Generate a comprehensive database specification document for the GreenHarvest Agricultural Co-op SQL Server database. Read all files in Schema/, Data/, and Specs/. Create a detailed spec at assets/outputs/step-02-database-specification.md with: complete table catalog (every column, type, constraint), PK/FK relationships, index inventory, stored procedure signatures with business rules, scalar function spec, trigger spec, view definition, cross-reference matrix, and SQL Server-specific feature catalog." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-02.txt"
git add assets/outputs/step-02* && git commit -m "Step 02: Generate Database Specification" && git tag step-02-generate-db-spec

Step 03 — Analyze Migration Complexity

Tag: step-03-analyze-complexity · Output: assets/outputs/step-03-migration-complexity.md

gh copilot -- -p "Analyze the migration complexity for migrating the GreenHarvest SQL Server database to PostgreSQL. Create assets/outputs/step-03-migration-complexity.md with: per-table/proc complexity ratings (Low/Medium/High/Critical), SQL Server to PostgreSQL feature mapping table, identified blockers and risks, effort estimation per object, recommended migration order, and risk mitigation strategies." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-03.txt"
git add assets/outputs/step-03* && git commit -m "Step 03: Analyze Migration Complexity" && git tag step-03-analyze-complexity

Step 04 — Design Target Schema

Tag: step-04-design-target-schema · Output: assets/outputs/step-04-target-schema.md

gh copilot -- -p "Design the modern target PostgreSQL schema for the GreenHarvest database migration. Create assets/outputs/step-04-target-schema.md with: design principles, schema mapping to snake_case, complete target DDL for all 6 tables, data type conversion table, PL/pgSQL functions, PostgreSQL trigger functions, view definitions, index strategy, and PostGIS extension requirements." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-04.txt"
git add assets/outputs/step-04* && git commit -m "Step 04: Design Target Schema" && git tag step-04-design-target-schema

Step 05 — Generate Migration Scripts

Tag: step-05-migration-scripts · Output: assets/outputs/step-05-migration-scripts.md

gh copilot -- -p "Generate complete migration scripts for the GreenHarvest SQL Server to PostgreSQL migration. Create assets/outputs/step-05-migration-scripts.md with: DDL migration (CREATE statements, indexes, constraints, PostGIS), PL/pgSQL functions, audit trigger, view creation, DML seed data conversion, stored procedure migration to PL/pgSQL, rollback scripts, and execution order." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-05.txt"
git add assets/outputs/step-05* && git commit -m "Step 05: Generate Migration Scripts" && git tag step-05-migration-scripts

Step 06 — Create Data Validation

Tag: step-06-data-validation · Output: assets/outputs/step-06-data-validation.md

gh copilot -- -p "Create comprehensive data validation queries for the GreenHarvest database migration. Create assets/outputs/step-06-data-validation.md with: row count validation, schema validation, computed column verification, FK integrity checks, index existence verification, stored procedure output comparison, trigger behavior validation, view output comparison, seed data verification, and edge case tests." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-06.txt"
git add assets/outputs/step-06* && git commit -m "Step 06: Create Data Validation" && git tag step-06-data-validation

Step 07 — Document Migration Plan

Tag: step-07-migration-plan · Output: assets/outputs/step-07-migration-plan.md

gh copilot -- -p "Create a comprehensive migration plan for the GreenHarvest SQL Server to PostgreSQL migration. Create assets/outputs/step-07-migration-plan.md with: executive summary, pre-migration phase (docker-compose setup), migration phases with timeline, rollback procedures, risk register, validation checklist, communication plan, post-migration tasks, and go/no-go decision criteria." --allow-all-tools --yolo 2>&1 | Tee-Object -FilePath "assets/outputs/step-07.txt"
git add assets/outputs/step-07* && git commit -m "Step 07: Document Migration Plan" && git tag step-07-migration-plan

Push

git push origin solution-final --tags

Output Summary

Step	Output File	Description
01	`step-01-explore-legacy-db.md`	Legacy database exploration and analysis
02	`step-02-database-specification.md`	Comprehensive database specification
03	`step-03-migration-complexity.md`	Migration complexity assessment
04	`step-04-target-schema.md`	Target PostgreSQL schema design
05	`step-05-migration-scripts.md`	DDL + DML migration scripts
06	`step-06-data-validation.md`	Data validation queries and procedures
07	`step-07-migration-plan.md`	Full migration plan with rollback

🌌 May your schemas be normalized and your migrations be lossless! 🌌

Name		Name	Last commit message	Last commit date
Latest commit History 9 Commits
Data		Data
Migration		Migration
Schema		Schema
Specs/schema-spec		Specs/schema-spec
assets		assets
.gitignore		.gitignore
APPMODLAB.md		APPMODLAB.md
README.md		README.md
SPEC2CLOUD.MD		SPEC2CLOUD.MD
docker-compose.yml		docker-compose.yml
imagegen.md		imagegen.md

Folders and files

Latest commit

History

Repository files navigation

🔬 STORED PROCS ANALYZED → 🧠 BUSINESS RULES EXTRACTED → 📐 SCHEMA MIGRATED!

🎮 LEVEL SELECT

🕹️ PLAYER STATS

💥 BOSS BATTLES

🎯 QUEST OBJECTIVES

🎨 ARCADE THEMES

🚀 POWER-UPS UNLOCKED

⏱️ SPEEDRUN TIME

🛠️ STARTER PACK (Prerequisites)

📂 LOOT TABLE (Key Files)

🌊 LEVEL PROGRESSION

🎮 BRANCH FLOW

🗃️ DATABASE COMPLEXITY

By The Numbers 📊

Sample Business Rules 🌾

🎯 ACHIEVEMENT UNLOCKED CONDITIONS

🌟 FINAL BOSS: THE VALIDATION SUITE

🎊 VICTORY SCREEN

📋 SOLUTION WALKTHROUGH

Setup

Step 01 — Explore Legacy Database

Step 02 — Generate Database Specification

Step 03 — Analyze Migration Complexity

Step 04 — Design Target Schema

Step 05 — Generate Migration Scripts

Step 06 — Create Data Validation

Step 07 — Document Migration Plan

Push

Output Summary

About

Topics

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Uh oh!

Contributors

Uh oh!

Languages

Packages