Report History RLS Policy Configuration
Component Detail
Description
Supabase Row Level Security policy definitions for the bufdir_report_history table. Ensures all queries are automatically scoped to the user's organization and that only coordinator and admin roles can read or write records.
report-history-rls-policy
Summaries
The Report History RLS Policy Configuration is the database-level security layer that guarantees compliance reports can never be viewed or modified by users outside the owning organization. Operating transparently beneath the application layer, it enforces two critical business rules: data is always scoped to the user's organization, and only coordinators and administrators may read or write report records. This means that even if an application bug or misconfigured API call attempts to access another organization's data, the database itself will silently block the query. For a platform serving sensitive social services data, this defense-in-depth approach is a material risk-reduction investment that supports Bufdir's data governance and audit requirements.
This is a low-complexity, configuration-level deliverable that lives at the database schema layer rather than in application code. It has no application-level dependencies, so it can be implemented independently of feature development and deployed as part of the initial database migration. The primary delivery risk is policy misconfiguration — an overly permissive rule could expose cross-organization report records, while an overly restrictive rule could silently block legitimate coordinator queries. A dedicated QA pass using test accounts from two separate organizations, exercising both coordinator and non-privileged roles, is essential before any report history feature goes to staging.
This policy should be peer-reviewed by someone familiar with Supabase RLS syntax, as errors are not always surfaced as explicit errors.
This component defines Supabase Row Level Security policies on the bufdir_report_history table, linked to the bufdir-report-record data model. The three logical interfaces — applyOrgScopeFilter, enforceRoleConstraint, and validateRetentionPolicy — map to distinct RLS policy expressions rather than callable functions. The org-scope filter should be implemented as a USING clause comparing organization_id to auth.jwt() -> 'organization_id', ensuring automatic query scoping without application-layer filtering. Role enforcement requires a separate policy checking the user's role claim against an allowlist of coordinator and admin.
The retention validation interface is most likely a CHECK constraint or a policy that prevents reads on records beyond the 5-year window. All policies should be tested with Supabase's RLS testing utilities and explicitly verified against service-role bypass to ensure policies are not inadvertently skipped.
Responsibilities
- Define RLS policies restricting read access to org-scoped records
- Restrict write access to coordinator and admin roles
- Enforce organization_id isolation for multi-tenant safety
- Document retention policy alignment (5-year Bufdir audit window)
Interfaces
applyOrgScopeFilter(query)
enforceRoleConstraint(role)
validateRetentionPolicy(createdAt)
Related Data Entities (2)
Data entities managed by this component