DataBrew 🍺

Turn raw data into strategic foresight.
A multi-tenant B2B analytics platform that ingests messy POS, delivery, and spreadsheet exports, then maps, cleans, and transforms them into clean, client-facing dashboards. Automatically.

🇿🇦 Built for the South African market  ·  💰 ZAR  ·  🧾 15% VAT  ·  🕑 Africa/Johannesburg

---

📖 Table of Contents

• ✨ What is DataBrew?
• 🎬 Feature Tour
  • 📊 Client Portal & Dashboards
  • 🔄 Data Pipeline: Ingest, Map, Publish
  • 🚚 Operations & Delivery
  • 🛠️ System, Admin & Analytics
• ⚡ Tech Stack
• 🏗️ Architecture
• 🚀 Quick Start (Local)
• 🧪 Quality & Tests
• 🔌 REST API
• 📁 Project Structure
• 🔒 Security
• 🐳 Deployment

---

✨ What is DataBrew?

Multi-branch businesses (restaurant groups, franchises, distributors) drown in data fragments. Every POS system, delivery app, and location exports its own messy CSV/Excel/PDF with its own product names and column layouts. DataBrew unifies all of it.

It ingests those raw exports, fuzzy-matches every product name to a canonical master catalog, runs the data through a three-stage ETL pipeline, and serves clean, branded, self-service dashboards to each client, with full multi-tenant isolation, role-based access, and a REST API.

  📥 Raw POS/delivery exports   ->   🧠 ML product mapping   ->   📊 Clean client dashboards

🏢 Multi-tenant	Every row scoped to a tenant; middleware + ORM-level isolation prevents cross-tenant leaks
🧠 ML auto-mapping	Seed-token + fuzzy matching maps raw product names to a master catalog with manual override
⚙️ 3-stage ETL	RawRecord -> StagingRecord -> AnalyticsRecord, materialized into a star schema
📊 Self-service BI	Branded client portal with KPIs, trends, maps, drill-downs, and exports
👥 6 roles	Superadmin, data engineer, mapper, delivery manager, client admin, client viewer
🔌 REST API	Tenant-scoped, throttled DRF endpoints for programmatic access

---

🎬 Feature Tour

📊 Client Portal & Dashboards

Each client logs into their own branded portal (logo, colours, tagline) and gets a self-service analytics workspace. No spreadsheets required.

Dashboard, Overview tab: headline KPIs (revenue, units, margin, avg price), a revenue trend chart, top products, and a revenue-by-category breakdown.

📍 Locations Interactive Leaflet map of every branch with per-store revenue & volume.	🗂️ Data Sources Every feed powering a tenant, with freshness and quality at a glance.
🗂️ Catalog Browse the product hierarchy and categories per tenant.	📤 Exports Saved export templates and downloadable reports (incl. PDF with tenant logo).

---

🔄 Data Pipeline: Ingest, Map, Publish

The engine room. Raw files become trustworthy analytics through a reviewable, reversible pipeline.

📥 Imports. Upload CSV/Excel/PDF, auto-detect columns, map them to standard fields, review duplicates & quality, then approve. SHA-256 hashing catches duplicate files, and a quality score grades each import.

🧩 Mapping. Every raw product name is matched to a canonical MasterProduct. The mapping workspace shows coverage per source, pending suggestions, and unmapped items. Mapping is seed-token based, so "Castle Lager 340ml" mapped once covers every branch and every future import.

⚡ Speed Mapper. A keyboard-first triage UI for blitzing through unmapped products: approve, reject, or skip without touching the mouse, with live progress and an undo trail.

---

🚚 Operations & Delivery

The internal cockpit for the team running data delivery across all clients.

Delivery Dashboard. A 6-month grid of which client received data when, freshness indicators, recent activity, and an at-a-glance data-quality overview.

👥 Clients Every tenant, paginated, with status & source counts.

🗓️ Delivery Overview Per-source mapping stats and refresh controls.

♻️ Analytics refresh runs as a tracked async task with six phases (starting -> deleting -> building -> curating -> snapshots -> finalizing) and live progress polling.

---

🛠️ System, Admin & Analytics

