🏠 Overview › ✅ Implementation Tasks ›✅ epic-bufdir-reporting-export-foundation-task-002

Create bufdir_column_schema_config database table

epic-bufdir-reporting-export-foundation-task-002 — Design and implement the bufdir_column_schema_config table to store per-organisation Bufdir column definitions with versioning support. Include columns for org_id, schema_version, column_definitions (JSONB), is_active, created_at, updated_at. Add unique constraint on (org_id, schema_version) and a partial index to enforce a single active schema per organisation.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file applies cleanly to a fresh Supabase instance with no errors
id column is UUID PRIMARY KEY DEFAULT gen_random_uuid()
org_id is UUID NOT NULL with FOREIGN KEY to the organisations table
schema_version is TEXT NOT NULL; UNIQUE constraint on (org_id, schema_version) prevents duplicate versions per organisation
column_definitions is JSONB NOT NULL; a CHECK constraint validates the JSONB value is a JSON array (jsonb_typeof(column_definitions) = 'array')
is_active is BOOLEAN NOT NULL DEFAULT false
created_at is TIMESTAMPTZ NOT NULL DEFAULT now(); updated_at is TIMESTAMPTZ NOT NULL DEFAULT now() with an auto-update trigger
A UNIQUE partial index on (org_id) WHERE is_active = true enforces that at most one schema per organisation can be active at any time
Attempt to set is_active = true for a second row with the same org_id is rejected by the partial unique index
Deactivating the current schema (is_active = false) and activating a new one in the same transaction succeeds
An index exists on (org_id, is_active) for fast active-schema lookups
Migration includes seed data for NHF, Blindeforbundet, and HLF with schema_version = '1.0.0' and is_active = true

Technical Requirements

frameworks
Supabase migrations (SQL)
PostgreSQL 15+ JSONB
apis
supabase CLI
Supabase Management API
data models
bufdir_column_schema_config
organisations (foreign key target)
performance requirements
Active schema lookup (WHERE org_id = $1 AND is_active = true) must use the partial index and return in < 5ms on typical hardware
JSONB column_definitions should be validated at insert time via CHECK, not at application layer
security requirements
Only users with an 'admin' or 'org_admin' role claim in their JWT should be permitted to INSERT or UPDATE schema config rows — enforce via RLS
column_definitions JSONB must not accept null — NOT NULL constraint required
schema_version must not be empty string — add CHECK (schema_version <> '')

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

The partial unique index (CREATE UNIQUE INDEX ... WHERE is_active = true) is the key mechanism for single-active-schema enforcement — do not attempt this with a trigger or application logic alone, as concurrent transactions could race. The JSONB CHECK constraint (jsonb_typeof(column_definitions) = 'array') is a lightweight guard; deeper structural validation of individual column definition objects should happen at the application layer in the Dart BufdirColumnMapperService. For the updated_at auto-update trigger, create a reusable trigger function set_updated_at() if one does not already exist in the project migrations — avoid duplicating trigger function definitions.

Seed data for NHF, Blindeforbundet, and HLF should define column_definitions as a JSONB array of objects with at minimum 'column_key', 'display_name', and 'null_value_policy' fields matching the ColumnMappingConfig schema documented in task-014.

Testing Requirements

SQL smoke-test or Dart integration test covering: (1) INSERT a schema with valid JSONB array succeeds; (2) INSERT with JSONB object (not array) is rejected by CHECK constraint; (3) INSERT duplicate (org_id, schema_version) pair is rejected by UNIQUE constraint; (4) setting is_active = true on a second row for the same org_id is rejected; (5) atomic swap — deactivate old, activate new in one transaction — succeeds and only one active row exists afterward; (6) updated_at is automatically updated on UPDATE. Run against local Supabase instance in CI.

Component
Bufdir Column Schema Config Repository
data medium
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.

Quick Links
All Tasks Execution Plan