Marius Mutu
e257fa5d5f
- US-001: mută queue_client.py în data_entry/services/ocr/ - US-002/003/004: oracle_receipt_writer + oracle_server_id în DB - US-005: receipt_handlers.py (PDF/photo/callback flow) - US-006: wire handlers în main.py, per-schema connect, seq_cod.nextval - US-007: .gitignore secrets/*.oracle_pass - US-008/009/010: teste unit + integration + E2E - setup-secrets.sh helper + template - docs/telegram/README.md actualizat cu arhitectura nouă Testat E2E pe DB live (MARIUSM_AUTO). COD din seq_cod.nextval. pypdfium2 fallback pentru PDF decode (fără poppler). Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
103 lines
5.2 KiB
Python
103 lines
5.2 KiB
Python
"""Shared SQLite database pentru toate datele auth-related (trusted devices, backup codes, email cache)."""
|
|
import aiosqlite
|
|
import logging
|
|
from pathlib import Path
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
DB_DIR = Path(__file__).parent.parent.parent / "backend" / "data"
|
|
DB_PATH = DB_DIR / "app.db"
|
|
|
|
|
|
async def get_db() -> aiosqlite.Connection:
|
|
conn = await aiosqlite.connect(DB_PATH)
|
|
conn.row_factory = aiosqlite.Row
|
|
return conn
|
|
|
|
|
|
async def init_app_db():
|
|
"""Create all auth-related tables. Safe to call multiple times."""
|
|
DB_DIR.mkdir(parents=True, exist_ok=True)
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
await db.execute("PRAGMA journal_mode=WAL")
|
|
await db.execute("PRAGMA busy_timeout=5000")
|
|
await db.execute("PRAGMA foreign_keys = ON")
|
|
|
|
# Telegram tables (delegate init from telegram/db/database.py)
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS telegram_users (
|
|
telegram_user_id INTEGER PRIMARY KEY,
|
|
username TEXT, first_name TEXT NOT NULL, last_name TEXT,
|
|
oracle_username TEXT, oracle_server_id TEXT,
|
|
jwt_token TEXT, jwt_refresh_token TEXT,
|
|
token_expires_at TIMESTAMP, linked_at TIMESTAMP,
|
|
last_active_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT 1
|
|
)""")
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS telegram_auth_codes (
|
|
code TEXT PRIMARY KEY, telegram_user_id INTEGER, oracle_username TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL,
|
|
used BOOLEAN DEFAULT 0, used_at TIMESTAMP, server_id TEXT,
|
|
FOREIGN KEY (telegram_user_id) REFERENCES telegram_users(telegram_user_id)
|
|
)""")
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS telegram_sessions (
|
|
session_id TEXT PRIMARY KEY, telegram_user_id INTEGER NOT NULL,
|
|
conversation_state TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (telegram_user_id) REFERENCES telegram_users(telegram_user_id)
|
|
)""")
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS email_auth_codes (
|
|
code TEXT PRIMARY KEY, email TEXT NOT NULL, oracle_username TEXT NOT NULL,
|
|
telegram_user_id INTEGER NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMP NOT NULL, used INTEGER DEFAULT 0, used_at TIMESTAMP,
|
|
failed_attempts INTEGER DEFAULT 0,
|
|
FOREIGN KEY (telegram_user_id) REFERENCES telegram_users(telegram_user_id)
|
|
)""")
|
|
|
|
# Trusted devices (migrated from JSON)
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS trusted_devices (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
username TEXT NOT NULL,
|
|
server_id TEXT,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)""")
|
|
|
|
# Backup codes (new)
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS backup_codes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL,
|
|
server_id TEXT,
|
|
code_hash TEXT NOT NULL,
|
|
used INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
used_at TIMESTAMP
|
|
)""")
|
|
|
|
# Email-server cache (migrated from in-memory)
|
|
await db.execute("""CREATE TABLE IF NOT EXISTS email_server_cache (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
email TEXT NOT NULL,
|
|
server_id TEXT NOT NULL,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(email, server_id)
|
|
)""")
|
|
|
|
# Indexes for telegram
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_auth_codes_telegram_user ON telegram_auth_codes(telegram_user_id)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_auth_codes_expires ON telegram_auth_codes(expires_at)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_sessions_telegram_user ON telegram_sessions(telegram_user_id)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_sessions_expires ON telegram_sessions(expires_at)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_email_auth_email ON email_auth_codes(email)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_email_auth_telegram_user ON email_auth_codes(telegram_user_id)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_email_auth_expires ON email_auth_codes(expires_at)")
|
|
|
|
# Indexes for new tables
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_trusted_devices_user ON trusted_devices(username, server_id)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_trusted_devices_expires ON trusted_devices(expires_at)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_backup_codes_user ON backup_codes(username, server_id)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_email_cache_email ON email_server_cache(email)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_email_cache_server ON email_server_cache(server_id)")
|
|
|
|
await db.commit()
|
|
logger.info("[APP_DB] Database initialized successfully")
|