🧠 ML Model Stats. A live view of the fuzzy-matching engine: the matching pipeline (barcode -> cross-tenant -> fuzzy name -> size/unit -> brand -> score), signal weights, engine configuration, and learning/acceptance metrics.

🗄️ Database Schema Explorer. Live introspection of every model across all apps, with row counts, relationships, and clickable foreign keys. (42 tables · 94 relationships in the demo dataset.)

📈 System Analytics. Platform-wide traffic, import volume, mapping coverage, and client breakdown.

👤 Users & Roles	⚙️ System Settings	📝 Audit Log
🔔 Notifications	🔐 Secure Login	⚖️ Legal / Docs

---

⚡ Tech Stack

Layer	Technology
🐍 Backend	Django 5.1.4, Python 3.12, Django REST Framework
🐘 Database	PostgreSQL 16 (star schema for analytics)
🔴 Cache / Queue	Redis 7 (with DB-cache fallback)
🖥️ Frontend	HTMX, Alpine.js, Chart.js, Leaflet (server-rendered, no SPA build step)
📦 Data	pandas, numpy, openpyxl, pdfplumber, RapidFuzz (fuzzy matching)
🔐 Security	Argon2, django-axes (brute-force lockout), PyJWT
🐳 Deployment	Docker, Docker Compose, Nginx, Gunicorn, WhiteNoise, Let's Encrypt
📡 Monitoring	Sentry, custom traffic analytics

💡 All front-end libraries are vendored locally under static/, so there is no runtime CDN dependency: dashboards render reliably offline and aren't subject to CDN or SRI drift.

---

🏗️ Architecture

🔄 Data Flow

📥 CSV / XLSX / PDF upload
        |
   📋 RawRecord ............ raw rows stored as-is, file SHA-256 hashed
        |
   🔗 ColumnMap ............ maps raw columns to standard fields
        |
   ✅ StagingRecord ........ cleaned, validated, VAT-resolved
        |
   🧠 MappingRule + ML ..... fuzzy / seed-token match to MasterProduct catalog
        |
   📊 AnalyticsRecord ...... fully mapped, analytics-ready
        |
   ⭐ FactSale + Dimensions  star schema powering every dashboard

🏢 Multi-Tenancy

Every model carries a tenant ForeignKey. TenantMiddleware scopes each request and TenantManager guarantees queries never leak across tenants, enforced at the middleware, view, and ORM layers.

⭐ Star Schema

Table	Role
FactSale	📊 Central fact, one row per product × store × date × source
DimDate	📅 Date dimension: day, week, month, quarter, year
DimProduct	🏷️ Product dimension, linked to the MasterProduct catalog
DimStore	📍 Store/location dimension, linked to MappedLocation

---

🚀 Quick Start (Local)

Prerequisites: Python 3.12, PostgreSQL 16, and libmagic (file-type detection: brew install libmagic on macOS, apt install libmagic1 on Debian/Ubuntu). Redis is optional; it falls back to the database cache.

# 1. Clone & create a virtualenv
git clone https://github.com/Nevvyboi/DataBrew.git
cd DataBrew
python3.12 -m venv venv && source venv/bin/activate    # Windows: venv\Scripts\activate
pip install -r requirements.txt

# 2. Create the database + an owning user
createuser databrew_user --pwprompt              # set a password when prompted
createdb -O databrew_user databrew               # -O makes the new user the DB owner

# 3. Configure environment
cp .env.example .env
#  - set DB_USER / DB_PASSWORD to match step 2
#  - generate a SECRET_KEY:  python -c "import secrets; print(secrets.token_urlsafe(50))"
#  - leave REDIS_URL blank to use the database cache fallback (no Redis needed)

# 4. Migrate & seed a full demo tenant (~56k sales rows, 67 products, 8 locations)
python manage.py migrate
python manage.py createcachetable          # backs the DB cache when REDIS_URL is blank
python manage.py generate_sample_data

# 5. Run
python manage.py runserver

Visit http://127.0.0.1:8000 and sign in to the seeded demo tenant:

Role	Email	Password
👤 Client Admin	[email protected]	demo1234

Create an internal superadmin to explore the operations, mapping, and system areas:

python manage.py createsuperuser

