authkit/migrations/0001_init.sql

-- 0001_init.sql
-- Initial authkit schema for PostgreSQL 16+. All tables prefixed authkit_ so
-- the library can be embedded in an existing application database. Each
-- migration owns its transaction and inserts its version row at the bottom;
-- the runner only orchestrates file discovery and concurrency.

BEGIN;

CREATE TABLE IF NOT EXISTS authkit_schema_migrations (
    version    TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL
);

-- Users. Password is nullable so accounts can be created without a credential
-- and have one set later (invite flows, magic-link-only accounts, etc.).
CREATE TABLE IF NOT EXISTS authkit_users (
    id                UUID PRIMARY KEY,
    email             TEXT NOT NULL,
    email_normalized  TEXT NOT NULL,
    email_verified_at TIMESTAMPTZ,
    password_hash     TEXT,
    session_version   INTEGER NOT NULL DEFAULT 0,
    last_login_at     TIMESTAMPTZ,
    created_at        TIMESTAMPTZ NOT NULL,
    updated_at        TIMESTAMPTZ NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS authkit_users_email_normalized_uniq
    ON authkit_users (email_normalized);

-- Opaque server-side sessions.
CREATE TABLE IF NOT EXISTS authkit_sessions (
    id_hash      BYTEA PRIMARY KEY,
    user_id      UUID NOT NULL REFERENCES authkit_users(id) ON DELETE CASCADE,
    user_agent   TEXT NOT NULL DEFAULT '',
    ip           TEXT,
    created_at   TIMESTAMPTZ NOT NULL,
    last_seen_at TIMESTAMPTZ NOT NULL,
    expires_at   TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS authkit_sessions_user_id_idx    ON authkit_sessions(user_id);
CREATE INDEX IF NOT EXISTS authkit_sessions_expires_at_idx ON authkit_sessions(expires_at);

-- Single-use tokens (refresh, email-verify, password-reset, magic-link, email-otp).
-- attempts_remaining is non-null only for tokens that allow retries (email_otp);
-- ConsumeToken decrements and zeroes-out on exhaustion.
CREATE TABLE IF NOT EXISTS authkit_tokens (
    hash               BYTEA NOT NULL,
    kind               TEXT  NOT NULL,
    user_id            UUID  NOT NULL REFERENCES authkit_users(id) ON DELETE CASCADE,
    chain_id           TEXT,
    -- chain_started_at is the timestamp of the first refresh in a chain.
    -- Copied forward on every rotation so the absolute-cap check in
    -- RefreshJWT is O(1). Non-null only for refresh-token rows.
    chain_started_at   TIMESTAMPTZ,
    consumed_at        TIMESTAMPTZ,
    attempts_remaining INTEGER,
    created_at         TIMESTAMPTZ NOT NULL,
    expires_at         TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (kind, hash)
);
CREATE INDEX IF NOT EXISTS authkit_tokens_user_id_idx    ON authkit_tokens(user_id);
CREATE INDEX IF NOT EXISTS authkit_tokens_expires_at_idx ON authkit_tokens(expires_at);
CREATE INDEX IF NOT EXISTS authkit_tokens_chain_id_idx
    ON authkit_tokens(chain_id) WHERE chain_id IS NOT NULL;

-- Service tokens. No owner column: these are machine credentials, intended to
-- be created by applications for outbound API calls or inbound automation.
-- Consumers tag them with whatever metadata they need via Name.
CREATE TABLE IF NOT EXISTS authkit_service_keys (
    id_hash      BYTEA PRIMARY KEY,
    name         TEXT NOT NULL,
    last_used_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL,
    expires_at   TIMESTAMPTZ,
    revoked_at   TIMESTAMPTZ
);

-- Roles, permissions, and abilities are seeded by the consumer (typically via
-- the cmd/roles, cmd/perms, cmd/abilities CLIs). They share the same shape:
-- normalised slug as the unique business key, optional human label.
CREATE TABLE IF NOT EXISTS authkit_roles (
    id         UUID PRIMARY KEY,
    slug       TEXT NOT NULL UNIQUE,
    label      TEXT,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE IF NOT EXISTS authkit_permissions (
    id         UUID PRIMARY KEY,
    slug       TEXT NOT NULL UNIQUE,
    label      TEXT,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE IF NOT EXISTS authkit_abilities (
    id         UUID PRIMARY KEY,
    slug       TEXT NOT NULL UNIQUE,
    label      TEXT,
    created_at TIMESTAMPTZ NOT NULL
);

-- Role ↔ Permission (defines what permissions a role grants).
CREATE TABLE IF NOT EXISTS authkit_role_permissions (
    role_id       UUID NOT NULL REFERENCES authkit_roles(id)       ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES authkit_permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

-- User ↔ Role (which roles a user holds).
CREATE TABLE IF NOT EXISTS authkit_user_roles (
    user_id    UUID NOT NULL REFERENCES authkit_users(id) ON DELETE CASCADE,
    role_id    UUID NOT NULL REFERENCES authkit_roles(id) ON DELETE CASCADE,
    granted_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (user_id, role_id)
);
CREATE INDEX IF NOT EXISTS authkit_user_roles_role_id_idx ON authkit_user_roles(role_id);

-- User ↔ Permission (direct grants, in addition to permissions resolved
-- through roles). GetUserPermissions returns the UNION of both paths.
CREATE TABLE IF NOT EXISTS authkit_user_permissions (
    user_id       UUID NOT NULL REFERENCES authkit_users(id)       ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES authkit_permissions(id) ON DELETE CASCADE,
    granted_at    TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (user_id, permission_id)
);
CREATE INDEX IF NOT EXISTS authkit_user_permissions_perm_id_idx ON authkit_user_permissions(permission_id);

-- ServiceKey ↔ Ability (which abilities a service key carries).
CREATE TABLE IF NOT EXISTS authkit_service_key_abilities (
    service_key_id_hash BYTEA NOT NULL REFERENCES authkit_service_keys(id_hash) ON DELETE CASCADE,
    ability_id          UUID  NOT NULL REFERENCES authkit_abilities(id)          ON DELETE CASCADE,
    granted_at          TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (service_key_id_hash, ability_id)
);
CREATE INDEX IF NOT EXISTS authkit_service_key_abilities_ability_idx ON authkit_service_key_abilities(ability_id);

INSERT INTO authkit_schema_migrations (version, applied_at) VALUES ('0001_init', now())
ON CONFLICT (version) DO NOTHING;

COMMIT;