+-------------------------------------------------------------------------+
|                       HTTP API REQUEST LIFECYCLE                        |
+-------------------------------------------------------------------------+
                                     |
                                     v
              +---------------------------------------------+
              | 1. Begin SQL Transaction (Postgres)         |
              |    - Create User Record                     |
              |    - Insert Invoice Details                 |
              +---------------------------------------------+
                                     |
                                     v
              +---------------------------------------------+
              | 2. Enqueue Job to External Cache (Redis)    |
              |    - `bullmq.add('send_welcome_email')`     |
              +---------------------------------------------+
                                     |
               +---------------------+---------------------+
               |                                           |
      [Network Drop / Error]                       [Database Lock Delay]
               |                                           |
               v                                           v
+-------------------------------+         +--------------------------------+
| Outcome A: DB Rollback        |         | Outcome B: Race Condition      |
| - Job runs in Redis worker    |         | - Redis worker runs instantly  |
| - Postgres row never committed|         | - Fails to find user record in |
| - Ghost customer receives     |         |   DB because transaction is    |
|   welcome email for failed sub|         |   still in flight (Crash/Drop) |
+-------------------------------+         +--------------------------------+

-- Create an optimized, index-backed background queue table
CREATE TABLE app_background_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    queue_name VARCHAR(64) NOT NULL DEFAULT 'default',
    payload JSONB NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'queued', -- queued, processing, completed, failed
    attempts INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 3,
    last_error TEXT,
    scheduled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial index for lightning-fast job acquisition by background workers
CREATE INDEX idx_app_background_jobs_polling 
ON app_background_jobs (queue_name, scheduled_at) 
WHERE status = 'queued';

# src/api/tenants.py
from fastapi import APIRouter, Depends, status
from sqlalchemy.orm import Session
from src.database import get_db
from src.models import Tenant, BackgroundJob

router = APIRouter(prefix="/tenants", tags=["Tenants"])

@router.post("/", status_code=status.HTTP_202_ACCEPTED)
def register_tenant(org_name: str, admin_email: str, db: Session = Depends(get_db)):
    # Open explicit database transaction
    with db.begin():
        # 1. Create domain entity
        new_tenant = Tenant(name=org_name, billing_email=admin_email)
        db.add(new_tenant)
        db.flush()  # Populates new_tenant.id without committing

        # 2. Enqueue background provisioning job atomically
        job = BackgroundJob(
            tenant_id=new_tenant.id,
            queue_name="tenant-provisioning",
            payload={
                "action": "PROVISION_WORKSPACE",
                "tenant_id": str(new_tenant.id),
                "email": admin_email
            }
        )
        db.add(job)
    
    # Transaction commits atomically; both row and job exist
    return {"status": "accepted", "tenant_id": new_tenant.id, "job_id": job.id}

# src/workers/job_consumer.py
import time
from sqlalchemy import text
from src.database import SessionLocal
from src.services.llm import execute_llm_chain

def poll_and_execute_job(worker_id: str):
    db = SessionLocal()
    try:
        # Atomically select and lock the next pending job
        lock_query = text("""
            UPDATE app_background_jobs
            SET status = 'processing',
                attempts = attempts + 1,
                updated_at = NOW()
            WHERE id = (
                SELECT id 
                FROM app_background_jobs
                WHERE status = 'queued' 
                  AND scheduled_at <= NOW()
                  AND queue_name = 'default'
                ORDER BY scheduled_at ASC
                LIMIT 1
                FOR UPDATE SKIP LOCKED
            )
            RETURNING id, tenant_id, payload, attempts;
        """)
        
        job = db.execute(lock_query).mappings().first()
        
        if not job:
            db.rollback()
            return False  # Queue is currently empty
            
        db.commit()  # Lock state is recorded
        
        # Process long-running external operation
        try:
            payload = job["payload"]
            # E.g., Execute deterministic AI pipeline or heavy processing
            process_payload(payload)
            
            # Mark job as completed
            db.execute(text("UPDATE app_background_jobs SET status = 'completed' WHERE id = :id"), {"id": job["id"]})
            db.commit()
        except Exception as err:
            # Handle retry logic and backoff
            db.execute(
                text("UPDATE app_background_jobs SET status = 'failed', last_error = :err WHERE id = :id"),
                {"id": job["id"], "err": str(err)}
            )
            db.commit()
            
        return True
    finally:
        db.close()