Add is_proxy_registered Column to Activities Table

epic-bufdir-data-aggregation-foundation-task-005 — Write the PostgreSQL migration that adds the is_proxy_registered boolean column (default false) to the activities table. Add an index on (org_id, is_proxy_registered) for aggregation query performance. Update existing RLS policies to include the new column and ensure it is set correctly by the proxy registration service. Document the deduplication implications for Bufdir COUNT DISTINCT calculations.

high priority low complexity database pending database specialist Tier 2

Acceptance Criteria

Migration adds is_proxy_registered BOOLEAN NOT NULL DEFAULT false to the activities table without locking the table (use ADD COLUMN with DEFAULT for zero-downtime)

Composite index on (org_id, is_proxy_registered) is created CONCURRENTLY to avoid table locks

All existing rows have is_proxy_registered = false after migration (default applied retroactively)

RLS policies are reviewed and updated so that is_proxy_registered can only be set to true by the proxy registration service role, not by end users

Migration rollback removes the column and index cleanly

A comment on the column documents its purpose: 'True when this activity was registered by a coordinator on behalf of a peer mentor (proxy). Used to deduplicate participant counts in Bufdir reports.'

generate_bufdir_report RPC (task-007) correctly excludes or handles proxy-registered activities per Bufdir's deduplication rules

Technical Requirements

frameworks

Supabase Migrations (SQL)

apis

Supabase CLI

data models

activities

bufdir_category_mappings (related context)

performance requirements

CREATE INDEX CONCURRENTLY must be used to avoid blocking production queries

EXPLAIN ANALYZE on a COUNT DISTINCT query with WHERE org_id = $1 AND is_proxy_registered = false must show index scan, not seq scan

security requirements

RLS policy must prevent peer mentors from self-setting is_proxy_registered = true

Only coordinator role or service_role may set is_proxy_registered = true

Column value must be auditable — consider adding a proxy_registered_by (UUID) column referencing the coordinator who registered

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Use `ALTER TABLE activities ADD COLUMN IF NOT EXISTS is_proxy_registered BOOLEAN NOT NULL DEFAULT false` for idempotency. Because activities tables in multi-org systems can be large, use `CREATE INDEX CONCURRENTLY` even in migrations — this requires running outside a transaction block (use `-- noqa: transaction` comment if using a migration tool that wraps in transactions). The deduplication logic for Bufdir is: when a participant appears in both a proxy-registered activity and a directly self-registered activity in the same period, they must be counted only once in COUNT DISTINCT. The generate_bufdir_report RPC should use the activities table directly (not a view) and apply the deduplication logic inline.

Document this in a migration comment and in the repo wiki.

Testing Requirements

Migration tests: (1) apply migration to a copy of production schema and verify no column conflicts, (2) verify DEFAULT false is applied to all pre-existing rows, (3) verify index exists and is used in EXPLAIN output for the aggregation query pattern. RLS tests: verify that a peer mentor JWT cannot UPDATE activities SET is_proxy_registered = true. Integration test: insert a proxy activity and a direct activity for the same participant_id, run the Bufdir COUNT DISTINCT query, verify participant is counted once not twice.

Component

Activity Category Mapping Configuration

infrastructure low

Dependencies (1)

Implement the PostgreSQL migration file that creates and enables Row Level Security policies on all relevant tables (activities, user_stories, components, etc.). Include USING and WITH CHECK clauses tied to app.current_org_id, grant policies per role (peer_mentor, coordinator, admin), and include a rollback strategy. Validate that no SELECT, INSERT, UPDATE, or DELETE can cross organization boundaries. epic-bufdir-data-aggregation-foundation-task-002

Epic Risks (3)

high impact medium prob security

Supabase RLS policies may not propagate correctly into RPC function execution context, causing org-scoping predicates to be silently ignored when the function is invoked with service_role key. This could lead to cross-org data exposure in production without any obvious error.

Mitigation & Contingency

Mitigation: Invoke all RPCs using the anon/authenticated key rather than service_role, write explicit WHERE org_id = auth.uid()::org_id predicates inside the RPC body as a secondary control, and include automated cross-org leakage tests in the CI pipeline from day one.

Contingency: If RLS bypass is discovered post-deployment, immediately revoke service_role usage in all aggregation paths and hotfix with explicit org_id parameters passed as function arguments validated server-side.

high impact medium prob dependency

Bufdir may update its official reporting category taxonomy between the mapping configuration being defined and the annual submission deadline. If the ActivityCategoryMappingConfig is compiled as a static Dart constant, it cannot be updated without an app release, potentially causing mapping failures that block submission.

Mitigation & Contingency

Mitigation: Store the mapping as a remote-configurable table (bufdir_category_mappings) in Supabase with a version field rather than as a hardcoded Dart constant. Fetch the current mapping at aggregation time so updates can be pushed without a new app release.

Contingency: If a mapping mismatch is detected during an active reporting cycle, coordinators can be temporarily directed to the manual Excel fallback while an emergency mapping update is pushed to the Supabase table.

high impact low prob technical

For large organisations like NHF with 1,400 local chapters and potentially tens of thousands of activity records per reporting period, the Supabase RPC aggregation query may exceed the default PostgREST statement timeout, causing the aggregation to fail with a 503 error.

Mitigation & Contingency

Mitigation: Add partial indexes on (organization_id, created_at) and (organization_id, activity_type_id) to the activities table before writing the RPC. Profile the query plan against a realistic fixture of 50,000 records during development and increase the statement_timeout setting for the RPC role if needed.

Contingency: Implement chunked aggregation fallback: split the period into monthly sub-ranges and aggregate each chunk client-side, merging results with UNION-style Dart logic before assembling the final payload.

Quick Links

All Tasks Execution Plan