schema.sql

-- Gitnet database schema.
--
-- This mirrors app/models/*.py exactly. It exists so the schema can be reviewed
-- or applied directly (e.g. to a fresh Neon database) without running Alembic,
-- and to make the data model easy to read in one place. If you change a model,
-- update this file and generate a matching Alembic revision (see alembic/).
--
-- Safe to run against an empty database. Run with:
--   psql "$DATABASE_URL" -f schema.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- for gen_random_uuid()

-- ---------------------------------------------------------------------------
-- Enums
-- ---------------------------------------------------------------------------

CREATE TYPE project_source_type AS ENUM ('github_import', 'zip_upload');
CREATE TYPE project_status AS ENUM ('initializing', 'active', 'archived', 'error');
CREATE TYPE workflow_run_status AS ENUM ('queued', 'running', 'succeeded', 'failed', 'cancelled');
CREATE TYPE workflow_run_trigger AS ENUM ('user', 'ai');
CREATE TYPE ai_task_status AS ENUM ('queued', 'running', 'succeeded', 'failed', 'cancelled');
CREATE TYPE activity_actor AS ENUM ('user', 'ai', 'system');

-- ---------------------------------------------------------------------------
-- Users & auth
-- ---------------------------------------------------------------------------

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           VARCHAR(255) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    display_name    VARCHAR(120),
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    is_verified     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_users_email ON users (email);

-- Refresh sessions. Access tokens are short-lived signed JWTs and are never
-- stored; only the refresh side (hashed, single-use, rotated on refresh) lives here.
CREATE TABLE sessions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    refresh_token_hash  VARCHAR(128) NOT NULL UNIQUE,
    device_label        VARCHAR(255),
    ip_address          VARCHAR(64),
    user_agent          VARCHAR(255),
    revoked             BOOLEAN NOT NULL DEFAULT FALSE,
    expires_at          TIMESTAMPTZ NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_used_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_sessions_user_id ON sessions (user_id);
CREATE INDEX ix_sessions_refresh_token_hash ON sessions (refresh_token_hash);

CREATE TABLE password_reset_tokens (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  VARCHAR(128) NOT NULL UNIQUE,
    used        BOOLEAN NOT NULL DEFAULT FALSE,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_password_reset_tokens_user_id ON password_reset_tokens (user_id);
CREATE INDEX ix_password_reset_tokens_token_hash ON password_reset_tokens (token_hash);

-- ---------------------------------------------------------------------------
-- GitHub App integration
-- ---------------------------------------------------------------------------

-- One row per GitHub App installation linked to a Gitnet account. Installation
-- access tokens themselves are never persisted (see app/services/github_app_auth.py) —
-- only minted on demand from the App private key and cached in-process.
CREATE TABLE github_installations (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    installation_id     BIGINT NOT NULL,
    account_login       VARCHAR(255) NOT NULL,
    account_type        VARCHAR(32) NOT NULL DEFAULT 'User',
    target_type         VARCHAR(32) NOT NULL DEFAULT 'User',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_installation_id UNIQUE (installation_id)
);
CREATE INDEX ix_github_installations_user_id ON github_installations (user_id);
CREATE INDEX ix_github_installations_installation_id ON github_installations (installation_id);

CREATE TABLE repositories (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    installation_id     UUID NOT NULL REFERENCES github_installations(id) ON DELETE CASCADE,
    github_repo_id      BIGINT NOT NULL,
    full_name           VARCHAR(255) NOT NULL,
    default_branch      VARCHAR(120) NOT NULL DEFAULT 'main',
    private             BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_repo_per_installation UNIQUE (installation_id, github_repo_id)
);
CREATE INDEX ix_repositories_installation_id ON repositories (installation_id);
CREATE INDEX ix_repositories_full_name ON repositories (full_name);

-- ---------------------------------------------------------------------------
-- Projects
-- ---------------------------------------------------------------------------

CREATE TABLE projects (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    repository_id       UUID REFERENCES repositories(id) ON DELETE SET NULL,
    name                VARCHAR(255) NOT NULL,
    source_type         project_source_type NOT NULL,
    status              project_status NOT NULL DEFAULT 'initializing',
    default_branch      VARCHAR(120) NOT NULL DEFAULT 'main',
    workspace_slug      VARCHAR(64) NOT NULL UNIQUE,
    last_activity_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_projects_owner_id ON projects (owner_id);
CREATE INDEX ix_projects_repository_id ON projects (repository_id);
CREATE INDEX ix_projects_workspace_slug ON projects (workspace_slug);

-- ---------------------------------------------------------------------------
-- Workflows
-- ---------------------------------------------------------------------------

-- `definition` is structured JSON (see direct.md), never a raw shell script —
-- every step still routes through the same controlled Gitnet command layer as
-- manual commands.
CREATE TABLE workflows (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name            VARCHAR(120) NOT NULL,
    description     TEXT,
    definition      JSONB NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_workflows_project_id ON workflows (project_id);

CREATE TABLE workflow_runs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id     UUID REFERENCES workflows(id) ON DELETE SET NULL,
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    status          workflow_run_status NOT NULL DEFAULT 'queued',
    triggered_by    workflow_run_trigger NOT NULL,
    exit_summary    JSONB,
    started_at      TIMESTAMPTZ,
    finished_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_workflow_runs_workflow_id ON workflow_runs (workflow_id);
CREATE INDEX ix_workflow_runs_project_id ON workflow_runs (project_id);

-- ---------------------------------------------------------------------------
-- Gitnet AI
-- ---------------------------------------------------------------------------

-- `tool_calls` stores a compact summary (tool name + args) rather than full
-- transcripts, which stay ephemeral (see the in-memory log broker). Never
-- stores raw Gemini API keys -- `gemini_key_used` is a slot label like "gemini_1".
CREATE TABLE ai_tasks (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    requested_by_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    instruction         TEXT NOT NULL,
    status              ai_task_status NOT NULL DEFAULT 'queued',
    tool_calls          JSONB,
    result_summary      TEXT,
    pr_url              TEXT,
    gemini_key_used     TEXT,
    started_at          TIMESTAMPTZ,
    finished_at         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_ai_tasks_project_id ON ai_tasks (project_id);

-- Persistent rotation state for each configured Gemini key, keyed by a stable
-- slot label rather than the key value itself, so state survives key rotation
-- in the environment without ever putting key material in the database.
CREATE TABLE gemini_key_states (
    slot                    VARCHAR(32) PRIMARY KEY,
    is_exhausted            BOOLEAN NOT NULL DEFAULT FALSE,
    exhausted_at            TIMESTAMPTZ,
    cooldown_until          TIMESTAMPTZ,
    consecutive_failures    INTEGER NOT NULL DEFAULT 0,
    last_used_at            TIMESTAMPTZ,
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Activity log
-- ---------------------------------------------------------------------------

-- Durable record of meaningful project events (commits, pushes, PRs, imports).
-- This is intentionally separate from ephemeral live command/log output.
CREATE TABLE activity_logs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    actor           activity_actor NOT NULL,
    action          VARCHAR(64) NOT NULL,
    detail          JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_activity_logs_project_id ON activity_logs (project_id);
CREATE INDEX ix_activity_logs_created_at ON activity_logs (created_at);