OpenCode session archive with frontend

If you work with AI coding assistants every day, you know the problem: the conversation log — every session, every prompt, every tool call — quietly grows into a multi-gigabyte database. Searching it becomes slow, backups take forever, and the things you actually want to remember (that fix for a tricky bug, that refactor pattern, that one command) are buried under thousands of rows of chit-chat.

This article describes a pattern we use in production: a self-hosted archive system that copies old opencode sessions out of the live database into a separate, compact, full-text-searchable archive — then exposes it through a fast web UI that your whole team can search, protected by single sign-on.

The best part: the whole thing is built from boring, proven pieces — SQLite, FTS5, a small Python script, cron, and a tiny web layer. No Elasticsearch cluster, no managed search service, no vendor lock-in.

Let me show you how to build it.

---

The Problem: Your Live Database Is Not a Library

AI coding assistants typically store their conversation history in a local database. Over months of daily use, that database accumulates:

• Sessions — one per conversation
• Messages — the back-and-forth between you and the assistant
• Parts — message segments (text, tool calls, code blocks)
• Events — internal telemetry rows that make up the bulk of the bloat

The numbers add up quickly. A year of daily work can easily produce tens of thousands of sessions and over a million event rows. The live database grows to gigabytes, and every query — including the ones the assistant itself runs — gets slower.

The core insight: your live database is a workspace, not a library. It should hold what’s recent and actively used. Old sessions belong in an archive — somewhere compact, indexed, searchable, and cheap to keep forever.

---

The Architecture at a Glance

The design has three logical parts:

1. The live system — the production database, kept small and fast.
2. The nightly export — a cron job that copies old sessions into the archive, scrubs them, and optionally prunes the live database.
3. The archive — a read-only SQLite database with a full-text index, served by a small web application with a chat-style frontend.

This separation gives you four properties that matter in production:

Property	How we get it
Live DB stays fast	Old sessions are removed nightly (or on a schedule)
Nothing is ever lost	Prune only deletes sessions that are already archived
Search is instant	FTS5 full-text index over the archive
The archive is safe to share	Secrets are redacted before they ever leave the live DB

---

The Nightly Export Job

Reading the live database safely

The first rule: never lock the production database. If your app uses SQLite in WAL mode (write-ahead logging), you can open the database in read-only mode from a separate process and read while the app writes. No downtime, no blocking.

import sqlite3

LIVE_DB = "file:/path/to/live.db?mode=ro"  # read-only connection
conn = sqlite3.connect(LIVE_DB, uri=True)

The watermark: incremental, idempotent, restart-safe

Copying everything every night is wasteful. Instead, we track a watermark: the timestamp of the most recently exported session. On each run we only export sessions newer than the watermark.

def get_watermark(archive_conn):
    row = archive_conn.execute(
        "SELECT value FROM archive_meta WHERE key = 'watermark'"
    ).fetchone()
    return row[0] if row else 0

def set_watermark(archive_conn, ts):
    archive_conn.execute(
        "INSERT OR REPLACE INTO archive_meta (key, value) VALUES ('watermark', ?)",
        (ts,),
    )

Two details make this robust:

• Idempotent writes — we use INSERT OR REPLACE, so re-exporting a session that changed mid-run simply overwrites it. Running the job twice is always safe.
• Fail-closed watermark — the watermark only advances after a batch completes successfully. If a batch fails halfway, nothing is skipped; the next run retries it.

def export_new_sessions(live_conn, archive_conn, watermark):
    cursor = live_conn.execute(
        """
        SELECT id, title, created_at, updated_at
        FROM session
        WHERE updated_at > ?
        ORDER BY updated_at
        """,
        (watermark,),
    )
    for batch in iter_batches(cursor, size=500):
        for session in batch:
            export_session(live_conn, archive_conn, session)
        archive_conn.execute("BEGIN")
        for session in batch:
            upsert_session(archive_conn, session)
        new_watermark = batch[-1]["updated_at"]
        set_watermark(archive_conn, new_watermark)
        archive_conn.commit()  # watermark advances only on success

