Mert

You fire up a MySQL client, connect to port 3306, send off your SQL, and the server parses, optimizes, hits an index, fetches rows, and packs the result back to you. You can picture that entire pipeline.

SQLite has none of that. No server process, no port, no wire protocol. Just a single file: my.db.

So the real question is — what exactly is stuffed inside that file that makes SELECT * FROM apples WHERE color='Yellow' return the right answer?

TinySqlite takes this apart across 740 lines of C++23. It doesn’t link against the official SQLite library. It opens a .db file’s raw binary and pries the data directly out of the disk bytes. We’ll follow its code path, peeling back SQLite’s file format layer by layer.

This article covers: file header → B-tree pages → varint encoding → the schema table → full table scans → index scans.

What SQLite Actually Is

Let’s get the definition straight first.

SQLite is an embedded relational database engine — in plain English: it’s a C library you compile into your program, and once you open a file, you can run SQL against it. No server, no install, no root password.

If you’re familiar with MySQL, here’s the mental model. MySQL is a restaurant — a dedicated kitchen (server process), waitstaff (connection handling), a complex ordering system (query optimizer). You sit down, say “SELECT,” and the back of house scrambles to bring you the dish.

SQLite is your fridge. Open it, grab what you need, nobody serves you. The entire database is a single data.db file. Copy it, carry it, done.

Traditional client-server database:           SQLite's embedded model:
┌─────────┐   TCP/network   ┌───────────┐   ┌──────────────────────────────┐
│ Your app │ ←───────────→ │ DB server  │   │ Your app                     │
└─────────┘                └───────────┘   │  ├── libsqlite.so (engine)    │
                                           │  ├── data.db (the only file)  │
                                           │  └── all ops are local calls  │
                                           └──────────────────────────────┘

Why should you care? You might not encounter MySQL every day, but you’re almost certainly already using SQLite. Your phone’s contacts, WeChat messages, Chrome bookmarks and browsing history — all stored in SQLite. Every iPhone, every Android device, every browser runs a SQLite instance. It’s probably the most deployed database engine on the planet, bar none.

Using it is trivial. Create a database, make a table, insert data, query:

$ sqlite3 test.db
sqlite> CREATE TABLE fruits (name TEXT, price INT);
sqlite> INSERT INTO fruits VALUES ('apple', 5);
sqlite> SELECT * FROM fruits WHERE price < 10;
apple|5

If you’re writing C/C++, include sqlite3.h and a handful of lines embed a full database in your program.

Great. You’re using it comfortably. But do you actually know — what do the bytes inside test.db look like?

Now flip roles. Stop being the user, become the reverse engineer. TinySqlite is a set of reverse-engineering notes that dissects the .db file’s binary structure piece by piece. Let’s begin.

Opening the File — How a .db File Is Organized

The Entire File Is a Chain of Pages

At the macro level, a .db file is astoundingly simple: it’s a sequence of fixed-size pages laid end to end. Every page is the same size (typically 4096 bytes), numbered starting from page 1.

Picture a bookshelf where every shelf slot is the same width. To find the 3rd book, you start from the shelf edge and count to position 3 × slot_width. SQLite pages work the same way — the data for page N starts at file offset (N-1) × page_size.

my.db file:
┌─────── page 1 ───────┐┌─────── page 2 ───────┐┌─────── page 3 ───────┐┌── ...
│ file header (1st 100B)││  page header         ││  page header         │
│ page_size = 4096      ││  type = 0x0D (leaf)  ││  type = 0x05 (interior)
│ num_tables = 3        ││  cells = [row1,row2] ││  child page ptrs     │
│ ...                   ││  ...                 ││  ...                 │
└───────────────────────┘└──────────────────────┘└──────────────────────┘

Page 1 is special — its first 100 bytes form the file header, storing global metadata. Every page after that has only a page header followed by actual data.

What the File Header Carries

The first 100 bytes of page 1 in every SQLite file follow a fixed format. The first 16 bytes are the magic string "SQLite format 300" — the file’s “ID card.” It tells any program that tries to read the file: hey, I’m a SQLite 3 format database.

Bytes 16–17 store the page size. Note that this is stored in big-endian — high byte first. If these two bytes read 0x10 0x00, that’s 4096. If the page size is 512, they’d be 0x02 0x00.

Here’s how TinySqlite reads the page size:

static constexpr size_t kPageSizeOffset = 16;

auto read_u16_be(size_t offset) const noexcept -> uint16_t {
    return static_cast<uint16_t>(data_[offset]) << 8
         | static_cast<uint16_t>(data_[offset + 1]);
}

// In the constructor:
page_size_(read_u16_be(kPageSizeOffset))

Two bytes assembled into a uint16_t. No magic.

Another critical number hides at byte offset 103 (corresponding to SQLite’s byte 56): the table count. TinySqlite reads this to know how many tables the database holds — both system and user tables.

static constexpr size_t kSchemaCountOffset = 103;
num_tables_(read_u16_be(kSchemaCountOffset))

What a B-tree Page Looks Like

Now that you know a file is a chain of pages, the next question is — what’s inside a page? How is data actually organized?

SQLite uses a B-tree to organize data. Each table is a B-tree, and each node in that tree is a page. Pages have two roles:

• Interior pages (type 0x05): These don’t store actual data rows. They store “signposts” — child page numbers and key ranges. Their job is navigation: which subtree contains the data you’re looking for.
• Leaf pages (type 0x0D): These hold the real row data. Every INSERT ultimately lands in a cell on some leaf page.

Visually:

              Page 2 (interior, 0x05)
               /                      
      Page ? (leaf, 0x0D)        Page ? (leaf, 0x0D)
       [Granny Smith]              [Fuji]
       [Golden Delicious]          [Honeycrisp]

A page’s internal structure (the page header) starts at page offset 0:

For interior pages, after the page header comes a rightmost child pointer (4 bytes), pointing to the rightmost child page. Then the cell pointer array — 2 bytes per cell, each pointing to that cell’s actual location within the page.

This “rightmost child + cell pointer array” structure is what enables the B-tree to hop between pages. We’ll expand on this when we cover full table scans.

At this point, you know three key facts:

1. A .db file is a sequence of fixed-size pages
2. The file header tells you the page size and how many tables exist
3. A B-tree organizes the data — interior pages navigate, leaf pages store rows

The next natural question — how does SQLite know which tables exist and where each table’s B-tree root lives? The answer is tucked inside a special table.

sqlite_master — The Database’s “Table of Contents”

The Table of Tables

SQLite has a hidden system table called sqlite_master. Think of it as the table of contents at the front of a book — it doesn’t store your business data, it describes the structure of the entire database.

sqlite_master (system table, exists in every .db file)
┌──────────┬───────────┬──────────┬─────────────────────────────┐
│ type     │ name      │ rootpage │ sql                         │
├──────────┼───────────┼──────────┼─────────────────────────────┤
│ "table"  │ "apples"  │ 2        │ "CREATE TABLE apples(...)"  │
│ "table"  │ "oranges" │ 4        │ "CREATE TABLE oranges(...)" │
│ "index"  │ "idx_..." │ 6        │ "CREATE INDEX..."           │
└──────────┴───────────┴──────────┴─────────────────────────────┘

