# sahilng/csv-proxy-duckdb architecture

> Architecture diagram of the GitHub repository sahilng/csv-proxy-duckdb, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-03-31). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/sahilng/csv-proxy-duckdb
- Repository: https://github.com/sahilng/csv-proxy-duckdb

## Overview

##### Project type and purpose
- Project Type: A micro web service/proxy.
- Purpose: To serve dynamically generated CSV files based on DuckDB queries via HTTP. It acts as an intermediary to facilitate live data delivery to mapping or visualization tools, with a simple authentication mechanism using a token.

##### File structure
- The project is simple with only a few key files:
- app.py: Contains the core Flask application including endpoints, routing, and the logic for querying DuckDB and generating CSV output.
- Dockerfile: Indicates that the application is containerized for deployment.
- requirements.txt: Lists Python dependencies.
- .gitignore and README.md are for project maintenance and documentation.
- This minimal structure tells us that almost all application logic sits inside app.py without an explicit separation into modules. However, the README hints at internal components (e.g., caching, logging, query handling).

##### Main architectural components
a. Web Server (Flask Application):
- Runs via app.py and listens for HTTP requests.
- Responsible for receiving requests, parsing URL parameters (database, schema, view), and authentication (via the motherduck_token query parameter).

b. DuckDB Integration:
- The service dynamically queries a local (or remote, if applicable) DuckDB instance based on the incoming request.
- It retrieves query results which are then formatted as CSV.

c. Caching Component:
- Uses a cache directory (default "cache") to store CSV files.
- The caching logic checks for file modification times against a configurable CACHE_MINUTES setting.

d. Logging:
- The application logs activities to a specified log file (default "app.log") and outputs logs at a level defined by the environment variable LOG_LEVEL.

e. Configuration Layer:
- Uses environment variables (like DB_PATH, CACHE_DIR, CACHE_MINUTES, PORT, etc.) to configure application behavior.

f. Authentication:
- Optionally authenticates requests using the motherduck_token, ensuring that secure access is enforced when querying MotherDuck.

g. Deployment Container (Docker):
- Dockerfile indicates containerization for streamlined deployment, offering an isolated and consistent environment.

##### Relationships and Interactions
- Incoming Client Request:
- A client sends an HTTP request (for example, http://localhost:3500/local/main/test.csv).
- An optional query parameter (motherduck_token) may be present.

- Request Routing:
- Flask routes the incoming URL to the appropriate endpoint in app.py.

- Query Processing:
- The Flask endpoint invokes query logic that connects to DuckDB (using the DB_PATH).
- It checks if a cached CSV exists and is still valid; if not, it executes the query.
- The obtained result is formatted as CSV.

- Caching Mechanism:
- The CSV file is stored in the designated CACHE_DIR.
- It uses file modification timestamps to decide whether to serve the cached version or to recompute the CSV.

- Response Delivery:
- The CSV content is served as an HTTP response, making it accessible to clients and allowing live data ingestion into other tools.

- Logging:
- All operations are logged to LOG_FILE for debugging and monitoring.

- Every component has a clear label.
- Configuration options, either as a separate "Configuration" node or annotations on the appropriate components.
- “Docker” container to show how deployment is handled.
- Be detailed: even if the project is simple, break it down into its core elements and their interactions.

## Diagram (Mermaid)

```mermaid
flowchart TD
    Client["Client"]:::client

    subgraph "Deployment Container (Docker)"
        Docker["Docker Container"]:::docker
        FW["Flask Web Server (app.py)"]:::flask
        Docker -->|"hosts"| FW
    end

    FW -->|"authenticates"| Auth["Authentication (Token Validation)"]:::auth
    FW -->|"queries"| DuckDB["DuckDB Integration & Query Processing"]:::db
    FW -->|"caches"| Cache["Caching Mechanism (CSV Storage)"]:::cache
    FW -->|"logs"| Log["Logging Module (app.log)"]:::log
    FW -->|"reads"| Config["Configuration Layer (Env Vars)"]:::config

    Client -->|"HTTP Request"| Docker

    click Docker "https://github.com/sahilng/csv-proxy-duckdb/tree/main/Dockerfile"
    click FW "https://github.com/sahilng/csv-proxy-duckdb/blob/main/app.py"
    click Auth "https://github.com/sahilng/csv-proxy-duckdb/blob/main/app.py"
    click DuckDB "https://github.com/sahilng/csv-proxy-duckdb/blob/main/app.py"
    click Cache "https://github.com/sahilng/csv-proxy-duckdb/blob/main/app.py"
    click Log "https://github.com/sahilng/csv-proxy-duckdb/blob/main/app.py"
    click Config "https://github.com/sahilng/csv-proxy-duckdb/blob/main/app.py"

    classDef client fill:#f9e79f,stroke:#b9770e,stroke-width:2px
    classDef flask fill:#85c1e9,stroke:#2e86c1,stroke-width:2px
    classDef auth fill:#f7dc6f,stroke:#f1c40f,stroke-width:2px
    classDef db fill:#82e0aa,stroke:#28b463,stroke-width:2px
    classDef cache fill:#aed6f1,stroke:#3498db,stroke-width:2px
    classDef log fill:#f5cba7,stroke:#d35400,stroke-width:2px
    classDef config fill:#d2b4de,stroke:#8e44ad,stroke-width:2px
    classDef docker fill:#fadbd8,stroke:#e74c3c,stroke-width:2px
```
