db/schema/009_user_completions.sql

-- 009_user_completions.sql
-- Per-category completion counter used by the certificate eligibility check
-- (CERT_REQUIRED_COMPLETIONS = 50 in app.core.constants).
--
-- A row in this table means the user has *successfully solved* a challenge
-- in `category`. The (user_id, category, challenge_id) UNIQUE makes
-- /api/training/solved idempotent: duplicate inserts are no-ops.
--
-- This table used to live in 010_vulnerability_hunter_challenges.sql
-- (which is where it was first introduced). It is split out into its
-- own migration here so that re-running the vulnerability-hunter
-- migration after the table is created elsewhere doesn't accidentally
-- drop the unique index.

BEGIN;

CREATE TABLE IF NOT EXISTS public.user_completions (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      uuid NOT NULL,
  category     text NOT NULL,            -- e.g. 'vulnerability-hunter', 'code-fixing', 'log-analysis'
  module       text NOT NULL DEFAULT '',
  challenge_id text NOT NULL,
  xp_awarded   integer DEFAULT 0,
  created_at   timestamptz DEFAULT now(),
  UNIQUE (user_id, category, challenge_id)
);

CREATE INDEX IF NOT EXISTS idx_user_completions_user_cat
  ON public.user_completions (user_id, category);

ALTER TABLE public.user_completions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Open read user_completions" ON public.user_completions;
CREATE POLICY "Open read user_completions"
  ON public.user_completions FOR SELECT USING (true);

DROP POLICY IF EXISTS "Service role insert user_completions" ON public.user_completions;
CREATE POLICY "Service role insert user_completions"
  ON public.user_completions FOR INSERT WITH CHECK (true);

COMMIT;