Each row represents a table, index, view, or trigger. The two most important columns are name (the table name) and rootpage (the root page number of that table’s B-tree). When you later run SELECT * FROM apples, that rootpage = 2 is how the engine finds the entry point to the apples table’s data.

So how do you find sqlite_master itself? Its data lives at a fixed location — page 1. The file header’s kSchemaCountOffset tells you how many rows there are, and right after it, starting at kCellPtrArrayStart (offset 108), is the cell pointer array — each 2-byte pointer references a cell within page 1 that belongs to sqlite_master.

But before we can actually parse those cells, we need two encoding tools — varint and serial type. They’re how SQLite “writes numbers” and “describes types” on disk.

Varint: Waste Not, Want Not

SQLite’s on-disk format leans heavily on a variable-length integer encoding called varint. The core idea is simple: small numbers take less space, big numbers take more.

The rule: each byte contributes its lower 7 bits as data, and the highest bit (bit 7) is a “continue” flag. If bit 7 is 1, there are more bytes coming. If it’s 0, this is the last byte. Up to 9 bytes, with the 9th byte using all 8 bits.

300 = 0b_0000_0010_0010_1100. Split into 7-bit groups: 0000010 and 0101100. Add the continue bit — high group gets 1 (10000010 = 0x82), low group gets 0 (00101100 = 0x2C). read_varint does the reverse, pulling the value back out of the byte stream:

auto read_varint(size_t offset) const noexcept -> VarintResult {
    uint64_t value = 0;
    for (int i = 0; i < 9; ++i) {
        auto byte = static_cast<uint8_t>(data_[offset + i]);
        if (i == 8) {
            value = (value << 8) | byte;
            return {value, 9};
        }
        value = (value << 7) | static_cast<uint64_t>(byte & 0x7F);
        if ((byte & 0x80) == 0)
            return {value, static_cast<size_t>(i) + 1};
    }
    std::unreachable();
}

Each iteration grabs 7 bits and shifts them into the result. The loop stops when it hits a byte whose high bit is 0. If it reaches the 9th byte without stopping, it uses the full 8 bits — that’s varint’s maximum width.

Serial Type: What Exactly Is in This Column

Every column of a record carries a serial type code on disk. It tells the parser: is this column NULL, an integer, text, and how many bytes does it occupy?

Notice the two special values 8 and 9 — the integer values 0 and 1 take up zero bytes on disk; the serial type alone encodes the value. SQLite’s disk format is this miserly.

The corresponding code in TinySqlite:

static constexpr auto serial_type_size(uint64_t serial_type) noexcept -> size_t {
    if (serial_type <= 4)
        return std::array{0, 1, 2, 3, 4}[serial_type];
    if (serial_type == 5) return 6;
    if (serial_type == 6 || serial_type == 7) return 8;
    if (serial_type >= 12 && serial_type % 2 == 0)
        return static_cast<size_t>((serial_type - 12) / 2);
    if (serial_type >= 13 && serial_type % 2 == 1)
        return static_cast<size_t>((serial_type - 13) / 2);
    return 0;
}

Decoding a Schema Row in Real Time

With varint and serial type in hand, we can now decode a single row from sqlite_master. Say this row describes the apples table: type="table", name="apples", rootpage=2, sql="CREATE TABLE apples(...)".

A schema table cell roughly follows this layout: [payload size (varint)] [rowid (varint)] [header size (varint)] [5 serial types (varint)] [body: actual data for 5 columns]

TinySqlite’s parse_schema_entry does exactly this: skip payload size and rowid → read 5 serial types → compute per-column byte sizes from each serial type → sequentially read type, name, tbl_name, rootpage, and sql from the body region.

You don’t need to memorize every step — just know that this is how you get a table name and its rootpage out of raw binary. Once you have the rootpage, you start traversing that table’s B-tree from the corresponding page.

SELECT + WHERE — How Data Actually Gets Found

The first three sections laid all the groundwork: file format, B-tree, schema table, encoding primitives. Now let’s answer the question that’s been hanging since the beginning —

You type SELECT name FROM apples WHERE color='Yellow'. What does SQLite actually do?

SQL parsing? TinySqlite handles it with string search — find FROM, split column names, find WHERE, extract conditions. Let’s skip past that in one sentence.

The interesting part is what comes next: finding the data.

Full Table Scan: Recursive Descent, Zero Missed Rows

Without an index, SQLite’s only option is a full table scan — read every row of the target table top to bottom, then filter with the WHERE clause. Sounds simple enough, but when a table spans multiple pages, how do you guarantee nothing is skipped?

The answer is in the B-tree traversal algorithm. The entry point is the rootpage — the page number recorded in sqlite_master. TinySqlite starts here:

auto rp = db.rootpage(sel.table);

From the rootpage, read_columns_values recursively walks the entire B-tree. The core logic:

• If it’s an interior page (0x05): each cell has a left child pointer pointing to a subtree. Iterate all cells, recursively process each subtree. There’s also a rightmost child pointer pointing to the rightmost subtree — can’t forget that one.
• If it’s a leaf page (0x0D): each cell is a data row. Read them one by one and match against the WHERE condition.

The rightmost child is the most overlooked piece of design — that 4-byte pointer sitting before the cell pointer array on interior pages. It points to the subpage covering the range “to the right” of all cells. Without it, the rightmost chunk of data simply gets dropped.

// Interior page processing logic
auto num_cells = read_u16_be(page_offset + 3);
auto right_child = read_u32_be(page_offset + 8);  // ← don't forget this one

for (uint32_t i = 0; i < num_cells; ++i) {
    auto cell_ptr = page_offset + read_u16_be(page_offset + 12 + i * 2);
    auto child = read_u32_be(cell_ptr);  // ← left child
    auto child_rows = read_columns_values(child, column_indices, filter);
    // ...collect rows from the child page...
}
auto right_rows = read_columns_values(right_child, column_indices, filter);
// ...collect rows from the rightmost child...

The recursion keeps diving, stops at leaf pages, reads data, and bubbles it back up. The entire B-tree gets fully traversed — not a single row is missed.

Column value extraction for each row relies on the serial type machinery from the previous section: read varints for serial types and byte widths, then pull data out of the payload region. If a column happens to be the one in the WHERE clause, compare on the spot.

Index Scan: Taking the B-tree Shortcut

The problem with full table scans is obvious: you’re only looking for color='Yellow', yet you’re reading every apple of every color. When a table has hundreds of thousands of rows, this hurts.

SQLite’s solution is an index. An index is itself a B-tree, but with a few twists:

• Page types are 0x02 (interior index page) and 0x0A (leaf index page)
• Cells don’t carry full rows. They carry index column values + rowid
• The B-tree is sorted by the indexed column

Back to WHERE color='Yellow'. If the apples table has an index on the color column, TinySqlite’s path becomes:

Step 1: Search the index B-tree, collect matching rowids.

The index_search function traverses the index B-tree to find every entry where color='Yellow'. Because the index pages are sorted by the color column, the search is binary — compare the index value on the current page, go left if the target is lower, collect the rowid on match, and stop (or continue searching the left subtree) if the target is higher. Vastly more efficient than a full table scan.

