init.sql

-- init.sql — Full schema for fresh Docker spin-up
-- Keep in sync with all migration files.
 
CREATE TABLE IF NOT EXISTS users (
    id                      INT           AUTO_INCREMENT PRIMARY KEY,
    email                   VARCHAR(255)  NOT NULL UNIQUE,
    password_hash           VARCHAR(255)  NULL,
    auth_provider           VARCHAR(50)   NOT NULL DEFAULT 'local',
    display_name            VARCHAR(255)  NULL,
    is_email_verified       TINYINT(1)    NOT NULL DEFAULT 0,
    profile_picture_url     VARCHAR(500)  NULL,
    failed_login_attempts   INT           NOT NULL DEFAULT 0,
    locked_until            TIMESTAMP     NULL,
    created_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id         INT           AUTO_INCREMENT PRIMARY KEY,
    user_id    INT           NOT NULL,
    token      VARCHAR(500)  NOT NULL UNIQUE,
    expires_at TIMESTAMP     NOT NULL,
    created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
 
CREATE TABLE IF NOT EXISTS otps (
    id         INT           AUTO_INCREMENT PRIMARY KEY,
    user_id    INT           NOT NULL,
    otp_code   VARCHAR(10)   NOT NULL,
    purpose    VARCHAR(50)   NOT NULL,
    expires_at TIMESTAMP     NOT NULL,
    used_at    TIMESTAMP     NULL,
    invalidated_at TIMESTAMP NULL,
    created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_otps_user_purpose_created (user_id, purpose, created_at),
    INDEX idx_otps_user_purpose_active (user_id, purpose, otp_code, used_at, invalidated_at, expires_at)
);
 
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id         INT           AUTO_INCREMENT PRIMARY KEY,
    user_id    INT           NOT NULL,
    token      VARCHAR(500)  NOT NULL UNIQUE,
    expires_at TIMESTAMP     NOT NULL,
    used_at    TIMESTAMP     NULL,
    created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS llm_provider_keys (
    user_id            INT           NOT NULL,
    provider           VARCHAR(32)   NOT NULL,
    encrypted_api_key  TEXT          NOT NULL,
    masked_last4       VARCHAR(16)   NOT NULL,
    is_default         TINYINT(1)    NOT NULL DEFAULT 0,
    last_tested_at     TIMESTAMP     NULL,
    created_at         TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, provider),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS canvases (
    id          INT           AUTO_INCREMENT PRIMARY KEY,
    user_id     INT           NOT NULL,
    title       VARCHAR(120)  NOT NULL,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_canvases_user_updated (user_id, updated_at, id)
);

CREATE TABLE IF NOT EXISTS main_node_conversations (
    id          INT           AUTO_INCREMENT PRIMARY KEY,
    canvas_id   INT           NOT NULL UNIQUE,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (canvas_id) REFERENCES canvases(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS main_node_messages (
    id               INT           AUTO_INCREMENT PRIMARY KEY,
    conversation_id  INT           NOT NULL,
    role             VARCHAR(16)   NOT NULL,
    content          TEXT          NOT NULL,
    provider         VARCHAR(32)   NULL,
    model            VARCHAR(128)  NULL,
    created_at       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES main_node_conversations(id) ON DELETE CASCADE,
    INDEX idx_main_node_messages_conversation_id (conversation_id, id)
);

CREATE TABLE IF NOT EXISTS canvas_nodes (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    canvas_id           INT           NOT NULL,
    parent_node_id      INT           NULL,
    kind                VARCHAR(32)   NOT NULL,
    title               VARCHAR(160)  NOT NULL,
    generation_mode     VARCHAR(16)   NOT NULL DEFAULT 'compact',
    summary             MEDIUMTEXT    NULL,
    category            VARCHAR(80)   NULL,
    content             MEDIUMTEXT    NOT NULL,
    source_prompt       TEXT          NULL,
    continuation_prompt TEXT          NULL,
    details_json        JSON          NULL,
    depth               INT           NOT NULL,
    sibling_order       INT           NOT NULL,
    created_at          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (canvas_id) REFERENCES canvases(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_node_id) REFERENCES canvas_nodes(id) ON DELETE CASCADE,
    INDEX idx_canvas_nodes_canvas_parent (canvas_id, parent_node_id, sibling_order, id)
);