Design Supabase schema for user identity and BankID verification status

epic-bankid-vipps-login-foundation-task-004 — Create the user_identities table in Supabase with columns: id (uuid PK), user_id (FK to auth.users), personnummer (text, encrypted at rest), bankid_verified (boolean), bankid_verified_at (timestamptz), vipps_sub (text), created_at, updated_at. Apply Row Level Security policies so users can only read and write their own identity record. Write and test the migration script.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file supabase/migrations/<timestamp>_create_user_identities.sql is committed and applies cleanly against a fresh Supabase project

user_identities table has columns: id uuid DEFAULT gen_random_uuid() PRIMARY KEY, user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, personnummer text, bankid_verified boolean NOT NULL DEFAULT false, bankid_verified_at timestamptz, vipps_sub text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()

UNIQUE constraint on user_id (one identity record per user)

Row Level Security is enabled on user_identities

RLS SELECT policy: auth.uid() = user_id

RLS INSERT policy: auth.uid() = user_id

RLS UPDATE policy: auth.uid() = user_id (users can update only their own record)

RLS DELETE policy: no delete allowed for regular users — DELETE is only allowed via service role (for GDPR purge via backend function)

personnummer column is excluded from default SELECT in application queries and must be explicitly selected — documented in comments in the migration file

A trigger function updates updated_at automatically on every UPDATE

Migration has been tested: apply → verify table exists with correct schema → apply again is idempotent (CREATE TABLE IF NOT EXISTS or supabase migration tooling handles this)

A rollback migration file is provided that drops the table and all associated policies

Technical Requirements

frameworks

Supabase

apis

Supabase Auth (auth.users reference)

PostgreSQL RLS

PostgreSQL triggers

data models

user_identities table

auth.users (Supabase built-in)

performance requirements

Index on user_id column for fast single-user lookups: CREATE INDEX idx_user_identities_user_id ON user_identities(user_id)

Index on vipps_sub for lookups during Vipps login callback: CREATE INDEX idx_user_identities_vipps_sub ON user_identities(vipps_sub) WHERE vipps_sub IS NOT NULL

security requirements

personnummer (Norwegian national identity number) is personally identifiable information under GDPR — the column must be encrypted at rest using Supabase Vault or pgcrypto, not stored as plaintext

RLS must be enabled and verified before any data is written — run SELECT relrowsecurity FROM pg_class WHERE relname = 'user_identities' in the migration verification step

Service role key (not anon key) must be used for the GDPR purge function — document this requirement in migration comments

vipps_sub is a Vipps-issued opaque identifier and must not be exposed in client-facing API responses without authorization review

ON DELETE CASCADE on user_id FK ensures identity data is purged when the auth.users record is deleted

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Use Supabase CLI for migration management: supabase migration new create_user_identities generates a timestamped file. For personnummer encryption: use pgcrypto's pgp_sym_encrypt / pgp_sym_decrypt with a key stored in Supabase Vault (not hardcoded in SQL). If pgcrypto is not available in the project tier, document the column as requiring application-level encryption before insert — the Flutter app must encrypt personnummer with the user's public key before writing it to this column, and the field type should remain text. For the updated_at trigger: CREATE OR REPLACE FUNCTION trigger_set_updated_at() RETURNS trigger AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql; — reuse this function across all tables that need it.

The bankid_verified_at column must only be set server-side via a Supabase Edge Function or backend service after BankID verification succeeds — do not allow client-side writes to this column. Consider a separate RLS UPDATE policy that restricts which columns the client can write (use column-level security or a separate Edge Function for markBankIdVerified).

Testing Requirements

Manual SQL verification test after applying the migration: (1) Insert a test user into auth.users, then insert a user_identities row — confirm it succeeds; (2) Attempt to insert a second user_identities row for the same user_id — confirm unique constraint violation; (3) As a different user JWT, attempt to SELECT the first user's identity row — confirm zero rows returned (RLS blocks it); (4) UPDATE the record and confirm updated_at changes automatically; (5) DELETE the auth.users row and confirm user_identities row is cascade-deleted. Document these steps in a test checklist file at supabase/tests/user_identities_rls_test.md.

Component

User Identity Repository

data medium

Epic Risks (3)

high impact medium prob technical

Flutter Secure Storage behavior differs between iOS Keychain and Android Keystore — key accessibility attributes (kSecAttrAccessibleWhenUnlocked vs. WhenUnlockedThisDeviceOnly) may cause tokens to become inaccessible after device restart or OS upgrade, breaking session restoration for returning users.

Mitigation & Contingency

Mitigation: Define explicit Keychain accessibility attributes during implementation and write integration tests on both platforms. Follow flutter_secure_storage documentation for cross-platform accessibility configuration.

Contingency: Implement a recovery flow that detects secure storage read failures and falls back to full re-authentication rather than crashing. Add a migration utility to re-write tokens with corrected attributes if a misconfiguration is discovered post-release.

high impact medium prob security

Personnummer is a legally sensitive national identifier under Norwegian GDPR implementation. If encryption-at-rest or data minimization requirements are not met before launch, the feature could be blocked by legal/compliance review from any of the four partner organizations.

Mitigation & Contingency

Mitigation: Ensure personnummer is only persisted after explicit user consent via the personnummer confirmation widget. Use Supabase column-level encryption for the personnummer field. Document the data processing basis and retention policy before the first TestFlight release.

Contingency: If legal review blocks the personnummer write-back, implement the feature as opt-in only with a deferred sync model, allowing BankID/Vipps login to proceed without storing the personnummer until compliance is confirmed.

medium impact high prob dependency

If the VippsOrgCostConfig data is not populated in Supabase for all four partner organizations before the feature ships, users from unconfigured organizations will see no Vipps login option and may report it as broken, creating confusion and support load.

Mitigation & Contingency

Mitigation: Create a seed migration script for Vipps org configuration and include it in the deployment checklist. Implement a clear admin UI warning when an organization is missing Vipps configuration.

Contingency: Add a feature flag in VippsOrgCostConfig so individual organizations can be enabled/disabled without a code deploy, allowing rapid remediation.

Quick Links

All Tasks Execution Plan