Step 2: Use rowids to locate individual rows in the table B-tree.

With a list of rowids in hand, read_row_by_rowid does point lookups on the table B-tree. Each point lookup follows a path similar to the recursive scan — compare rowids on interior pages to decide which child page to descend into — but it hunts for a single row rather than traversing every cell.

if (sel.where) {
    auto idx_rp = db.index_rootpage(sel.table, sel.where->column);
    if (idx_rp) {
        std::vector<uint64_t> rowids;
        db.index_search(*idx_rp, sel.where->value, rowids);
        for (auto rid : rowids) {
            auto row = db.read_row_by_rowid(*rp, rid, col_indices);
            // ...collect results...
        }
        return result;
    }
}
// No index, fall back to full table scan
return QueryResult{.rows = db.read_columns_values(*rp, col_indices, filter)};

The code above is the core decision logic in TinySqlite’s execute_query: if there’s an index, use it; otherwise, do a full scan. SQL parsing, schema rootpage lookup, B-tree traversal, WHERE filtering, index search — everything covered so far converges into these dozen lines.

Of course, production-grade SQLite is far more complex. It has a query optimizer to pick among indexes, WAL journaling for crash recovery, multi-version concurrency control, B-tree page splits and rebalancing. But the core skeleton — file header → page → B-tree → schema → full scan / index scan — is exactly this.

Databases Aren’t That Mysterious

Starting from the client-server architecture contrast with MySQL, we’ve peeled all the way down to the .db file’s bedrock: fixed-size pages strung together into a tree, a handful of header bytes telling you page size and table count, a schema table whose varint-encoded rows describe where every table lives, interior B-tree pages serving as signposts and leaf pages holding the actual data, and indexes as separate B-trees already sorted for you.

740 lines of C++23, zero external dependencies, spanning the full path from a binary file header to a SELECT query result. It won’t run TPC-C, and it’s not going to replace libsqlite3 in your project. But if you want to see what every byte in a .db file is doing, it’s exactly enough.

Code at https://github.com/Tenaryo/TinySqlite — this is a teaching-grade implementation, not an industrial one. Issues and feedback are still welcome.

Most people set up an AI agent and immediately start thinking about multi-agent architectures. Orchestrators, specialist swarms, automated pipelines. That’s Level 4 thinking applied to a Level 1 setup, and it’s how you end up with a fleet of agents shipping garbage at scale.

Hermes Agent by Nous Research (160K+ stars, fastest-growing open-source agent of 2026) is built for exactly this kind of progressive scaling. It’s self-hosted, self-improving, stores everything locally in SQLite, and supports multi-agent orchestration out of the box as of v0.6.0.

But the framework below isn’t Hermes-specific. It applies to any agent system. The tool doesn’t matter as much as the progression.

Here are the four levels, what each one looks like in practice, and how to know when you’re actually ready to move up.

First: What Hermes Agent Is

Hermes is an autonomous AI agent that runs on your machine or VPS. It takes a goal, breaks it into steps, picks from 47 built-in tools to execute, and iterates until the task is done. Everything stays local.

What sets it apart: after each task, Hermes writes a structured record of what worked and what didn’t into episodic memory. On future tasks with similar patterns, it retrieves those records and adjusts its approach before starting. It also creates reusable “skills” from experience, essentially building procedural memory that improves over time.

It connects to 20+ messaging platforms (Telegram, Discord, Slack, WhatsApp, Signal, and more), supports MCP servers, and runs across 6 terminal backends (local, Docker, SSH, Daytona, Singularity, Modal).

Install:

curl -fsSL https://raw.githubusercontent.com/NousResearch/hermes-agent/main/scripts/install.sh | bash

Or via pip:

pip install hermes-agent
hermes postinstall

Then configure:

hermes doctor      # check your environment
hermes model       # pick a model
hermes config set  # add API keys
hermes             # start the agent

Takes about 60 seconds on Linux, macOS, or WSL2.

Level 1: The Main Agent

You → Your Soul Hermes Agent

This is where everyone starts, and where most people should stay for weeks, not days.

Your single Hermes instance is your prototype area. You test workflows here. You refine prompts. You figure out which tasks the agent handles well and which ones it fumbles. You build up its memory and skills on your specific work.

At this level, Hermes doubles as your orchestrator by default. You give it a complex task, it breaks it down, it executes. The self-improving loop is already running: every completed task makes it slightly better at similar tasks next time.

What to do at Level 1

• Run real work through it daily. Not toy examples. Actual tasks from your workflow. The memory system only gets useful with real data.
• Manage its memory actively. Use /recall to search what it remembers and /remember to manually save important context. Correct it when it gets things wrong.
• Install skills or let it create them. Skills are procedural memory. Hermes can build them from experience, or you can install community-contributed ones from the Skills Hub.
• Connect one messaging platform. Telegram is the easiest. Run hermes gateway setup to get always-on access from your phone. This changes the dynamic from “sitting at my terminal to use AI” to “texting my agent whenever I need something.”

When to move on

When you have at least 2-3 workflows that are consistently producing good output. Not acceptable output. Not “close enough.” Good output that you’d be comfortable shipping without heavy editing.

This is the most important checkpoint in the entire framework. Everything that comes after multiplies the quality you establish here.

Level 2: Specialized Agents

You → SEO Agent
You → Content Pipeline Agent
You → DevOps Agent

Once a workflow is solid and repeatable, break it out into its own Hermes instance with its own credentials, memory, and scope.

Why separate instances?

Context pollution. An agent that handles your SEO research, your email drafting, and your code reviews is juggling three different domains in one memory space. Its SEO skills get diluted by code review patterns. Its writing voice gets contaminated by technical documentation habits.

Specialized agents have cleaner memory, more focused skills, and better output because they only learn from one domain.

How to do this practically

Each Hermes instance runs independently. Use different configuration profiles, or spin each one up in its own Docker container or VPS.

# Different profiles for different agents
HERMES_PROFILE=seo hermes
HERMES_PROFILE=contentpipeline hermes
HERMES_PROFILE=devops hermes

Each profile gets its own SQLite database, its own memory, its own skill library. You talk to each one directly. You’re still the orchestrator at this stage, manually deciding which agent handles which task.

What to do at Level 2

• Write a scope document for each agent. What it does, what it doesn’t do, what tools it has access to. This isn’t bureaucracy. It’s how you prevent scope creep across agents.
• Let each agent build its own skill library within its domain. The SEO agent’s skills should be about keyword research and competitor analysis, not email copywriting.
• Keep the count low. 2-3 specialists is plenty to start. The temptation to spin up a new agent for every task is strong. Resist it.

When to move on

When you’re spending more time routing tasks between agents than actually reviewing their output.

Level 3: Orchestrated Team

You → Orchestrator Agent
           ↓
     Your Specialized Agents

Now you bring the orchestrator agent back. But this time it’s not your prototype agent wearing multiple hats. It’s a dedicated Hermes instance whose only job is routing tasks to your specialists and synthesizing their outputs.

