What's Inside
- Where an occasionally connected platform's design survives
- Originals, UTF-8 extractions, and a manifest
- A schema that splits text from retrieval history
- A Python importer that hashes bytes and text separately
- Querying "occasionally connected" with source type and date in view
- Following a search hit back to the saved original
Where an Occasionally Connected Platform's Design Survives
How do you reconstruct an occasionally connected.NET application platform when its design survives only in pieces? Some of it is in manuals, some in release notes, some in developer presentations, and some in trade coverage written at the time. Each piece was written for a different reader at a different moment.
The four document types record different things. A manual describes intended behavior. A release note identifies what changed in a particular release. A developer presentation explains the architecture to a specific audience. Contemporary commentary gives an outside reading of all of it. A careful researcher keeps these voices apart, and also keeps two fields apart that are easy to merge: the date a document was published and the platform version it describes.
Here is a concrete task. Find a passage about offline synchronization. Then work out which version of the platform that passage describes.
A keyword hit only solves the first half. The second half depends on what surrounds the passage, such as the title page, a revision history, a release heading, or an explicit version statement. A small local corpus built on SQLite FTS5 can keep all of that context attached to every match.
Originals, UTF-8 Extractions, and a Manifest
Prepare the files before writing any import code. Save each original. Extract its text into a separate UTF-8 file. Record how the two relate in a JSON manifest. Done this way, extraction becomes a step you can repeat and inspect, and it never happens silently inside the importer.
For a PDF that has a text layer, extract the text. For scanned pages, run OCR first. Before you accept an extraction, open the original next to it and check three places: the synchronization heading, the version statement next to it, and the page boundary.
archive/
originals/
synchronization-manual.pdf
release-notes.html
text/
synchronization-manual.txt
release-notes.txt
manifest.json
schema.sql
import_corpus.py
corpus.sqlite
The filenames above describe prepared inputs. They are placeholders, not a list of documents known to survive. The manifest is a JSON array. Each object in it carries these fields:
- title and source_kind, which is one of
primary_specification,primary_release_note,primary_presentation, orsecondary_commentary. - published_date, written as YYYY, YYYY-MM, or YYYY-MM-DD, with a matching date_precision of year, month, or day. If the date is unknown, set both fields to null.
- retrieved_at, the actual UTC capture time in ISO 8601 format.
- original_url and archive_url. Leave these null when you don't have them rather than guessing a historical address.
- original_path and text_path, both relative to the manifest's directory.
A Schema That Splits Text From Retrieval History
The design rests on two definitions. A document is one distinct sequence of original bytes. A capture is one recorded place and time where those bytes were retrieved. The searchable title and body belong to the document. Provenance belongs to the capture.
This split matters most when the same manual turns up twice. If each retrieval location were indexed as its own document, a mirrored copy would look like a second discovery. Keying captures to the original file's SHA-256 avoids that and still keeps both retrieval histories.
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS documents (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
raw_sha256 TEXT NOT NULL UNIQUE,
text_sha256 TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS documents_text_sha256
ON documents(text_sha256);
CREATE TABLE IF NOT EXISTS captures (
id INTEGER PRIMARY KEY,
document_id INTEGER NOT NULL REFERENCES documents(id),
capture_key TEXT NOT NULL UNIQUE,
source_kind TEXT NOT NULL CHECK (source_kind IN (
'primary_specification', 'primary_release_note',
'primary_presentation', 'secondary_commentary'
)),
published_date TEXT,
date_precision TEXT CHECK (date_precision IN ('year','month','day')),
retrieved_at TEXT NOT NULL,
original_url TEXT,
archive_url TEXT,
original_path TEXT NOT NULL,
text_path TEXT NOT NULL,
CHECK (
(published_date IS NULL AND date_precision IS NULL) OR
(published_date IS NOT NULL AND date_precision IS NOT NULL)
)
);
CREATE INDEX IF NOT EXISTS captures_document_id
ON captures(document_id);
CREATE VIRTUAL TABLE IF NOT EXISTS documents_fts
USING fts5(title, body);
Only raw_sha256 is unique. Two different files can produce the same extracted text, for example two editions, two layouts, or two runs of the extractor. For that reason the text hash is indexed but not enforced as unique.
The trade-off is deliberate. A document keeps the title and extraction from its first import. A later capture of the same bytes adds provenance without quietly replacing that indexed text.
A Python Importer That Hashes Bytes and Text Separately
The importer runs the schema first. Creating the virtual table also tests whether FTS5 is available before any source is touched. If schema creation reports that the fts5 module is unavailable, switch to an SQLite build with FTS5 enabled before running the importer.
import hashlib
import json
from pathlib import Path
import sqlite3
import sys
manifest_path = Path(sys.argv[1]).resolve()
schema_path = Path(sys.argv[2]).resolve()
db_path = Path(sys.argv[3]).resolve()
base = manifest_path.parent
items = json.loads(manifest_path.read_text(encoding='utf-8'))
fields = (
'source_kind', 'published_date', 'date_precision', 'retrieved_at',
'original_url', 'archive_url', 'original_path', 'text_path'
)
def sha256(data):
return hashlib.sha256(data).hexdigest()
con = sqlite3.connect(db_path)
con.execute('PRAGMA foreign_keys = ON')
try:
con.executescript(schema_path.read_text(encoding='utf-8'))
with con:
for item in items:
original = (base / item['original_path']).resolve()
extracted = (base / item['text_path']).resolve()
raw_hash = sha256(original.read_bytes())
body = extracted.read_text(encoding='utf-8')
text_hash = sha256(body.encode('utf-8'))
found = con.execute(
'SELECT id FROM documents WHERE raw_sha256 = ?',
(raw_hash,)
).fetchone()
if found is None:
cur = con.execute(
'INSERT INTO documents '
'(title, body, raw_sha256, text_sha256) '
'VALUES (?, ?, ?, ?)',
(item['title'], body, raw_hash, text_hash)
)
document_id = cur.lastrowid
con.execute(
'INSERT INTO documents_fts(rowid, title, body) '
'VALUES (?, ?, ?)',
(document_id, item['title'], body)
)
else:
document_id = found[0]
capture = {field: item.get(field) for field in fields}
capture['original_path'] = original.relative_to(base).as_posix()
capture['text_path'] = extracted.relative_to(base).as_posix()
identity = dict(capture, raw_sha256=raw_hash)
capture_key = sha256(json.dumps(
identity, sort_keys=True, ensure_ascii=False,
separators=(',', ':')
).encode('utf-8'))
con.execute(
'INSERT INTO captures '
'(document_id, capture_key, source_kind, published_date, '
'date_precision, retrieved_at, original_url, archive_url, '
'original_path, text_path) '
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) '
'ON CONFLICT(capture_key) DO NOTHING',
(document_id, capture_key,
*(capture[field] for field in fields))
)
finally:
con.close()
Run it from the archive directory:
python import_corpus.py manifest.json schema.sql corpus.sqlite
The importer computes two hashes, and they cover different things:
- Raw hash: covers the original bytes exactly as saved.
- Text hash: covers the decoded text re-encoded as UTF-8. Because Python's text reader normalizes line endings, two extractions that differ only in line endings produce the same text hash.
- SQL safety: every value is passed as a parameter, and all document, FTS, and capture writes share one transaction.
The capture key comes from the original hash plus the normalized capture metadata. It does not use a counter that increases with each run. As a result, importing an unchanged manifest a second time leaves the counts where they were. A new archived URL, local path, or genuine retrieval time adds a new capture under the same document ID.
Rerun, Don't Restamp: keep the recorded retrieved_at value unchanged when you simply rerun the import. If you overwrite it, the same capture turns into a new and false one.
Two different originals can still share an extracted-text hash. Treat those pairs as review candidates and merge nothing automatically, because extraction can throw away layout differences:
SELECT text_sha256, COUNT(*) AS originals, GROUP_CONCAT(id) AS document_ids
FROM documents GROUP BY text_sha256 HAVING COUNT(*) > 1;
Querying "Occasionally Connected" With Source Type and Date in View
Start with the historical wording, then widen the search to general synchronization terms. The query below joins the FTS table back to documents and captures, so every match keeps its full context.
sql = '''
SELECT d.id, d.title,
snippet(documents_fts, 1, '[', ']', ' … ', 24) AS excerpt,
bm25(documents_fts, 5.0, 1.0) AS score,
c.source_kind, c.published_date, c.date_precision,
c.retrieved_at,
COALESCE(NULLIF(c.archive_url, ''), c.original_path) AS location,
c.original_path AS saved_original
FROM documents_fts
JOIN documents AS d ON d.id = documents_fts.rowid
JOIN captures AS c ON c.document_id = d.id
WHERE documents_fts MATCH ?
ORDER BY score, d.id, c.retrieved_at
'''
phrase_rows = con.execute(sql, ('"occasionally connected"',)).fetchall()
term_rows = con.execute(
sql, ('synchronization OR synchronisation OR "offline sync"',)
).fetchall()
The snippet returns up to 24 tokens from the body column. The bm25 function weights the title at 5.0 and the body at 1.0, and lower scores sort first. Those weights only tune retrieval. They say nothing about how authoritative a document is historically. For the full operator grammar, see the SQLite FTS5 query syntax and ranking documentation.
To filter by source type, add AND c.source_kind = ? just before ORDER BY and bind primary_specification. Then run the same query again with secondary_commentary. To limit results to a date range, add AND c.date_precision = 'day' AND c.published_date >= ? AND c.published_date < ?, binding an inclusive start date and an exclusive end date. Search year-only and month-only records separately. Padding them out to a specific day would invent a date the record doesn't have.
When you display results, group the rows by d.id. Show the title and excerpt once, and list the captures underneath. A mirrored manual should look like one passage with two retrieval histories, not two separate findings.
To check the build, import the unchanged manifest twice and compare the document and capture counts after each run. Then confirm that every document has an index entry:
SELECT d.id FROM documents AS d
LEFT JOIN documents_fts AS f ON f.rowid = d.id
WHERE f.rowid IS NULL;
Following a Search Hit Back to the Saved Original
A few habits keep the archive trustworthy:
- Keep every original file.
- Leave incomplete publication dates incomplete.
- Keep specifications clearly separate from commentary in anything you export.
- When you deduplicate by original bytes, keep the capture history.
Back up the whole directory together after the importer has exited and closed its connection. That means the originals, the extracted text, the manifest, schema.sql, and the importer, all next to corpus.sqlite. Keeping the same relative layout means the saved paths still resolve after a restore.
Every FTS match carries a rowid that joins it back to one document and to each saved record of where that document came from. The saved original contains the page where the version statement can be checked; neither the search ranking nor the publication date supplies that statement.

Join the Conversation
Share your thoughts.
Your Comment