Migration Sequencing V1

Intent

Define a deterministic migration sequence for implementing the V1 relational blueprint with minimal risk and clear rollback boundaries.

Sequencing Principles

1. Identity first Person and identity bindings are prerequisites for nearly all domain rows.

2. Structure before activity Learning structure and memberships must exist before attempts and competency evidence.

3. Evidence before derived state Append-oriented evidence tables should be introduced before derived summary/state tables.

4. Exportability is not optional Portable history coverage is built in from first assessment rollout.

5. Sync columns early updated_at_us, operation-link metadata, and deterministic apply-sequence columns should be available before client sync mutation traffic scales.

Phase Plan

Phase 0: Foundations and Enums

Create shared enums/domains and helper primitives:

• Lifecycle statuses (offering, membership, attempt, qualification)
• Scope and role enums
• Competency state enums

Verification:

• Enum values match architecture docs
• No duplicate semantic enums across table groups

Phase 1: Identity and Organization

Create:

• person
• person_profile
• organization
• identity_binding
• organization_affiliation
• person_role_grant

Verification:

• External identity uniqueness constraints hold
• Temporal checks enforce valid windows

Phase 2: Learning Structure and Membership

Create:

• learning_container
• offering
• offering_membership
• learning_unit
• unit_release_rule

Verification:

• Unified membership uniqueness works for multi-role participation
• Offering and unit sequencing indexes satisfy expected query patterns

Phase 3: Assessment and Feedback

Create:

• assessment_definition
• assessment_attempt
• assessment_score
• feedback_entry
• submission_artifact

Verification:

• Attempt uniqueness per person and assessment
• Feedback history is append-safe and queryable by attempt timeline
• Score provenance fields are non-null where required

Phase 4: Competency Framework

Create:

• competency_framework
• competency_framework_version
• competency_node
• competency_node_edge
• competency_alignment
• person_competency_evidence
• person_competency_state

Verification:

• Framework version immutability controls are in place
• Evidence-to-state derivation can be executed deterministically
• Alignment temporal windows prevent historical rewrite ambiguity

Phase 5: Qualification and Portability

Create:

• qualification_definition
• qualification_award
• progress_snapshot
• activity_event
• change_audit_entry
• export_import_batch

Verification:

• Default export query includes full attempt and feedback history
• Audit entries can link mutations to source operation IDs

Phase 6: Sync Operation Linkage

Add or finalize:

• last_operation_id (or validated equivalent) on mutable sync tables
• last_apply_sequence (or equivalent deterministic ordering cursor) on mutable sync tables
• Source operation propagation into activity_event and change_audit_entry

Verification:

• Every client-submitted mutation has trace link to pgxsinkit operation log
• Admin/import/batch/system writes targeting syncable tables are validated through the same batch ingress and operation log
• LWW apply order can be reconstructed from deterministic apply sequence

Rollback and Safety Gates

For each phase:

1. Pre-migration snapshot and integrity baseline query set
2. Apply migration
3. Run integrity verification queries
4. Run contract tests and sync smoke tests
5. Promote only if checks pass

If checks fail:

• Use phase-local rollback scripts where feasible
• Otherwise execute documented compensation migration

Recommended Verification Query Families

1. Temporal integrity checks
2. Uniqueness and duplicate detection checks
3. FK orphan checks
4. Export completeness checks for attempt and feedback history
5. Competency evidence-to-state consistency checks
6. Sync operation linkage completeness checks

Implementation Anchors

• Migration files and ordering: packages/db
• Migration-aware contract updates: packages/domain
• Sync linkage and mutation tracing: packages/offline-data and apps/api

Open Decisions

1. Whether last_operation_id is enforced FK to operations_log.id or a validated non-FK operation identifier
2. Whether portability verification queries become CI-gated from phase 3 onward
3. Whether competency state transitions get explicit dedicated table in v1 or v1.1