OperSight System Design

Date: 2025-11-07 Scope: Detailed design for a solution that enables uploading, downloading, storing large files in Azure Blob Storage, coordinating AI processing, and managing metadata using a Django REST Framework API backed by an SQLite (.db3) database. This document assumes you will keep SQLite for now (db3) but includes implementation notes and caveats to reduce risk.

Goals and constraints

Allow browser (React) clients to list/upload/download files and see processed outputs.
Keep a single canonical metadata store (SQLite .db3) managed by DRF (App Service). Use SQLite even though it has limitations — design around them.
Stream blobs directly between clients/AI workers and Azure Blob Storage for performance (avoid piping bytes through the small App Service instance).
Allow AI machine(s) to process raw blobs and upload results to a processed container, and to reliably update metadata via DRF.
Keep system resilient: idempotent processing, job leasing, ETag/checksum verification, and clear status transitions.

High-level architecture

Notes: Browser & AI use direct blob access (SAS or Managed Identity) to avoid routing bytes through the small App Service. The API remains the single source of truth for file metadata.

Components & responsibilities

React Browser client
- Request file listings & metadata from DRF API.
- Ask DRF for an upload SAS (signed URL) and perform direct uploads to raw container.
- Confirm upload to DRF or rely on Event Grid (choose 1 approach; recommended: both — client confirm for UX, Event Grid as backup).
- Request download SAS for files or receive a download link.
Django REST Framework API (Azure App Service)
- Authorizes users and issues short-lived SAS tokens for upload/download.
- Stores metadata in db3 SQLite: files table, processed_files, jobs (see schema section).
- Enqueues messages to the Storage Queue (or relies on Event Grid) for processing.
- Receives processed-file notifications from AI Machine and updates metadata (idempotent).
Azure Blob Storage
- Containers: raw (user uploads), processed (AI outputs), archive (optional)
- Emit events (blob created) to Event Grid and Storage Queue.
Event / Queue
- Event Grid: Source-of-truth for blob create events (optional but recommended).
- Storage Queue: Job queue for workers; messages contain file_id, blob_path, sas_read_url (optional), job_id.
AI Machine(s)
- Poll or listen to the Storage Queue, claim jobs, and acquire a blob lease (optional) to avoid duplicate processing.
- Download raw blob (SAS or managed identity), process it streaming to minimize memory usage, upload result to processed container.
- POST result metadata to the API (with job_id, checksum, etag, size).

Deployment assumptions and constraints (SQLite-specific)

DRF App Service: Small instance. SQLite .db3 file will be stored in App Service file system (or in a mounted Azure Files share). App Service restarts and scaling operations can cause the filesystem to be replaced — treat this as a risk.
SQLite caveats:
- SQLite supports concurrent readers but serializes writers. Heavy concurrent writes will cause database is locked errors.
- App Service may restart, and .db3 on the container file system can be lost if not persisted to a network share. Use Azure Files (SMB) mount for persistence if possible.
- Use WAL journal mode for better concurrency and performance. Set PRAGMA journal_mode=WAL; and PRAGMA synchronous=NORMAL;.
- Keep transactions short and use retries/backoff on write failures.
- Prefer serializing critical writes through DRF only; do not let multiple processes write to the DB concurrently from different machines unless careful locking is implemented.

Temporary mitigation plan (sticking with SQLite):

Keep only DRF writing to the DB. AI worker never writes directly to the DB — it must call DRF's endpoints to update metadata. This retains single-writer property and avoids multiple writers on the SQLite file.
Use a Storage Queue to notify DRF of jobs and keep AI machines stateless regarding metadata updates.

Database schema (SQLite `.db3`)

-- Files table for raw uploads
CREATE TABLE files (
  id TEXT PRIMARY KEY, -- UUID
  user_id TEXT,
  blob_container TEXT, -- 'raw' or 'processed'
  blob_path TEXT,
  filename TEXT,
  size INTEGER,
  content_type TEXT,
  etag TEXT,
  checksum TEXT,
  status TEXT, -- pending, uploaded, queued, processing, processed, failed
  job_id TEXT,
  created_at TEXT,
  updated_at TEXT,
  metadata_json TEXT
);

-- Processed files table (optional; could also be files with container='processed')
CREATE TABLE processed_files (
  id TEXT PRIMARY KEY,
  source_file_id TEXT,
  blob_path TEXT,
  size INTEGER,
  content_type TEXT,
  etag TEXT,
  checksum TEXT,
  job_id TEXT,
  created_at TEXT
);

-- Jobs table for server-side tracking
CREATE TABLE jobs (
  id TEXT PRIMARY KEY,
  file_id TEXT,
  worker_id TEXT,
  status TEXT, -- queued, claimed, processing, success, failed
  attempts INTEGER DEFAULT 0,
  last_error TEXT,
  created_at TEXT,
  updated_at TEXT
);

