pinakes/migrations/postgres/V11__session_persistence.sql

-- Session persistence for database-backed sessions
-- Replaces in-memory session storage

CREATE TABLE IF NOT EXISTS sessions (
    session_token TEXT PRIMARY KEY NOT NULL,
    user_id TEXT,
    username TEXT NOT NULL,
    role TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    last_accessed TIMESTAMPTZ NOT NULL
);

-- Index for efficient cleanup of expired sessions
CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);

-- Index for listing sessions by username
CREATE INDEX IF NOT EXISTS idx_sessions_username ON sessions(username);