Schema Strategy

Intent

Define how relational schema, domain contracts, and migration policy evolve together without breaking core model invariants.

Authoritative Layers

1. Relational schema source: Drizzle in packages/db
2. Runtime contract source: Zod contracts in packages/domain
3. API boundary enforcement: service layer in apps/api
4. Offline projection/sync constraints: packages/offline-data

No single layer should drift independently.

Design Rules

1. Additive-first migrations Prefer additive changes before destructive changes.

2. Backward-compatible transition windows Keep old and new fields/structures compatible during migration windows.

3. Explicit deprecation lifecycle Mark deprecated model surfaces and remove only after documented migration completion.

4. Temporal and audit fields by default Core mutable entities should include creation/update metadata and audit traceability.

5. Enum discipline Lifecycle/status enums must be versioned carefully and documented in lifecycle docs.

6. External sync engine version discipline pgxsinkit remains external and is consumed via npm version pinning; breaking changes are introduced via major versions rather than hidden compatibility layers.

7. Operation trace linkage Mutable sync-participating tables should carry operation-link metadata (last_operation_id or validated equivalent) plus deterministic last_apply_sequence to support replay and audit.

8. Single ingress for syncable tables Syncable domain-table writes must use pgxsinkit batch ingress with bulk-plpgsql-artifact; per-route CRUD paths are not accepted for those tables.

Contract Alignment Workflow

For every model change:

1. Update core model docs if semantics change
2. Update Drizzle schema
3. Update Zod contracts
4. Update sync projections/conflict rules
5. Update integration mapping docs if event/record shape changed
6. Add or update tests

Migration Policy

Minimum migration checklist:

1. Forward migration script
2. Rollback or compensation plan
3. Data backfill strategy where required
4. Integrity verification query set
5. Sync impact assessment

Breaking Change Policy

A change is breaking if it alters:

• Person history continuity semantics
• Membership role/status meaning
• Assessment or qualification core relationships
• Export/import compatibility for learner records

Breaking changes require:

• Architecture note in docs
• Explicit migration playbook
• Compatibility window plan

Naming and Identity Guidance

• Use stable surrogate IDs for core entities
• Keep external identifiers in dedicated binding tables
• Avoid embedding mutable semantics into primary keys

Validation Boundaries

• Input validation at API boundary with Zod
• Domain-level validation for invariants before persistence
• DB constraints for last-line integrity checks

Implementation Anchors

• Schema and migrations: packages/db
• Shared validators/types: packages/domain
• Sync shape and convergence behavior: packages/offline-data
• Mutation and authorization workflows: apps/api

Open Decisions

1. Compatibility window duration for deprecated fields
2. Migration approval gate requirements for production rollout
3. Level of automated schema-doc parity checks in CI
4. Exact cross-project contract for linking local row updates to pgxsinkit operation IDs
5. Whether operation linkage on syncable rows should be enforced FK to operations_log.id or a validated non-FK identifier contract