Indexes: index files(status), jobs(status), files(user_id) for fast lookups.

Detailed flows (mermaid diagrams + descriptions)

1) Direct Upload Flow (Browser → Blob → API)

Notes:

API ensures files row exists with status = pending when issuing SAS. After upload confirmation API validates blob and marks uploaded.
The Queue message triggers worker processing (see next flow).

2) Processing Flow (Queue → AI Worker → Blob → API)

Notes:

Worker must operate idempotently: job_id present ensures retries don't double-insert processed metadata.
Prefer using blob leases to avoid two workers processing same blob concurrently (see housekeeping below).

3) Download Flow (Browser → API → Blob)

Notes:

Download SAS should be short lived and scoped to the single blob path; DRF can embed read permissions only.

Idempotency and integrity

Job IDs & idempotency keys: Generate a job_id at job creation. AI worker includes job_id in any success/failure callback. DRF uses job_id to deduplicate.
ETag / checksum verification: Store the blob ETag and an application-level checksum (e.g., SHA256) for processed outputs. When AI reports completion, DRF can verify the reported checksum/ETag matches the blob metadata via an Azure SDK call.
Blob leases: Before processing, worker can attempt to acquire a short blob lease (client side) to mark exclusive processing. If lease fails, worker should skip and let other workers handle it.
Queue-based claim semantics: Use Storage Queue invisibility timeout or Service Bus peek-lock to ensure single consumer at a time. Worker claims message and must delete it on success.
Retries and DLQ: If a job fails more than N attempts, mark files.status='failed' and move job to DLQ for manual inspection.

API endpoints (conceptual)

Uploads

POST /api/uploads/initiate/ → body: {filename,size,content_type} → response {file_id, upload_path, sas_url}
POST /api/uploads/complete/ → body: {file_id, etag, size} → response 200 (enqueues job)

Files

GET /api/files/ → list metadata
GET /api/files/{id}/ → metadata
POST /api/files/{id}/download/ → returns download SAS

Jobs / Processing

GET /api/jobs/next/ (optional for pull model) → returns {job_id, file_id, read_sas}
POST /api/jobs/{job_id}/claim/ → body {worker_id} → claim job
POST /api/processed/complete/ → body {job_id, source_file_id, processed_blob_path, etag, checksum, size} → validates and updates DB

Admin

POST /api/files/{id}/requeue/ → re-enqueue
POST /api/files/{id}/archive/ → move file to archive

Error handling & recovery

Upload incomplete: If browser fails to call /complete, Event Grid still notifies of blob creation. DRF should periodically reconcile blobs in raw container with DB rows and create missing records or flag anomalies.
DB locked (SQLite): Implement exponential backoff + retry for DB writes. Monitor for locking frequency and consider increasing WAL checkpoint frequency or moving to networked store.
Worker failure mid-process: Rely on queue invisibility timeout for message re-delivery; ensure processed uploads are atomic (upload to temporary path + rename/copy to final path or upload with overwrite=False policies).
Duplicate processed uploads: Use job_id dedupe and processed_files unique constraints on (source_file_id, job_id).

Operational concerns

Backups: Regularly back up the .db3 file to blob storage. Because the DB will be live, perform a consistent backup via sqlite3 VACUUM INTO or sqlite3 .backup() API.
Migration plan: Keep migrations scripts ready to switch to PostgreSQL later; design schema to be compatible with Django ORM migrations.
Monitoring: Track queue depth, worker failure rate, DRF error rates, and storage account metrics (ingress/egress/calls). Use Application Insights for DRF and log SAS issuance with user id and client IP for auditing.
Scaling: When you need scale, move DB to managed Postgres and host API on larger App Service or containerized environment. Workers can be autoscaled in AKS or VMSS.

Security

SAS tokens: Short lifetime (minutes–hours depending on network conditions), scoped to one blob path and minimal permissions (write for upload, read for download).
AI worker identity: Prefer Managed Identity for the AI machine and give it RBAC role Storage Blob Data Reader on raw container and Storage Blob Data Contributor on processed container. If that’s not possible, issue per-job SAS tokens from DRF.
Authentication to DRF: OAuth/JWT tokens for users; API authenticates user and returns SAS tied to user_id.
Auditing: Log SAS issuance and completion events in Application Insights.

Low-level implementation notes (developer-friendly)

Django configuration:
- Use django-environ for secrets.
- Use azure.storage.blob package for SAS generation and verification.
- Use PRAGMA journal_mode=WAL on DB connection startup.
- Limit concurrent Gunicorn workers (or Uvicorn workers) to avoid heavy simultaneous writes.
SAS generation snippet (conceptual)