Redaction: privacy by design

Here’s the part most archives get wrong. If you copy conversations verbatim, you’re also copying API keys, passwords, tokens, and secrets that appeared in the conversation — and you’re putting them in a place that might be shared with the whole team, backed up, or indexed.

Our rule: sensitive data never enters the archive at all. We scrub every exported row with a key-aware sanitizer that recognizes and removes secret patterns:

import re

SECRET_PATTERNS = [
    re.compile(r"(?i)(password|passwd|pwd|token|secret|api[_-]?key|private[_-]?key)\s*[=:]\s*\S+"),
    re.compile(r"sk-[A-Za-z0-9]{20,}"),          # API key prefixes
    re.compile(r"(?i)(bearer|basic)\s+[A-Za-z0-9._~+/=-]+"),
    re.compile(r"AKIA[0-9A-Z]{16}"),             # cloud access keys
]

def redact_text(text: str) -> str:
    for pattern in SECRET_PATTERNS:
        text = pattern.sub("[redacted]", text)
    return text

Two extra decisions that matter:

1. Tool inputs and outputs are omitted entirely. In our schema, tool-call rows keep only the tool name and a truncated, redacted input snippet. The full input/output — which is where secrets hide — never leaves the live database. This is a deliberate trade: you lose the ability to replay a tool call, but you also lose the ability to leak a credential.
2. Fail-closed sanitizers. If redaction encounters something weird (deeply nested JSON, unexpected types), it fails safe: the content is omitted rather than passed through.

The archive schema

The archive database is small, well-defined, and easy to reason about. It mirrors only the tables that matter, with one important addition: a search content table that aggregates each session into a single searchable row.

-- The searchable content: one row per session
CREATE TABLE session_search (
    id            INTEGER PRIMARY KEY,
    title         TEXT,
    agent         TEXT,
    model         TEXT,
    body          TEXT,          -- all messages joined with newlines
    time_updated  INTEGER,
    message_count INTEGER
);

-- Metadata (watermark etc.)
CREATE TABLE archive_meta (
    key   TEXT PRIMARY KEY,
    value TEXT
);

---

Instant Search with SQLite FTS5

This is where the archive becomes genuinely useful. SQLite ships with FTS5, a full-text search engine that’s fast enough for tens of thousands of documents and needs no external service.

External content tables: small index, instant queries

The smart trick is to use an external-content FTS5 table. Instead of duplicating the content inside the index, the FTS table stores only the inverted index and pulls the content from the main table when needed. The index stays small and writes stay cheap.

CREATE VIRTUAL TABLE session_search_fts USING fts5(
    title, agent, model, body,
    content='session_search',          -- external content table
    content_rowid='id'
);

Searching is a simple MATCH query:

SELECT s.id, s.title, s.body, s.time_updated
FROM session_search s
JOIN session_search_fts f ON f.rowid = s.id
WHERE session_search_fts MATCH 'sqlite fts5 optimization'
ORDER BY rank
LIMIT 20;

Keeping the index in sync with triggers

Because the FTS table is external-content, you must keep it in sync yourself. The standard way is triggers on the content table:

CREATE TRIGGER session_search_ai AFTER INSERT ON session_search BEGIN
  INSERT INTO session_search_fts(rowid, title, agent, model, body)
  VALUES (new.id, new.title, new.agent, new.model, new.body);
END;

CREATE TRIGGER session_search_ad AFTER DELETE ON session_search BEGIN
  INSERT INTO session_search_fts(session_search_fts, rowid, title, agent, model, body)
  VALUES ('delete', old.id, old.title, old.agent, old.model, old.body);
END;

CREATE TRIGGER session_search_au AFTER UPDATE ON session_search BEGIN
  INSERT INTO session_search_fts(session_search_fts, rowid, title, agent, model, body)
  VALUES ('delete', old.id, old.title, old.agent, old.model, old.body);
  INSERT INTO session_search_fts(rowid, title, agent, model, body)
  VALUES (new.id, new.title, new.agent, new.model, new.body);
