feat(HRT-202): Billing tables + consumption endpoint

Phase 1 — Added 3 SQLite tables to billing_db.py:
- invoices (invoice_number, user_id, period, amount, status, pdf_path)
- transactions (user_id, invoice_id, type, amount, stripe_payment_intent)
- consumption_log (user_id, date, api_calls, endpoint)
- PRAGMA foreign_keys = ON in get_db()
- Dataclass model classes for documentation

Phase 2 — GET /api/v1/billing/consumption?month=YYYY-MM:
- JWT auth required, user can only query own data
- YYYY-MM validation (422 on malformed)
- Configurable PLAN_LIMITS via env vars (not hardcoded)
- Monthly aggregation from consumption_log
- Alert semantics: 80% soft (X-Billing-Alert: soft_limit_warning)
                  100% hard (X-Billing-Alert: hard_limit_reached)
- Proper error handling (200 with zeros for no data)

Pre-checks addressed:
- PRAGMA foreign_keys = ON added to get_db()
- saas_subscriptions.plan column verified present
- Invoice format: FACT-{YYYYMM}-{XXXX} (future generation)
- Dataclass models added

Co-Authored-By: Paperclip <noreply@paperclip.ing>

api_v1/routes/billing.py

@@ -55,6 +55,23 @@ PLAN_NAMES = {
     "pro": "Pro",
 }
 
+# Plan consumption limits (configurable, not hardcoded)
+# Override via env vars: BILLING_LIMIT_FREE_API_CALLS, BILLING_LIMIT_PREMIUM_API_CALLS, etc.
+PLAN_LIMITS = {
+    "free": {
+        "monthly_api_calls": int(os.environ.get("BILLING_LIMIT_FREE_API_CALLS", "300")),
+        "monthly_tokens": int(os.environ.get("BILLING_LIMIT_FREE_TOKENS", "100000")),
+    },
+    "premium": {
+        "monthly_api_calls": int(os.environ.get("BILLING_LIMIT_PREMIUM_API_CALLS", "3000")),
+        "monthly_tokens": int(os.environ.get("BILLING_LIMIT_PREMIUM_TOKENS", "1000000")),
+    },
+    "pro": {
+        "monthly_api_calls": int(os.environ.get("BILLING_LIMIT_PRO_API_CALLS", "30000")),
+        "monthly_tokens": int(os.environ.get("BILLING_LIMIT_PRO_TOKENS", "10000000")),
+    },
+}
+
 # ──────────────────────────────────────────────────────────────
 # DB helpers
 # ──────────────────────────────────────────────────────────────
@@ -654,6 +671,122 @@ def _handle_payment_succeeded(db, event):
     logger.info("invoice.payment_succeeded: user %s payment cleared", user_id)
 
 
