What Is the SQLite-Derived Tracker Index in career-ops? Architecture and Features

The SQLite-derived tracker index in career-ops is a read-only database generated from the markdown applications table that provides fast, schema-validated queries while preserving the markdown file as the single source of truth.

The santifer/career-ops repository stores job application data in a plain markdown table at data/applications.md. When this table grows to hundreds of rows, direct editing becomes fragile—stray pipe characters, malformed dates, or duplicate IDs can corrupt the structure. To solve this, tracker.mjs builds a SQLite-derived tracker index that provides a stable, queryable view of the data while keeping the markdown file as the immutable source of truth.

Architecture and Source of Truth

The index follows a write-through-markdown architecture where all mutations happen in the markdown file, and the database is regenerated automatically when changes are detected.

Markdown as the Canonical Source

Unlike traditional database-centric applications, data/applications.md remains the sole source of truth. The SQLite database is purely derived and disposable—it can be deleted and rebuilt from the markdown at any time. This design ensures that the human-readable table remains the authoritative record while the database serves only as a query optimization layer.

SQLite Schema and Tables

As implemented in tracker.mjs, the index uses the built-in node:sqlite module (requiring Node ≥ 22.5) and creates three distinct tables:

• applications: Stores the core application data with columns for id, pos, date, company, role, score, status, pdf, report, and notes
• status_events: Maintains a history of status changes for tracking application progression
• meta: Stores the SHA-256 hash of the markdown file (md_sha256) to detect when the source has changed and requires re-indexing

Status Normalization

The index enforces data integrity by loading canonical status values from templates/states.yml. During the sync process, any non-canonical status labels found in the markdown are normalized to their proper equivalents, preventing inconsistent categorization across the dataset.

Core Functionalities

The tracker.mjs script provides several command-line interfaces for interacting with the derived index.

Sync and Validation

The sync command parses the markdown table, repairs placeholder cells, assigns missing IDs, normalizes statuses, and writes the results into the SQLite database.

# Rebuild the index from the markdown source

node tracker.mjs sync

# Run diagnostics without writing to the database

node tracker.mjs sync --check

During sync, the script reports mojibake cells, scores in wrong columns, unknown statuses, duplicate or missing IDs, malformed dates, and stray pipe characters that could corrupt the table structure.

Query Capabilities

The index supports filtered queries that would be inefficient against raw markdown. You can filter by status, company, role, date range, or ID, with output available as JSON or markdown tables.

# Find all "Applied" entries for companies containing "Acme"

node tracker.mjs query --status Applied --company Acme

# Output results as JSON for piping to other tools

node tracker.mjs query --status Applied --json

Status History Tracking

For any specific application, you can retrieve a chronological list of status changes using the history command.

# View status-change history for application ID 42

node tracker.mjs history --id 42

This queries the status_events table to show the complete lifecycle of an application.

Export and Repair

The export command writes the canonical markdown table from the current database state, useful for repairing corrupted files or creating backups.

# Export the current database back to markdown format

node tracker.mjs export --out repaired-applications.md

Configuration and Environment

By default, the derived database is created at the same path as the markdown file with a .db extension (e.g., data/applications.db). You can override this location using the CAREER_OPS_TRACKER_DB environment variable.

The system implements auto-resync: query and history commands automatically check the stored SHA-256 hash in the meta table against the current markdown file. If the hashes differ, the index regenerates automatically before executing the query, ensuring that all operations always reflect the latest data.

Summary

• The SQLite-derived tracker index in tracker.mjs provides a read-only, schema-validated view of the data/applications.md table.
• The architecture preserves the markdown file as the single source of truth while enabling fast SQL queries.
• Uses the built-in node:sqlite module with tables for applications, status_events, and meta data.
• Supports sync (with --check validation), query (with JSON output), history, and export operations.
• Automatically rebuilds when the markdown changes via SHA-256 hash comparison.
• Normalizes statuses against templates/states.yml to maintain data consistency.

Frequently Asked Questions

What happens if I delete the SQLite database file?

You can safely delete the .db file at any time. Simply run node tracker.mjs sync to regenerate the SQLite-derived tracker index from the markdown source. The database is completely disposable and contains no data that isn't already present in data/applications.md.

Why does the index remain read-only?

The index is read-only to preserve the single source of truth principle. All writes must go through the markdown file (via manual editing or merge-tracker.mjs), ensuring that the human-readable table always reflects the complete state of the dataset. This prevents synchronization conflicts between the database and the markdown.

How does the tracker detect when the markdown has changed?

The meta table stores the SHA-256 hash of the markdown file after each sync. Before executing queries, tracker.mjs compares the stored hash against the current file hash. If they differ, the system automatically triggers a resync to ensure query results reflect the latest edits.

Can I use a custom location for the SQLite database?

Yes. By default, the database is created alongside the markdown file (e.g., data/applications.db). You can override this by setting the CAREER_OPS_TRACKER_DB environment variable to your desired path before running any tracker.mjs commands.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}