END;

The optimization that changed everything

There’s one FTS5 gotcha that cost us a lot of debugging time: fragmentation. As you insert and delete rows, the FTS index accumulates many small segments. Queries — especially COUNT(*) — degrade from milliseconds to seconds.

The fix is a single command, run after each export:

INSERT INTO session_search_fts(session_search_fts) VALUES ('optimize');

In our case, this took a COUNT(*) on the index from 20 seconds down to 3 milliseconds. If your search UI ever feels sluggish, run this first.

---

Pruning the Live Database (Carefully)

Phase two is optional but recommended: delete old sessions from the live database so it stays small. The safety rule is absolute:

Never delete a session that isn’t already in the archive.

And a practical rule: always keep the N most-recent sessions as a floor, no matter how old they are. Someone’s active conversation must never be pruned mid-session.

def prune_sessions(live_conn, archive_conn, keep_days=30, floor=30):
    now = int(time.time())
    cutoff = now - keep_days * 86400
    # Sessions that are BOTH old AND archived
    cursor = live_conn.execute(
        """
        SELECT s.id FROM session s
        WHERE s.updated_at < ?
          AND s.id IN (SELECT id FROM archive.session)   -- archived?
        ORDER BY s.updated_at DESC
        LIMIT -1 OFFSET ?                                 -- keep the floor
        """,
        (cutoff, floor),
    )
    ids = [row[0] for row in cursor]
    delete_sessions(live_conn, ids)  # batched, FK cascade

The actual deletion must respect foreign keys — deleting a session should cascade to its messages and parts, otherwise you leave orphaned rows and the database doesn’t actually shrink.

Tip: if the app’s tables don’t declare FOREIGN KEY ... ON DELETE CASCADE, delete child rows explicitly (message → part → session) or use a single transaction with PRAGMA foreign_keys = ON.

---

The Web Layer: Datasette + a Chat-Style SPA

6.1 Why Datasette

For serving the archive we use Datasette — a small Python web app that turns SQLite databases into browsable, queryable websites with a JSON API for free. It gives you:

• A browse GUI for the tables (great for debugging)
• A JSON API (/db.json, ?sql=...&_shape=array) for the frontend
• A plugin system for auth, redirects, and custom behavior
• Read-only serving with configurable query time limits

6.2 A chat-style frontend (the part users actually see)

A raw database GUI is fine for admins but not for a team. We built a small single-page app that mimics the chat experience of the assistant itself:

• Sessions view — a paginated list of session cards (title, agent, model, last updated, message count).
• Search — a full-text search box; results show sessions with highlighted snippets.
• Conversation view — click a session to read it like a chat: user messages right, assistant messages left, collapsible reasoning, tool-call cards.
• Jump-to-hit — when you open a session from a search result, the page scrolls to the exact message containing the match and flashes it.

The frontend talks to the JSON API only:

async function search(query, offset = 0) {
  const sql = `
    SELECT s.id, s.title, s.agent, s.model, s.time_updated, s.message_count,
           snippet(session_search_fts, 5, '[', ']', '…', 12) AS snippet
    FROM session_search s
    JOIN session_search_fts ON session_search_fts.rowid = s.id
    WHERE session_search_fts MATCH :q
    ORDER BY rank LIMIT 20 OFFSET :offset`;
  const params = new URLSearchParams({ sql, q: query, _shape: 'array', offset });
  const res = await fetch(`/api/database.json?${params}`);
  return res.json();
}

Note on performance: Datasette (and most SQLite web layers) let you set sql_time_limit_ms and cache settings. With a big FTS index, set a generous query time limit (e.g. 20 s) so cold-cache queries don’t return 504s.

---

Single Sign-On: OIDC Without Touching the Proxy

The archive contains potentially sensitive history, so anonymous access must be denied and team members should log in with their existing corporate account. The clean way is OIDC against your identity provider (Authentik, Keycloak, Okta, Entra ID — any OpenID Connect provider).

