Zero-Downtime Schema Migration Framework with Backfill & Rollback

[meshackyaro]

Description

This issue aims to address the implementation of Zero-Downtime Schema Migration Framework with Backfill & Rollback.

The core objective is to: Provide expand/contract migrations with online backfill jobs, progress tracking, and safe rollback so schema changes never require downtime or lock the gigs table.

By completing this feature, we will ensure that the TrustFlow protocol maintains its high standards for security, usability, and decentralized logic. This issue requires careful attention to the Backend architecture and adherence to the existing project conventions.

When picking up this issue, please ensure you document any new dependencies or architectural decisions made during development.

Component

Backend

Difficulty

🔴 Hard

Tasks

• Review Node.js API architecture in backend/
• Implement logic for: Zero-Downtime Schema Migration Framework with Backfill & Rollback
• Write unit tests for the new endpoints using Supertest
• Update Swagger terminology / OpenAPI specs
• Verify PostgreSQL queries are optimized
• Run npm run lint

Acceptance Criteria

• Feature accurately implements the objective: Provide expand/contract migrations with online backfill jobs, progress tracking, and safe rollback so schema changes never require downtime or lock the gigs table.
• Any PR that introduces TypeScript errors is automatically blocked
• CI pipeline runs in under 3 minutes per PR
• Code is properly reviewed and approved by codeowners

Estimated Time

3-5 days

[nurudeenheroic]

Hello! I'm confident I can resolve this issue while maintaining code quality and project standards. I'd love the opportunity to work on it.

[grantfox-oss]

🦊 GrantFox — @comzzy-comzzy has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #169)
2. Your PR will be reviewed by the trustflow-protocol maintainers

Good luck! Track your progress on GrantFox.

[bbjiggy]

Hi team,

I would like to apply for this issue! Designing an expand/contract migration framework is critical to ensure zero-downtime updates and prevent locking the gigs table during large-scale database schema changes.

Here is my proposed technical architecture and execution strategy:

Implementation Breakdown

1. Expand/Contract Pattern & Strategy
   Phase 1 (Expand): Add new columns/tables as nullable or with defaults, creating dual-write database triggers or application-level writes so both old and new schema structures update concurrently without locks.

Phase 2 (Backfill): Implement an asynchronous, batch-based online backfill worker with configurable batch sizes and pause/resume logic to minimize I/O load on PostgreSQL.

Phase 3 (Contract): Provide safe cleanup scripts to remove legacy columns/triggers only after backfill completion and code deployment verification.

2. Progress Tracking & Safe Rollback
   Migration Audit System: Store migration states, backfill progress percentages, and batch offsets in a dedicated schema_migration_jobs metadata table.

Rollback Mechanics: Ensure every expansion step has an automated, non-destructive rollback path that safely detaches triggers and drops non-critical columns without corrupting existing production data.

3. API, Documentation & Testing
   Expose administrative REST endpoints to monitor backfill progress, trigger manual pauses, or initiate rollbacks.

Document endpoints using OpenAPI/Swagger specs and write Supertest integration tests validating concurrent backfills, lock-free query execution, and rollback routines.