Hermes v0.6.0 added multi-agent orchestration. The orchestrator analyzes a complex task, identifies the optimal work breakdown, and spawns specialist worker agents with tailored context. Each worker gets its own scope and tools, returns a verifiable artifact, and records the handoff.

Example workflow

You tell the orchestrator: “Research competitors in the CRM space and draft a blog post about our differentiators.”

The orchestrator:

1. Routes the research task to your Research agent
2. Takes the research output and routes the writing task to your Content agent
3. Synthesizes the outputs into a final deliverable
4. Returns it to you for review

You still review the final output. You’re not out of the loop. You’re just not manually routing between agents anymore.

What to do at Level 3

• Set up task tracking. Kanban-style works well. You need visibility into what each agent is working on, what’s queued, and what’s done.
• Define handoff protocols. What does the research agent pass to the content agent? What format? What level of detail? Ambiguous handoffs create ambiguous output.
• Review regularly. Quality issues compound fast in multi-agent setups. A small drift in the research agent’s output becomes a big problem by the time it’s been through two more agents.

When to move on

When the orchestrator’s routing decisions are consistently correct and the specialist outputs consistently meet your quality bar without heavy editing.

Level 4: Automated Team

Cron Job / Trigger Events → Orchestrator Agent
                       ↓
                 Full Agent Team

This is where you step out of the loop for routine work. Cron jobs and event triggers fire tasks into the orchestrator. The orchestrator routes them to the team. The team handles the work asynchronously.

What this looks like in practice

• Every Monday at 8am, the orchestrator triggers your SEO agent to pull keyword rankings, your content agent to draft the weekly newsletter outline, and your ops agent to generate a metrics report.
• When a new competitor blog post is published (event trigger), the research agent analyzes it and the content agent drafts a response piece.
• When a support ticket hits a specific tag, the ops agent drafts a response for your review queue.

The task bus handles queuing and routing. Agents pick up work, complete it, and log results. You check in when you want to, not because you have to.

What to do at Level 4

• Start with one automated workflow, not ten. Get one cron job running reliably before adding more. Debugging a broken automation is harder when you have twelve of them running simultaneously.
• Build in quality gates. Not every output needs your review, but have the orchestrator flag anything that falls below a confidence threshold for human review.
• Monitor closely at first. The trust you build here is earned, not assumed. Look at outputs daily for the first two weeks, then taper to spot-checks.

The Part That Matters More Than Any of This

Take small steps. You do NOT want to automate slop.

If your output at Level 1 is mediocre, you are about to scale mediocrity. 20 agents shipping low-quality work at speed is worse than 3 shipping great work slowly. Every level multiplies whatever quality you’ve established at the level before it.

I’d rather run fewer agents with better output than max the agent count and spit out more of the same.

The progression isn’t about moving fast. It’s about moving when you’re ready. Level 1 might take you a month. Level 2 might take another month. That’s fine. The agents aren’t going anywhere. Your quality bar is what matters.

Resources

• NousResearch/hermes-agent (160K+ stars)
• Official documentation
• Installation guide
• Multi-agent orchestration (v0.6.0)
• Skills catalog

I write about practical AI agent workflows, open-source tools, and the infrastructure behind them at Web After AI. No hype, just stuff you can actually use.

Series Roadmap

• Product Overview (1/6)
• Architecture: N-Tier Design, Decoupling, and Microservices (2/6)
• Frontend: Angular, NgRx, and OpenAPI Integration (3/6)
• Machine Learning in Practice (4/6)
• Database Design and Data Modeling (5/6)
• Engineering Tradeoffs: Limited Time vs. Ideal Architecture (6/6)

Closing the Chapter: When “Good Enough” is the Engineering Solution

After a year and a half of active development, countless commits, and a steep learning curve, Claprec is finally shipped. This series has documented the journey, but this final post isn’t just a victory lap. It is a retrospective on the gritty reality of engineering tradeoffs.

We often read about architecture in a vacuum – perfectly scalable, perfectly optimized. But in the real world, constraints exist. This post covers the tension between limited time and perfection, the architectural debt accumulated, and why I chose to ship a functional app rather than pursue an unattainable ideal.

The Timeline and The “Last 99%”

The first commit for Claprec dates back to October 19, 2022. It wasn’t a continuous sprint; life happened. I graduated, worked for a year as a Frontend Engineer, and juggled other projects. However, the final push took everyone’s favorite timeline: the “last 1% of work“, which inevitably consumes 99% of the effort.

For the last two and a half months, I dedicated intense focus to finishing the app. There were no strict deadlines (post-graduation), only a self-imposed drive to close this chapter.

The result? A functional application. It follows the programmer’s iteration mantra:

1. Make it functional. (Status: Done)
2. Make it performant. (Status: Acknowledged, deferred)
3. Make it beautiful. (Status: Secondary priority)

For a demonstration of skills and a valid product delivery, functional is sufficient.

Architectural Decisions: The N-Tier Constraint

One of the defining constraints of this project was the architectural requirement: N-Tier Architecture.

If I were starting from scratch today with total freedom, I would likely lean towards Clean Architecture for better dependency inversion and maintainability. However, N-Tier was a requirement for my university curriculum, and it taught me a critical lesson: strict boundary enforcement.

The Trap: The Distributed Monolith

My biggest mistake in the early stages was being loose with tier boundaries. I paid the price later. Refactoring a specific part of the primary microservice to restore proper separation of concerns took me two intensive weeks of full-day work.

In N-Tier, if you aren’t strict, you are heading for a disaster.

The Silver Lining: Velocity

Despite the complexity of N-Tier, there is a distinct advantage: speed of entity creation. Once the layers are in place, adding a new entity with full CRUD functionality is incredibly fast. This allowed me to build out the backend logic rapidly when I needed to.

Performance and Scalability: The Reality Check

Let’s be honest about the state of the shipped product. The application is visibly slow.

The Immediate Missing Piece: There is no caching layer.

• Immediate priority: Implementing Redis to offload read pressure and reduce latency would be the first commercial step. Without it, the database takes the full hit of almost every request.
• Media delivery: For media files (pictures and documents), I would use a CDN. A key advantage is that the database and the backend already support this setup, so the infrastructure is ready for integration whenever a CDN is provisioned.

The Deployment Reality

• Current setup: Deployed to a VPS using Docker and Nginx.
• Why: It works. It was fast to set up.
• The tradeoff: This is not a scalable architecture for a million-user startup. It is a monolithic deployment on a single node.

The Cloud-Native “What If”

If I were architecting this for high-scale production, the roadmap would look vastly different:

• CI/CD: GitHub Actions for automated testing and deployment pipelines.
• Orchestration: Kubernetes (K8s) for auto-scaling and self-healing.
• Infrastructure as Code: Terraform for reproducible cloud provisioning.
• Observability: Prometheus + Grafana for real-time metrics and alerting.

While the current VPS setup lacks the bells and whistles of a cloud-native stack, it fulfills the project’s primary goal: it works.

Technical Debt & Hard Lessons

Engineering maturity isn’t about writing perfect code; it’s about knowing which corners to cut and understanding the cost of doing so. Here is my itemized bill of technical debt:

The Debugging Gap