Two common approaches:

1. Reverse-proxy auth — the proxy (nginx, HAProxy, Traefik) handles OIDC and passes the identity via headers. Powerful, but it changes your proxy configuration and can conflict with other routes.
2. App-level OIDC plugin — the application itself implements the OIDC client. No proxy changes at all. This is what we chose.

For Datasette there was no maintained OIDC plugin, so we wrote a small one (~150 lines) modeled on the well-known datasette-auth0 plugin (the same pattern the datasette-auth-osm project uses). It’s tiny because it reuses the platform’s own primitives:

• datasette.sign() for signing cookies and the OIDC state parameter
• datasette.set_cookie() with httponly, secure, samesite="lax"
• the standard actor_from_request hook to set the logged-in actor

The OIDC flow in 100 lines

import secrets
import httpx
from datasette.utils.asgi import Response

AUTH_STATE_COOKIE = "auth-state"

async def start_login(datasette, request):
    # 1. Generate a random state, sign it into a cookie
    state = secrets.token_hex(16)
    signed = datasette.sign({"state": state}, namespace="authentik-oidc")
    response = Response.redirect(authorization_url(state))
    response.set_cookie(
        AUTH_STATE_COOKIE,
        signed,
        httponly=True,
        secure=True,
        samesite="lax",
    )
    return response

async def callback(datasette, request):
    # 2. Validate state (constant-time!), exchange code for tokens
    expected = datasette.unsign(request.cookies.get(AUTH_STATE_COOKIE),
                                namespace="authentik-oidc")
    if not expected or not secrets.compare_digest(
        expected["state"], request.args.get("state", "")
    ):
        return Response.text("Invalid state", status=400)

    code = request.args.get("code")
    token_resp = await _exchange_code(datasette, code)
    profile = await _fetch_profile(datasette, token_resp["access_token"])

    # 3. Set the signed actor cookie — the user is logged in
    actor = {"id": f"authentik-{profile['sub']}", "name": profile.get("name")}
    response = Response.redirect("/")
    response.set_cookie(
        "ds_actor",
        datasette.sign({"a": actor, "e": expiry}, namespace="actor"),
        httponly=True, secure=True, samesite="lax",
    )
    return response

The three security details that matter:

Detail	Why
Signed state param	Prevents login CSRF — an attacker can’t forge a callback
secrets.compare_digest	Constant-time comparison, no timing side-channels
HttpOnly + Secure + SameSite=Lax cookies	No JavaScript access, HTTPS only, CSRF-resistant

The endpoint gotcha that wasted an afternoon

Different identity providers expose their OIDC endpoints at different paths. Our provider served authorize, token, and userinfo without the application slug in the path — but the JWKS (JSON Web Key Set) URL with it. A naive implementation that derives endpoints by string-splicing the issuer URL breaks.

The robust approach: derive every endpoint from the discovery document (.well-known/openid-configuration) at startup, and never guess:

async def discover(config):
    async with httpx.AsyncClient(timeout=10) as client:
        r = await client.get(config["issuer"].rstrip("/") + "/.well-known/openid-configuration")
        r.raise_for_status()
        doc = r.json()
    return {
        "authorization_endpoint": doc["authorization_endpoint"],
        "token_endpoint": doc["token_endpoint"],
        "userinfo_endpoint": doc["userinfo_endpoint"],
    }

Group-gated access

Single sign-on alone isn’t enough — you also want authorization. The identity provider should enforce that only members of a specific group can log in to the archive:

• Create a group (e.g. archive-users) in the identity provider.
• Bind the app’s access policy to that group.
• Add/remove users from the group — no code changes, no redeploys.

In our setup, the app’s allow-rule is simply “any authenticated actor is admitted” — the provider decides who is allowed to authenticate. This keeps the app dumb and the policy centralized.

---

Putting It All Together

The cron job

A single cron entry drives the whole pipeline:

# Archive export + prune — runs nightly at 02:00
0 2 * * * root /opt/archive/run.sh >> /var/log/archive.log 2>&1

