docs/migration-runbook.md

EPK Migration Runbook & Rollback Plan

Source platform: Base44 (proprietary, locked-in)
Target platform: Self-hosted on Hetzner Cloud (PostgreSQL, Redis, MinIO, kubeadm K8s + Hetzner LB + DNS)
Estimated timeline: 10–11 weeks (per architecture audit)
Critical blockers: 7 requiring full reimplementation, 3 partial, 5 platform constraints

---

Phase 0 — Pre-Migration Setup

Prerequisites

• Hetzner Cloud account with API token configured
• Domain DNS managed in Hetzner (or delegated NS records)
• Terraform ≥ 1.6 installed and validated (terraform validate passes)
• kubectl, helm, psql, redis-cli available locally
• Base44 admin access for data export
• Staging environment deployed and smoke-tested
• Runbook reviewed and signed off by team

Environment Variables (secrets)

Variable	Source	Target
HETZNER_TOKEN	1Password / Vault	TF_VAR_hcloud_token
DOMAIN	DNS zone	epk.example.com
DB_PASSWORD	Generated	PostgreSQL superuser
REDIS_PASSWORD	Generated	Redis ACL
MINIO_ACCESS_KEY / MINIO_SECRET_KEY	Generated	MinIO root
JWT_SECRET	Generated (256-bit)	Auth service
OAUTH_*_CLIENT_ID / OAUTH_*_SECRET	Provider dashboards	6 OAuth connectors

---

Phase 1 — Data Export from Base44

1.1 Export Strategy

• Frontend-only export: Base44 GitHub export contains only React frontend code. Backend (DB, auth, functions, realtime) is proprietary and not exported.
• Data export: Use Base44 admin dashboard → "Export Data" (JSON/CSV per entity). For large datasets, request Base44 support for full DB dump.
• Entities to export: All inferred EPK entities from audit (users, projects, tasks, comments, attachments, subscriptions, webhooks, audit_logs, etc.).

1.2 Export Checklist

• Inventory all entities from base44-audit.md data models section
• Request full DB dump from Base44 support (if dataset > 100k rows)
• Export each entity to dated directory: exports/YYYYMMDD/<entity>.json
• Verify row counts match admin dashboard counters
• Compute SHA256 checksums for each file; store in exports/YYYYMMDD/CHECKSUMS.txt
• Archive to cold storage (S3/MinIO) with retention tag

1.3 Known Limitations

• No access to Base44 internal IDs for auth users (map by email)
• Realtime subscription state not exportable — rebuild from scratch
• File attachments stored in Base44 CDN — download URLs expire in 24h

---

Phase 2 — Schema Transformation Scripts

2.1 Mapping Rules (MongoDB-compat → PostgreSQL + RLS)

Base44 Type	Postgres Type	Notes
ObjectId	uuid (gen_random_uuid())	PK on all tables
Date	timestamptz	UTC normalized
DBRef	uuid FK + RLS policy	Enforce ownership
Array<subdoc>	JSONB column / child table	Normalize if queried
Binary	bytea or MinIO object ref	> 1MB → MinIO

2.2 Transformation Pipeline

