# conorwarrilow/flask_micro_datawarehouse architecture

> Architecture diagram of the GitHub repository conorwarrilow/flask_micro_datawarehouse, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-06-19). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/conorwarrilow/flask_micro_datawarehouse
- Repository: https://github.com/conorwarrilow/flask_micro_datawarehouse

## Overview

##### 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

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Browser
    Browser["Web Browser\nHTML/CSS/JS\nCodeMirror\nAJAX"]:::frontend

    %% Flask Application Box
    subgraph "Flask Application" 
        direction TB
        AppFactory["Application Factory\n(powerpy/__init__.py)"]:::backend
        Config["Configuration\n(powerpy/config.py)"]:::backend
        Entry["Entrypoint\n(run.py)"]:::backend

        subgraph "Authentication Module"
            UsersBP["Users Blueprint"]:::backend
        end

        subgraph "Query Engine Module"
            DatabasesBP["Databases Blueprint"]:::backend
            WorksheetsBP["Worksheets Blueprint"]:::backend
        end

        subgraph "Dashboard & Analytics Module"
            DashboardsBP["Dashboards Blueprint"]:::backend
        end

        subgraph "Error Handling Module"
            ErrorsBP["Error Handling Blueprint"]:::backend
        end

        Models["SQLAlchemy Models\n(powerpy/models.py)"]:::backend
        SQLUtils["DuckDB Utilities\n(powerpy/sqlutils.py)"]:::backend
        Utils["General Utilities\n(powerpy/utils.py)"]:::backend
    end

    %% Data Stores
    DuckDBStore[(DuckDB File Store)]:::datastore
    SQLiteDB[(SQLite User DB)]:::datastore

    %% External Service
    SMTP["SMTP/Email Service"]:::external

    %% Connections
    Browser -->|HTTP/HTTPS| Entry
    Entry -->|loads| AppFactory
    AppFactory -->|uses| Config
    AppFactory -->|registers| UsersBP
    AppFactory -->|registers| DatabasesBP
    AppFactory -->|registers| WorksheetsBP
    AppFactory -->|registers| DashboardsBP
    AppFactory -->|registers| ErrorsBP

    UsersBP -->|SQLAlchemy ORM| SQLiteDB
    DatabasesBP -->|Execute SQL| DuckDBStore
    WorksheetsBP -->|Execute SQL| DuckDBStore
    DashboardsBP -->|SQLAlchemy ORM| SQLiteDB

    DatabasesBP -->|calls| SQLUtils
    WorksheetsBP -->|calls| SQLUtils
    UsersBP -->|calls| Utils
    DashboardsBP -->|calls| Utils
    ErrorsBP -->|calls| Utils

    DuckDBStore -->|Read/Write .duckdb| SQLUtils
    Models -->|defines| SQLiteDB

    UsersBP -->|send email| SMTP

    Browser -->|AJAX autosave| WorksheetsBP
    Browser -->|REST API| DatabasesBP
    Browser -->|REST API| DashboardsBP
    Browser -->|form submit| UsersBP

    %% Click Events
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/tree/main/powerpy/templates/"
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/tree/main/powerpy/static/"
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/static/js/sqlcode2.js"
    click Entry "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/run.py"
    click AppFactory "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/__init__.py"
    click Config "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/config.py"
    click UsersBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/users/routes.py"
    click UsersBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/users/forms.py"
    click UsersBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/users/utils.py"
    click DatabasesBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/databases/routes.py"
    click DatabasesBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/databases/forms.py"
    click DatabasesBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/databases/utils.py"
    click WorksheetsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/worksheets/routes.py"
    click WorksheetsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/worksheets/forms.py"
    click WorksheetsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/worksheets/utils.py"
    click DashboardsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/dashboards/routes.py"
    click DashboardsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/dashboards/forms.py"
    click DashboardsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/dashboards/utils.py"
    click ErrorsBP "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/errors/handlers.py"
    click Models "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/models.py"
    click SQLUtils "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/sqlutils.py"
    click Utils "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/utils.py"
    click DuckDBStore "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/sqlutils.py"
    click SQLiteDB "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/models.py"
    click Config "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/config.py"
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/static/base.css"
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/static/js/utils.js"
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/static/js/filetree.js"
    click Browser "https://github.com/conorwarrilow/flask_micro_datawarehouse/blob/main/powerpy/static/js/worksheettree.js"

    %% Styles
    classDef frontend fill:#ADD8E6,stroke:#000
    classDef backend fill:#90EE90,stroke:#000
    classDef datastore fill:#FFA500,stroke:#000
    classDef external fill:#D3D3D3,stroke:#000
```