And run.sh is deliberately boring:

#!/bin/bash
set -euo pipefail
python3 /opt/archive/archive_sessions.py        # incremental export + FTS rebuild
python3 /opt/archive/archive_sessions.py --prune  # remove old, archived sessions

The complete schema, in one file

Here’s the full archive bootstrap SQL — copy it, adapt the columns, and you’re running:

PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS session (
    id          INTEGER PRIMARY KEY,
    title       TEXT,
    agent       TEXT,
    model       TEXT,
    created_at  INTEGER,
    updated_at  INTEGER
);

CREATE TABLE IF NOT EXISTS message (
    id          INTEGER PRIMARY KEY,
    session_id  INTEGER NOT NULL REFERENCES session(id) ON DELETE CASCADE,
    role        TEXT,           -- 'user' | 'assistant' | 'tool'
    content     TEXT,
    created_at  INTEGER
);

CREATE TABLE IF NOT EXISTS session_search (
    id            INTEGER PRIMARY KEY,
    title         TEXT,
    agent         TEXT,
    model         TEXT,
    body          TEXT,
    time_updated  INTEGER,
    message_count INTEGER
);

CREATE TABLE IF NOT EXISTS archive_meta (
    key   TEXT PRIMARY KEY,
    value TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS session_search_fts USING fts5(
    title, agent, model, body,
    content='session_search',
    content_rowid='id'
);

-- (add the three sync triggers from Chapter 4 here)

The full export flow, end to end

def run_export(live_path, archive_path):
    live = sqlite3.connect(f"file:{live_path}?mode=ro", uri=True)
    archive = sqlite3.connect(archive_path)

    watermark = get_watermark(archive)
    for batch in fetch_new_sessions(live, watermark):
        for s in batch:
            scrub_and_export(live, archive, s)   # redaction happens here
        rebuild_search_rows(archive)             # refresh session_search
        archive.commit()
        set_watermark(archive, batch[-1]["updated_at"])
        archive.commit()

    archive.execute("INSERT INTO session_search_fts(session_search_fts) VALUES ('optimize')")
    archive.commit()
    print("Export complete.")

---

Lessons Learned

If you build something like this, here are the lessons we wish we’d known from day one:

1. Use WAL mode everywhere. Read-only archives and concurrent writers stop being a problem entirely.
2. INSERT OR REPLACE + watermark = free idempotency. Re-running the job is always safe; that makes cron failures boring instead of scary.
3. Run 'optimize' on your FTS index after every export. Fragmentation is the silent killer of search performance. Our COUNT(*) went from 20 s to 3 ms.
4. Redact before you store, not after. A secret that never enters the archive can never leak from it. Scrubbing at query time is a trap.
5. Omit tool inputs/outputs entirely. That’s where secrets live. Keep names and truncated redacted snippets.
6. Prune only what’s archived. The “never delete unarchived data” rule is the difference between an archive and a data-loss incident.
7. Keep a recency floor. Always keep the N most recent sessions, whatever their age.
8. Derive OIDC endpoints from discovery, never from string splicing. Providers differ in ways that will waste your afternoon.
9. Set generous SQL time limits for cold caches. A 20-second limit costs nothing and prevents scary 504s after a restart or a big import.
10. Make the UI look like the tool. A chat-style frontend is instantly familiar to anyone who uses AI assistants — adoption follows.

---

Conclusion

You don’t need a search cluster to build a genuinely useful conversation archive. With SQLite, FTS5, one Python script, cron, and a small web layer, you get:

• A live database that stays fast and small
• A compact, full-text-searchable archive that keeps everything forever
• A chat-style UI your whole team will actually use
• SSO-protected access with group-based authorization
• Zero vendor lock-in, zero recurring cost, and full control of your data

The full pipeline — export, redact, index, serve, search — is about 500 lines of Python plus a small frontend. It runs unattended every night, and it turns a growing pile of conversation history into a searchable institutional memory.