# What Is the SQLite-Derived Tracker Index in career-ops? Architecture and Features > Discover the SQLite-derived tracker index in career-ops. Learn how this read-only database offers fast, validated queries while keeping markdown as the single source of truth. - Repository: [Santiago Fernández de Valderrama/career-ops](https://github.com/santifer/career-ops) - Tags: architecture - Published: 2026-06-13 --- **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`](https://github.com/santifer/career-ops/blob/main/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`](https://github.com/santifer/career-ops/blob/main/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`](https://github.com/santifer/career-ops/blob/main/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. ```bash # 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. ```bash # 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. ```bash # 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. ```bash # 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`](https://github.com/santifer/career-ops/blob/main/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`](https://github.com/santifer/career-ops/blob/main/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`](https://github.com/santifer/career-ops/blob/main/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.