auth.sql

-- ============================================================
-- 1. Core Entities
-- ============================================================

-- A user account is created automatically on first SSO login.
-- Profile fields (display_name, avatar_url) are sourced from the identity provider.
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           VARCHAR(255) NOT NULL UNIQUE,
    display_name    VARCHAR(100) NOT NULL,
    avatar_url      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- A tenant/workspace that groups users together.
CREATE TABLE organizations (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) NOT NULL UNIQUE, -- used in URLs
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ============================================================
-- 2. RBAC
-- ============================================================

-- A single capability declared by a service, e.g. (service='auth', resource='member', action='invite').
-- Services register their own permissions on startup via the sync API.
CREATE TABLE permissions (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    service     VARCHAR(50) NOT NULL,
    resource    VARCHAR(50) NOT NULL,
    action      VARCHAR(50) NOT NULL,
    description TEXT,

    UNIQUE (service, resource, action)
);

-- Roles are declared by services in their manifests and synced on startup.
-- Users cannot create or modify roles; all roles are service-defined.
CREATE TABLE roles (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    service     VARCHAR(50) NOT NULL,
    name        VARCHAR(50) NOT NULL,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (service, name)
);

-- Declares which permissions a role grants. Populated by the service sync API.
CREATE TABLE role_permissions (
    role_id       UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,

    PRIMARY KEY (role_id, permission_id)
);

-- ============================================================
-- 3. Organization Membership
-- ============================================================

-- Tracks which users belong to which organizations and under what role.
-- Roles are service-defined; org admins assign them but cannot create new ones.
-- A user can be a member of multiple organizations with different roles.
CREATE TABLE organization_members (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id         UUID NOT NULL REFERENCES roles(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (organization_id, user_id)
);

CREATE INDEX idx_org_members_user ON organization_members(user_id);

-- ============================================================
-- 4. Identity Provider
-- ============================================================

-- Binds a user to an external SSO provider account (e.g. Google).
-- The access/refresh tokens here belong to the provider, used to call provider APIs.
-- One user may have accounts on multiple providers in the future.
CREATE TABLE oauth_accounts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider            VARCHAR(30) NOT NULL,            -- e.g. 'google'
    provider_account_id VARCHAR(255) NOT NULL,
    access_token        TEXT,
    refresh_token       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (provider, provider_account_id)
);

CREATE INDEX idx_oauth_accounts_user ON oauth_accounts(user_id);

-- ============================================================
-- 5. Auth Server Sessions
-- ============================================================

-- Represents a user's login state on the auth server's own domain.
-- The session cookie holds the raw token; only its hash is stored here.
-- An active session allows the user to skip re-authentication (SSO effect).
CREATE TABLE sessions (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  VARCHAR(255) NOT NULL UNIQUE,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sessions_user ON sessions(user_id);

-- ============================================================
-- 6. OAuth 2.0 Provider (Authorization Code Flow + PKCE)
-- ============================================================

-- Registered client applications that are allowed to request authorization.
-- Confidential clients (server-side apps) have a client_secret.
-- Public clients (SPA/mobile) leave client_secret NULL and rely solely on PKCE.
CREATE TABLE oauth_clients (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id       VARCHAR(100) NOT NULL UNIQUE,
    client_secret   VARCHAR(255),                    -- NULL for public clients
    name            VARCHAR(100) NOT NULL,
    redirect_uris   TEXT[] NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Short-lived codes issued to the client after the user authenticates.
-- Stores the PKCE code_challenge so the server can verify code_verifier on exchange.
-- Each code is single-use and must be deleted immediately after exchange.
CREATE TABLE authorization_codes (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code_hash             VARCHAR(255) NOT NULL UNIQUE,
    client_id             UUID NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
    user_id               UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    redirect_uri          TEXT NOT NULL,
    code_challenge        VARCHAR(128) NOT NULL,
    code_challenge_method VARCHAR(10) NOT NULL DEFAULT 'S256',
    expires_at            TIMESTAMPTZ NOT NULL,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Long-lived tokens issued to clients to obtain new access tokens without re-login.
-- Only the hash is stored; the raw token is returned once and never persisted.
-- Deleting a row revokes the token.
CREATE TABLE refresh_tokens (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token_hash  VARCHAR(255) NOT NULL UNIQUE,
    client_id   UUID NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);