🏠 Overview › ✅ Implementation Tasks ›✅ epic-cognitive-accessibility-foundation-task-001

Database schema for wizard draft persistence

epic-cognitive-accessibility-foundation-task-001 — Design and implement the SQLite/Supabase database schema for persisting wizard draft state. Create tables for draft sessions, step progress, and partial form data. Include auto-save timestamps, user/session identifiers, and TTL fields for draft expiry. Ensure the schema supports the ≤5 wizard steps constraint at the data layer.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

A `wizard_drafts` table exists in Supabase with columns: id (uuid PK), user_id (uuid FK → auth.users), wizard_type (text, non-null), current_step_index (integer, 0–4 CHECK constraint enforcing ≤5 steps), step_data (jsonb), created_at (timestamptz), updated_at (timestamptz), expires_at (timestamptz)
The CHECK constraint `current_step_index BETWEEN 0 AND 4` is enforced at the database level
Row Level Security (RLS) is enabled on `wizard_drafts`; a policy allows SELECT/INSERT/UPDATE/DELETE only where `auth.uid() = user_id`
A Supabase database function or pg_cron job deletes rows where `expires_at < now()` — TTL expiry is enforced server-side
Default TTL for a draft is 7 days from last `updated_at`; this is set via a trigger that updates `expires_at = updated_at + interval '7 days'` on every row update
The schema supports multiple concurrent drafts per user for different `wizard_type` values (e.g., 'activity', 'event')
A unique partial index exists on `(user_id, wizard_type)` where `expires_at > now()` to prevent duplicate active drafts of the same type per user
Migration script is idempotent — running it twice does not error or duplicate objects
Schema is documented in a migration file in `supabase/migrations/` with a descriptive name and timestamp prefix

Technical Requirements

frameworks
Flutter
Supabase
apis
Supabase REST API
Supabase Realtime (optional, for cross-device sync)
data models
WizardDraft
WizardStepData
performance requirements
Index on `(user_id, wizard_type)` to ensure draft lookup by user+type is O(log n)
JSONB `step_data` column must not exceed 64KB per row; enforce via CHECK constraint or application-layer validation
TTL cleanup job must run at least once per hour to prevent table bloat
security requirements
RLS must be enabled — no row is accessible without a matching `auth.uid()` check
Service role key must never be used client-side; all draft operations use the anon/user JWT
`step_data` jsonb must not store raw PII fields (e.g., full personnummer) in plaintext — store references or masked values only
expires_at enforcement prevents indefinite data retention, supporting GDPR data minimization

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use a single `wizard_drafts` table with a `wizard_type` discriminator column rather than separate tables per wizard — this keeps the schema flat and avoids migration complexity as new wizard types are added. Store all step form data in a single JSONB `step_data` column keyed by step index (e.g., `{"0": {...}, "1": {...}}`); this avoids normalizing wizard-specific fields into the schema. The `current_step_index` column represents the highest step the user has reached, not necessarily the current view — the UI can navigate freely within reached steps. The CHECK constraint on `current_step_index BETWEEN 0 AND 4` enforces the ≤5 steps product constraint at the data layer as a safety net, but the application layer must also enforce this.

Use Supabase's `supabase gen types dart` to generate Dart type-safe models from this schema after migration. Confirm with the team whether offline-first (Hive/SQLite local cache) is required — if so, a mirrored local schema may be needed, but this task covers only the Supabase layer.

Testing Requirements

Database-level tests: write Supabase SQL tests (using pgTAP or equivalent) that verify: (1) RLS blocks cross-user reads, (2) the step_index CHECK constraint rejects values outside 0–4, (3) the unique partial index prevents duplicate active drafts per user+wizard_type, (4) the TTL trigger correctly sets expires_at on insert and update. Migration tests: run the migration script on a clean Supabase test project and verify all tables, indexes, triggers, and policies exist as expected. Integration tests in Flutter: write repository integration tests (against a local Supabase Docker instance or a dedicated test project) that insert, fetch, update, and delete draft rows via the Dart client and assert correct behavior including RLS enforcement when using different user JWTs.

Component
Wizard Draft Repository
data low
Epic Risks (4)
high impact medium prob technical

The error message registry and help content registry both depend on bundled JSON assets loaded at startup. If asset loading fails silently (e.g. malformed JSON, missing pubspec asset declaration), the entire plain-language layer falls back to empty strings or raw error codes, breaking the accessibility guarantee app-wide.

Mitigation & Contingency

Mitigation: Implement eager validation of both assets during app initialisation with an assertion failure in debug mode and a structured error log in release mode. Add integration tests that verify asset loading in the Flutter test harness on every CI run.

Contingency: Ship a hardcoded minimum-viable fallback message set directly in Dart code so the app always has at least a safe generic message, preventing a blank or code-only error surface.

medium impact medium prob dependency

The AccessibilityDesignTokenEnforcer relies on dart_code_metrics custom lint rules. If the lint toolchain is not already configured in the project's CI pipeline, integrating a new linting plugin may cause unexpected build failures or require significant CI configuration work beyond the estimated scope.

Mitigation & Contingency

Mitigation: Audit the existing dart_code_metrics configuration in the project before starting implementation. Scope the lint rules to a separate Dart package that can be integrated incrementally, starting with the most critical rule (hard-coded colors) and adding others in subsequent iterations.

Contingency: Fall back to Flutter test-level assertions (using the cognitive-accessibility-audit utility) to catch violations in CI if the lint plugin integration is delayed, preserving enforcement coverage without blocking the epic.

medium impact low prob technical

WizardDraftRepository must choose between shared_preferences and Hive for local persistence. Choosing the wrong store for the data volume (e.g. shared_preferences for complex nested wizard state) can lead to serialisation bugs or performance degradation, particularly on lower-end Android devices used by some NHF members.

Mitigation & Contingency

Mitigation: Define a clean repository interface first and implement shared_preferences as the initial backend. Profile serialisation round-trip time with a realistic wizard state payload (≈10 fields) before committing to either store.

Contingency: Swap the persistence backend behind the repository interface without touching wizard UI code, which is possible precisely because the repository abstraction isolates the storage detail.

medium impact high prob scope

The AccessibilityDesignTokenEnforcer scope could expand significantly if a large portion of existing widgets use hard-coded values. Discovering widespread violations during this epic would force either a major refactor or a decision to exclude legacy components, potentially reducing the enforcer's coverage and value.

Mitigation & Contingency

Mitigation: Run a preliminary audit of existing widgets using a simple grep for hard-coded hex colors and raw pixel values before implementation begins. Use the results to set a realistic remediation boundary for this epic and log all out-of-scope violations as tracked tech-debt items.

Contingency: Scope the enforcer to new and modified components only (via file-path filters in dart_code_metrics config), shipping a partial but immediately valuable coverage rather than blocking the epic on full-codebase remediation.

Quick Links
All Tasks Execution Plan