Supabase Backend Core
Feature Detail
Description
The Supabase Backend Core establishes the foundational backend infrastructure for the Likeperson application, encompassing the Supabase project configuration, database schema initialization, authentication integration, real-time subscription setup, and Edge Functions scaffolding. It provides the single Supabase client service that all other features consume for database reads/writes, authentication state, and real-time updates. This layer also defines the Row Level Security (RLS) policies that enforce role-based data access at the database level, providing defense-in-depth for sensitive personal data handled under GDPR.
Analysis
Supabase is the single backend platform underpinning authentication, data persistence, real-time features, and server-side logic for all organizations. A correctly configured Supabase core with RLS policies is non-negotiable for GDPR compliance — the application handles sensitive personal data including health information (epikrise references), location data, and financial reimbursement records. Real-time subscriptions enable coordinators to see live updates to assignment statuses and peer mentor availability without polling. The Edge Functions infrastructure enables server-side processing for encrypted assignment dispatch and Bufdir report generation without exposing API keys to mobile clients.
The Supabase client is initialized as a singleton Riverpod provider using supabase_flutter, with environment-based configuration (dev/staging/prod) managed via dart-define or a .env loader. The full database schema (all tables from the blueprint) is managed via Supabase migrations in a /supabase/migrations directory under version control. RLS policies are defined per table using Supabase's policy DSL, tied to auth.uid() and custom JWT claims for role and organization. Real-time subscriptions use Supabase Realtime channels with Riverpod StreamProviders. Edge Functions are written in TypeScript/Deno and deployed via Supabase CLI. The client service exposes typed repository interfaces consumed by feature-level data repositories.
Dependencies
Definition of Done
Components (5)
User Stories (10)
As a As a Global Administrator
I want I want to monitor database performance metrics, API request rates, and resource usage across the Supabase platform
So that So that I can proactively identify performance bottlenecks, plan capacity upgrades, and detect anomalous usage patterns before they impact end users
- Given multiple organizations are actively using the platform, when the admin views Supabase dashboard metrics, then aggregate and per-project usage statistics are visible
- Given a slow database query is occurring frequently, when the admin reviews the pg_stat_statements data, then the problematic query is identifiable with execution time and frequency
- Given API error rates spike above a threshold, when the admin checks request logs, then error types (400, 401, 403, 500) are broken down with request paths to identify the source
- +2 more
As a As a Global Administrator
I want I want to configure and manage the CI/CD pipeline integration with Supabase CLI for automated migration and function deployment
So that So that schema changes and Edge Function updates are deployed consistently and reproducibly without manual intervention, reducing the risk of environment drift
- Given a migration is merged to the main branch, when the CI/CD pipeline runs, then the migration is automatically applied to staging before any manual promotion step to production
- Given a migration has a syntax error, when the pipeline attempts to apply it, then the pipeline fails with a clear error message and staging database remains unchanged
- Given the pipeline deploys an Edge Function update, when the deployment succeeds, then the new function version is active and the previous version is preserved for rollback
- +2 more
As a As a Global Administrator
I want I want to monitor the health and performance of Supabase Realtime channel subscriptions across all active organizations
So that So that coordinators and peer mentors receive live updates on assignment statuses and availability without polling failures going undetected
- Given multiple organizations have active real-time subscriptions, when the admin views Supabase platform metrics, then active connection counts and message throughput are visible per channel
- Given a Realtime channel experiences repeated disconnections, when the reconnection logic executes, then the subscription recovers without manual intervention and the admin can see the reconnection events in logs
- Given the admin reviews channel configuration, when listing active subscriptions, then each channel shows its filter criteria (e.g., assignment_id, organization_id) confirming no cross-organization data leakage via Realtime
- +2 more
As a As a Global Administrator
I want I want to provision a new member organization's context on the Supabase platform including database records, RLS scope, and integration configuration
So that So that the new organization is fully isolated from existing organizations with correct data access boundaries and can begin using the platform without manual database manipulation
- Given a new organization is being onboarded, when the admin creates the organization record and assigns an admin user, then the new admin can log in and access only their organization's data
- Given the new organization's admin performs database operations, when RLS evaluates their JWT claims, then only data scoped to their organization_id is returned
- Given the new organization has unique terminology (e.g., 'frivillig' instead of 'likeperson'), when the admin configures org_labels, then all UI labels reflect the organization's terminology without code changes
- +2 more
As a As a Global Administrator
I want I want to configure Supabase Storage buckets with appropriate access policies for receipts, documents, and certificates
So that So that uploaded files are stored securely with access limited to the uploading user and authorized coordinators, preventing unauthorized file access across organizations
- Given a peer mentor uploads an expense receipt, when another peer mentor from the same organization attempts to access the file URL, then access is denied with a 403 response
- Given a coordinator accesses their organization's receipt storage bucket, when listing files, then only receipts from peer mentors in their organization are visible
- Given the admin configures a bucket for encrypted assignment documents, when the bucket policy is reviewed, then direct public access is disabled and access requires a valid JWT
- +2 more
As a As a Global Administrator
I want I want to deploy and manage TypeScript/Deno Edge Functions via Supabase CLI for server-side operations that must not expose API keys to mobile clients
So that So that encrypted assignment dispatch, Bufdir report generation, and accounting webhook processing can execute securely on the server without leaking credentials to the mobile app
- Given a new Edge Function is developed, when the admin runs supabase functions deploy, then the function is available at the expected endpoint in the target environment
- Given an Edge Function requires a secret API key, when the admin sets the secret via supabase secrets set, then the key is accessible to the function at runtime but not exposed to mobile clients
- Given an Edge Function is invoked, when the admin reviews function logs, then request/response details, execution time, and any errors are visible in Supabase dashboard
- +2 more
As a As a Global Administrator
I want I want to configure and manage Supabase project settings across dev, staging, and production environments
So that So that each environment is correctly isolated, preventing dev data from contaminating production and enabling safe testing of migrations before release
- Given a new environment is being provisioned, when the admin configures environment variables, then the Supabase client initializes with the correct project URL and anon key for that environment
- Given the admin updates production credentials, when the app is deployed, then all database reads/writes route to the production Supabase project without cross-environment data leakage
- Given dev, staging, and production environments exist, when the admin views configuration, then each environment shows distinct project IDs and connection strings
- +1 more
As a As a Global Administrator
I want I want to configure the JWT token structure to include custom claims for user role and organization membership
So that So that RLS policies can enforce organization-scoped data access without requiring additional database lookups on every request
- Given a user authenticates successfully, when the JWT is decoded, then it contains role, organization_id, and local_association_id claims matching the user's database record
- Given a user belongs to multiple organizations, when their JWT is generated, then it includes claims for all applicable organization memberships
- Given a user's role changes in the database, when they refresh their token, then the new JWT reflects the updated role claim within the configured token refresh window
- +2 more
As a As a Global Administrator
I want I want to deploy database schema migrations via Supabase CLI with full version control in /supabase/migrations
So that So that all schema changes are auditable, reversible, and consistently applied across all environments without manual database interventions
- Given a new migration file is added to /supabase/migrations, when the admin runs supabase db push, then the migration is applied to the target environment and recorded in the migrations table
- Given multiple pending migrations exist, when the admin deploys, then migrations are applied in sequential order without skipping or reordering
- Given a migration fails midway, when the admin checks migration status, then the failed migration is clearly identified and the database remains in its pre-migration state
- +2 more
As a As a Global Administrator
I want I want to define, review, and audit Row Level Security (RLS) policies on all database tables using auth.uid() and custom JWT claims
So that So that each user can only access data their role and organization membership permits, providing defense-in-depth for sensitive personal data under GDPR
- Given a peer mentor user attempts to query another user's activities, when the SELECT query executes, then RLS returns zero rows without exposing the unauthorized data
- Given a coordinator queries activities, when RLS evaluates the policy, then only activities within their organization's scope are returned
- Given the global admin reviews RLS policies, when listing all policies via Supabase dashboard or CLI, then every table with sensitive data shows active RLS policies with documented policy definitions
- +3 more