-- ============================================================================
-- Krow — OAuth 2.1 access and refresh tokens
--
-- Phase 3, migration 3 of 3. One row per issued token, access and refresh
-- alike, because they share every lifecycle question worth asking: is it known,
-- has it expired, has it been revoked, whose is it, and what may it reach.
--
-- THE RAW TOKEN IS NEVER STORED. token_hash holds SHA-256, and the CHECK below
-- refuses anything that is not 64 hex characters — so a raw token, which is
-- base64url of random bytes, cannot physically be written to this column. This
-- mirrors sessions.token_hash from 000004 exactly, and for the same reason: a
-- dump of this table must not be replayable as a login.
--
-- TOKEN FAMILIES AND REUSE DETECTION
--
-- Refresh tokens rotate: spending one issues its replacement and consumes the
-- old one. family_id ties a lineage together, which is what makes theft
-- detectable. If a consumed refresh token is presented again, either the
-- legitimate client is retrying or an attacker is replaying a stolen token, and
-- there is no way to tell which. OAuth 2.1's answer is to assume the worse case
-- and revoke the entire family — the attacker loses access, and the legitimate
-- client is forced through a fresh authorization it can complete. Without
-- family_id the best available response is to revoke nothing.
--
-- Target schema: public. No system schema is read or written.
-- ============================================================================

SET search_path = public;

CREATE TABLE oauth_tokens (
  id            uuid        PRIMARY KEY DEFAULT gen_random_uuid(),

  -- SHA-256 of the token, lowercase hex. Never the token.
  token_hash    text        NOT NULL,

  -- 'access' or 'refresh'. One table, because the questions asked of both are
  -- the same; a type column rather than two tables, because a lookup that had
  -- to try two tables would eventually try only one.
  token_type    text        NOT NULL,

  -- Rotation lineage. Every token minted from the same authorization shares a
  -- family, so reuse detection can revoke all of them at once.
  family_id     uuid        NOT NULL,

  client_id     text        NOT NULL REFERENCES oauth_clients (client_id) ON DELETE CASCADE,

  -- ON DELETE CASCADE: a deleted user must not leave a live token behind.
  user_id       uuid        NOT NULL REFERENCES users (id) ON DELETE CASCADE,

  -- Denormalised at issue time for auditing and for cheap per-tenant queries
  -- ("what is this organisation's Claude usage"). NEVER the authority on
  -- tenancy: identity is rebuilt from the live user row at validation, so a
  -- suspended or moved user is caught on their next call rather than at expiry.
  org_id        uuid        NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,

  scopes        text[]      NOT NULL,

  -- RFC 8707. The MCP server this token is for. Validation requires it to
  -- match this deployment's canonical resource URI, which is what stops a
  -- token minted for another service being spent here.
  audience      text        NOT NULL,

  created_date  timestamptz NOT NULL DEFAULT now(),

  -- Sliding is not a concept here: an access token expires and the client
  -- refreshes. expires_at is the only deadline an access token has.
  expires_at    timestamptz NOT NULL,

  -- Set when a refresh token is spent. A consumed refresh token presented
  -- again is the reuse signal that revokes the family.
  consumed_at   timestamptz,

  -- Set by revocation: an explicit disconnect, a family revocation, or a
  -- suspended account being cleaned up.
  revoked_at    timestamptz,
  revoked_reason text,

  last_used_at  timestamptz,

  CONSTRAINT oauth_tokens_token_hash_key UNIQUE (token_hash),
  CONSTRAINT oauth_tokens_token_hash_sha256 CHECK (token_hash ~ '^[0-9a-f]{64}$'),
  CONSTRAINT oauth_tokens_type_check CHECK (token_type IN ('access', 'refresh')),
  CONSTRAINT oauth_tokens_expires_after_created CHECK (expires_at > created_date),
  CONSTRAINT oauth_tokens_scopes_present CHECK (array_length(scopes, 1) >= 1),
  CONSTRAINT oauth_tokens_audience_present CHECK (length(btrim(audience)) > 0)
);

-- Validation reads by hash on every single MCP request; the UNIQUE constraint
-- above is that index.

-- Reuse detection and family revocation: given one token, revoke its lineage.
CREATE INDEX oauth_tokens_family_idx ON oauth_tokens (family_id);

-- "Which apps has this user connected", and revoking everything for a user
-- whose account was suspended. Partial, because revoked rows are never the
-- answer to either question.
CREATE INDEX oauth_tokens_user_active_idx ON oauth_tokens (user_id)
  WHERE revoked_at IS NULL;

-- The sweep of expired rows.
CREATE INDEX oauth_tokens_expires_idx ON oauth_tokens (expires_at);

COMMENT ON TABLE oauth_tokens IS
  'OAuth access and refresh tokens. Stores SHA-256 of each token and never the '
  'token itself. family_id ties a rotation lineage together so that replay of a '
  'consumed refresh token can revoke the whole family.';

COMMENT ON COLUMN oauth_tokens.token_hash IS
  'Lowercase hex SHA-256 of the token. Never the token.';

COMMENT ON COLUMN oauth_tokens.family_id IS
  'Rotation lineage. Presenting a consumed refresh token revokes every row '
  'sharing this id — the OAuth 2.1 response to a possible stolen token.';

COMMENT ON COLUMN oauth_tokens.audience IS
  'RFC 8707 resource indicator. Must match this deployment''s canonical MCP '
  'resource URI at validation, or the token is refused.';

COMMENT ON COLUMN oauth_tokens.org_id IS
  'The tenant at issue time, for audit and reporting only. Tenancy is re-read '
  'from the live user row on every validation and is never taken from here.';