Database CLI Commands

Overview

The Database CLI provides commands for managing the SQLite database used by degenbot to store pool metadata, liquidity positions, Aave market data, and other blockchain-derived information. Commands are available for creating, backing up, compacting, inspecting, healing, and resetting the database. The argv vocabulary is Rust-owned (ADR-051) — see the Rust CLI page.

Background: Database Architecture

SQLite with Write-Ahead Logging (WAL)

Degenbot uses SQLite with WAL mode for improved concurrency and performance:

• WAL mode: Allows simultaneous reads and writes

• Journal file: Stores changes before committing to main database

• Auto vacuum: Enabled to reclaim free space automatically

• Checkpointing: WAL checkpoint performed before backups for consistency

Database Schema

The database contains multiple tables organized by domain:

• ERC20 tokens: erc20_tokens - Token metadata for all tracked assets

• Pools: pools plus the family-specific V2/V3/V4 tables for pool metadata

• Liquidity positions: liquidity_positions and managed-pool position tables for tick-level data

• Initialization maps: initialization_maps for tick bitmaps

• Aave V3: aave_v3_markets, aave_v3_assets, aave_v3_users, position tables, and aave_v3_contracts

• Exchanges: exchanges for tracking active DEX deployments

• Pool managers: pool_managers for Uniswap V4 pool managers

The Rust degenbot-db crate owns the schema and its typed row representations. Python callers use the stable mirror in src/degenbot/db/; there is no Python ORM or session layer.

Schema ownership (Rust-owned)

The database schema is Rust-owned (ADR-052). The current schema revision is stamped in _degenbot_db_schema_version; a legacy alembic_version table marks the file as Alembic-era, and ensure_schema heals it at open — an out-of-place rebuild to the current Rust SCHEMA_HEAD, preserving the old file as a *.bak. There is no in-tree migration-script directory and no step the user must apply by hand. See ADR-052 for the forward version-lock and the heal-at-open contract.

Commands

The command vocabulary is Rust-owned: degenbot-cli declares it over degenbot-cli-core’s database arms. The authoritative flag/exit-code reference is the Rust CLI page; the domain behaviour below is unchanged.

degenbot database backup

Back up the database to a .bak file.

degenbot database backup

Behavior

1. Checkpoint WAL: Performs a full WAL checkpoint to ensure data consistency

2. Create backup: Copies database to [database_path].bak

3. Error handling: Raises BackupExists if backup file already exists

4. Confirmation: Prompts to overwrite existing backup if found

Example Usage

degenbot database backup

Backup File Location

The backup file is created in the same directory as the database with a .bak suffix:

• Database: /path/to/database.db

• Backup: /path/to/database.db.bak

degenbot database reset

Remove and recreate the database with an empty schema.

degenbot database reset

Behavior

1. Confirmation: Prompts user to confirm deletion

2. Remove database: Deletes the existing database file

3. Create new database: Initializes with current schema

4. Configure SQLite: Sets WAL mode, auto vacuum, and creates all tables

5. Stamp schema version: Writes _degenbot_db_schema_version at the Rust SCHEMA_HEAD

6. Initial vacuum: Performs VACUUM to optimize storage

Example Usage

degenbot database reset

degenbot database upgrade (RETIRED)

The subcommand is retired (ADR-052 D4): the database upgrades itself at open. The Rust console still accepts the argv, but renders a pointed error and exits non-zero:

the database upgrades itself at open; for an explicit repair, run `degenbot database heal`

Use degenbot database heal for an explicit out-of-place repair, or degenbot database inspect to read the schema state without writing.

degenbot database compact

Compact the database to reclaim free space.

degenbot database compact

Behavior

1. Connect to database: Opens SQLite connection

2. Run VACUUM: Rebuilds database file, removing free space and defragmenting

3. Log completion: Records compaction completion in logs

Example Usage

degenbot database compact

When to Use

Use after large deletions, before backups, or when database has grown significantly with many deletions.

Database Initialization

When a new database is created (via reset or programmatically), the Rust degenbot-db core performs the same steps the Python path once did, with no Alembic stamp:

1. Create/connect the SQLite file.

2. Enable WAL journal mode for concurrent reads/writes.

3. Enable auto_vacuum=FULL to reclaim space.

4. Create every table at the current Rust SCHEMA_HEAD.

5. VACUUM once for a compact initial file.

6. Stamp _degenbot_db_schema_version with SCHEMA_HEAD.

The stable Python mirror in src/degenbot/db/__init__.py delegates to the same Rust ops over the degenbot._ffi.db_* seam.

Database Schema Changes

Forward version-lock (Rust-owned)

Schema changes are Rust-owned (ADR-052 D2). A binary whose RUST_SCHEMA_VERSION is ahead of the file’s stamp applies the pending embedded ALTER steps strictly in order at open (each step in its own transaction; a failed step rolls back to the last-good stamp and refuses loudly). A file stamped newer than the running binary is refused (schema N > binary M), so an old reader never silently misreads a new database. There is no Alembic revision to generate and no user-applied migration step.

Configuration

The database path is configured via settings:

# From degenbot.config.settings
settings.database.path  # pathlib.Path to database file

Default database location depends on the platform and configuration.

Error Handling

BackupExists: Raised when backup file already exists. User can choose to overwrite or abort.

Schema newer than the binary: The open refuses with “the binary is older than the database (schema N > binary M)” — upgrade the binary, never the file. A stale Alembic-era file is healed at open instead (ADR-052 D1).

Related Functions

Database operations and typed reads

The stable Python mirror in src/degenbot/db/__init__.py is a thin delegation to the Rust degenbot-db operations:

• db_backup_database(src, dst) - Create an online backup of a database

• db_compact_database(path) - Reclaim free space with VACUUM

• db_heal_database(path) - Perform an out-of-place dump-and-restore rebuild (ADR-011)

• db_inspect_schema_state(path) - Inspect schema ownership without writing

Database creation has no Python mirror entry: the Rust degenbot-db core materialises the parent directory, the file, and the schema on first open, so an operator points the bot at the resolved settings.database.path (see Configuration) and reads the result with db_inspect_schema_state(path) or degenbot database inspect. There is no Python function to call to create the file.

For a typed read, pass the database path and chain-scoped key directly to the mirror. The result is a Rust-backed row, not an ORM instance:

from degenbot.db import db_fetch_pool_row, db_inspect_schema_state

schema_state = db_inspect_schema_state(database_path)
pool_row = db_fetch_pool_row(database_path, chain_id, pool_address)

There is no bot.db() session API. The database schema upgrades itself at open, and the degenbot.db mirror is the only supported Python database interface.

Dependencies

• Database: SQLite 3.x

• Python interface: stable degenbot.db typed mirror over Rust degenbot-db

• Schema: Rust degenbot-db (SCHEMA_HEAD, heal-at-open, forward version-lock)

• CLI: the Rust degenbot console (degenbot-cli)

• Logging: degenbot logging module

Example Workflows

Initial Setup: Reset database, activate exchanges, then run updates.

Regular Maintenance: Backup before updates, then compact if database grew significantly.

Schema Upgrade: Nothing to run — the database upgrades itself at open (ADR-052). Use degenbot database inspect to read the schema state and degenbot database heal for an explicit repair.