Create org_feature_flags Supabase table defaulted to disabled
epic-driver-and-confidentiality-management-foundation-task-007 — Create the org_feature_flags table in Supabase with columns for org_id, feature_key (e.g., 'driver_and_confidentiality'), enabled boolean defaulting to false, and audit timestamps. Add RLS policies ensuring only org admins can read their own flag rows. Insert seed row for Blindeforbundet's org during migration with enabled=false.
Acceptance Criteria
Technical Requirements
Implementation Notes
Keep the feature_key CHECK constraint list in sync with the DriverFeatureFlagConfig constants defined in task-008 — these two must always agree. Use a Postgres trigger function (set_updated_at) that is likely already defined elsewhere in the schema for other tables; reuse it here rather than creating a duplicate. For the seed row, look up Blindeforbundet's org_id from the organizations table by slug or name in the migration script using a subquery: INSERT INTO org_feature_flags (org_id, feature_key, enabled) SELECT id, 'driver_and_confidentiality', false FROM organizations WHERE slug = 'blindeforbundet' ON CONFLICT DO NOTHING. This avoids hardcoding a UUID that might differ between environments.
The client-side restriction (no UPDATE) is enforced by having no RLS UPDATE policy defined — Supabase denies by default when no policy matches.
Testing Requirements
SQL migration tests: (1) verify table schema matches specification; (2) verify unique constraint rejects duplicate (org_id, feature_key) insert; (3) verify RLS allows org admin to SELECT their own flag; (4) verify RLS blocks coordinator role from SELECT; (5) verify RLS blocks cross-org SELECT for admin role; (6) verify client cannot INSERT or UPDATE (only service_role can); (7) verify seed row exists for Blindeforbundet with enabled=false; (8) verify idempotent re-run does not create duplicates. Dart unit test: verify FeatureFlagConfig.fromJson correctly deserializes the Supabase row format.
Row-level security policies for driver assignments and declarations must correctly scope data to the coordinator's chapter without leaking records across organizations. An incorrect RLS predicate could silently return empty result sets or, worse, expose cross-org data, both of which are difficult to detect in unit tests.
Mitigation & Contingency
Mitigation: Write dedicated RLS integration test scenarios with multiple org fixtures asserting both data isolation and correct data visibility. Use Supabase's built-in policy testing utilities and review policies with a second developer.
Contingency: If RLS policies prove too complex to get right quickly, implement application-layer org scoping as a temporary guard while RLS is fixed in a follow-up, with an explicit security review gate before production deployment.
The declaration audit logger must produce tamper-evident records. If the database allows updates or deletes on audit rows, the compliance guarantee is broken. Supabase does not natively prevent row deletion by default.
Mitigation & Contingency
Mitigation: Implement an insert-only RLS policy on the audit table that denies UPDATE and DELETE for all roles including the service role. Add a database trigger that rejects mutation attempts and logs the attempt itself.
Contingency: If immutability cannot be enforced at the database level within the sprint, store audit entries in an append-only Supabase Edge Function log stream as a temporary alternative, with a migration plan to the proper table once constraints are implemented.
The org-feature-flag-service caches flag values to avoid repeated database reads. If the cache is not invalidated promptly after an admin toggles the flag, coordinators may see stale UI state — either seeing driver features when they should not, or not seeing them when they should.
Mitigation & Contingency
Mitigation: Use a Supabase Realtime subscription to listen for changes on the driver_feature_flag_config table and invalidate the in-memory cache immediately on change. Set a short TTL (60 seconds) as a safety net.
Contingency: If Realtime subscription proves unreliable, expose a manual cache-bust endpoint accessible from the admin toggle action, ensuring the cache is cleared synchronously on every flag change.