Spaces:
Sleeping
Sleeping
| """ | |
| 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 | |
| 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) | |