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

Implement RLS policies for schema config table

epic-bufdir-reporting-export-foundation-task-005 — Write and apply Row Level Security policies on bufdir_column_schema_config to enforce strict org-scoped access. Coordinators and admins within an organisation may SELECT their own schema versions. Only super-admins may INSERT or UPDATE schema versions. No cross-organisation reads are permitted under any role.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

RLS is enabled on bufdir_column_schema_config with ALTER TABLE ... ENABLE ROW LEVEL SECURITY
A SELECT policy named 'schema_config_org_read' allows authenticated users with role 'coordinator' or 'admin' to read only rows where org_id matches their JWT claim (auth.jwt() ->> 'org_id')
An INSERT policy named 'schema_config_super_admin_insert' restricts inserts exclusively to users with role 'super_admin' in JWT claims
An UPDATE policy named 'schema_config_super_admin_update' restricts updates exclusively to users with role 'super_admin'
No DELETE policy is defined — deletes are blocked by default (append-only enforcement)
A user from org A cannot SELECT any row belonging to org B, verified by test with two distinct org tokens
A coordinator attempting INSERT receives a 42501 permission denied error from Supabase
A super_admin can INSERT and UPDATE rows for any org without restriction
All policies are idempotent (DROP POLICY IF EXISTS before CREATE POLICY) for safe re-application in migrations
Policies are documented in a migration file under supabase/migrations/ with a descriptive timestamp prefix

Technical Requirements

frameworks
Supabase (PostgreSQL RLS)
PostgreSQL policy expressions
apis
Supabase Auth JWT claims (auth.jwt())
PostgreSQL auth.uid() helper
data models
bufdir_column_schema_config (org_id, version, columns, created_at, created_by)
user JWT claims: org_id, role
performance requirements
Policy expressions must use indexed columns only — org_id must have a btree index
JWT claim extraction (auth.jwt() ->> 'org_id') should not trigger full table scans; confirm EXPLAIN ANALYZE on SELECT policy
security requirements
JWT claim 'org_id' must be set server-side during Supabase Auth sign-in (not user-supplied)
JWT claim 'role' must be a Supabase custom claim set via a trusted hook or Edge Function — never client-supplied
No SECURITY DEFINER functions may be used to bypass RLS for this table
BYPASSRLS privilege must NOT be granted to any application role
Migration must be reviewed by a second developer before merging to main

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Write policies as a numbered Supabase migration file (e.g. 20260326000005_bufdir_schema_config_rls.sql). Use DROP POLICY IF EXISTS guards before each CREATE POLICY to make the migration re-runnable. The org_id check should be: (auth.jwt() ->> 'org_id')::uuid = org_id — cast both sides to uuid to avoid implicit type coercion bugs.

For the super_admin role check use: (auth.jwt() ->> 'role') = 'super_admin'. Avoid using auth.uid() = created_by as the sole guard since that would allow a coordinator who created a row to later modify it. After applying locally via supabase db push, run EXPLAIN (ANALYZE, BUFFERS) on a representative SELECT to confirm the index is used. Do not use a single combined policy with OR logic across roles — separate policies per role are easier to audit and revoke individually.

Testing Requirements

Integration tests required against a Supabase test instance (separate project or local supabase CLI). Test matrix: (1) coordinator with matching org_id can SELECT — expect rows returned; (2) coordinator with non-matching org_id receives empty result set, not an error; (3) admin with matching org_id can SELECT; (4) coordinator attempting INSERT receives PostgrestException with code '42501'; (5) super_admin can INSERT a new schema version and retrieve it; (6) super_admin can UPDATE an existing schema version; (7) any role attempting DELETE receives '42501'. Each test must use a distinct JWT token minted with the appropriate claims. Use flutter_test with a Supabase test client initialised via environment variables.

No mocking of the database layer — all tests must hit the real RLS engine.

Component
Bufdir Column Schema Config Repository
data medium
Dependencies (1)
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. epic-bufdir-reporting-export-foundation-task-002
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