hottea

Generate SQL INSERT statements for Forgejo heatmap injection.

Setup

Prerequisites

• Python 3.10+
• Git
• A Forgejo instance with API access
• A Forgejo API token

Install

pip install hottea

Environment Variables

Variable	Description
HOTTEA_FORGEJO_URL	Forgejo API base URL (e.g., https://git.example.com)
HOTTEA_FORGEJO_TOKEN	Forgejo API authentication token
HOTTEA_FORGEJO_USERNAME	Target Forgejo username (repo owner)
HOTTEA_FORGEJO_REPO	Target repository name

Usage

Quick Start

# 1. Set your environment variables
export HOTTEA_FORGEJO_URL=https://git.example.com
export HOTTEA_FORGEJO_TOKEN=your_token_here
export HOTTEA_FORGEJO_USERNAME=your_username
export HOTTEA_FORGEJO_REPO=your_repo

# 2. Navigate to a git repository
cd ~/projects/my-project

# 3. Preview what would be generated
hottea --dry-run

# 4. Generate SQL for all commits by the configured git user
hottea --output heatmap.sql

# 5. Apply the SQL to your Forgejo database
sqlite3 /path/to/forgejo.db < heatmap.sql

Typical Workflow

1. First run — use --dry-run to verify the output
2. Generate — use --output to save the SQL file
3. Inspect — review the generated SQL manually if desired
4. Back up — stop Forgejo and copy your database
5. Apply — use sqlite3 to run the SQL
6. Verify — check the action table for expected heatmap records
7. Subsequent runs — use --end-date to avoid duplicates

Avoiding Duplicates

On your first run, note the date of the latest commit included:

hottea --end-date 2024-12-31 --output heatmap.sql

On subsequent runs, use the same end-date to only include new commits up to that cutoff. This prevents duplicate heatmap entries.

Command-Line Flags

Flag	Description
--end-date YYYY-MM-DD	Only include commits on or before this date
--output FILE / -o	Write SQL to file instead of stdout
--dry-run	Preview generated SQL without API calls or writes
--verbose / -v	Enable detailed debug logging
--help	Show full help with examples and troubleshooting

Database Instructions

Applying the SQL

1. Stop your Forgejo instance to prevent writes during migration

2. Back up your database:

   cp /path/to/forgejo.db /path/to/forgejo.db.bak
   

3. Apply the generated SQL:

   sqlite3 /path/to/forgejo.db < output.sql
   

4. Restart Forgejo and verify the heatmap

Verifying Data Integrity

After applying the SQL, verify the records were inserted correctly:

# Count records added for your user and repo
sqlite3 /path/to/forgejo.db "SELECT COUNT(*) FROM action WHERE user_id=0;"

# View the most recent records
sqlite3 /path/to/forgejo.db "SELECT * FROM action ORDER BY id DESC LIMIT 10;"

The user_id column will be 0 for public repositories or your user ID for private repositories (actor-scoped visibility).

Rollback

If something goes wrong:

# Stop Forgejo
# Restore the backup
cp /path/to/forgejo.db.bak /path/to/forgejo.db
# Restart Forgejo

Always test on a non-production instance first.

Performance Considerations

• Batch size: The generated SQL wraps all inserts in a single transaction (BEGIN/COMMIT), which is efficient for SQLite3
• Large repositories: For repos with 10,000+ matching commits, the SQL file may be large. SQLite3 handles this well, but ensure sufficient disk space
• Indexing: The action table in Forgejo is indexed on user_id and created_unix. No additional indexing is needed
• Frequency: Running weekly or monthly is sufficient for most heatmap use cases. Daily runs are unnecessary

Future Database Support

Extensible Architecture

The SQL generator uses a class-based architecture for multi-database support:

SqlGenerator (ABC)
├── SQLiteGenerator   ← current default
├── MySQLGenerator    ← planned
└── PostgreSQLGenerator  ← planned

Each backend implements begin_transaction(), commit_transaction(), and inherits generate_insert() and generate_sql() from the base class. Adding a new database requires creating a new subclass.

MySQL/MariaDB Migration Path

1. Use MySQLGenerator instead of SQLiteGenerator
2. MySQL uses START TRANSACTION instead of BEGIN TRANSACTION
3. The action table schema is identical
4. No SQL syntax changes needed for INSERT statements

PostgreSQL Support Roadmap

1. Use a PostgreSQLGenerator (not yet implemented)
2. PostgreSQL uses BEGIN / COMMIT (same as SQLite3)
3. Future consideration: UUID primary keys, SERIAL vs AUTOINCREMENT
4. Requires separate connection logic for direct injection mode

Phasing Out SQLite3

When migrating to MariaDB in production:

1. Export existing data from SQLite3
2. Import into MariaDB with schema adjustments
3. Switch to MySQLGenerator for new SQL generation
4. Keep SQLite3 support for development and testing

Troubleshooting

Problem	Solution
"Git executable not found"	Install git and ensure it's on PATH
"Not a git repository"	Run from inside a git repository
Authentication failures	Verify HOTTEA_FORGEJO_TOKEN is valid and has API access
Empty output	Check HOTTEA_FORGEJO_USERNAME matches the repo owner
Duplicate records	Use --end-date to exclude previously inserted commits
Unexpected results	Run with --verbose to see detailed debug output