A key mistake was not measuring execution times during development. I treated performance as an afterthought rather than a feature. This led to “visibly slow” endpoints that require painful retrospective optimization.

Thread Safety Nightmares

I initially neglected thread-safe operations. This was a significant mistake. It produced elusive bugs that I had to solve later – often under pressure. Lesson: concurrency issues do not fix themselves; they compound interest.

Polyglot Persistence

Retrospectively, forcing a relational database for everything was a mistake.

• Better Approach: Use specialized storage. For example, a write-optimized time-series database for activity logs, and NoSQL database for archives and logs. This separation would significantly improve performance by aligning each data type with a storage system optimized for its access and write patterns.

The Frontend Grind: 101 Pages

On the frontend, the tradeoffs are visible. I built 101 pages under tight deadlines.

• UX Polish: Sacrificed.
• User Flows: Inconsistent in places.

This is the reality of feature completion. In a startup environment, often “it works” beats “it looks perfect“.

Evolution of Tooling: LLMs

This project was started before ChatGPT existed. Throughout the lifecycle of Claprec, I witnessed the explosion of LLMs.

I integrated them into my workflow, watching the tooling evolve. They are fantastic helpers for some parts of software engineering – generating boilerplate, clarifying language details, writing tests, and drafting standard functions.

However, this journey reinforced a crucial distinction: actual engineering and debugging cannot be fully solved by LLMs. LLMs, built on gradient descent and backpropagation, are powerful tools, but they cannot solve novel problems that haven’t been documented on the internet yet. You still need to know what to ask and why the answer works.

Bonus: Book Recommendations

Throughout the development of Claprec, I read a few books to help guide my decisions. These are of quality and I highly recommend them:

RESTful Web Services Cookbook by Subbu Allamaraju was extremely useful for the backend. It helped me design my API, ensuring that the result is a standardized REST API that strictly follows REST practices.

On the frontend side, Universal Principles of UX by Irene Pereyra and Refactoring UI by Adam Wathan & Steve Schoger were very helpful resources. They provided the theoretical and practical advice I needed to navigate the design process.

Closing the Chapter

Claprec is not perfect. It is slow in places, the UX needs polish, and the architecture carries the scars of university constraints. But it is a bridge between academic theory and real-world product delivery.

This project tested my endurance and discipline. It is a testament to the fact that finishing is a skill in itself.

If you prefer video format, I also published a video containing all six videos from the LinkedIn series in their original, unmodified form and sequential order, providing high-level overviews of the engineering decisions behind Claprec: Watch it on YouTube.

What’s Next?

I am closing the book on Claprec to focus on new challenges.

• Side projects: I’m considering new challenges: engineering a system capable of handling 3 million HTTP requests per second, learning TLA+ or Verilog, or working on an interesting embedded systems project.
• Professional opportunities: I am open to new roles. If my experience with Claprec (.NET, Angular, MSSQL) – along with my experience in Next.js (React), Vue.js, Laravel (PHP), Node.js, and Python – matches what you or your company needs, let’s talk (Linkedin).

I want to acknowledge Haris Kordić for contributing during the initial stages of the project, including the product vision, design prototypes, and backend implementation.
I also want to acknowledge Benjamin Markanović for his feedback on design decisions throughout the project, and Haris Sejmenović for helping with testing and validating the application before release.

Thank you for following this series.

If you enjoyed this deep dive, please feel free to connect with me on LinkedIn. I will be posting a special closing update there shortly where I share a personal milestone: receiving the Rector’s Award for academic achievements.

It is good to close this chapter. Now, onto the next build.

With the series now complete, I’d love to hear your thoughts on these tradeoffs or the entire project in the comments below.

I wanted a small, local tool that would search the news for brand mentions. I didnt want to pay over $100 a month so I decided to build my own.

What I created was a tool that would search the news with a Google News API every morning for a list of keywords, run each result through a LLM for sentiment and a one-sentence summary, save everything to SQLite, and ping me the results on Slack/a web app.

The whole project came out to about 1,000 lines of Python across ten files. It is a Flask app with a SQLite database and a single HTML dashboard.

Here’s how it’s wired together, with the code that matters from each layer.

Repo: google-news-monitor on GitHub. Install instructions at the bottom.

The pipeline

The whole tool is one pipeline:

keyword → Google News API → OpenAI enrichment → SQLite → (dashboard | REST | CLI | Slack)

Every interface (the dashboard form, the REST API, the CLI, the daily cron) ends up calling the same process_keyword() function. Here is the entire core loop, from monitor/pipeline.py:

def process_keyword(keyword, num=30, when="1d", gl=None):
    keyword = keyword.strip()
    fetched = search.fetch_google_news(keyword, num=num, when=when, gl=gl)

    new_count = 0
    for art in fetched:
        if not art.get("url"):
            continue
        ai_result = ai.enrich_article(keyword, art.get("title") or "",
                                      art.get("snippet") or "")
        row = {
            "keyword": keyword,
            "title": art["title"],
            "url": art["url"],
            "source": art.get("source"),
            "snippet": art.get("snippet"),
            "published_at": art.get("published_at"),
            "sentiment": ai_result["sentiment"],
            "ai_summary": ai_result["summary"],
        }
        article_id = db.save_article(row)
        if article_id is not None:
            new_count += 1
            alerts.check_article(keyword, row, article_id)

    return {"keyword": keyword, "fetched": len(fetched), "new": new_count}

Fetch, enrich, save, alert. That is the whole tool, minus the interfaces wrapped around it.

Fetching from the Google News API

I’m using SearchApi.io as the entry point to the Google News API.
One issues i ran into was, google news matching is loose. search “niche company 1” and half the results are for the similar niche company 2, nothing to do with you. So there’s a per-article flag for whether your keyword actually appears in the article body, with a toggle to hide everything else.

From monitor/search.py:

SEARCHAPI_URL = "https://www.searchapi.io/api/v1/search"

def fetch_google_news(keyword, num=30, when=None, gl=None):
    params = {
        "engine": "google_news",
        "q": keyword.strip(),
        "nfpr": 1,                 # turn off "did you mean..."
        "num": num,
        "api_key": os.environ["SEARCHAPI_KEY"],
    }
    if when:
        params["when"] = when      # 1h, 1d, 7d, 1m, 1y
    if gl:
        params["gl"] = gl.lower()  # 2-letter country code

    resp = requests.get(SEARCHAPI_URL, params=params, timeout=30)
    resp.raise_for_status()
    data = resp.json()

    articles = []
    for item in data.get("organic_results", []) or []:
        articles.append({
            "title": item.get("title"),
            "url": item.get("link"),
            "source": item.get("source", {}).get("name"),
            "snippet": item.get("snippet"),
            "published_at": parse_date(item.get("date")),
        })
    return articles

One thing the Google News API will trip you up on: the date field arrives as free-form strings like "1 week ago", "May 30, 2023", or "Yesterday". Not ISO timestamps. If you store those verbatim, your SQL filters will silently break and your charts will sort "May 30" alphabetically next to "2026-05-14". I wrote a small parser (monitor/dates.py) that normalizes everything to YYYY-MM-DD on the way in.