exports/YYYYMMDD/*.json
      │
      ▼
scripts/transform/
  ├── 01_normalize_ids.py      # ObjectId → UUID, build ID map
  ├── 02_flatten_arrays.py     # Subdoc arrays → child tables
  ├── 03_enforce_rls.py        # Add owner_id, gen policies
  ├── 04_attachments_to_minio.py  # Download + upload, rewrite refs
  └── 05_validate_schema.py    # pg_dump --schema-only diff
      │
      ▼
staging_import/YYYYMMDD/*.sql

2.3 Script Checklist

• 01_normalize_ids.py — deterministic UUID v5 (namespace = Base44 export ID)
• 02_flatten_arrays.py — configurable depth (default 1), foreign keys indexed
• 03_enforce_rls.py — generates CREATE POLICY per table per role (anon, user, admin)
• 04_attachments_to_minio.py — parallel downloads (8 workers), resumable, Verifies checksums
• 05_validate_schema.py — compares generated DDL against schema/target.sql (committed)
• All scripts: idempotent, structured JSON logs, exit code 0 on success
• Unit tests for each transform (tests/transform/test_*.py)

---

Phase 3 — Import Validation

3.1 Staging Import Procedure

# 1. Fresh staging DB (Terraform destroys/recreates)
terraform -chdir=infra/terraform apply -target=hcloud_server.db -auto-approve

# 2. Run schema
psql "$STAGING_DB_URL" -f schema/target.sql

# 3. Load data
psql "$STAGING_DB_URL" -f staging_import/YYYYMMDD/01_core.sql
psql "$STAGING_DB_URL" -f staging_import/YYYYMMDD/02_relations.sql
psql "$STAGING_DB_URL" -f staging_import/YYYYMMDD/03_attachments.sql

# 4. Validate
python scripts/validate_import.py --env staging

3.2 Validation Checks

Check	Tool	Threshold
Row count per table	validate_import.py	±0.1% vs export
Referential integrity	pg_dump --data-only --inserts	0 FK violations
RLS policy coverage	SELECT * FROM pg_policies	100% tables with owner_id
Attachment checksums	MinIO mc stat + DB file_sha256	100% match
Auth user mapping	validate_import.py --auth	100% emails mapped
Index usage	pg_stat_user_indexes	No unused indexes > 100MB

3.3 Import Checklist

• Staging DB provisioned fresh (no residual data)
• Schema applied without errors
• All 3 data batches loaded in order
• Validation script exits 0
• Spot-check 10 random entities end-to-end (UI + API)
• Performance baseline: EXPLAIN ANALYZE on top 5 queries < 50ms p95

---

Phase 4 — DNS Cutover

4.1 Pre-Cutover (T-24h)

• TTL on all epk.example.com records reduced to 60s
• Hetzner Load Balancer health checks passing (green for 1h)
• Staging promoted to "candidate" — same infra, separate DNS label candidate.epk.example.com
• Team smoke test on candidate (Phase 5) passes
• Rollback DNS records pre-staged in Hetzner DNS console

4.2 Cutover Procedure (T=0)

# 1. Verify candidate one last time
curl -sf https://candidate.epk.example.com/healthz

# 2. Switch apex + www CNAME in single API call (atomic)
hcloud dns record update --zone epk.example.com --id <apex_id> --value <lb_ip>
hcloud dns record update --zone epk.example.com --id <www_id>  --value <lb_ip>

# 3. Confirm propagation (check from 3 regions)
for r in us eu asia; do dig @$r.epk.example.com +short; done

# 4. Update TTL back to 3600
hcloud dns record update --zone epk.example.com --id <apex_id> --ttl 3600

4.3 Post-Cutover (T+0 to T+1h)

• Monitor LB request rate, error rate, latency (Grafana dashboard)
• Verify realtime WebSocket connections establishing
• Confirm OAuth redirects resolve to new domain
• Run automated smoke suite (Phase 5) every 5 min for 30 min

---

Phase 5 — Smoke Tests

5.1 Automated Suite (scripts/smoke_test.py)

Test	Description	Timeout
healthz	GET /healthz returns 200 + {"status":"ok"}	5s
auth_login	POST /auth/login → JWT + refresh cookie	10s
auth_refresh	POST /auth/refresh rotates tokens	5s
oauth_google	Full OAuth flow (headless)	30s
crud_project	Create → Read → Update → Delete project	15s
realtime_sub	WS connect → subscribe → receive event	20s
file_upload	POST /files → 201 + MinIO object exists	30s
webhook_delivery	Trigger webhook → verify signature + retry	30s

5.2 Manual Spot-Checks

• Login as admin, create project, invite team member
• Team member accepts invite, sees project
• Realtime: open two browsers, edit same task → sync < 500ms
• File upload > 10MB → progress bar, thumbnail generates
• Export project data (CSV) → downloads correctly

5.3 Smoke Test Checklist

• Automated suite: 8/8 pass
• Manual spot-checks: 5/5 pass
• No console errors in browser devtools
• All OAuth providers redirect to correct callback URL
• Webhook endpoints receive and verify signatures

---

Phase 6 — Rollback Triggers & Procedure

6.1 Automatic Rollback Triggers

Metric	Threshold	Window	Action
HTTP 5xx rate	> 5%	5 min	Auto-rollback DNS
p95 latency	> 2s	5 min	Alert + manual decision
WebSocket connect failure	> 10%	5 min	Alert + manual decision
DB replication lag	> 30s	2 min	Alert + manual decision
Smoke test failure	Any critical test fails	1 run	Auto-rollback DNS

6.2 Manual Rollback Decision Matrix

Scenario	Decision	Owner
Single non-critical test flaky	Investigate, re-run	On-call
Multiple critical tests fail	Rollback immediately	On-call
Data inconsistency reported	Rollback immediately	TL + On-call
Performance degrade, recovering	Monitor 15 min, then decide	TL

6.3 Rollback Procedure (DNS-based, < 2 min)

# 1. Revert DNS to Base44 (pre-staged records)
hcloud dns record update --zone epk.example.com --id <apex_id> --value <base44_ip>
hcloud dns record update --zone epk.example.com --id <www_id>  --value <base44_ip>

# 2. Verify Base44 responding
curl -sf https://epk.example.com/healthz

# 3. Notify team + stakeholders
./scripts/notify.py --channel "#epk-migration" --text "ROLLBACK executed at $(date -u +%H:%M UTC). Base44 live."

# 4. Preserve staging for forensics
#    (Terraform does NOT destroy — keep candidate env)

6.4 Post-Rollback

• Document incident in incidents/YYYYMMDD-rollback.md
• Root cause analysis within 48h
• Fix → re-run Phase 3 validation → schedule new cutover

---

Phase 7 — Post-Migration (T+24h to T+7d)

7.1 Stabilization Checklist

• Monitoring dashboards green for 24h
• No P1/P2 incidents
• Backup/restore drill: restore staging from prod snapshot
• Cost review: Hetzner bill vs projection
• Documentation updated: DEPLOYMENT.md, ARCHITECTURE.md
• Team retrospective scheduled

7.2 Decommission Base44 (T+7d)

• Confirm zero traffic to Base44 (Cloudflare analytics)
• Cancel Base44 subscription
• Revoke Base44 API keys / OAuth credentials
• Archive final Base44 export to cold storage (7-year retention)

---

Appendix A — Command Reference

# Deploy staging
terraform -chdir=infra/terraform apply -var environment=staging

# Deploy prod (after cutover approval)
terraform -chdir=infra/terraform apply -var environment=prod

# Run smoke tests
python scripts/smoke_test.py --env prod --junit output/smoke.xml

# Validate import
python scripts/validate_import.py --env staging --strict

# Generate checksums
sha256sum exports/YYYYMMDD/*.json > exports/YYYYMMDD/CHECKSUMS.txt

# Notify team
python scripts/notify.py --channel "#epk-migration" --text "Message"

---

Appendix B — Contacts

Role	Name	Primary	Escalation
Migration Lead
Backend TL
DevOps
Base44 Support	support@base44.com	Ticket	Phone: +1-xxx-xxx-xxxx
Hetzner Support	support@hetzner.com	Ticket

---

Sign-Off

Phase	Reviewer	Date	Signature
Pre-Migration
Data Export
Schema Transform
Import Validation
DNS Cutover
Smoke Tests
Post-Migration

---

Generated from Base44 audit (t_014f13b3) and Terraform infra (t_a8af80b0). Review with team before execution.