How conorwarrilow/flask_micro_datawarehouse works
Project Type & Purpose
• This is a full-stack, monolithic web application (“Flask Micro Data Warehouse”) that provides a lightweight data-warehouse experience using DuckDB for analytical queries and SQLite for user/auth data.
• The front end is vanilla HTML/CSS/JS (with CodeMirror for the query editor). The back end is a Flask app broken into blueprints (dashboards, databases, users, posts, worksheets, etc.). DuckDB files hold user databases/schemas/tables; SQLite (via SQLAlchemy) holds user accounts, query history, and action logs.
Main Components
a. Frontend UI
• HTML templates (Jinja2) under powerpy/templates/
• Static assets under powerpy/static/ (CSS, JS, images)
• CodeMirror instance for SQL worksheets
b. Flask Backend
• run.py / application factory in powerpy/__init__.py
• Blueprint modules (dashboards, databases, users, posts, worksheets, errors, main)
• Controllers (routes.py), forms (WTForms), utils/helpers
c. Storage & Engines
• DuckDB (.duckdb files) for user‐created databases, schemas, tables, queries
• SQLite via SQLAlchemy ORM for user accounts, authentication, query logs, action audit
d. External Services
• SMTP/email server (for verification & password reset)
• Optional encryption/key‐management library for at-rest encryption
• (Future: S3/Blob stores, Snowflake, external data sources)
Relationships & Data Flows
1. Browser ▶ (HTTP/HTTPS) ▶ Flask front-controller
• Requests hit run.py → application context → blueprint route
2. Flask Route ▶ Controller Logic
• Validate form/input, call service/utils
• Authentication middleware checks user session
3. Controller ▶ Database Layer
• If metadata/auth → SQLAlchemy → SQLite
• If analytical query or schema manipulation → DuckDB Python driver
4. DuckDB Engine ↔ .duckdb files on disk
5. SQLite Engine ↔ .db or in-memory file
6. Flask ▶ Response
• Render Jinja2 template OR return JSON (for AJAX calls)
7. Browser ← Response (HTML/JSON/CSS/JS)
8. For email: Flask Mail util ▶ SMTP server
Architectural Patterns & Design Principles
• MVC / MVT:
• Models: SQLAlchemy models + DuckDB schema abstractions in powerpy/models.py & sqlutils.py
• Views: Jinja2 templates + JS front end
• Controllers: Flask blueprints (routes.py in each subpackage)
• Blueprint-based modularization
• Adjacency-list model for worksheet/file hierarchy stored in SQLite
• Separation of concerns: static assets vs. templates vs. logic vs. config
• Single-node monolith with clear module boundaries