""" app/models/db.py Database schema, connection management, and initialisation. Uses SQLite with WAL mode for single-server MVP. Migration path: swap sqlite3 for asyncpg/SQLAlchemy when ≥100 DAU. """ import os import json import sqlite3 import threading import logging from contextlib import contextmanager logger = logging.getLogger(__name__) DATA_DIR = os.path.join(os.getcwd(), "data") DB_FILE = os.path.join(DATA_DIR, "eatlytic.db") os.makedirs(DATA_DIR, exist_ok=True) def get_connection() -> sqlite3.Connection: conn = sqlite3.connect(DB_FILE, check_same_thread=False, timeout=15) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA foreign_keys=ON") conn.execute("PRAGMA synchronous=NORMAL") # fast + safe in WAL mode return conn @contextmanager def db_conn(): """Thread-safe context manager: auto-commit on success, rollback on error.""" conn = get_connection() try: yield conn conn.commit() except Exception: conn.rollback() raise finally: conn.close() def init_db() -> None: """Idempotent schema creation. Run at startup.""" with db_conn() as conn: conn.executescript(""" -- ── USERS (Phase 1: real accounts) ─────────────────────── CREATE TABLE IF NOT EXISTS users ( id TEXT PRIMARY KEY, -- UUID email TEXT UNIQUE, phone TEXT UNIQUE, name TEXT DEFAULT '', created_at TEXT DEFAULT (datetime('now')), last_login TEXT DEFAULT (datetime('now')), is_pro INTEGER DEFAULT 0, pro_expires TEXT DEFAULT NULL, stripe_customer_id TEXT DEFAULT NULL, -- Razorpay customer scan_count_month INTEGER DEFAULT 0, scan_month TEXT DEFAULT '', streak_days INTEGER DEFAULT 0, last_scan_date TEXT DEFAULT '', tdee REAL DEFAULT 0, persona TEXT DEFAULT 'General Adult', language TEXT DEFAULT 'en', onboarding_done INTEGER DEFAULT 0 ); -- ── SESSIONS / TOKENS ───────────────────────────────────── CREATE TABLE IF NOT EXISTS sessions ( token TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, created_at TEXT DEFAULT (datetime('now')), expires_at TEXT NOT NULL, device_hint TEXT DEFAULT '' ); CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id); -- ── LEGACY DEVICE KEYS (for anonymous users) ────────────── CREATE TABLE IF NOT EXISTS devices ( device_key TEXT PRIMARY KEY, user_id TEXT REFERENCES users(id), -- NULL = anonymous created_at TEXT DEFAULT (datetime('now')), is_pro INTEGER DEFAULT 0, month TEXT DEFAULT '', scan_count INTEGER DEFAULT 0, streak_days INTEGER DEFAULT 0, last_scan_date TEXT DEFAULT '', persona TEXT DEFAULT 'General Adult', language TEXT DEFAULT 'en', tdee REAL DEFAULT 0, onboarding_done INTEGER DEFAULT 0 ); -- ── SCANS ───────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS scans ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT REFERENCES users(id), device_key TEXT, product_name TEXT DEFAULT 'Unknown', score INTEGER DEFAULT 0, verdict TEXT DEFAULT '', calories REAL DEFAULT 0, protein REAL DEFAULT 0, carbs REAL DEFAULT 0, fat REAL DEFAULT 0, sodium REAL DEFAULT 0, fiber REAL DEFAULT 0, sugar REAL DEFAULT 0, persona TEXT DEFAULT '', language TEXT DEFAULT 'en', scanned_at TEXT DEFAULT (datetime('now')), analysis_json TEXT DEFAULT '{}', -- Moat columns: verified data feeds proprietary DB verified INTEGER DEFAULT 0, verified_by TEXT DEFAULT NULL, verified_at TEXT DEFAULT NULL, barcode TEXT DEFAULT NULL, brand TEXT DEFAULT NULL, category TEXT DEFAULT NULL ); CREATE INDEX IF NOT EXISTS idx_scans_user ON scans(user_id); CREATE INDEX IF NOT EXISTS idx_scans_device ON scans(device_key); CREATE INDEX IF NOT EXISTS idx_scans_date ON scans(scanned_at); CREATE INDEX IF NOT EXISTS idx_scans_product ON scans(product_name); -- ── DAILY LOGS ──────────────────────────────────────────── CREATE TABLE IF NOT EXISTS daily_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT REFERENCES users(id), device_key TEXT, log_date TEXT NOT NULL, meal_name TEXT DEFAULT '', calories REAL DEFAULT 0, protein REAL DEFAULT 0, carbs REAL DEFAULT 0, fat REAL DEFAULT 0, sodium REAL DEFAULT 0, fiber REAL DEFAULT 0, sugar REAL DEFAULT 0, source TEXT DEFAULT 'scan', -- scan | manual | search logged_at TEXT DEFAULT (datetime('now')) ); CREATE INDEX IF NOT EXISTS idx_daily_user_date ON daily_logs(user_id, log_date); CREATE INDEX IF NOT EXISTS idx_daily_dev_date ON daily_logs(device_key, log_date); -- ── ALLERGEN PROFILES ───────────────────────────────────── CREATE TABLE IF NOT EXISTS allergen_profiles ( device_key TEXT PRIMARY KEY, user_id TEXT REFERENCES users(id), allergens TEXT DEFAULT '[]', conditions TEXT DEFAULT '[]', updated_at TEXT DEFAULT (datetime('now')) ); -- ── PROPRIETARY FOOD DATABASE (Phase 2 moat) ────────────── -- Every scan feeds this. After 10K entries, it's a data asset. CREATE TABLE IF NOT EXISTS food_products ( id INTEGER PRIMARY KEY AUTOINCREMENT, barcode TEXT UNIQUE, -- EAN-13 if available name TEXT NOT NULL, brand TEXT DEFAULT '', category TEXT DEFAULT '', -- Verified nutrition per 100g calories_100g REAL DEFAULT 0, protein_100g REAL DEFAULT 0, carbs_100g REAL DEFAULT 0, fat_100g REAL DEFAULT 0, sodium_100g REAL DEFAULT 0, fiber_100g REAL DEFAULT 0, sugar_100g REAL DEFAULT 0, sat_fat_100g REAL DEFAULT 0, -- Eatlytic scoring eatlytic_score INTEGER DEFAULT 0, fssai_compliant INTEGER DEFAULT 0, ingredients_raw TEXT DEFAULT '', allergens_json TEXT DEFAULT '[]', -- Data provenance source TEXT DEFAULT 'llm_scan', -- llm_scan | human_verified | off_import scan_count INTEGER DEFAULT 0, -- how many times scanned verified INTEGER DEFAULT 0, verified_by TEXT DEFAULT NULL, created_at TEXT DEFAULT (datetime('now')), updated_at TEXT DEFAULT (datetime('now')) ); CREATE INDEX IF NOT EXISTS idx_food_barcode ON food_products(barcode); CREATE INDEX IF NOT EXISTS idx_food_name ON food_products(name); CREATE INDEX IF NOT EXISTS idx_food_brand ON food_products(brand); -- ── ACCURACY BENCHMARKS (Phase 2) ───────────────────────── CREATE TABLE IF NOT EXISTS benchmarks ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_name TEXT NOT NULL, ground_truth_json TEXT NOT NULL, -- hand-verified nutrition data llm_output_json TEXT DEFAULT '{}', ocr_text TEXT DEFAULT '', f1_score REAL DEFAULT 0, score_delta REAL DEFAULT 0, -- LLM score vs verified score field_accuracy TEXT DEFAULT '{}', -- per-field accuracy JSON tested_at TEXT DEFAULT (datetime('now')), model_used TEXT DEFAULT '' ); -- ── NPS ─────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS nps_responses ( id INTEGER PRIMARY KEY AUTOINCREMENT, device_key TEXT, user_id TEXT REFERENCES users(id), score INTEGER NOT NULL, comment TEXT DEFAULT '', submitted_at TEXT DEFAULT (datetime('now')) ); -- ── PAYMENTS ────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS payments ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT REFERENCES users(id), device_key TEXT, razorpay_order_id TEXT UNIQUE, razorpay_payment_id TEXT UNIQUE, razorpay_signature TEXT DEFAULT '', amount_paise INTEGER DEFAULT 19900, -- ₹199 currency TEXT DEFAULT 'INR', status TEXT DEFAULT 'created', -- created|paid|failed plan TEXT DEFAULT 'pro_monthly', created_at TEXT DEFAULT (datetime('now')), paid_at TEXT DEFAULT NULL ); -- ── B2B API KEYS ────────────────────────────────────────── CREATE TABLE IF NOT EXISTS api_keys ( api_key TEXT PRIMARY KEY, client_name TEXT NOT NULL, plan TEXT DEFAULT 'business', scans_this_month INTEGER DEFAULT 0, month TEXT DEFAULT '', active INTEGER DEFAULT 1, created_at TEXT DEFAULT (datetime('now')) ); -- ── CACHES ──────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS ocr_cache ( cache_key TEXT PRIMARY KEY, result_json TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now')) ); CREATE TABLE IF NOT EXISTS ai_cache ( cache_key TEXT PRIMARY KEY, result_json TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now')) ); """) logger.info("Database ready: %s", DB_FILE) # ── Cache helpers ────────────────────────────────────────────────────── def get_ocr_cache(key: str): try: with db_conn() as c: row = c.execute("SELECT result_json FROM ocr_cache WHERE cache_key=?", (key,)).fetchone() return json.loads(row["result_json"]) if row else None except Exception: return None def set_ocr_cache(key: str, value: dict): try: with db_conn() as c: c.execute("INSERT OR REPLACE INTO ocr_cache(cache_key,result_json) VALUES(?,?)", (key, json.dumps(value))) except Exception as exc: logger.warning("set_ocr_cache: %s", exc) def get_ai_cache(key: str): try: with db_conn() as c: row = c.execute("SELECT result_json FROM ai_cache WHERE cache_key=?", (key,)).fetchone() return json.loads(row["result_json"]) if row else None except Exception: return None def set_ai_cache(key: str, value: dict): try: with db_conn() as c: c.execute("INSERT OR REPLACE INTO ai_cache(cache_key,result_json) VALUES(?,?)", (key, json.dumps(value))) except Exception as exc: logger.warning("set_ai_cache: %s", exc)