`db/migrations/0001_auth.sql`

52 lines
-- 0001_auth.sql — auth + 2FA tables
-- Apply: psql -d abrams_os -f db/migrations/0001_auth.sql

BEGIN;

CREATE TABLE IF NOT EXISTS auth_credential (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  password_hash   text NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  rotated_at      timestamptz,
  UNIQUE (user_id)
);

CREATE TABLE IF NOT EXISTS auth_totp (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  secret_encrypted bytea NOT NULL,
  secret_iv       bytea NOT NULL,
  secret_tag      bytea NOT NULL,
  enrolled_at     timestamptz,           -- NULL until first successful TOTP verify
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id)
);

CREATE TABLE IF NOT EXISTS auth_session (
  id                  text PRIMARY KEY,        -- random, sent in cookie
  user_id             text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  step_up_at          timestamptz,             -- last successful TOTP step-up
  created_at          timestamptz NOT NULL DEFAULT now(),
  expires_at          timestamptz NOT NULL,
  ip                  inet,
  user_agent          text,
  revoked_at          timestamptz
);
CREATE INDEX IF NOT EXISTS auth_session_user_idx ON auth_session (user_id, expires_at DESC);

CREATE TABLE IF NOT EXISTS auth_event (
  id              bigserial PRIMARY KEY,
  occurred_at     timestamptz NOT NULL DEFAULT now(),
  user_id         text REFERENCES user_account(id) ON DELETE SET NULL,
  session_id      text REFERENCES auth_session(id) ON DELETE SET NULL,
  event_type      text NOT NULL,             -- signup, signin_pwd_ok, signin_pwd_fail, totp_enroll, totp_ok, totp_fail, step_up_ok, step_up_fail, signout, session_revoked, import_initiated
  ip              inet,
  user_agent      text,
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS auth_event_user_idx ON auth_event (user_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS auth_event_type_idx ON auth_event (event_type, occurred_at DESC);

COMMIT;