from azure.storage.blob import generate_blob_sas, BlobSasPermissions
from datetime import datetime, timedelta

def generate_upload_sas(container, blob_name, ttl_minutes=15):
    sas = generate_blob_sas(
        account_name=AZURE_ACCOUNT_NAME,
        container_name=container,
        blob_name=blob_name,
        account_key=AZURE_ACCOUNT_KEY,
        permission=BlobSasPermissions(write=True, create=True),
        expiry=datetime.utcnow() + timedelta(minutes=ttl_minutes)
    )
    return f"https://{AZURE_ACCOUNT_NAME}.blob.core.windows.net/{container}/{blob_name}?{sas}"

SQLite tuning
- On Django startup, execute: PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA busy_timeout=3000;
- Use short transactions (with transaction.atomic():) and keep write sections minimal to reduce lock contention.
- Add a retry helper that catches OperationalError: database is locked and retries with exponential backoff (sleep(0.1 * 2**attempt)) up to ~5 attempts.
- Log every time a retry occurs; if frequency rises, accelerate the migration to a managed database.

AI worker reference implementation (conceptual)

Worker loop outline

import json
import time
from azure.storage.queue import QueueClient
from azure.storage.blob import BlobClient
import requests


class Worker:
  def __init__(self, queue_url, processed_container, api_base, credential):
    self.queue = QueueClient.from_queue_url(queue_url, credential=credential)
    self.processed_container = processed_container
    self.api_base = api_base
    self.credential = credential

  def run_forever(self):
    while True:
      messages = self.queue.receive_messages(messages_per_page=1, visibility_timeout=300)
      for message in messages:
        try:
          payload = json.loads(message.content)
          self.handle_message(payload)
          self.queue.delete_message(message)
        except Exception:
          self.queue.update_message(message, visibility_timeout=30)
          # TODO: log exception and metrics
      time.sleep(1)

  def handle_message(self, payload):
    job_id = payload["job_id"]
    source_sas = payload["read_sas"]
    target_blob = payload["processed_blob_path"]
    blob_client = BlobClient.from_blob_url(source_sas)
    with blob_client.download_blob() as stream:
      processed_bytes = self.process(stream.readall())
    dest_client = BlobClient.from_blob_url(target_blob, credential=self.credential)
    dest_client.upload_blob(processed_bytes, overwrite=True)
    requests.post(f"{self.api_base}/api/processed/complete/", json={
      "job_id": job_id,
      "source_file_id": payload["file_id"],
      "processed_blob_path": target_blob,
      "size": len(processed_bytes),
      "checksum": payload.get("checksum"),
    })

  def process(self, bytes_in):
    # placeholder for AI logic
    return bytes_in

Key practices
- Honor queue invisibility timeout; extend or abandon message if processing exceeds timeout.
- Stream large blobs to disk or chunked memory to avoid holding entire payload in RAM.
- Use managed identity or workload identity for Azure-authenticated blob operations when possible.

Queue message schema

Raw upload job message (JSON)

{
  "job_id": "uuid",
  "file_id": "uuid",
  "blob_container": "raw",
  "blob_path": "raw/user123/2025/11/07/file.ext",
  "read_sas": "https://...",
  "processed_blob_path": "https://.../processed/user123/2025/11/07/file.ext",
  "content_type": "application/octet-stream",
  "expected_checksum": "sha256:...",
  "attempt": 1
}

Design notes
- Include attempt counter so workers can adjust behavior (e.g., bail out after three retries).
- Precompute processed_blob_path in the API to keep naming deterministic.
- If workers use managed identity, omit SAS and have workers build clients with credentials instead.

Testing strategy

Unit tests
- DRF serializers and views: upload initiation/completion, processed callbacks, SAS issuance.
- Retry helpers for SQLite lock handling and blob metadata verification utilities.
Integration tests
- End-to-end scenario with Azurite: upload → queue message → worker simulation → processed callback.
- Duplicate queue message replay to prove idempotency.
Load tests
- k6/Locust script for concurrent upload requests; monitor API latency and lock metrics.
Failure-mode drills
- Simulate worker crash mid-job (do not delete message) and validate redelivery handling.
- Delete processed blob before callback; confirm API rejects inconsistency (409/422) and leaves job for retry.

Deployment & automation

Infrastructure as Code
- Use Bicep/Terraform to define storage account, containers, queues, Event Grid subscriptions, App Service, worker compute, and Key Vault references.
CI/CD
- Pipeline stages: lint/test → build (container or zip deploy) → provision infra → apply migrations → smoke test.
- Capture build artifacts (migration SQL, IaC plan) for audit purposes.
Secrets management
- Store storage keys/connection strings in Key Vault; use managed identity references so secrets are not stored in pipelines.

