critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

BufdirSchemaConfigRepository is a Dart class accepting SupabaseClient as a constructor dependency
getActiveSchema(String orgId) returns the BufdirColumnSchema row with the highest version number for the given org, or null if none exists
getAllVersions(String orgId) returns a List<BufdirColumnSchema> for the org ordered by version descending
upsertSchemaVersion(BufdirColumnSchema schema) performs an upsert on the database and throws SchemaUpsertForbiddenException if the current user is not super_admin (check JWT claim before making the call)
upsertSchemaVersion throws SchemaVersionConflictException if a schema with the same version number already exists for the org and the columns differ
Local caching is implemented via a Riverpod AsyncNotifier that holds the active schema per org_id and invalidates on upsert
Cache is keyed by org_id — fetching schema for org A does not serve cached data for org B
detectSchemaDrift(BufdirColumnSchema a, BufdirColumnSchema b) returns a SchemaDriftResult listing added columns, removed columns, and renamed columns (by position heuristic)
detectSchemaDrift is a pure function with no side effects and is independently testable
BufdirColumnSchema is an immutable data class with fields: id (String), org_id (String), version (int), columns (List<BufdirColumnDefinition>), created_at (DateTime), created_by (String)
BufdirColumnDefinition has fields: key (String), label (String), data_type (String), required (bool)

Technical Requirements

frameworks
Dart (latest)
Riverpod (AsyncNotifier for caching)
supabase_flutter (SupabaseClient)
freezed (BufdirColumnSchema, BufdirColumnDefinition, SchemaDriftResult)
apis
Supabase PostgREST: from('bufdir_column_schema_config').select().eq('org_id').order('version', ascending: false).limit(1)
Supabase PostgREST: from('bufdir_column_schema_config').upsert()
Supabase Auth: client.auth.currentSession?.user for JWT claim extraction
data models
BufdirColumnSchema (id, org_id, version, columns, created_at, created_by)
BufdirColumnDefinition (key, label, data_type, required)
SchemaDriftResult (addedColumns, removedColumns, renamedColumns, hasDrift bool)
bufdir_column_schema_config table
performance requirements
Cache must serve repeated getActiveSchema calls for the same org within 1ms (in-memory Riverpod state)
Remote fetch for getActiveSchema must include .limit(1) to avoid loading all versions unnecessarily
getAllVersions must support server-side pagination with optional limit/offset
security requirements
upsertSchemaVersion must check JWT role claim ('super_admin') client-side and throw before making the network call — provides clear user feedback without relying solely on RLS rejection
org_id in upsert payload must always be taken from the schema object, never from free input, and validated as non-empty UUID
Version numbers must be validated as positive integers before upsert

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Implement the Riverpod cache as an AsyncNotifier keyed by org_id using a family modifier: schemaConfigProvider.family. On upsert, call ref.invalidate(schemaConfigProvider(orgId)) to force a fresh fetch on next access. For detectSchemaDrift, compare List using column key as the unique identifier — two columns with the same position but different keys are a rename candidate. Return a SchemaDriftResult freezed class with hasDrift (bool), addedKeys (List), removedKeys (List), and renamedPairs (List<(String from, String to)>).

Keep this function in a separate schemaDriftUtils.dart file for testability. The upsert conflict check (same version, different columns) should be performed by fetching the existing row before upsert and comparing — do not rely on a database constraint alone since Supabase upsert silently overwrites by default.

Testing Requirements

Unit tests with mocktail: (1) getActiveSchema returns null when Supabase returns empty list; (2) getActiveSchema returns the highest-version row when multiple rows exist; (3) upsertSchemaVersion throws SchemaUpsertForbiddenException when JWT role is not super_admin; (4) upsertSchemaVersion calls Supabase upsert with correct payload; (5) detectSchemaDrift returns empty SchemaDriftResult for identical schemas; (6) detectSchemaDrift correctly identifies one added column; (7) detectSchemaDrift correctly identifies one removed column; (8) cache returns cached value on second call without triggering Supabase fetch; (9) cache invalidates after upsertSchemaVersion. Integration tests: verify that getActiveSchema fetches the live active schema and that upsertSchemaVersion succeeds for a super_admin token. Minimum 85% line coverage on the repository and drift detection logic.

Epic Risks (2)
high impact medium prob security

RLS policies for the audit log and schema config tables must correctly handle multi-chapter membership hierarchies (up to 1,400 local chapters for NHF). Incorrect policies could either over-expose data across organisations or prevent legitimate coordinator access, both of which are serious compliance failures.

Mitigation & Contingency

Mitigation: Design RLS policies using the existing org hierarchy resolver pattern. Write integration tests that verify cross-organisation isolation with representative fixture data covering NHF's multi-level hierarchy before merging.

Contingency: If RLS policies prove too complex to express safely in Postgres, implement a Supabase Edge Function as a data access proxy that enforces isolation in application code, with RLS serving as a secondary defence layer.

medium impact medium prob scope

Bufdir's column schema is expected to evolve as Norse Digital Products negotiates a simplified digital reporting format. If the schema config versioning model is too rigid, applying Bufdir schema updates without a code deployment could fail, forcing emergency releases.

Mitigation & Contingency

Mitigation: Design the schema config table to store the full JSON column mapping as a JSONB field with a version number. Provide an admin API to upsert new versions without any schema migration required.

Contingency: If the versioning model is insufficient for a Bufdir schema change, fall back to a code deployment with the updated default schema, using the database config only for org-specific overrides.