moviedice/supabase/migrations/00004_recovery_and_sessions.sql

-- 00004_recovery_and_sessions.sql
--
-- Auth-A v3 rewrite: replace synthetic-identity-based recovery with a custom
-- recovery_codes table (Argon2id hashed + HMAC-SHA256 prefix index for O(1)
-- lookup) and a user_sessions table for server-side session tracking
-- (revocation + 30d absolute cap).
--
-- See: research/PLAN-AUTH-A.md (v3) and research/COOKIE-SHAPE-DECISION.md.

ALTER TABLE public.users DROP COLUMN IF EXISTS recovery_code;

CREATE TABLE public.recovery_codes (
  user_id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  argon2_hash text NOT NULL,
  prefix_hmac bytea NOT NULL,  -- HMAC-SHA256(pepper, code) truncated to first 8 bytes
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_recovery_codes_prefix ON public.recovery_codes(prefix_hmac);

ALTER TABLE public.recovery_codes ENABLE ROW LEVEL SECURITY;
-- No policies; service role only.

COMMENT ON TABLE public.recovery_codes IS
  'Argon2id-hashed recovery codes with HMAC prefix index. Service role only. Single-use; deleted on claim atomically via DELETE ... RETURNING.';

CREATE TABLE public.user_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  iat_original timestamptz NOT NULL DEFAULT now(),
  last_seen_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz
);

CREATE INDEX idx_user_sessions_user_active ON public.user_sessions(user_id) WHERE revoked_at IS NULL;

ALTER TABLE public.user_sessions ENABLE ROW LEVEL SECURITY;
-- No policies; service role only.

COMMENT ON TABLE public.user_sessions IS
  'Server-side session records. JWT carries session_id; touch endpoint validates revoked_at IS NULL and now() - iat_original < 30d.';