Create Supabase organization_configs table schema
epic-organization-feature-flags-foundation-task-001 — Design and implement the Supabase database schema for the organization_configs table, including columns for organization_id, flag_key, enabled, rollout_percentage, min_app_version, activation_date, and metadata. Enforce multi-tenant isolation at the query level using organization_id as the partition key.
Acceptance Criteria
Technical Requirements
Implementation Notes
Structure the migration as a single .sql file with clear sections: (1) CREATE TABLE, (2) CREATE INDEX statements, (3) ALTER TABLE ENABLE ROW LEVEL SECURITY, (4) CREATE POLICY statements, (5) CREATE TRIGGER for updated_at. For the updated_at trigger, use the standard moddatetime extension if available in your Supabase project (supabase/extensions), otherwise write a simple PL/pgSQL trigger function. The flag_key column should be constrained to lowercase alphanumeric with hyphens using a CHECK (flag_key ~ '^[a-z0-9-]+$') pattern to prevent typos and case mismatch bugs. The metadata JSONB column is intentionally flexible — document its expected shape in a comment rather than enforcing a strict schema, as feature flag metadata varies by flag type.
For the Dart repository layer (693-feature-flag-repository), the table name, column names, and RLS policy must match exactly — establish a shared constants file for table/column name strings to prevent typo-driven runtime errors. Consider adding a Supabase Realtime subscription channel for the organisation_configs table so the app can receive flag changes without polling — this supports the incremental rollout requirement described in the workshop findings.
Testing Requirements
Database tests should be written as Supabase pgTAP tests (if the project uses pgTAP) or as Dart integration tests that exercise the repository layer against a local Supabase instance. Test cases: (1) INSERT a valid flag row and SELECT it back — assert all columns match; (2) attempt INSERT with rollout_percentage = 101 — assert check constraint violation; (3) attempt INSERT with duplicate (organization_id, flag_key) — assert unique constraint violation; (4) authenticate as org A user and attempt to SELECT org B rows — assert zero rows returned (RLS test); (5) attempt INSERT via the anon key — assert RLS denies the write; (6) UPDATE a row and assert updated_at is newer than created_at. In Flutter: write a Dart unit test for the FeatureFlagRepository.getEnabledFlags(organizationId) method using a mocked Supabase client, asserting that the correct query filter and column selection are applied.
Supabase RLS policies for organization_configs may have gaps that allow cross-organization reads if the JWT claim for organization_id is absent or malformed, leading to data leakage between tenants.
Mitigation & Contingency
Mitigation: Implement RLS policies using auth.uid() joined against a memberships table to derive organization_id rather than trusting a client-supplied claim. Write integration tests that simulate a cross-org read attempt and assert it returns zero rows.
Contingency: If a gap is discovered post-launch, immediately disable the affected RLS policy, roll back the migration, and re-implement with a parameterized policy tested against all organization fixture data.
Dart does not have a built-in semantic version comparison library; a naive string comparison (e.g., '2.10.0' < '2.9.0' lexicographically) would cause rollout evaluator to produce incorrect eligibility results for organizations on different app versions.
Mitigation & Contingency
Mitigation: Use the pub.dev `pub_semver` package or implement a proper three-segment integer comparison. Add parameterized unit tests covering 20+ version pairs including double-digit minor/patch segments.
Contingency: If incorrect comparison is discovered in production, push a hotfix with corrected comparison logic and temporarily disable phase-gated flags until all affected organizations have updated to the corrected version.
Persistent local cache written to shared_preferences or Hive could become corrupted or deserialized incorrectly after an app update changes the FeatureFlag schema, causing startup crashes or all flags defaulting to disabled.
Mitigation & Contingency
Mitigation: Wrap all cache reads in try/catch with explicit fallback to the all-disabled default map. Version the cache key (e.g., `feature_flags_v2_{orgId}`) so schema changes automatically invalidate old entries.
Contingency: If cache corruption is detected in a release, publish an app update that clears the versioned cache key on first launch and re-fetches from Supabase.