Spaces:
Sleeping
Sleeping
File size: 13,845 Bytes
a17af42 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | """
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)
|