Activity types database schema and migrations

epic-activity-type-configuration-admin-interface-task-001 — Create the Supabase database schema for the activity_types table including columns for display_name, org_label_override, description, default_duration_minutes, is_travel_eligible, is_report_required, is_reimbursement_trigger, bufdir_category, status (active/archived), org_id, and timestamps. Write migration scripts and set up RLS policies scoped to coordinator role per organisation.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Table `activity_types` exists in Supabase with all required columns: id (uuid, primary key, default gen_random_uuid()), org_id (uuid, not null, FK to organisations), display_name (text, not null, max 80 chars), org_label_override (text, nullable), description (text, nullable), default_duration_minutes (integer, not null, default 30, check > 0), is_travel_eligible (boolean, not null, default false), is_report_required (boolean, not null, default false), is_reimbursement_trigger (boolean, not null, default false), bufdir_category (text, not null, FK or check constraint to valid enum values), status (text, not null, default 'active', check in ('active', 'archived')), created_at (timestamptz, not null, default now()), updated_at (timestamptz, not null, default now())

Migration script is idempotent and can be run safely in both fresh and existing databases

RLS policy `activity_types_coordinator_select` allows coordinators to SELECT only rows where org_id matches their organisation

RLS policy `activity_types_coordinator_insert` allows coordinators to INSERT rows only with their own org_id

RLS policy `activity_types_coordinator_update` allows coordinators to UPDATE rows only within their own organisation

RLS policy `activity_types_coordinator_delete` is absent — coordinators may only archive (set status = 'archived'), not hard delete

Org admin role has full CRUD RLS policy with org_id scoping

An `updated_at` trigger exists that automatically sets updated_at = now() on any row update

Index exists on (org_id, status) for efficient listing queries

Index exists on (org_id, bufdir_category) to support Bufdir reporting queries

All RLS policies are enabled on the table (RLS is ON)

Migration script is versioned (e.g., 20260326_001_create_activity_types.sql) and recorded in the Supabase migration history

Empty organisation cannot see or modify activity types from another organisation — verified by cross-org query test

Technical Requirements

frameworks

Supabase (PostgreSQL)

Supabase CLI for migrations

apis

Supabase REST API

Supabase Auth (for RLS JWT claims)

data models

activity_types

organisations

user_roles

performance requirements

SELECT query for listing all active activity types per org completes in < 100ms

Composite index on (org_id, status) ensures efficient filtered scans

Table must scale to 500+ activity types per organisation without degradation

security requirements

Row Level Security (RLS) must be ON — no row accessible without an authenticated JWT

org_id on INSERT must be derived from the authenticated user's JWT claim, not a client-supplied value

Service role key must NOT be used from the Flutter client — use anon key with RLS only

bufdir_category must be constrained to approved values via CHECK constraint to prevent invalid Bufdir submissions

Archived rows remain immutable — no updates allowed on status = 'archived' rows except by org admin

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Use Supabase CLI `supabase migration new create_activity_types` to generate a versioned migration file. Define org_id RLS derivation using `auth.jwt() ->> 'org_id'` or a custom claim set during login — confirm which claim shape the project's auth flow uses before writing policies. Use a CHECK constraint for bufdir_category values rather than a separate lookup table to keep the schema self-contained; the Flutter enum (task-003) must stay in sync. Add a comment block at the top of the migration file listing all dependent tasks (task-002 through task-006) so future developers understand the dependency chain.

The `status` column should use 'active'/'archived' strings (not booleans) to leave room for future states (e.g., 'draft'). Do NOT use ENUM types in Postgres — use CHECK constraints for easier future value additions without migrations.

Testing Requirements

Write SQL integration tests using pgTAP or Supabase's built-in test runner: (1) assert all required columns exist with correct types and constraints; (2) test RLS — a coordinator JWT from org A cannot read, insert, or update rows from org B; (3) test that INSERT with a mismatched org_id is rejected; (4) test that the updated_at trigger fires correctly on UPDATE; (5) test that the bufdir_category CHECK constraint rejects invalid values; (6) test that hard DELETE is blocked for coordinator role. In Flutter, write widget/integration tests that confirm the Supabase client receives a 403 or empty result when querying another org's rows. Aim for 100% coverage of RLS policy branches.

Component

Activity Type Admin Screen

ui medium

Epic Risks (3)

high impact medium prob dependency

The Bufdir reporting category list is defined externally by Bufdir and may change between reporting years. If the dropdown in ActivityTypeFormScreen is hardcoded, existing activity type mappings could become invalid after a Bufdir schema update, breaking export validation for all organisations.

Mitigation & Contingency

Mitigation: Store the valid Bufdir category list in a Supabase configuration table (bufdir_categories) rather than as a Dart constant, so it can be updated by an admin without a mobile app release. Load the list in the form screen via a lightweight repository call cached locally.

Contingency: If the Bufdir category list cannot be externalised before the admin screen ships, expose a manual override field that allows coordinators to enter a raw Bufdir category code as a fallback, and schedule the configuration table migration as a follow-up task.

medium impact medium prob technical

Reusing ActivityTypeFormScreen for both creation and editing requires careful Riverpod provider scoping. If the form provider is not properly reset between navigation events, stale values from a previously edited type may pre-populate a new creation form, leading to incorrect data being saved.

Mitigation & Contingency

Mitigation: Scope the form state provider to the route using Riverpod's autoDispose modifier, ensuring the state is torn down when the screen is popped. Write a widget test that navigates to edit type A, pops, navigates to create new, and asserts all fields are empty.

Contingency: If provider scoping proves complex with the current router setup, fall back to separate widget implementations for create and edit that share a common form widget but maintain independent provider instances.

high impact low prob integration

Archiving an activity type must not break historical Bufdir export queries that filter activities by type. If the export pipeline performs an INNER JOIN against only active activity types, archived types will cause historical activities to be silently excluded from exports, producing incorrect reporting data.

Mitigation & Contingency

Mitigation: Audit all downstream query builders (Bufdir export, stats aggregation) before shipping the archive feature to confirm they join against all activity types regardless of is_active status. Add an integration test that archives a type, then asserts historical activity records for that type still appear in export queries.

Contingency: If a downstream query is discovered to filter on is_active post-launch, apply a targeted Supabase view fix that unions active and archived types for export contexts without requiring a mobile app update.

Quick Links

All Tasks Execution Plan