OpenAI enrichment with a JSON guardrail

For every article I want two things: a sentiment label (positive, negative, neutral) and a one-sentence summary. The trick is to force OpenAI to return parseable JSON so the database ingestion never sees free-form text.

From monitor/ai.py:

ARTICLE_SYSTEM_PROMPT = (
    "You are a media-monitoring analyst. For each article you receive, "
    "classify the sentiment toward the tracked brand/keyword and write a "
    "one-sentence summary. You MUST return a single JSON object - no prose, "
    "no markdown, no code fences. "
    'Schema: {"sentiment": "positive"|"negative"|"neutral", '
    '"summary": "<one sentence>"}. Never include any other keys."
)

def enrich_article(keyword, title, snippet):
    resp = openai_client().chat.completions.create(
        model=os.environ.get("OPENAI_MODEL", "gpt-4o-mini"),
        response_format={"type": "json_object"},   # enforce JSON
        messages=[
            {"role": "system", "content": ARTICLE_SYSTEM_PROMPT},
            {"role": "user", "content":
                f"Tracked keyword: {keyword}n"
                f"Article title: {title}n"
                f"Article snippet: {snippet or '(no snippet)'}"},
        ],
        temperature=0.2,
    )
    data = json.loads(resp.choices[0].message.content or "{}")
    sentiment = (data.get("sentiment") or "neutral").lower()
    if sentiment not in {"positive", "negative", "neutral"}:
        sentiment = "neutral"
    return {"sentiment": sentiment, "summary": (data.get("summary") or "").strip()}

response_format={"type": "json_object"} forces the model to emit valid JSON. The system prompt also redundantly says “no prose, no markdown, no code fences” because models sometimes ignore the format flag anyway. Belt and braces.

There’s also a summarize_period(keyword, articles) function that takes the full article list for a time window and writes a paragraph-long narrative summary. Same JSON-only pattern. That’s what powers the “AI period summary” block at the top of the dashboard report.

SQLite with self-healing schema

I wanted zero setup steps. No flask db upgrade, no SQL files to apply by hand. So the database creates itself on first connection:

SCHEMA = """
CREATE TABLE IF NOT EXISTS articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    keyword TEXT NOT NULL,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    source TEXT,
    snippet TEXT,
    published_at TEXT,
    sentiment TEXT,
    ai_summary TEXT,
    fetched_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(keyword, url)
);
-- ...more tables for keywords, alerts, period summaries
"""

@contextmanager
def connect(path=DB_PATH):
    first_run = not os.path.exists(path)
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    if first_run:
        conn.executescript(SCHEMA)
        conn.commit()
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()

UNIQUE(keyword, url) does the deduplication. If you re-search the same keyword, articles you already have don’t get re-saved and don’t get re-billed for OpenAI calls.

Three interfaces, one core

The dashboard, the REST API, and the CLI all sit on top of the same pipeline.process_keyword(). Each one is small.

The REST blueprint lives in monitor/api.py:

@bp.post("/api/search")
def run_search():
    data = request.get_json(silent=True) or {}
    keyword = (data.get("keyword") or "").strip()
    if not keyword:
        return jsonify({"status": "error", "message": "keyword required"}), 400
    result = pipeline.process_keyword(keyword,
                                       num=int(data.get("num") or 30),
                                       when=data.get("when") or None,
                                       gl=data.get("gl") or None)
    return jsonify({"status": "ok", "result": result})

The CLI is in cli.py:

@cli.command()
@click.argument("keyword")
@click.option("--when", default="1d")
@click.option("--num", default=50)
@click.option("--gl", default=None)
def search(keyword, when, num, gl):
    result = pipeline.process_keyword(keyword, num=num, when=when, gl=gl)
    click.echo(json.dumps(result, indent=2))

The dashboard is one HTML file (templates/dashboard.html) using Tailwind via CDN and Chart.js for the volume chart. There is no build step. Forms POST to the same endpoints.

The GET endpoints auto-fetch when the DB is empty for that keyword. A single URL is enough to spin up a fresh monitor for a new term, which makes it easy to plug into your own scripts or hand to an AI agent that needs to know what the press is saying about a brand.

REST API

All endpoints return JSON. The server runs on 127.0.0.1:5000 by default.

The report and matches endpoints auto-fetch on first use. If there is nothing in the database for that keyword yet, the pipeline runs first and the report comes back populated. Subsequent calls are instant. Pass ?fetch=true to force a refresh.

Query parameters for /api/report and /api/matches:

• period: daily, weekly, monthly, or all (default: all for matches, weekly for report)
• fetch: true to force a fresh fetch even when data already exists
• num: max results from Google News when auto-fetching (default 50)
• when: 1h, 1d, 7d, 1m, 1y (default: any time)
• gl: 2-letter country code, e.g. us, gb, de

Example. A single URL is enough to spin up a fresh monitor for a brand new keyword:

GET http://127.0.0.1:5000/api/matches/n8n?period=all

Daily cron, in-process

APScheduler runs the daily job inside the same Flask process, so there is no system cron to configure and no separate worker to deploy.

def start_scheduler():
    hour = int(os.environ.get("DAILY_CRON_HOUR", "8"))
    minute = int(os.environ.get("DAILY_CRON_MINUTE", "0"))
    sched = BackgroundScheduler(daemon=True)
    sched.add_job(pipeline.run_all_monitored, trigger="cron",
                  hour=hour, minute=minute, id="daily_monitor",
                  replace_existing=True)
    sched.start()

run_all_monitored() walks the list of keywords flagged with monitored=1 in the database and runs the full pipeline for each.

Alerts

The alerts module checks every freshly-saved article against a list of risk phrases (lawsuit, breach, outage, scandal, …), checks if OpenAI returned negative sentiment, and posts a formatted message to Slack if either trips. It also tracks a 14-day rolling baseline and fires a separate alert if today’s article volume is 3x that baseline.

RISK_PHRASES = ["lawsuit", "sued", "investigation", "breach", "hack",
                "outage", "scandal", "fired", "resigns", "bankruptcy", "recall"]

def check_article(keyword, article, article_id):
    haystack = (article.get("title", "") + " " + article.get("snippet", "")).lower()
    matched = [p for p in RISK_PHRASES if p in haystack]
    reasons = []
    if matched:
        reasons.append(f"risk phrase: {', '.join(matched)}")
    if article.get("sentiment", "").lower() == "negative":
        reasons.append("negative sentiment")
    if reasons:
        db.save_alert(keyword, article_id, "; ".join(reasons))
        send_slack(format_slack(keyword, reasons, article))

Install it

git clone https://github.com/SamJale/Google-News-Monitor-API.git
cd google-news-monitor
pip install -r requirements.txt
python app.py

A browser tab opens at http://127.0.0.1:5000/. Add your SearchApi.io and OpenAI keys through the Settings button in the UI (or edit .env directly).

If you want to drive it from the terminal:

python cli.py add "anthropic"                       # start tracking
python cli.py search "anthropic" --when 7d --num 50 # one-shot
python cli.py report "anthropic" --period weekly    # see the saved data
python cli.py cron                                  # run the daily job now

Things I would change if I were building it again

