0003_sessions.sql

1234567891011121314151617181920212223242526272829303132333435363738394041-- 0003_sessions.sql
--
-- B-priv-1: per-session revocation, JTI tracking, refresh-token model.
--
-- Pre-B-priv-1 the muntin_session cookie was a 14-day bearer JWT
-- with no jti and no server-side store, so:
--   - A rotated JWT_SECRET invalidated every active session.
--   - A leaked token could not be revoked short of secret rotation.
--   - No multi-device hygiene (sign-out elsewhere).
--
-- The new model issues two JWTs at /verify: a 15-minute access JWT
-- and a 30-day refresh JWT. Both carry the same jti, which is the
-- primary key of this table. revoked_at flips to non-NULL on
-- /sign-out or admin revoke; requireAuth + /refresh both check
-- revoked_at IS NULL before honouring a token.
--
-- device_fingerprint is SHA-256(user_agent) -- not IP-tracking,
-- just enough to label "this device's session" in the future
-- /settings/security surface. device_label is human-set when
-- B-gtm-2 (the passcode + "Stay signed in on this phone" toggle)
-- lands.

CREATE TABLE IF NOT EXISTS sessions (
  id                  TEXT PRIMARY KEY,            -- jti
  user_id             TEXT NOT NULL REFERENCES users(id),
  org_id              TEXT NOT NULL REFERENCES orgs(id),
  kid                 TEXT NOT NULL,               -- JWT key version
  device_fingerprint  TEXT NOT NULL,
  device_label        TEXT,                        -- nullable; set by B-gtm-2
  access_expires_at   TEXT NOT NULL,               -- ISO 8601
  refresh_expires_at  TEXT NOT NULL,               -- ISO 8601
  created_at          TEXT NOT NULL,               -- ISO 8601
  revoked_at          TEXT,                        -- ISO 8601 or NULL
  revoked_reason      TEXT                          -- 'sign_out' | 'rotation' | 'admin'
);

CREATE INDEX IF NOT EXISTS sessions_user_idx ON sessions(user_id);
CREATE INDEX IF NOT EXISTS sessions_org_idx ON sessions(org_id);
CREATE INDEX IF NOT EXISTS sessions_active_idx
  ON sessions(refresh_expires_at)
  WHERE revoked_at IS NULL;