Goose vs Prisma Migrate, and the soft-delete-vs-audit decision

Two migration philosophies, owned side by side. The comparison is crisp because both serve the same purpose in different runtimes.

Goose (zoth)

• Migrations are SQL files (migrations/postgres/001–007_*.sql) embedded into the binary (embed.FS) — the artifact carries its own schema history; no migration tool needs installing on the cluster, the container, or CI.
• Applied at API startup (cmd/api/main.go → internal/repositories/postgres/migrate.go): takes pg_advisory_lock(72183921) — concurrent migrators (two replicas rolling) serialize — then goose.Up(db, "."). Failure is fatal: the process exits before ListenAndServe, so a broken migration can never serve traffic.
• Versioning discipline: files are immutable once applied; new changes = new numbered files. The one soft spot: sqlc.yaml reads migrations 001–005 only, so SQLC is blind to outbox/vector tables — the generated layer and the live schema have a known, bounded divergence (raw database/sql covers the gap). Saying this unprompted shows you understand generated-artifact drift as a class of problem.
• The 007 vector migration is a gem: ALTER COLUMN embedding TYPE vector(768) USING NULL::vector — it resets embeddings because dimension changes invalidate them (a 1536-dim vector cannot be truncated to 768); re-ingestion follows. Migration-as-reindex: model changes that alter embedding space require regeneration, and the schema tool should encode that honestly (NULL, not garbage).

Prisma Migrate (traverse)

• Schema-first: schema.prisma is the source of truth; prisma migrate dev diffs it into SQL migrations; migrate deploy applies them (container start, exit 1 on failure).
• sha256-per-migration: editing an applied migration file makes deploy fail — immutability enforced by the tool, not convention. (Zoth achieves the same by convention + goose's own versioning table.)
• CI replays migrations (scripts/ci-setup-db.sh) because db push can't express CHECK constraints and partial unique indexes the social-graph tests assert — CI runs the real migration SQL, not a schema-push approximation. Test-what-you-ship applied to DDL.
• The gotcha you lived: a @db.Date column serializes a JS Date by its UTC calendar date — the timezone library normalizes to UTC midnight explicitly, and zonedStartOfDay resolves in two passes (DST-safe) while zonedEndOfDay steps +26 h instead of adding 24 (a DST day is 23 or 25 h). Schema details leaking into date semantics is a real class of bug worth citing.

The soft-delete-vs-audit decision

Same question, two correct answers, by domain:

• Zoth: hard delete + append-only hash-chained audit. "What happened to this request?" is answered by the audit chain (who, what, when, with what prev_hash) — immutable history is the product. Deletes are real deletes (cascades clean the tenant); the record of the deletion lives in audit_logs forever. No WHERE-deleted_at tax, no ghost unique rows.
• Traverse: soft delete where undo matters. User.deletedAt + 7-day grace + POST /auth/recover (password or authenticated) — a product feature: accidental deletion shouldn't nuke streak history. Notifications and social rows hard-delete or expire (requests older than 30 days → EXPIRED, terminal but retained as history — note that's a status, not a soft delete: the row remains queryable as history, deliberately).

The interview formulation: "Deleted is a business question. If the business answer is 'we must prove what existed' → append-only log + hard deletes (zoth, compliance domain). If it's 'users make mistakes' → soft delete with grace + purge job (traverse, consumer domain). If it's 'this is history' → terminal status, not deletion (friend requests, broadcast states)."