• The OpenAI enrichment runs serially. For a keyword that returns 50 articles, that’s 50 sequential API calls. Easy win: parallelize with asyncio or a thread pool.
• The data.db file lives in the project root. Probably should default to ~/.google-news-monitor/data.db for cleaner installs.
• No retry logic on transient API errors. SearchApi.io and OpenAI both occasionally 500. Add exponential backoff.

If you build any of these, send a PR.

If you want to see what the running app looks like, screenshots are on the GitHub README. It is MIT licensed, runs entirely on your machine, and has no telemetry or sign-up.

Disclosure: I work at SearchApi.io, which is the Google News data source this tool uses. Worth saying upfront before anyone digs.

Avikal did not begin as an archival security tool.

It began with a question: can data be hidden inside chess?

Today, RookDuel Avikal is an open-source archival security system built around the .avk format. It is designed for long-term private storage, layered access control and future-governed release workflows. The project combines conventional cryptographic protection with chess-rooted metadata transport, a Devanagari keyphrase path, optional post-quantum protection and TimeCapsule-based delayed access.

But the journey did not start from cryptography or archive software. It started from curiosity.

How the idea began

The idea started in 2024, when I became deeply interested in steganography — the technique of hiding data inside another medium. Since chess has been a major part of my life, one question naturally came to my mind: can data be hidden inside chess?

That question became the starting point of Avikal.

At first, my focus was not on building a secure archive system. I was mainly exploring chess steganography and trying to understand how much data could be hidden inside chess games, moves, notation and variations. I worked on ideas such as recursive variations in chess steganography, where chess variations could be used to represent more hidden information.

For some time, this looked like a very interesting direction. But while researching and testing the idea, I reached a practical and mathematical limitation. There was only so much data that could be meaningfully hidden through that method. Beyond a point, the structure became difficult, inefficient and not practical for the kind of system I wanted to build.

That was one of the first major walls in the project.

I did not want to give up on the idea completely, because the connection between chess and hidden data still felt powerful. So instead of forcing chess steganography to do everything, I continued researching how files, archives and secure containers are normally created.

From ZIP files to Avikal

During this time, I started studying how ZIP files are built and shipped. That changed the direction of the project.

A normal archive file is basically a container. It takes one or more files and stores them together inside a single package. A ZIP file can hold documents, images, folders or other files in one place. It can also compress them so they take less space and become easier to share.

This idea is everywhere.

Many people think a .docx file is only a Word document, but modern Office files also use ZIP-based packaging. If someone opens a .docx file as a ZIP package, they can find internal files and folders that describe the document, its content, formatting and relationships.

That idea became important for me.

I realised that instead of trying to hide large amounts of data only inside chess, I could build my own archival tool where the archive itself becomes a structured container. But unlike a normal ZIP-style package, Avikal would not just package files together. It would have its own identity, its own metadata structure and its own access model.

That is when Avikal slowly started becoming a real archival security project.

What Avikal is

RookDuel Avikal is best understood as a composed archival security system, not just a simple encrypted file wrapper.

In simple words, Avikal is designed to seal files into a protected archive while keeping the archive structure clear, layered and future-aware. It separates the larger encrypted file data from the smaller control information that explains how the archive should be opened, verified or governed.

In the current implementation, an Avikal archive contains two main parts:

• keychain.pgn
• payload.enc

The payload.enc file carries the encrypted archive payload. This is where the protected file data is stored.

The keychain.pgn file carries protected archive metadata using chess-rooted PGN transport. In simple terms, this means Avikal uses chess notation as a structured carrier for archive control information, while the actual security still comes from established cryptographic methods.

So the basic idea is simple:

A normal archive stores files together.

A protected archive encrypts files.

Avikal goes further by separating the archive into a data plane and a control plane.

The data plane is the encrypted payload.

The control plane is the metadata layer, and in Avikal that layer is represented through chess-rooted PGN.

How Avikal works in simple terms

Avikal uses established cryptographic components for protection. According to the current whitepaper, Avikal uses AES-256-GCM for authenticated payload protection, Argon2id for password and keyphrase hardening, HKDF for key separation, and an OpenSSL 3.5+ provider-backed path for optional post-quantum operation.

In simple terms:

• AES-256-GCM protects the file data and helps detect tampering.
• Argon2id makes passwords and keyphrases harder to attack through guessing.
• HKDF helps separate keys so that one key is not reused for too many purposes.
• The optional post-quantum path exists for users who want an additional future-aware protection route.

Avikal is not trying to invent new cryptographic primitives. Its contribution is in how it brings multiple parts together into one archive architecture: file protection, metadata transport, keyphrase access, future release and optional post-quantum assistance.

The chess layer

The chess layer is the most distinctive part of Avikal, but it is also the easiest to misunderstand.

Avikal does not use chess as a replacement for encryption. It uses chess as a metadata transport and archive-identity layer.

The chess-rooted metadata system carries protected archive metadata through PGN form. The whitepaper describes this as a steganographic transport built from legal move selection, recursive PGN variation trees and authenticated preprocessing of the metadata payload.

In simpler words, Avikal can represent protected archive metadata inside chess notation in a structured way. The file can look like a chess PGN structure, while the actual protected data remains handled through cryptographic methods.

This is where my earlier research into chess steganography became part of the archive system.

The idea that began as “can data be hidden inside chess?” later became a practical metadata layer inside Avikal.

Chess is not a gimmick here, but it is also not a magic cipher. It is a structural layer that gives Avikal a unique identity while keeping the security foundation separate.

The Devanagari keyphrase path

Another important part of Avikal is its Devanagari keyphrase system.

This idea also came from something I was personally exploring.

For some time, I had been interested in cryptocurrency and crypto wallets. One thing that caught my attention was the way many wallets use recovery phrases or seed phrases. A user receives a set of words, and those words can later help recover access to the wallet.

That idea felt powerful because it made a complex security process more human-readable.

But most of these systems are heavily English-centric. That made me think: can a serious archive system support an Indian-language keyphrase path?

That question slowly became Avikal’s Devanagari keyphrase system.

In crypto wallets, a recovery phrase usually works like a human-readable backup. Instead of asking the user to remember a random-looking secret, the wallet gives a sequence of words. Those words represent underlying secret material in a structured way, often with validation so mistakes can be detected.

Avikal is not a crypto wallet, and its keyphrase system is not the same thing as a wallet recovery phrase. But the inspiration came from that idea: using words as a structured access path.

In Avikal, the Devanagari keyphrase path is treated as a real access mechanism. According to the whitepaper, it uses fixed vocabularies, checksum validation, canonical normalization and integration into the archive key-derivation process.

The current system supports:

• password-only protection,
• keyphrase-only protection,
• password and keyphrase together.

The default keyphrase path uses 21 words, and the canonical secret remains the normalized Devanagari phrase. Romanized input may help users type or select words, but the canonical form remains Devanagari.

For me, this part of Avikal was important because archival security should not always be imagined only through English-first systems.

TimeCapsule

Another idea that came during this process was the TimeCapsule feature.

