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

Create Bufdir aggregation query functions

epic-bufdir-report-period-selection-foundation-task-003 — Implement Supabase RPC functions or materialized views that support range-based activity count queries against the activities table. Functions must accept organisation ID, start date, end date parameters and return aggregate counts grouped by Bufdir category codes.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

A Supabase RPC function get_bufdir_activity_count(org_id, start_date, end_date) exists and returns the total activity count for the given organisation and date range
A Supabase RPC function get_bufdir_range_breakdown(org_id, start_date, end_date) exists and returns rows with bufdir_category_code and activity_count columns
Both functions enforce RLS so callers can only access data for organisations they belong to
Queries use indexed columns (org_id, activity_date) to avoid sequential table scans
Functions handle NULL or missing bufdir_category_code values by grouping them under an 'uncategorised' bucket
Both functions return an empty result set (not an error) when no activities exist for the given range
Invalid date ranges (end_date < start_date) return an empty result set or a PostgreSQL error that is handled gracefully by the caller
Functions are documented with inline SQL comments describing parameters and return shapes
A migration file (SQL) is committed to the repository so the schema can be reproduced in any environment

Technical Requirements

frameworks
Supabase (PostgreSQL RPC / pl/pgsql)
apis
Supabase RPC endpoint
data models
Activity
BufdirCategory
performance requirements
Both RPC functions must return results in under 500 ms for organisations with up to 10 000 activity records
Composite index on (org_id, activity_date, bufdir_category_code) must be created to support range queries
security requirements
Functions must be defined as SECURITY DEFINER only if RLS cannot be applied directly; otherwise use SECURITY INVOKER
Row-level security policies on the activities table must prevent cross-organisation data leakage
Function signatures must not expose internal table names or schema details in error messages

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Prefer Supabase RPC functions (CREATE FUNCTION) over materialized views to avoid stale data issues — materialized views require explicit refresh scheduling. Use pl/pgsql with DATE_TRUNC or direct date comparisons on the activity_date column. Ensure the activities table has a composite index on (org_id, activity_date); add it in the same migration if it does not exist. The bufdir_category_code column should be a foreign key to a bufdir_categories lookup table, or at minimum a constrained text column.

Use COALESCE(bufdir_category_code, 'uncategorised') in the GROUP BY clause. Commit the function definitions as a numbered Supabase migration file (e.g. 20260001_bufdir_aggregation_functions.sql) so they can be applied deterministically via the Supabase CLI.

Testing Requirements

Write SQL-level tests using pgTAP or manual test scripts executed against a staging Supabase project. Test cases: (1) returns correct count for a known date range, (2) returns correct per-category breakdown, (3) returns empty result for a date range with no activities, (4) RLS prevents access to another organisation's data, (5) handles NULL bufdir_category_code gracefully, (6) performance test with 10 000 rows completes under 500 ms. Document expected inputs and outputs in the migration file comments.

Component
Bufdir Aggregation 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