schema.sql

-- Minimal SQLite schema for Wirehook

CREATE TABLE IF NOT EXISTS webhook_endpoints (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    method TEXT NOT NULL,
    auth_type TEXT NOT NULL,
    secret_id TEXT,
    enabled INTEGER NOT NULL DEFAULT 1,
    log_headers INTEGER NOT NULL DEFAULT 1,
    passthrough_response INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (secret_id) REFERENCES secrets(id)
);

CREATE TABLE IF NOT EXISTS routes (
    id TEXT PRIMARY KEY,
    endpoint_id TEXT NOT NULL,
    filter_expr TEXT,
    transform_id TEXT,
    destination_id TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    priority INTEGER NOT NULL DEFAULT 100,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (endpoint_id) REFERENCES webhook_endpoints(id),
    FOREIGN KEY (destination_id) REFERENCES destinations(id)
);

CREATE TABLE IF NOT EXISTS destinations (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    config_json TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS events (
    id TEXT PRIMARY KEY,
    endpoint_id TEXT NOT NULL,
    payload_json TEXT NOT NULL,
    headers_json TEXT NOT NULL,
    signature_ok INTEGER NOT NULL DEFAULT 0,
    received_at TEXT NOT NULL,
    FOREIGN KEY (endpoint_id) REFERENCES webhook_endpoints(id)
);

CREATE TABLE IF NOT EXISTS deliveries (
    id TEXT PRIMARY KEY,
    event_id TEXT NOT NULL,
    destination_id TEXT NOT NULL,
    status TEXT NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    max_attempts INTEGER NOT NULL DEFAULT 1,
    last_error TEXT,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (event_id) REFERENCES events(id),
    FOREIGN KEY (destination_id) REFERENCES destinations(id)
);

CREATE TABLE IF NOT EXISTS schedules (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    cron_expr TEXT NOT NULL,
    timezone TEXT NOT NULL,
    destination_id TEXT NOT NULL,
    method TEXT NOT NULL,
    path TEXT,
    payload_template TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    next_run_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (destination_id) REFERENCES destinations(id)
);

CREATE TABLE IF NOT EXISTS schedule_runs (
    id TEXT PRIMARY KEY,
    schedule_id TEXT NOT NULL,
    status TEXT NOT NULL,
    response_status INTEGER,
    error TEXT,
    run_at TEXT NOT NULL,
    FOREIGN KEY (schedule_id) REFERENCES schedules(id)
);

CREATE TABLE IF NOT EXISTS secrets (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    provider TEXT NOT NULL,
    ref TEXT NOT NULL,
    version TEXT,
    rotated_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS endpoint_signatures (
    endpoint_id TEXT PRIMARY KEY,
    scheme TEXT NOT NULL,
    header_name TEXT NOT NULL,
    prefix TEXT,
    timestamp_header TEXT,
    tolerance_seconds INTEGER,
    base_string TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (endpoint_id) REFERENCES webhook_endpoints(id)
);

CREATE INDEX IF NOT EXISTS idx_webhook_endpoints_name ON webhook_endpoints(name);
CREATE INDEX IF NOT EXISTS idx_routes_endpoint_id ON routes(endpoint_id);
CREATE INDEX IF NOT EXISTS idx_events_endpoint_id ON events(endpoint_id);
CREATE INDEX IF NOT EXISTS idx_deliveries_event_id ON deliveries(event_id);
CREATE INDEX IF NOT EXISTS idx_schedules_next_run_at ON schedules(next_run_at);
CREATE INDEX IF NOT EXISTS idx_schedule_runs_schedule_id ON schedule_runs(schedule_id);