I wanted Avikal to support files that could be sealed for a future time, instead of being opened immediately through a normal password flow. The idea was to make it useful for situations like personal records, institutional submissions, legal disclosures, future notes or any file that should be protected until a later release time.

In Avikal, TimeCapsule is not only a user-interface timestamp. It is part of the archive-level release model.

The whitepaper describes two current TimeCapsule provider directions:

• drand, for a public future-unlock path,
• Aavrit, for a signed authority-backed path.

The reason I focused on drand is because drand provides public, verifiable randomness. In simple terms, it is a public randomness network that releases random values over time. For a delayed-access system, this is useful because an archive can be connected to a future public release event instead of depending only on a private person manually releasing a password.

The basic idea is simple:

Before the chosen time, the required public value is not available.

After the chosen time, the value becomes publicly available and verifiable.

This makes drand useful for a public future-unlock path. It helps Avikal represent delayed access at the archive level, instead of leaving everything only to trust, memory or manual procedure.

Aavrit is different. It represents a signed authority-backed direction, where delayed access can depend on a controlled release workflow. In the current implementation, the desktop application is the principal Aavrit surface, while the CLI does not yet expose full Aavrit workflow parity.

So TimeCapsule is Avikal’s way of making delayed access part of the archive design. It is not only “hide the password until later.” It is a more structured attempt to make time-governed access part of the archive itself.

The post-quantum direction

The direction became even clearer when I came across the concept of “Harvest Now, Decrypt Later.”

This idea genuinely caught my attention and worried me.

The concern is simple but serious: encrypted data can be collected today and stored for the future. Even if attackers cannot decrypt it now, they may try to decrypt it later when quantum computers become powerful enough to break parts of today’s public-key cryptography.

For short-term data, this may not always feel urgent. But for archival data, it becomes much more serious.

Some files need to remain private for years or decades. These can include legal records, institutional documents, research files, private archives, personal records, business documents and other long-term data.

That is why the threat feels real.

It is not only about what quantum computers can do today. It is about data being collected today for a future where today’s protection may not be enough.

That was the point where I started connecting Avikal with post-quantum cryptography.

I began researching how post-quantum protection could be implemented in a practical way. The best option I found for my project direction was to work with OpenSSL-supported cryptographic methods, because OpenSSL is widely used, actively maintained and trusted in real-world security infrastructure.

Implementing this direction properly took around five months of focused work inside Avikal.

According to the current Avikal whitepaper, the optional PQC-assisted mode uses an OpenSSL provider boundary. The implemented suite includes:

• ML-KEM-1024
• X25519
• ML-DSA-87
• SLH-DSA-SHA2-256s

In simple terms, this optional path is meant to add a future-aware protection layer using post-quantum cryptographic methods. It does not mean Avikal claims to be “quantum-proof forever.” It means Avikal includes an optional design path that aligns with the growing need to prepare for quantum-era risks.

Building with AI, but not depending blindly on AI

During the development process, one of the biggest lessons I learned was that security is not only about writing code. It is about design.

The design of cryptographic modules, the source of the cryptographic methods, how keys are handled, how data is sealed, how metadata is structured, and how different parts of the system connect — all of these matter deeply. I spent a lot of my personal time studying these areas because I did not want Avikal to become just a random experimental tool with unsafe design decisions.

AI helped me during development, especially in coding, debugging and exploring implementation ideas. But one important thing I understood clearly is that AI does not automatically give the best security architecture by default. It can suggest code, but it does not always know the best design choice for a serious cryptographic system unless the developer already knows what to ask, what to reject and what to verify.

That is why I had to keep researching independently, comparing approaches, reading about industry practices and checking whether the implementation direction made sense. AI was useful as an assistant, but the responsibility of design, judgement and final implementation choices remained mine.

While designing Avikal, my goal was to follow the best practical security practices I could implement at my current stage. I wanted the project to combine established cryptographic methods, optional post-quantum protection, chess-based metadata encoding, and an Indian-language keyphrase path in a way that is transparent and open for review.

Desktop, CLI and shared core

Avikal currently exists in two operational surfaces.

The desktop application uses Electron as the application shell, a React frontend, a local FastAPI backend and the shared archive core. This is the richer interactive environment for users.

The Python CLI is distributed as the avikal command through the Python package. It provides scriptable access to the same archive engine for operations such as encode, decode, inspect, contents, validate, rekey and doctor.

The important part is that both surfaces are built around the same archive core.

This matters because many tools slowly become inconsistent between GUI and CLI versions. Avikal’s design tries to reduce that problem by keeping one shared archive engine behind both surfaces.

Rekey support

Avikal also includes rekey support for supported archives.

In simple terms, rekey means changing archive credentials without rewriting the entire encrypted payload.

The whitepaper explains that rekey works by opening the protected metadata with old credentials, recovering the wrapped payload key, re-wrapping the same payload key with new credentials, rewriting the control-plane state and keeping payload.enc unchanged.

This makes credential rotation cleaner because the system does not need to rebuild the whole encrypted payload stream in supported cases.

There are limits. Rekey currently applies only to regular rekey-capable archives. PQC archive rekey and provider TimeCapsule rekey are not currently supported.

Current status

Today, Avikal includes regular archive encode and decode, multi-file archive support, Devanagari keyphrase support, password-only, keyphrase-only and combined access paths, chess-rooted metadata transport through PGN, optional post-quantum archive logic, TimeCapsule support, rekey support for regular rekey-capable archives, a Windows-focused desktop application, a Python CLI and one shared archive core across desktop and CLI.

Avikal is currently in beta.

I do not consider it a final product yet. It is still under review and active development. There are many things pending, including fuller CLI feature availability, Linux support, macOS support, wider testing, bug fixing, better documentation and independent security review.

At present, the desktop version is mainly focused on Windows users. Even on Windows, Avikal is still being tested across different situations so that bugs, errors and practical issues can be found and improved.

What Avikal does not claim

It is important to clearly say what Avikal does not claim.

Avikal does not claim that chess itself provides encryption strength. The security of the archive comes from established cryptographic methods. Chess is used as a metadata transport and structural encoding layer, giving the archive format a unique identity inspired by the game that started the idea.

Avikal also does not claim to be unbreakable, quantum-proof forever, or a replacement for fully audited enterprise security products.

The system does not claim to solve host compromise, malware, keylogging, memory scraping on an already compromised machine, weak user-selected credentials, loss of required credentials or .avkkey, provider outages or external service failures.

I am releasing Avikal openly because I want developers, cryptography researchers, security reviewers and open-source contributors to examine it, test it, challenge it and help improve it.

Why Avikal is open-source

After nearly two years of exploration, failed directions, research, redesign and development, Avikal finally became a working open-source project.

For me, open source is important because a security-related project should be visible, reviewable and open to criticism. Avikal is still early, and it needs more review, testing and improvement. Making it public is part of that process.

This project began with a question about hiding data in chess. Over time, it became a much larger project about archival security, post-quantum protection, Indian-language keyphrases and the role of chess as a structural idea in technology. It is still early. It is still developing. But after two years of work, Avikal is now public, open-source and ready for review.

Project links

Website / White Paper: https://avikal.rookduel.tech
Source code: https://github.com/RookDuel/Avikal