veola/internal/db/schema.sql

PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    username      TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role          TEXT NOT NULL DEFAULT 'user',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS items (
    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
    name                  TEXT NOT NULL,
    search_query          TEXT,
    url                   TEXT,
    category              TEXT,
    target_price          REAL,
    ntfy_topic            TEXT NOT NULL,
    ntfy_priority         TEXT DEFAULT 'default',
    poll_interval_minutes INTEGER DEFAULT 60,
    include_out_of_stock  INTEGER DEFAULT 0,
    min_price             REAL,
    exclude_keywords      TEXT,
    listing_type          TEXT,
    condition             TEXT,
    region                TEXT,
    actor_active          TEXT,
    actor_sold            TEXT,
    actor_price_compare   TEXT,
    use_price_comparison  INTEGER DEFAULT 0,
    active                INTEGER DEFAULT 1,
    last_polled_at        DATETIME,
    last_poll_error       TEXT,
    best_price            REAL,
    best_price_currency   TEXT,
    best_price_store      TEXT,
    best_price_url        TEXT,
    best_price_image_url  TEXT,
    best_price_title      TEXT,
    created_at            DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_items_active ON items(active);

CREATE TABLE IF NOT EXISTS item_marketplaces (
    item_id     INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    position    INTEGER NOT NULL,
    marketplace TEXT NOT NULL,
    PRIMARY KEY (item_id, position)
);

CREATE INDEX IF NOT EXISTS idx_item_marketplaces_item ON item_marketplaces(item_id);

CREATE TABLE IF NOT EXISTS results (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id     INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    title       TEXT,
    price       REAL,
    currency    TEXT NOT NULL,
    url         TEXT,
    source      TEXT,
    image_url   TEXT,
    matched_query TEXT,
    alerted     INTEGER DEFAULT 0,
    found_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    ends_at     DATETIME
);

CREATE INDEX IF NOT EXISTS idx_results_item ON results(item_id, found_at DESC);
CREATE INDEX IF NOT EXISTS idx_results_dedup ON results(item_id, url);

CREATE TABLE IF NOT EXISTS price_history (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id     INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    price       REAL NOT NULL,
    store       TEXT,
    polled_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_price_history_item ON price_history(item_id, polled_at DESC);

CREATE TABLE IF NOT EXISTS settings (
    key         TEXT PRIMARY KEY,
    value       TEXT,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT OR IGNORE INTO settings (key, value) VALUES
    ('apify_api_key', ''),
    ('ntfy_base_url', ''),
    ('ntfy_default_topic', 'veola'),
    ('global_poll_interval_minutes', '60'),
    ('match_confidence_threshold', '0.6');

-- ebay_api_usage tracks Browse API calls per day so Veola can surface
-- consumption and halt polling before the developer keyset's daily call
-- limit is exceeded. usage_date is YYYY-MM-DD in US Pacific time, matching
-- eBay's own quota reset.
CREATE TABLE IF NOT EXISTS ebay_api_usage (
    usage_date TEXT PRIMARY KEY,
    call_count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS sessions (
    token  TEXT PRIMARY KEY,
    data   BLOB NOT NULL,
    expiry REAL NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sessions_expiry ON sessions(expiry);