Migrate all entities to versioned tuple-3 pattern with timestamp + deleted

[JulesGosnell]

Summary

Migrate all database entities to use the versioned tuple-3 pattern with simplified timestamp columns. This ensures true data immutability - the application can only INSERT, never UPDATE or DELETE.

Current State

We have a mix of:

• Versioned entities (tuple-3): (organizationId, id, version) with createdAt, updatedAt, deletedAt, isLatest, supersededAt
• Non-versioned entities: Simple id PK with createdAt, updatedAt, deletedAt

Proposed Pattern

For ALL entities, use tuple-3 versioning:

model Entity {
  organizationId String
  id             String       @default(cuid())
  version        Int          @default(1)
  
  // Version tracking (simplified)
  isLatest       Boolean      @default(true)
  timestamp      DateTime     @default(now())  // Replaces createdAt/updatedAt/supersededAt
  deleted        Boolean      @default(false)  // Replaces deletedAt
  
  // Entity data...
  
  @@id([organizationId, id, version])
  @@index([organizationId, id, isLatest])
}

How timestamps work:

• Creation time: timestamp on version 1
• Last update time: timestamp on latest version
• Deletion time: timestamp on the version where deleted=true

Benefits:

1. True immutability: Every change = new row, no UPDATEs needed
2. Simpler schema: 2 columns (timestamp, deleted) instead of 4 (createdAt, updatedAt, deletedAt, supersededAt)
3. Consistent pattern: Same approach for all entities
4. Full audit trail: Every version preserved with who/when/why
5. Point-in-time queries: Can reconstruct state at any moment
6. DB-level protection: App role can be restricted to INSERT only

Entities to Migrate

Currently Versioned (update column pattern):

• [x] Organization → add tuple-3 PK
• [x] User ✓
• [x] CareWorker ✓
• [x] Client ✓
• [x] CarePlan ✓
• [x] RiskAssessment ✓
• [x] Visit ✓
• [x] VisitNote ✓
• [x] TrainingModule ✓
• [x] Policy → needs tuple-3
• [x] PolicyQuiz ✓
• [x] PolicyQuizQuestion ✓
• [x] PolicyQuizAttempt ✓
• [x] PolicyQuizAnswer ✓

Need Full Migration to Versioned:

• [ ] JobRole
• [ ] TrainingRoleRequirement
• [ ] CareWorkerAvailability
• [ ] ClientFunding
• [ ] FundingSource
• [ ] CarePlanDomain
• [ ] TaskTemplate
• [ ] VisitType
• [ ] VisitTypeTaskTemplate
• [ ] VisitTask
• [ ] TrainingCompletion
• [ ] ModuleSection
• [ ] ModuleContent
• [ ] QuizQuestion
• [ ] QuizAttempt
• [ ] QuizAnswer
• [ ] ModuleProgress
• [ ] TrainingBookmark
• [ ] TrainingContentHash
• [ ] PolicyVersion
• [ ] PolicyAcknowledgement
• [ ] OnboardingRequirement
• [ ] Document
• [ ] Contract
• [ ] HolidayRequest
• [ ] HolidayBalance
• [ ] Alert
• [ ] ComplianceOverride
• [ ] DataConsent
• [ ] DataRequest
• [ ] DataRetentionPolicy
• [ ] VisitTypePrice
• [ ] RateMultiplier
• [ ] BankHoliday
• [ ] ClientPriceOverride
• [ ] CareWorkerPayRate
• [ ] CareWorkerVisitTypeRate
• [ ] OvertimeRule
• [ ] ExpenseRate
• [ ] Invoice
• [ ] InvoiceLineItem
• [ ] Payment
• [ ] CreditNote

Possibly Exempt (truly ephemeral):

• [ ] PushToken - device tokens, replaced frequently
• [ ] Notification - transient, could expire
• [ ] NotificationPreferences - user settings, low audit value

Migration Strategy

1. Phase 1: Update existing versioned entities to use timestamp + deleted
2. Phase 2: Migrate high-value entities (Invoice, Payment, Document, Contract)
3. Phase 3: Migrate configuration entities (VisitType, FundingSource, etc.)
4. Phase 4: Migrate operational entities (VisitTask, TrainingCompletion, etc.)
5. Phase 5: Decide on ephemeral entities

Breaking Changes

• All queries need WHERE isLatest = true AND deleted = false
• Relations become more complex (reference by id, query with isLatest)
• Prisma relations may need to be removed in favor of manual queries
• API endpoints need to use versioned update pattern

Related

• #116 - Database immutability (this supersedes that approach)
• #133 - Database constraints

Labels

enhancement, database, architecture