+# ──────────────────────────────────────────────────────────────
+# GET /api/v1/billing/consumption
+# ──────────────────────────────────────────────────────────────
+
+
+def _parse_month(month: str):
+    """Validate YYYY-MM format, return (year, month) tuple or None."""
+    import re
+    if not re.match(r"^\d{4}-\d{2}$", month):
+        return None
+    parts = month.split("-")
+    y, m = int(parts[0]), int(parts[1])
+    if m < 1 or m > 12:
+        return None
+    return y, m
+
+
+@billing_bp.route("/consumption", methods=["GET"])
+@jwt_required_middleware
+def consumption_status():
+    """
+    Return current month consumption vs plan limits for the authenticated user.
+    ---
+    tags:
+      - Billing
+    security:
+      - Bearer: []
+    parameters:
+      - in: query
+        name: month
+        type: string
+        required: false
+        description: "Month in YYYY-MM format (default: current month)"
+    responses:
+      200:
+        description: Consumption status with usage, limits, and alerts
+      400:
+        description: Invalid parameters
+      422:
+        description: Malformed month format
+    """
+    user = request.current_user
+    month = request.args.get("month", datetime.now().strftime("%Y-%m"))
+    parsed = _parse_month(month)
+    if not parsed:
+        return jsonify({"error": "Format mois invalide. Utiliser YYYY-MM"}), 422
+
+    year, mon = parsed
+    plan = user.get("plan", "free")
+    limits = PLAN_LIMITS.get(plan, PLAN_LIMITS["free"])
+
+    db = get_db()
+    try:
+        # Aggregate consumption for the given month
+        month_start = f"{year:04d}-{mon:02d}-01"
+        if mon == 12:
+            month_end = f"{year + 1:04d}-01-01"
+        else:
+            month_end = f"{year:04d}-{mon + 1:02d}-01"
+
+        row = db.execute(
+            """SELECT
+                   COALESCE(SUM(api_calls), 0) AS total_api_calls
+               FROM consumption_log
+               WHERE user_id = ? AND date >= ? AND date < ?""",
+            (str(user["id"]), month_start, month_end),
+        ).fetchone()
+
+        total_api_calls = row["total_api_calls"] if row else 0
+
+        # Calculate alert levels
+        api_limit = limits["monthly_api_calls"]
+        api_pct = round((total_api_calls / api_limit * 100), 1) if api_limit > 0 else 0
+
+        alerts = []
+        if api_pct >= 100:
+            alerts.append({
+                "type": "hard",
+                "metric": "api_calls",
+                "message": "Limite mensuelle d'appels API atteinte.",
+                "current": total_api_calls,
+                "limit": api_limit,
+            })
+        elif api_pct >= 80:
+            alerts.append({
+                "type": "soft",
+                "metric": "api_calls",
+                "message": f"Appels API à {api_pct}% de la limite mensuelle.",
+                "current": total_api_calls,
+                "limit": api_limit,
+            })
+
+    except Exception as e:
+        logger.error("Consumption query error for user %s: %s", user["id"], e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+    resp = jsonify({
+        "user_id": user["id"],
+        "plan": plan,
+        "month": month,
+        "consumption": {
+            "total_api_calls": total_api_calls,
+            "limit_api_calls": api_limit,
+            "usage_pct": api_pct,
+        },
+        "alerts": alerts,
+    })
+    if any(a["type"] == "hard" for a in alerts):
+        resp.headers["X-Billing-Alert"] = "hard_limit_reached"
+    elif any(a["type"] == "soft" for a in alerts):
+        resp.headers["X-Billing-Alert"] = "soft_limit_warning"
+    return resp, 200
+
+
 # ──────────────────────────────────────────────────────────────
 # On-import: ensure DB migration ran
 # ──────────────────────────────────────────────────────────────

billing_db.py

@@ -13,6 +13,8 @@ Run once:
 import sqlite3
 import os
 import logging
+from dataclasses import dataclass
+from typing import Optional
 
 DB_PATH = os.environ.get("TURF_SAAS_DB", "/home/h3r7/turf_saas/turf_saas.db")
 logger = logging.getLogger("turf_saas.billing_db")
@@ -21,6 +23,7 @@ logger = logging.getLogger("turf_saas.billing_db")
 def get_db():
     conn = sqlite3.connect(DB_PATH)
     conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA foreign_keys = ON")
     return conn
 
 
@@ -101,12 +104,59 @@ def migrate_billing_tables():
         CREATE INDEX IF NOT EXISTS idx_saas_subs_stripe       ON saas_subscriptions(stripe_subscription_id);
         CREATE INDEX IF NOT EXISTS idx_subscriptions_stripe   ON subscriptions(stripe_subscription_id);
         CREATE INDEX IF NOT EXISTS idx_subscriptions_customer ON subscriptions(stripe_customer_id);
+
+        -- HRT-202: Billing tables (invoices, transactions, consumption_log)
+        CREATE TABLE IF NOT EXISTS invoices (
+            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
+            invoice_number      TEXT    NOT NULL UNIQUE,
+            user_id             INTEGER NOT NULL REFERENCES users(id),
+            period_start        TEXT    NOT NULL,
+            period_end          TEXT    NOT NULL,
+            plan                TEXT    NOT NULL,
+            amount_cents        INTEGER NOT NULL,
+            currency            TEXT    NOT NULL DEFAULT 'EUR',
+            status              TEXT    NOT NULL DEFAULT 'pending'
+                                    CHECK(status IN ('pending','paid','overdue','cancelled','refunded')),
+            pdf_path            TEXT,
+            stripe_invoice_id   TEXT,
+            created_at          TEXT    NOT NULL DEFAULT (datetime('now')),
+            paid_at             TEXT
+        );
+
+        CREATE TABLE IF NOT EXISTS transactions (
+            id                      INTEGER PRIMARY KEY AUTOINCREMENT,
+            user_id                 INTEGER NOT NULL REFERENCES users(id),
+            invoice_id              INTEGER REFERENCES invoices(id),
+            type                    TEXT    NOT NULL
+                                        CHECK(type IN ('subscription','overage','credit','refund')),
+            amount_cents            INTEGER NOT NULL,
+            currency                TEXT    NOT NULL DEFAULT 'EUR',
+            stripe_payment_intent_id TEXT,
+            description             TEXT,
+            created_at              TEXT    NOT NULL DEFAULT (datetime('now'))
+        );
+
+        CREATE TABLE IF NOT EXISTS consumption_log (
+            id          INTEGER PRIMARY KEY AUTOINCREMENT,
+            user_id     INTEGER NOT NULL REFERENCES users(id),
+            date        TEXT    NOT NULL,
+            api_calls   INTEGER NOT NULL DEFAULT 0,
+            endpoint    TEXT,
+            created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
+            UNIQUE(user_id, date, endpoint)
+        );
+
+        CREATE INDEX IF NOT EXISTS idx_invoices_user     ON invoices(user_id);
+        CREATE INDEX IF NOT EXISTS idx_invoices_status   ON invoices(status);
+        CREATE INDEX IF NOT EXISTS idx_transactions_user ON transactions(user_id);
+        CREATE INDEX IF NOT EXISTS idx_consumption_user  ON consumption_log(user_id);
+        CREATE INDEX IF NOT EXISTS idx_consumption_date  ON consumption_log(date);
     """)
 
     conn.commit()
     conn.close()
     print(
-        "[billing_db] Migration complete: subscriptions + billing_events tables ready."
+        "[billing_db] Migration complete: subscriptions + billing_events + invoices + transactions + consumption_log ready."
     )
 
 
@@ -115,6 +165,51 @@ if __name__ == "__main__":
     migrate_billing_tables()
 
 
+# ──────────────────────────────────────────────────────────────
+# Model classes (documentation / type hints)
+# ──────────────────────────────────────────────────────────────
+
+
+@dataclass
+class Invoice:
+    id: Optional[int] = None
+    invoice_number: str = ""
+    user_id: int = 0
+    period_start: str = ""
+    period_end: str = ""
+    plan: str = ""
+    amount_cents: int = 0
+    currency: str = "EUR"
+    status: str = "pending"
+    pdf_path: Optional[str] = None
+    stripe_invoice_id: Optional[str] = None
+    created_at: str = ""
+    paid_at: Optional[str] = None
+
+
+@dataclass
+class Transaction:
+    id: Optional[int] = None
+    user_id: int = 0
+    invoice_id: Optional[int] = None
+    type: str = "subscription"
+    amount_cents: int = 0
+    currency: str = "EUR"
+    stripe_payment_intent_id: Optional[str] = None
+    description: Optional[str] = None
+    created_at: str = ""
+
+
+@dataclass
+class ConsumptionLog:
+    id: Optional[int] = None
+    user_id: int = 0
+    date: str = ""
+    api_calls: int = 0
+    endpoint: Optional[str] = None
+    created_at: str = ""
+
+
 # ──────────────────────────────────────────────────────────────
 # Re-exported helpers for test usage
 # (primary implementations live in api_v1/routes/billing.py)