Step,Title,Instruction,Command,Expected Result
1,Audit current data contracts,Compare prisma/schema.prisma vs src/schemas/* (Zod) vs openapi/openapi.yaml; list enum/name/type drift.,—,audit.md added with a concise diff + decisions.
2,Verify local/managed MySQL,Ensure DATABASE_URL in .env points to your MySQL; confirm login works.,mysql -h localhost -u root -p -e ‘SELECT 1;’,MySQL reachable; .env is correct.
3,Install deps,Install Node deps so Prisma CLI/client are available.,npm i,node_modules present; prisma scripts runnable.
4,Define Prisma enums,”Add Role, Category, Priority, EventType, EventStatus, ZapKind enums in prisma/schema.prisma.”,code prisma/schema.prisma,Enums exist and referenced by models.
5,Add/verify core models,”Ensure models: User, Profile, Calendar, Response, Idea, PlanRun, Plan, PlanDay, Event, EventComment, Message.”,—,Models compile; basic relations defined.
6,AI bookkeeping,”Add AiRequest { id, userId, promptId, inputHash(unique), outputId?, createdAt } for AI run de-dup.”,—,AiRequest model added with unique(inputHash).
7,MySQL KV tables,Add IdempotencyKey and RateLimitCounter models to replace Redis KV for idem and rate limit.,—,Two small tables present with indexes/uniques.
8,Tighten Event,”Event uses enums for category/priority/type/status; add recurrenceRrule, constraintsJson, version, CHECK startTs < endTs.”,—,Event validated; constraint compiles (MySQL 8).
9,PlanDay integrity,”Add @@unique([planId, date]) to prevent duplicate days.”,—,Uniqueness guaranteed in DB.
10,Hot-path indexes,”Indexes: Event(calendarId,startTs), Plan(calendarId,windowStart), Message(userId,createdAt).”,—,Indexes declared for fast range queries.
11,Run migration,Generate and apply migration for all above changes.,npx prisma migrate dev –name data_layer_consolidation,Migration created and applied locally.
12,Generate Prisma client,Regenerate the client to pick up schema changes.,npm run prisma:generate,@prisma/client updated.
13,Seed dataset (definitions),”Create scripts/seed.data.ts: 2 demo users, 1 calendar each, phase responses, 50 ideas, one successful PlanRun+Plan (90 days), PlanDays (first 7), ~20 Events across categories.”,create file: scripts/seed.data.ts,Typed seed content ready for use.
14,Seed script (idempotent),Implement scripts/seed.ts using upsert/createMany; avoid duplicates on re-run; attach plan/events to calendars.,edit scripts/seed.ts,Seeder compiles; idempotent behavior verified.
15,Run seeding,Populate DB with realistic data.,npm run seed,Users/calendars/ideas/plan/planDays/events inserted.
16,Prisma Studio QA,Visually check relational integrity and enum values.,npm run prisma:studio,Data looks correct; no null FKs; dates sane.
17,Event repo (TX overlap),Create src/data/eventRepo.ts with createEventsWithTx() that rejects overlaps via SELECT range check inside a transaction.,create file: src/data/eventRepo.ts,Conflicting blocks yield 422 at service level.
18,Calendar queries,”Add getViewport(calendarId, from, to) using index; add latestPlan(calendarId) in planRepo.”,”create files: src/data/planRepo.ts, src/data/calendarRepo.ts”,Queries return ordered results; unit tests later verify.
19,MySQL idempotency middleware,Implement src/http/idempotency.mysql.ts storing/replaying responses in IdempotencyKey with TTL.,create file: src/http/idempotency.mysql.ts,Idempotent POSTs replay correctly within TTL.
20,MySQL rate limiter,”Implement src/http/rateLimit.mysql.ts token-bucket using RateLimitCounter unique(scope,windowStart).”,create file: src/http/rateLimit.mysql.ts,429 emitted after configured threshold.
21,Unify enums in Zod,Sync Zod enums/types in src/schemas/* with Prisma enums; regenerate OpenAPI from Zod.,npm run typecheck && npm run openapi:lint,No enum drift; OpenAPI valid; TS SDK still compiles.
22,Routes → calendarId,”Update /plan, /events, /calendar to require calendarId (temp default to first calendar).”,—,Handlers compile; client updated to send calendarId.
23,Golden contract tests,Freeze example request/response fixtures; compare in tests to detect contract drift.,create file: tests/contracts/golden.test.ts,Failures surface if response shapes change.
24,DB smoke tests,”Add tests/db.smoke.test.ts for counts, latestPlanByCalendar, viewport ordering, enum integrity.”,npm run test -t db.smoke,Smoke tests pass.
25,Constraint tests,tests/event.validation.test.ts: DB rejects start>=end; service rejects overlap; ensure proper error codes.,npm run test -t event.validation,Both constraints enforced correctly.
26,Flow tests (stub AI),tests/api.flow.test.ts: register→login→phase1→ideas:generate(stub)→plan:generate→GET calendar range.,npm run test -t api.flow,End-to-end passes with seeded data.
27,EXPLAIN queries,Run EXPLAIN on viewport + latest plan to confirm index usage; adjust where/order if needed.,”mysql -e “”EXPLAIN SELECT * FROM Event WHERE calendarId=? AND startTs BETWEEN ? AND ? ORDER BY startTs”””,Using composite indexes; no full table scans.
28,Readiness endpoint,Add /ready that checks SELECT 1 on DB and presence of QStash env keys; return 200 only if all good.,curl -s http://localhost:4000/ready,Green when DB up and env configured.
29,Backup script,Add scripts/backup.sh using mysqldump + compression; document restore in IMPLEMENTATION.md.,bash scripts/backup.sh,Backup artifact produced; restore steps documented.
30,Finalize + tag,Commit schema + seeds + repos + tests; run full test suite; tag release (v0.data-solid).,npm run test && git tag v0.data-solid && git push –tags,Green CI (if used); version tag created.