🧰 Set DEBUG_TOOLBAR=0 in your environment to hide the Django Debug Toolbar (e.g. for clean screenshots).

---

🧪 Quality & Tests

python manage.py test     # tenant isolation, ML matching, VAT, ETL, API
ruff check .              # lint (config in ruff.toml)

The test suite focuses on the load-bearing logic: multi-tenant data isolation (the ORM TenantManager plus the DRF API), the fuzzy product-matching engine, VAT breakdowns, seed-token generation, the ETL transitions, and the role-permission matrix. Every push and PR runs lint, a migrations check, and the full suite with coverage via GitHub Actions.

---

🔌 REST API

Tenant-scoped, authenticated, and rate-throttled (Django REST Framework). All endpoints live under /api/v1/.

Method	Endpoint	Description
GET	/api/v1/snapshots/	Pre-computed period snapshots (revenue/volume/cost)
GET	/api/v1/products/	Master product catalog (filter by brand)
GET	/api/v1/locations/	Mapped store locations
GET	/api/v1/sources/	Data sources
GET	/api/v1/kpis/?start=YYYY-MM-DD&end=YYYY-MM-DD	Pre-computed KPIs for a date range

Auth: IsAuthenticated + IsTenantMember (responses are automatically filtered to the caller's tenant).

---

📁 Project Structure

DataBrew/
├── apps/
│   ├── accounts/       👤 Users, Tenants, Roles, audit logging, onboarding wizard
│   ├── ingestion/      📥 DataSource, ImportJob, RawRecord, ColumnMap (upload & parse)
│   ├── mapping/        🧩 MasterProduct catalog, MappingRule, ML matching, locations
│   ├── pipeline/       ⚙️ StagingRecord, AnalyticsRecord, ImportSnapshot (ETL)
│   ├── dashboards/     📊 Star schema, client portal, KPIs, shared dashboards
│   ├── delivery/       🚚 Operations dashboard, async RefreshTask worker
│   ├── notifications/  🔔 AlertRule, Notification, dispatch engine
│   ├── operations/     🛠️ System settings, schema viewer, ML stats, health
│   ├── analytics/      📈 Traffic & performance analytics
│   ├── api/            🔌 REST API (DRF): serializers, permissions, throttling
│   ├── sharing/        🤝 Cross-tenant shared data-source access
│   └── core/           🧱 Shared utilities: image serving, middleware, template tags
├── config/             ⚙️ Settings (base/development/production), URLs, WSGI
├── templates/          🎨 Server-rendered HTML (HTMX partials, email templates)
├── static/             🖼️ CSS, JS (vendored libs), brand assets, screenshots
├── locale/             🌍 Translations (French, Italian)
├── nginx/              🌐 Reverse proxy: SSL, rate limiting, security headers
├── scripts/            🚀 deploy.sh · update.sh · backup.sh
├── sample_data/        📋 Demo CSVs for testing imports
├── Dockerfile          🐳 Multi-stage production image
└── docker-compose*.yml 🐳 Local & production stacks

---

🔒 Security

Feature	Detail
🔐 Passwords	Argon2 hashing, 10+ char minimum, common-password rejection
🚫 Brute force	5 attempts then a 1-hour lockout (django-axes), plus a login honeypot
🕵️ Enumeration	Constant-time password check prevents email enumeration
🛡️ CSRF	SameSite=Strict cookies, token on every form
🔒 HTTPS	SSL-only with HSTS (1 year) in production
📎 File uploads	MIME type + extension + magic-byte validation
🏢 Tenant isolation	Enforced at the middleware, view, and ORM layers
📝 Audit trail	Every login, logout, password change, and import is logged

---

🐳 Deployment

bash scripts/deploy.sh    # first-time: prompts for domain/email, builds images,
                          #   runs migrations, configures SSL (Let's Encrypt)
bash scripts/update.sh    # pull, rebuild, migrate, zero-downtime restart

All containers run restart: always behind Nginx + Gunicorn, so a server reboot brings the whole stack back online automatically.

---

📄 License

Released under the MIT License, free to use, modify, and build on, with attribution.

---

_{Built with ☕ and 🔥 in South Africa}