SmartMail Database

SmartMail runs two storage systems side by side with overlapping responsibilities. See Architecture for how they're used at runtime.

SQLite (SQLAlchemy) — app/database.py

Fixed path sqlite:///<repo_root>/smartmail.db, hardcoded (not overridable via DB_DSN, despite that env var existing elsewhere). Confirmed on disk (~288 MB).

Table	Purpose	Live?
audit_logs	trace_id, action, subject_id, outcome, latency_ms, payload_hash, timestamp	Yes — 900k+ rows
metrics	name, value, timestamp	Yes — 900k+ rows
emails	Full email content mirror	No — 0 rows, superseded by Mongo
ingest_cursor	last_received_datetime, last_processed	No — 0 rows; a JSON-file version also exists (app/ingest_cursor.json)
attachment_text	OCR/extracted attachment text	No — 0 rows
key_value_extraction	GPT key-value extraction results	No — 0 rows
processed_emails	Dedup/processing tracker	No — 0 rows
thread_summaries	conversation_id, summary, bullet_points, last_message_id, last_message_dt, fingerprint, updated_at	No — 0 rows, and its own read/write helpers (save_thread_summary/get_thread_summary in this file) reference summary_json/summary_md attributes that don't exist on this model — would error if ever exercised

No foreign keys anywhere in this module — every table is independent.

Key functions: get_db_session() (fresh session per call, create_all on every call), init_db(), audit_log(...)/metrics_emit(...) (write, swallow exceptions), dashboard_totals(since_utc)/recent_audit_logs(limit) (read, feed the /dashboard route).

app/database.py also independently opens its own second MongoDB connection (get_mongo_collection) at the bottom of the file — despite the file otherwise being the SQLAlchemy module.

app/db.py is a DB_BACKEND=mongo|sqlite switch shim that re-exports either app.database or app.database_mongo's functions — but app/main.py doesn't import from it; it imports directly and simultaneously from both underlying modules, so this abstraction is effectively dead, and it has a latent bug (os.getenv("DB_BACKEND").lower() throws if the var is unset — only safe because .env happens to set it).

MongoDB — app/database_mongo.py (the real, actively-used content store)

Connection: MONGODB_URI → falls back to MONGO_URI → falls back to a hardcoded default (see Security note below). This deployment's .env sets MONGO_URI/MONGO_DB, so the fallback path is what's actually used. Database name similarly via MONGODB_DB/MONGO_DB.

Collection	Shape (key fields)	Written by
email_summaries	messageId, threadId, from/to/cc/bcc, receivedAt, direction, full_text, clean_text, subject, summary_text, category, shouldGenerateDraft, isDraft, isRead, updatedAt	save_email_summary() (poller + "open message"); the main per-email record, unique on messageId
emails	Raw cache — at least 3 inconsistent shapes depending on writer (see below)	upsert_email(), app/email_store.py:upsert_graph_message()
thread_summaries	{thread_id, summary, ...} (an earlier conversation_id/summary_json/summary_md shape is defined but dead — see Architecture §5)	roll_thread_summary()
categories	name, description, prompt (prompt = free text or a structured rule dict)	Admin Category tab, ensure_default_categories() seeds 8 defaults
gpt_prompts	key, name, prompt — editable GPT reply-drafting templates	Admin GPT Prompt tab
metrics	name, value, timestamp	add_metric() — a separate write path from the SQLite metrics_emit() above, same conceptual data
audit_logs	Two different shapes depending on entry point (audit_log(...) vs add_audit(event, detail))	Same duplication pattern as metrics
admin_users	username, email, password_hash, role, is_active, createdAt	Admin Users tab — not the same credential store the /login form actually checks (see User Guide)

Other Mongo client modules, each opening their own separate connection to the same emails/email_summaries collections rather than sharing one: app/mongo_client.py (used by app/email_store.py), app/mongo_emails.py (used by main.py's "recent inbox" admin view). Four independent MongoClient(...) instantiations exist across the codebase in total.

Categorisation logic

• _infer_category(...) — hardcoded keyword/sender heuristic (fallback).
• infer_category_dynamic(...) — checks each admin-configured category's prompt (parsed as a rule: match_any, sender_contains_any, fields, or sender:/subject:/body: prefixes on free text) before falling back to the heuristic above. This is keyword matching, not an LLM call, despite being labelled "prompt" in the admin UI.
• recategorise_recent_email_summaries(limit=800) — re-runs classification over recent summaries when a category is added/edited/deleted, so results update immediately.

Config

• config/schema.json is a 0-byte empty file — contributes nothing despite its name.
• config/load_config.py merges config/config.ini sections with env-var overrides, but its DB_DSN lookup reads purely from os.getenv("DB_DSN", "") — it never actually consults the .ini section dict for this key. No Mongo keys are read through this loader at all; Mongo config is entirely ad hoc os.getenv(...) calls scattered across the Mongo modules.
• app/config.py is a second, separate env loader (loads env/.env.development) also exposing a DB_DSN constant — unclear which of the two loaders is authoritative, since app/database.py/ app/database_mongo.py read their own env vars directly and use neither loader.

Known inconsistencies (useful to know before changing this code)

1. Audit logs and metrics are recorded in both SQLite (actively, 900k+ rows) and MongoDB — ambiguous which is the source of truth; main.py's dashboard reads the SQLite versions while other code paths write the Mongo versions.
2. save_thread_summary/get_thread_summary are defined twice in app/database_mongo.py with incompatible signatures; the second definition silently wins.
3. The emails Mongo collection has at least three different document shapes depending on whether database_mongo.upsert_email, email_store.upsert_graph_message, or the unused email_store.graph_message_to_doc wrote the document.
4. requirements.txt lists psycopg[binary] (a Postgres driver) with no Postgres connection code anywhere in the repo — appears to be an unused/leftover dependency.

Security note

A live-looking MongoDB URI with embedded username and password is hardcoded as the os.getenv(..., "<default>") fallback in five separate files (app/database.py, app/database_mongo.py, app/mongo_client.py, app/mongo_emails.py, app/main.py), and the real credentials, plus the OpenAI API key and Graph client secret, are committed in plaintext in .env/env/.env.development. Values are intentionally not reproduced here — recommend removing the hardcoded fallback defaults and ensuring .env files are excluded from any shared copies of the repo (they are already gitignored, but the checked-out working tree has them present).