Create declaration_templates Supabase table with versioning
epic-driver-and-confidentiality-management-foundation-task-001 — Create the declaration_templates database table in Supabase with org-scoped RLS policies, version numbering, soft-delete support, and audit columns. Include migration script, indexes on org_id and version, and ensure historical templates remain retrievable even after a new version is published.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use Supabase CLI `supabase migration new declaration_templates` to generate the migration file. The is_active flag approach for 'current version' requires a trigger or application logic to ensure only one row per org has is_active=true at a time — consider a partial unique index `WHERE is_active = true` instead of a boolean flag, or enforce via application layer. The template_content column should store the full JSON schema of the declaration form to support rendering without additional lookups. Add a comment on the table (`COMMENT ON TABLE declaration_templates IS '...'`) for discoverability.
Ensure the migration includes `ALTER TABLE declaration_templates ENABLE ROW LEVEL SECURITY;` as a separate statement after table creation.
Testing Requirements
Write SQL-level tests or Supabase edge function tests verifying: (1) INSERT succeeds for authorized org user, (2) INSERT fails for user from different org, (3) SELECT returns only rows for the authenticated user's org, (4) DELETE attempt returns error (RLS blocks it), (5) soft-deleted row is excluded from default active query, (6) historical version is still retrievable by explicit ID after soft-delete. Run migration against a clean Supabase test project to confirm idempotency.
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.