Migration path to managed database

Preparation

Ensure Django models remain compatible with Postgres (use UUIDField, JSONField, DateTimeField(auto_now=True) as needed).
Abstract DB connection settings so stage environment can run on Postgres before production cutover.

Data export/import

Export SQLite via python manage.py dumpdata or VACUUM INTO.
Import into Postgres using Django loaddata or custom script with psycopg.

Cutover

Schedule maintenance window, deploy configuration pointing to Postgres, run migrations, import seed data, and backfill any blob metadata via reconciliation script.

Post-cutover

Enable automated backups, PITR, and performance tier monitoring.
Update runbooks and remove SQLite-specific retry logic once stable.

Observability & runbook

Metrics
- App Service: request latency, error rate, SQLite lock count (custom log-based metric).
- Storage: queue length, throughput, success/failure operations.
- Worker: processing time percentiles, retry counts, backlog age.
Logging
- Emit structured logs containing file_id, job_id, status_from, status_to, user info.
- Correlate blob operations via client_request_id so diagnostics tie back to metadata updates.
Alerting
- Threshold alerts for queue length, failure rate, SAS issuance errors.
- Synthetic transaction hitting upload-initiate endpoint hourly to detect outages.
Runbook actions
- Manual requeue: POST /api/files/{id}/requeue/.
- Break stale blob lease: az storage blob lease break --lease-break-period 0.
- Metadata reconciliation script to compare blob listing vs DB entries; schedule nightly.

Risks & mitigations

SQLite concurrency saturation → keep DRF as single writer, use WAL, monitor locks, plan Postgres migration.
Poison queue messages → configure poison queue, alert when threshold reached, add dashboard card for investigation.
SAS leakage → issue short-lived SAS, bind to specific blob path, log issuance, consider IP range restrictions.
Worker outage → autoscale workers based on queue length, add manual failover script, maintain backlog SLA.
Unexpected blob costs → keep AI in same region, enable lifecycle policies, compress processed outputs where possible.

Open questions / decisions

Event Grid vs direct queue for triggering processing — recommendation is Event Grid plus DRF reconciliation; final decision pending.
AI worker hosting location and identity model (Azure with managed identity vs external with SAS tokens).
Blob retention / lifecycle policies for raw vs processed containers.
Need for resumable uploads from browser (Azure JS SDK block blobs) and offline resume support.
Authentication provider for DRF (Azure AD, custom JWT, or both) and how that affects SAS scoping.

Next steps

Implement DRF endpoints (uploads/initiate, uploads/complete, processed/complete, files/{id}/download).
Stand up storage resources, Event Grid subscription, and queue; codify via IaC.
Prototype AI worker queue consumer; validate idempotency with duplicate message tests.
Configure monitoring dashboards and alert rules in Application Insights/Azure Monitor.
Draft detailed Postgres migration plan and timeline.

Summary

Direct blob transfers (SAS or managed identity) keep throughput high and costs low, while DRF + SQLite maintain metadata authority with idempotent workflows. Event-driven coordination via queues avoids races, and the mitigation tactics outlined let SQLite survive short term. Prioritize instrumentation, automation, and the managed database migration for long-term resilience.

OperSight System Design

Goals and constraints​

High-level architecture​

Components & responsibilities​

Deployment assumptions and constraints (SQLite-specific)​

Database schema (SQLite .db3)​

Detailed flows (mermaid diagrams + descriptions)​

1) Direct Upload Flow (Browser → Blob → API)​

2) Processing Flow (Queue → AI Worker → Blob → API)​

3) Download Flow (Browser → API → Blob)​

Idempotency and integrity​

API endpoints (conceptual)​

Error handling & recovery​

Operational concerns​

Security​

Low-level implementation notes (developer-friendly)​

AI worker reference implementation (conceptual)​

Queue message schema​

Testing strategy​

Deployment & automation​

Migration path to managed database​

Observability & runbook​

Risks & mitigations​

Open questions / decisions​

Next steps​

Summary​

Goals and constraints

High-level architecture

Components & responsibilities

Deployment assumptions and constraints (SQLite-specific)

Database schema (SQLite `.db3`)

Detailed flows (mermaid diagrams + descriptions)

1) Direct Upload Flow (Browser → Blob → API)

2) Processing Flow (Queue → AI Worker → Blob → API)

3) Download Flow (Browser → API → Blob)

Idempotency and integrity

API endpoints (conceptual)

Error handling & recovery

Operational concerns

Security

Low-level implementation notes (developer-friendly)

AI worker reference implementation (conceptual)

Queue message schema

Testing strategy

Deployment & automation

Migration path to managed database

Observability & runbook

Risks & mitigations

Open questions / decisions

Next steps

Summary