Terminal-based timesheet reporting for Jortt with DuckDB backend

A Python project that ingests data from the Jortt API into a local DuckDB database using dlt (data load tool) with declarative REST API configuration and a Textual UI.

Features

• Declarative REST API Configuration: Uses dlt's built-in REST API source with minimal custom code
• OAuth 2.0 Authentication: Secure authentication with the Jortt API using access tokens
• Multiple Resources: Ingests projects and project line items from Jortt API
• Local DuckDB Storage: Stores data in a local DuckDB database file
• Automatic Pagination: Built-in pagination handling by dlt
• Semantic Layer: Built-in semantic model with pre-aggregated tables for reporting
• Textual TUI: Rich terminal-based user interface with:
  • Keyboard navigation for weeks and months
  • Toggle between hours and euros metrics

Prerequisites

• Python 3.11 or higher
• uv package manager
• Jortt API credentials (OAuth client ID and secret)

Setup

1. Clone or navigate to the project directory

cd jortt-report

2. Install dependencies

The project uses uv for dependency management:

uv sync

3. Configure environment variables

Edit your .env file and add your credentials:

# Jortt API Configuration (OAuth Client Credentials)
JORTT_CLIENT_ID=your_client_id_here
JORTT_CLIENT_SECRET=your_client_secret_here
JORTT_SCOPES=invoices:read invoices:write

# Optional: manually provide access token (will be fetched automatically if not provided)
# JORTT_ACCESS_TOKEN=your_access_token_here

# Optional: customize database path (default: jortt.duckdb)
DATABASE_PATH=jortt.duckdb

Getting Jortt API Credentials

1. Visit the Jortt Developer Portal
2. Register your application to get your Client ID and Client Secret
3. Add them to your .env file
4. The pipeline will automatically fetch access tokens using the Client Credentials Grant flow
5. Common scopes: invoices:read, invoices:write, customers:read, customers:write

Usage

Get an Access Token (Optional)

If you want to manually fetch an access token to test your credentials:

uv run python -m jortt_report.auth

This will display your access token and token details. However, the pipeline will automatically fetch tokens when needed.

Run the pipeline

uv run python -m jortt_report

The pipeline will:

1. Automatically fetch an access token using your client credentials if needed
2. Extract projects and project line items from the Jortt API with automatic pagination
3. Load the data into a local DuckDB database file (jortt.duckdb by default)
4. Store tables in the raw schema

View Reports

After running the pipeline, you can view your timesheet data using the Textual TUI:

Textual TUI (Terminal-based)

Local development:

uv run jortt-report

Install globally with uvx (recommended):

uvx jortt-report

This launches a rich terminal UI with:

• Async pipeline execution: Press 'r' to refresh data without blocking the UI
• Weekly and monthly reports: Side-by-side timesheet views
• Keyboard navigation:
  • Arrow keys (←/→) to navigate weeks
  • Shift+Arrow keys to navigate months
  • 'm' to toggle between hours and euros
  • 'q' to quit
• Custom theme: Blue (#3399CC) color scheme
• Instant feedback: Notifications appear immediately for pipeline operations

Query the data

You can also query the data directly using DuckDB:

# Open the DuckDB CLI
duckdb jortt.duckdb

# Or use Python
python
>>> import duckdb
>>> conn = duckdb.connect('jortt.duckdb')
>>> conn.execute("SELECT * FROM raw.timesheet LIMIT 5").fetchdf()
>>> conn.execute("SELECT * FROM raw.timesheet_by_week LIMIT 5").fetchdf()

just

Alternatively, install just and use the just command runner.

Project structure

jortt-report/
├── jortt_report/
│   ├── __init__.py
│   ├── __main__.py       # Main entry point - runs pipeline
│   ├── auth.py           # OAuth authentication helper
│   ├── pipeline.py       # DLT pipeline with REST API config
│   ├── datamart.py       # Semantic layer & aggregation tables
│   └── tui.py            # Textual terminal UI
├── tests/                # Test suite
├── .env                  # Environment variables
├── .gitignore
├── justfile              # just command runner
├── pyproject.toml        # Project configuration
└── README.md

How It Works

1. OAuth Authentication: The pipeline uses your Client ID and Secret to obtain an access token via OAuth 2.0 Client Credentials Grant
2. Declarative Configuration: The REST API source is configured using a simple dictionary structure with endpoints, authentication, and pagination settings
3. Data Extraction: dlt's built-in REST API source handles all API calls and pagination automatically
4. Data Loading: DLT loads the data into a local DuckDB file using the replace write disposition
5. Semantic Layer: Creates a unified timesheet view and pre-aggregated tables using boring-semantic-layer
6. Visualization: Textual TUI provides a terminal-based interface to query and navigate the optimized aggregation tables

Authentication Flow

The project supports two authentication methods:

1. Automatic (Recommended): Provide JORTT_CLIENT_ID and JORTT_CLIENT_SECRET in your .env file. The pipeline will automatically fetch access tokens as needed.

2. Manual: Fetch an access token manually and provide it as JORTT_ACCESS_TOKEN in your .env file.

Data Model

The pipeline creates the following tables in the local DuckDB file (in the raw schema):

Source Tables

• projects: Main table containing project data from the Jortt API
• project_line_items: Table containing project line item data
• customers: Customer information
• _dlt_loads: DLT metadata table tracking load operations
• _dlt_pipeline_state: DLT state management table
• _dlt_version: DLT version information

Views

• timesheet: Unified view joining projects and line items with customer info

Aggregation Tables

Pre-computed aggregations for fast reporting:

• timesheet_by_date: Daily aggregations per project (hours, value, count)
• timesheet_by_week: Weekly aggregations per project
• timesheet_by_month: Monthly aggregations per project

Additional nested tables may be created automatically by dlt for nested JSON structures (e.g., projects__customer_record__cc_emails).

All tables and views are accessible with the raw. prefix (e.g., raw.timesheet, raw.timesheet_by_week).

Extending the Pipeline

To add more resources from the Jortt API, simply add them to the resources list in the configuration in pipeline.py:

"resources": [
    {
        "name": "projects",
        "endpoint": {
            "path": "projects",
            "params": {
                "per_page": 100,
            },
        },
    },
    {
        "name": "project_line_items",
        "endpoint": {
            "path": "project_line_items",
            "params": {
                "per_page": 100,
            },
        },
    },
    # Add new resources here
    {
        "name": "invoices",
        "endpoint": {
            "path": "invoices",
            "params": {
                "per_page": 100,
            },
        },
    },
]

That's it! No custom Python code needed. The dlt REST API source handles everything automatically.

Resources

• Jortt API documentation
• DLT documentation
• DLT REST API source documentation
• DuckDB documentation
• Textual
• uv

License

GNU General Public License v3.0