A FastAPI service for extracting Gmail messages and storing them in DuckLake with configurable backend support (DuckDB for development, PostgreSQL for production).
- Gmail API Integration: Extract messages with batch processing and retry logic
- Background Task Processing: Non-blocking API with progress tracking
- DuckLake Storage: Modern data lake storage with DuckDB (dev) or PostgreSQL (prod) metadata catalog
- Configurable Storage: Customizable data paths and metadata schemas
- Docker Support: Containerized deployment with secret management
- REST API: FastAPI-based service with comprehensive error handling
- Type Safety: Full type hints and Pydantic models for robust data validation
-
Setup Environment
cp env.template .env # Edit .env with your Gmail API settings -
Prepare Gmail Token
# Ensure secrets/ directory contains: # - token.json (Gmail API token) # - ducklake_setup.dev.sql (DuckLake configuration for development)
-
Install Dependencies
uv sync
-
Run Server
uv run api-server
Server will start on
http://localhost:8000with DuckDB backend and DuckLake storage.
-
Setup Environment
cp env.template .env # Edit .env with your Gmail API settings -
Prepare Secrets
# Ensure secrets/ directory contains: # - token.json (Gmail API token) # - ducklake_setup.sql (DuckLake configuration for production)
-
Deploy
docker-compose up -d
-
Verify
curl http://localhost:8080/health
curl -X POST "http://localhost:8000/extract" \
-H "Content-Type: application/json" \
-d '{
"query": "from:[email protected]",
"max_results": 1000,
"fetch_config": {
"messages_per_batch": 25,
"response_format": "full",
"max_retry_attempts": 3,
"initial_retry_delay": 1.0
}
}'Response:
{
"task_id": "abc123-def456-ghi789",
"status": "started",
"message": "Extraction task started successfully"
}Note: The API returns immediately with a task ID. Use the status endpoint to track progress.
curl "http://localhost:8000/extract/abc123-def456-ghi789/status"Response:
{
"task_id": "abc123-def456-ghi789",
"status": "running",
"progress": 50,
"message": "Retrieved 500 messages, saving to datalake...",
"started_at": "2025-01-13T10:30:00",
"completed_at": null
}# Server Configuration
SERVER_HOST=0.0.0.0
SERVER_PORT=8000
SERVER_RELOAD=false
SERVER_LOG_LEVEL=INFO
# DuckLake Configuration
DUCKLAKE_SETUP_PATH=/path/to/your/ducklake_setup.sql
# Gmail API Configuration
GMAIL_API_TOKEN_PATH=/path/to/your/gmail/token.json
GMAIL_API_SCOPES='["https://www.googleapis.com/auth/gmail.readonly"]'DuckLake configuration is managed through SQL setup files:
- Development:
secrets/ducklake_setup.dev.sql- Uses local DuckDB for metadata catalog - Production:
secrets/ducklake_setup.sql- Uses PostgreSQL for metadata catalog with S3 storage
The setup files define:
- Metadata catalog location (DuckDB file or PostgreSQL connection)
- Data storage path (local filesystem or S3)
- Schema configuration
- Encryption settings
message-extract/
├── src/gmail_datalake_extractor/ # Main application code
│ ├── api.py # FastAPI endpoints with background tasks
│ ├── auth.py # Gmail API authentication
│ ├── config.py # Configuration management with Pydantic
│ ├── extract/ # Message extraction logic
│ │ └── extract.py # Core extraction and DuckLake integration
│ ├── messages.py # Gmail API message operations
│ ├── models.py # Pydantic data models for API
│ ├── server.py # Server startup and configuration
│ └── sql/ # SQL templates
│ └── attach_ducklake.sql # DuckLake attachment script
├── docs/ # Documentation
│ ├── DEPLOYMENT.md # Docker deployment guide
│ └── API_README.md # API reference
├── secrets/ # Sensitive credentials and configs
│ ├── token.json # Gmail API token
│ ├── ducklake_setup.sql # Production DuckLake config
│ └── ducklake_setup.dev.sql # Development DuckLake config
├── data/ # DuckLake data storage
│ ├── main/messages/ # Parquet files
│ └── messages.duckdb # DuckDB metadata catalog (dev)
├── notebooks/ # Jupyter notebooks for exploration
├── tests/ # Test files
├── docker-compose.yml # Docker orchestration
├── Dockerfile # Container definition
├── pyproject.toml # Python project configuration
└── env.template # Environment configuration template
If you encounter an invalid_grant or RefreshError when using the Gmail API, it means your refresh token has expired or been revoked. This typically happens when:
- The refresh token hasn't been used for 6+ months
- The token was revoked in your Google Account settings
- The OAuth client credentials changed
To fix this, re-authenticate:
-
Using the auth helper script (recommended):
uv run gmail-auth
The script will:
- Extract client credentials from your existing token (if available)
- Open a browser for OAuth authorization
- Generate and save a new token
If you have a separate
credentials.jsonfile from Google Cloud Console:uv run gmail-auth --credentials /path/to/credentials.json
-
Manual re-authentication:
- Delete or rename your current
token.jsonfile - Re-run the OAuth flow using Google's OAuth 2.0 Playground or your own script
- Save the new token to the configured path
- Delete or rename your current
The improved error messages will now provide specific guidance when authentication fails.
- API Documentation - Complete API reference with examples
- Deployment Guide - Docker deployment and configuration
- Architecture Guide - System design and DuckLake integration
Once the server is running, visit:
- Swagger UI: http://localhost:8000/docs
- ReDoc: http://localhost:8000/redoc
- FastAPI: Modern Python web framework with automatic API documentation
- DuckLake: Modern data lake storage with ACID transactions
- DuckDB: High-performance analytical database for metadata catalog
- PostgreSQL: Production metadata catalog backend
- Pydantic: Data validation and settings management
- Gmail API: Google's RESTful API for Gmail access
- Docker: Containerized deployment
- uv: Fast Python package manager