Document data foundation architecture and RLS policy decisions

epic-driver-and-confidentiality-management-foundation-task-014 — Write developer-facing documentation covering: the Supabase table schemas for all 4 tables, the RLS policy rationale for each table (especially insert-only audit log), the feature flag default-disabled strategy, the versioned template retrieval pattern, and the soft-delete audit preservation approach. Include a dependency diagram showing how OrgFeatureFlagService gates all downstream epics.

medium priority low complexity documentation pending documentor Tier 5

Acceptance Criteria

Documentation file is placed in docs/architecture/driver-data-foundation.md (or equivalent agreed location in the repository)

All 4 table schemas are documented with column names, data types, nullability, default values, and foreign key relationships

Each RLS policy is documented with: the policy name, the SQL expression, and a prose explanation of the business rule it enforces

The insert-only audit log policy rationale explicitly explains why UPDATE and DELETE are prohibited (compliance, tamper-proof trail)

The feature flag default-disabled strategy is documented with the specific code path that returns false on error or missing record

The versioned template retrieval pattern is documented with an example query and explanation of why version pinning is used

The soft-delete approach is documented with the fields used (deleted_at, is_deleted) and why physical deletion is avoided (audit preservation)

A Mermaid dependency diagram is included showing OrgFeatureFlagService → downstream epics/features it gates

Documentation is written in English, uses clear headings, and is navigable via a table of contents

A new developer following the documentation alone can understand the data layer without reading source code

Technical Requirements

frameworks

Dart

Supabase

apis

Supabase PostgREST

Supabase RLS (PostgreSQL Row Level Security)

data models

org_feature_flags table

declaration_templates table

driver_assignments table

declaration_audit_log table

security requirements

Documentation must not include real credentials, real org IDs, or production data — use placeholder values in all examples

RLS policy documentation must clearly state which roles (anon, authenticated, service_role) bypass or are subject to each policy

Execution Context

Execution Tier

Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

View Full Execution Plan

Implementation Notes

Use Mermaid for the dependency diagram (supported natively in GitHub Markdown and most documentation platforms). Structure the document with these top-level sections: Overview, Table Schemas, RLS Policies, Design Decisions (feature flag strategy, versioning, soft-delete), Dependency Graph, FAQ. For the RLS section, format each policy as a code block with the SQL followed by a bullet-point explanation. Keep examples concrete — show a sample row for each table with realistic (but fictional) data.

Cross-reference the DriverFeatureFlagConfig constants file so readers can find the authoritative list of feature keys. If the team uses Notion or Confluence, mirror the markdown there; keep the markdown file in the repo as the source of truth.

Testing Requirements

No automated tests for documentation. Peer review acceptance criterion: at least one other engineer on the team must read the document and confirm they can answer the following questions without looking at source code: (1) What does the audit log RLS prevent? (2) How does a new org get the driver feature enabled? (3) What happens if a template version is deleted — are old declarations affected?

Document review comments must be resolved before the task is closed.

Component

Driver Feature Flag Configuration

infrastructure low

Dependencies (2)

Write integration tests for DeclarationTemplateRepository (versioned fetch), DriverAssignmentRepository (CRUD and soft-delete), DeclarationRepository (lifecycle state transitions), and DeclarationAuditLogger (insert-only enforcement, all event types). Use a test Supabase project or local emulator. Verify RLS policies reject cross-org queries. epic-driver-and-confidentiality-management-foundation-task-012 Write unit tests for OrgFeatureFlagService covering: flag enabled returns true, flag disabled returns false, missing record defaults to false, error returns false, cache hit avoids second DB call, and cache invalidation triggers re-fetch. Use mock Supabase client. Also test DriverFeatureFlagConfig constants and model serialization. epic-driver-and-confidentiality-management-foundation-task-013

Epic Risks (3)

high impact medium prob security

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.

high impact medium prob security

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.

medium impact low prob technical

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.

Quick Links

All Tasks Execution Plan