🏠 Overview › ✅ Implementation Tasks ›✅ epic-bufdir-report-period-selection-foundation-task-001

Define period configuration Supabase schema

epic-bufdir-report-period-selection-foundation-task-001 — Create the Supabase database table and RLS policies for per-organisation period configuration presets. The table must store preset name, start date, end date, and organisation ID with row-level security ensuring organisations can only read and write their own presets.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

A new table `report_period_presets` exists in the Supabase PostgreSQL database with columns: id (uuid, primary key, default gen_random_uuid()), organization_id (uuid, not null, foreign key → organizations.id), name (text, not null), start_date (date, not null), end_date (date, not null), created_at (timestamptz, not null, default now()), updated_at (timestamptz, not null, default now())
A CHECK constraint enforces that end_date >= start_date on every row
A unique constraint exists on (organization_id, name) to prevent duplicate preset names within the same organisation
Row-Level Security (RLS) is enabled on the table
An RLS SELECT policy allows authenticated users to read only rows where organization_id matches their JWT claim (e.g., auth.jwt() -> 'organization_id')
An RLS INSERT policy allows authenticated users to insert rows only where the organization_id matches their JWT claim
An RLS UPDATE policy allows authenticated users to update only their own organisation's rows
An RLS DELETE policy allows authenticated users to delete only their own organisation's rows
The service role (used by Edge Functions) bypasses RLS as per Supabase default behavior — this is acceptable and intentional
A migration file is created in the Supabase migrations directory (supabase/migrations/) with a descriptive timestamped filename
Applying the migration to a fresh Supabase instance succeeds without errors
Rolling back the migration (if rollback script provided) leaves the database in its prior state
The table is documented with a brief SQL comment explaining its purpose

Technical Requirements

frameworks
Supabase
apis
Supabase PostgreSQL 15
Supabase Auth (JWT claims for RLS)
data models
annual_summary
bufdir_export_audit_log
performance requirements
An index exists on organization_id to ensure efficient per-organisation queries
An index exists on (organization_id, start_date, end_date) to support date-range lookups for preset matching
security requirements
RLS must be enabled before any data is inserted — never disable RLS on this table
The JWT claim used for RLS (organization_id) must match the claim name established by the Supabase Auth configuration in this project — verify with the existing auth setup
No SELECT policy may allow cross-organisation reads — test by querying with a JWT from organisation A for rows belonging to organisation B and confirming zero rows returned
Service role key is server-side only — never expose it in the Flutter mobile client
All date fields use date (not timestamp) type to avoid timezone ambiguity in period boundary comparisons

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use the Supabase CLI to create the migration file: `supabase migration new report_period_presets`. Write the migration in SQL, not through the Supabase Studio UI, to ensure it is version-controlled and reproducible. Extract the organisation_id from the JWT via `(auth.jwt() ->> 'organization_id')::uuid` in RLS policies — confirm this matches the claim name used by existing tables in the project (check existing RLS policies for the correct pattern). Define USING and WITH CHECK clauses on all RLS policies to cover both read guards and write guards separately.

Add `updated_at` trigger using the project's standard `moddatetime()` trigger function if it exists, or define it inline. Consider adding a `description` text column (nullable) now to avoid a future migration for a likely-needed field. Coordinate with the PeriodConfigurationRepository implementation task to confirm the Dart model aligns with this schema.

Testing Requirements

Database integration tests: apply the migration to a local Supabase instance (supabase start). Test RLS isolation: insert a row for org-A, then query using a JWT for org-B and assert zero rows returned. Test CHECK constraint: attempt to insert a row where start_date > end_date and assert the insert is rejected with a constraint violation error. Test unique constraint: insert two presets with the same name for the same organisation and assert the second insert is rejected.

Test that an authenticated user can insert, select, update, and delete their own organisation's presets. Run `supabase db lint` or equivalent to confirm no RLS policy warnings on the new table.

Component
Period Configuration Repository
data low
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for period preset configuration may be missing or incorrectly scoped, causing one organisation's presets to leak to another or write operations to fail silently.

Mitigation & Contingency

Mitigation: Define and review RLS policies for the bufdir_period_presets table in the migration file before any repository code is written. Include an integration test that verifies cross-organisation isolation using two distinct org credentials.

Contingency: If RLS is misconfigured in production, immediately disable the period preset fetch endpoint and fall back to hardcoded global presets until the policy is corrected and redeployed.

medium impact medium prob technical

The activities table may lack a composite index on (organisation_id, activity_date), causing the range count query in BufdirAggregationRepository to perform a full table scan and exceed acceptable response time for large organisations.

Mitigation & Contingency

Mitigation: Add a migration that creates a composite index on (organisation_id, activity_date) as part of this epic. Benchmark the count query against a representative dataset (10 000+ rows) before marking the epic complete.

Contingency: If query latency is unacceptable after indexing, move the count query to a Supabase RPC function that leverages a materialised view or partial index, accepting a slight staleness window.

medium impact medium prob technical

Flutter's native date picker widgets have known accessibility gaps (missing semantic labels, non-standard focus traversal) that may prevent WCAG 2.2 AA compliance out of the box, requiring a custom implementation.

Mitigation & Contingency

Mitigation: Evaluate third-party accessible date picker packages (e.g., table_calendar with custom semantics) against WCAG 2.2 AA criteria before beginning implementation. Document the chosen approach in the epic kick-off.

Contingency: If no package meets accessibility requirements, implement a simple text-field-based date entry with explicit semantic labels and format hints as an accessible fallback, deferring a fully visual calendar to a later iteration.

Quick Links
All Tasks Execution Plan