init.sql

-- ================================================================
-- init.sql — Auto-executed by MySQL on first container startup
-- Location: mounted to /docker-entrypoint-initdb.d/
-- ================================================================

USE taskqueue_db;

-- ----------------------------------------------------------------
-- TABLE: tasks
-- Single source of truth for every task in the system.
-- Written by producer (INSERT), updated by worker (UPDATE status).
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tasks (
    -- UUID as primary key: globally unique, safe for distributed inserts,
    -- and clients can know their task ID before the DB row is created.
    id              VARCHAR(36)     NOT NULL PRIMARY KEY,

    -- Task classification
    type            VARCHAR(50)     NOT NULL COMMENT 'EMAIL | REPORT | NOTIFICATION',
    status          VARCHAR(30)     NOT NULL COMMENT 'QUEUED | PROCESSING | COMPLETED | FAILED | DEAD_LETTER',
    priority        INT             NOT NULL DEFAULT 0 COMMENT 'Higher value = higher priority (Phase 6)',

    -- The actual work data stored as a JSON string.
    -- Flexible: email tasks store recipient/subject, report tasks store date ranges.
    payload         TEXT            NOT NULL,

    -- Client-supplied idempotency key used to deduplicate repeated submissions.
    idempotency_key VARCHAR(100)    NOT NULL UNIQUE,

    -- Retry tracking
    retry_count     INT             NOT NULL DEFAULT 0,
    max_retries     INT             NOT NULL DEFAULT 3,

    -- Error tracking for failed tasks
    error_message   TEXT            NULL,

    -- Worker tracking and lifecycle timing
    worker_instance_id VARCHAR(100) NULL,
    started_at      TIMESTAMP       NULL,

    -- Audit timestamps
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at    TIMESTAMP       NULL     COMMENT 'Set when status becomes COMPLETED or DEAD_LETTER',

    -- Indexes for common query patterns
    -- "Get all tasks with status QUEUED" — dashboard queries
    INDEX idx_status    (status),
    -- "Get all tasks of type EMAIL" — monitoring queries
    INDEX idx_type      (type),
    -- "Get tasks created in the last hour" — time-based queries
    INDEX idx_created   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- TABLE: task_logs
-- Immutable audit trail — one row per processing attempt.
-- Never update this table. Only INSERT.
-- Lets you answer: "Which worker processed task X? How long did it take?"
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS task_logs (
    id              BIGINT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    task_id         VARCHAR(36)     NOT NULL,

    -- Which worker instance processed this (Docker hostname or env variable)
    worker_id       VARCHAR(100)    NULL,

    -- Attempt number — starts at 1, increments on each retry
    attempt         INT             NOT NULL,

    -- Outcome of this specific attempt
    status          VARCHAR(30)     NOT NULL COMMENT 'SUCCESS | FAILURE',
    message         TEXT            NULL     COMMENT 'Human-readable result or error',

    -- Optional detailed error payload
    error_details   TEXT            NULL,

    -- Processing duration in milliseconds — useful for performance monitoring
    duration_ms     BIGINT          NULL,

    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_task_log_task
        FOREIGN KEY (task_id) REFERENCES tasks(id)
        ON DELETE CASCADE,           -- If task is deleted, delete its logs too

    INDEX idx_task_id (task_id),
    INDEX idx_worker  (worker_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;