AI Image Generator — SQL source

-- AI Image Generator — D1 (SQLite) usage-gate schema and queries for
-- FLUX.1-schnell image generation.
--
-- Language: SQL (SQLite dialect — Cloudflare D1)
-- Source:   CosmoDev polyglot showcase port, from migrations/0002 and the
--           /api/image-gen route. Counts only — no prompts, no images, no
--           raw IPs: the per-IP key is a truncated SHA-256 of the client IP.

-- Per-IP hourly buckets. `hour` is 'YYYY-MM-DDTHH' in UTC.
CREATE TABLE IF NOT EXISTS image_gen_ip_usage (
  ip_hash TEXT NOT NULL,
  hour    TEXT NOT NULL,
  count   INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (ip_hash, hour)
);

-- Site-global daily budget. `day` is 'YYYY-MM-DD' in UTC.
CREATE TABLE IF NOT EXISTS image_gen_daily (
  day   TEXT PRIMARY KEY,
  count INTEGER NOT NULL DEFAULT 0
);

-- Increment-then-read: the Nth concurrent request sees its own increment, so
-- two requests cannot both consume the last slot. Denied attempts also count
-- — conservative under abuse (a hammering IP burns its own window).
INSERT INTO image_gen_ip_usage (ip_hash, hour, count) VALUES (?1, ?2, 1)
ON CONFLICT(ip_hash, hour) DO UPDATE SET count = count + 1;

INSERT INTO image_gen_daily (day, count) VALUES (?1, 1)
ON CONFLICT(day) DO UPDATE SET count = count + 1;

-- Read back the post-increment counters that feed checkGate().
SELECT count FROM image_gen_ip_usage WHERE ip_hash = ?1 AND hour = ?2;
SELECT count FROM image_gen_daily WHERE day = ?1;

-- The verdict itself lives in application code (daily cap of 100 wins, then
-- the per-IP hourly cap of 10), but expressed as one SQL statement:
WITH ip_count AS (
  SELECT count FROM image_gen_ip_usage WHERE ip_hash = ?1 AND hour = ?2
), daily_count AS (
  SELECT count FROM image_gen_daily WHERE day = ?3
)
SELECT CASE
  WHEN (SELECT count FROM daily_count) >= 100 THEN 'RATE_LIMITED_DAILY'
  WHEN (SELECT count FROM ip_count) >= 10 THEN 'RATE_LIMITED_IP'
  ELSE 'OK'
END AS verdict;

-- The daily retry hint, as SQL: whole minutes (>= 1) to the next UTC midnight.
SELECT MAX(1, CAST(CEIL(
  (julianday(date('now') || '+1 day') - julianday('now')) * 24 * 60
) AS INTEGER)) AS retry_after_minutes;