How sahilng/csv-proxy-duckdb works
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.