Reference for documentation.db, the SQLite database backing all in-app help: Tier 1/2 tooltips, plus the Tier 3 web content they link to, served in-process by DocumentationRequestInterceptor with WebServer as the HTTP fallback. Read this before touching anything under localWebServer/, idetooltips/, or plugin-manager/.../documentation/, or before writing/editing SQL against this database.
This is a prebuilt database — CoGo never creates or migrates its schema at runtime (see ADR 0001, exception 3). Every consumer reads it; one, PluginDocumentationManager, also writes rows into it, inserting the tooltips and Tier 3 content a plugin contributes into tables it does not own (see How CoGo talks to this database below). The schema is owned by the separate OfflineDocumentationTools project (the docdb-studio tool); never change it from this repo.
- Installed path:
context.getDatabasePath("documentation.db")(Environment.DOC_DBincommon/.../utils/Environment.java), i.e. the app's privatedatabases/dir. - Bundled as an asset and extracted by
BundledAssetsInstaller/SplitAssetsInstallerat first-time setup only:InstallationViewModelruns the asset install while no JDK or Android SDK is installed, and returns early once both are. An app update keeps the installed file whatever the new APK bundles, so an install first set up with a version 2 database keeps it, and from documentation version 3 on (ADFA-3305) shows no tooltips; clearing the app's data is what gets it the bundled file. That is accepted for ADFA-3305. - Debug override: if
/sdcard/Download/documentation.dbexists and is newer than the installed copy,DocumentationContentSource(behind both Tier 3 transports) andToolTipManagerswap to it at request time (timestamp-compared per request, not just at startup) — a fast way to test a new database on-device without reinstalling. The comparison is on modification time, andadb pushpreserves the source file's mtime -- so pushing a database saved earlier than the one already on the device silently does not swap, and the app keeps serving the old one with no error anywhere. Follow a push withadb shell touch /sdcard/Download/documentation.db(this cost real debugging time on ADFA-5153). A version 2 file pushed there now shows no tooltips: this build refuses tooltip tables below version 3, and the log line names the debug database and its path. Convert a copy withOfflineDocumentationTools'migrate_tooltip_schema.pybefore pushing it.WebServer's debug logging and experiment flags are also file-flag-gated under/sdcard/Download/(CodeOnTheGo.webserver.debug,CodeOnTheGo.exp,CodeOnTheGo.webserver.cs0). - Don't trust a local copy's on-disk schema or row content as ground truth without checking freshness first. Any manually downloaded or debug-override copy is independent of git history — a stale one can have a different schema (e.g. missing
UNIQUE(path)ortemplateId) or be missing rows that already exist in the current, maintained database. A stale copy caused a real near-miss in ADFA-5088: a SQL script validated against it would have silently overwritten curated production tooltip content for several tags. Diff or re-download before authoring SQL against a local copy's state, not just before shipping it.
A star schema: a large fact table at the center, small dimension tables around it. There are two fact tables — Content (Tier 3 web content) and Tooltips (Tier 1/2 tooltips) — because they serve different lookup patterns.
CREATE TABLE Content (
id INTEGER PRIMARY KEY AUTOINCREMENT,
path TEXT NOT NULL,
languageID INTEGER NOT NULL,
content BLOB NOT NULL,
contentTypeID INTEGER NOT NULL,
templateId INTEGER,
FOREIGN KEY (languageID) REFERENCES Languages(id),
FOREIGN KEY (contentTypeID) REFERENCES ContentTypes(id),
UNIQUE(path)
);One row per file the web server can serve (HTML, CSS, JS, image, video, PDF, ...) — 30,000+ rows. Key points:
pathis the lookup key (indexed via theUNIQUEconstraint) and is whatWebServermatches the HTTP request path against. Paths carry a short source prefix to avoid collisions between doc sets, e.g.k/index.html(Kotlin) vsj/index.html(Java).contentis compressed — Brotli for text-like formats, format-specific compression otherwise (images/video/fonts).ContentTypes.compressionsays which. In a database declaringMAJOR >= 2, every row withContentTypes.compression = 'brotli'is compressed against the single shared dictionary inCompressionDictionary(see below): the bulk of them converted in one pass by ADFA-5153, and plugin-contributed Tier 3 rows written that way at install time byPluginDocumentationManager(ADFA-5240). So the compression of a row follows from the database's declared version alone — there is no per-row flag, and none is needed. That matters because the two are not distinguishable by inspection, and only half-distinguishable by decode: attaching no dictionary to a stream that needs one reliably throws (IOException, "corrupted input"), since distances into the dictionary region are out of bounds for any spec-compliant decoder. Attaching the wrong dictionary is not reliable in either direction -- one of a different length, or with nothing valid at the offsets the stream references, usually throws, but one of the same length holding plausible bytes there decodes cleanly to content that is simply wrong (both cases verified inBrotliDictionaryDecodeTest). Never use decode success, or failure, as evidence about which dictionary was used. Content over 1 MB is split across multiple rows: the first row's path is the base path, continuation rows arepath-1,path-2, ... (languageId = 1), reassembled byDocumentationContentSourcebefore returning.templateId:0(or unset) meanscontentis legacy HTML with presentation baked in (the pre-CMS Release 0/1 format). A positive value meanscontentis JSON facts only, rendered through the matching row inTemplates(a Pebble template) — the ongoing move to a proper CMS that de-duplicates presentation across near-identical pages (e.g.sin/cosdocs).- The
UNIQUE(path)constraint rejects any duplicatepath, regardless oflanguageID— a second language for an existing path isn't supported yet (onlyEN-uscurrently exists). Getting there needs an upstream schema change to composite uniqueness on(path, languageID)(see Known rough edges below).
Dimensions: Languages(id, value) (4-letter codes, e.g. EN-us); ContentTypes(id, value, compression) (MIME type + compression scheme, ~30 rows).
The shape below is documentation version 3 (ADFA-3305), written by OfflineDocumentationTools' migrate_tooltip_schema.py. Version 2 kept the text in Tooltips (an AUTOINCREMENT table with a separate UNIQUE(categoryId, tag) index) and description/uri in every TooltipButtons row; this build reads and writes neither, and refuses a database declaring less than 3 (DatabaseVersionResolver.requireKeyedTooltips).
CREATE TABLE TooltipTexts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
summary TEXT NOT NULL,
detail TEXT NOT NULL
);
CREATE TABLE Tooltips (
id INTEGER NOT NULL,
categoryId INTEGER NOT NULL,
tag TEXT NOT NULL,
PRIMARY KEY (categoryId, tag),
FOREIGN KEY(categoryId) REFERENCES TooltipCategories(id),
FOREIGN KEY(id) REFERENCES TooltipTexts(id)
) WITHOUT ROWID;
CREATE TABLE TooltipButtons (
tooltipId INTEGER NOT NULL,
buttonNumberId INTEGER NOT NULL,
descriptionId INTEGER NOT NULL,
uriId INTEGER NOT NULL,
PRIMARY KEY (tooltipId, buttonNumberId),
FOREIGN KEY(tooltipId) REFERENCES TooltipTexts(id),
FOREIGN KEY(buttonNumberId) REFERENCES TooltipButtonNumbers(id),
FOREIGN KEY(descriptionId) REFERENCES TooltipButtonDescriptions(id),
FOREIGN KEY(uriId) REFERENCES TooltipButtonUris(id)
) WITHOUT ROWID;
-- plus TooltipButtonDescriptions(id INTEGER PRIMARY KEY, description) and TooltipButtonUris(id INTEGER PRIMARY KEY, uri)summaryis Tier 1 (the initial popup),detailis Tier 2 (after "See more"); both may contain HTML.- Looked up by
(categoryId, tag), which the IDE stamps on the UI widget that owns the tooltip.Tooltipsis keyed on exactly that, so the lookup is a search of the table itself, and the text is one more lookup by id inTooltipTexts. The text lives apart because aWITHOUT ROWIDtable is an index B-tree: whole rows in its interior pages and any row over 1,002 bytes (at 4 KB pages) spilled to a mostly empty overflow page, which cost 1.18 MB on the 2026-10-18 database. - A new tooltip takes its id from
TooltipTexts(AUTOINCREMENT, so never one a deleted tooltip had);Tooltipshas no rowid, so an insert into it returns none. TooltipCategories(id, category)is a tiny dimension table (four categories at the time of writing).TooltipButtonsholds the Tier 3 links shown at the bottom of a Tier 2 tooltip, keyed on(tooltipId, buttonNumberId), so a tooltip's buttons are one range of the key, already in display order. Each distinct description and uri is stored once, inTooltipButtonDescriptionsandTooltipButtonUris("View full documentation" alone used to be stored 43,703 times). A uri should resolve to aContent.path(after stripping?query/#fragment). Neither lookup table has aUNIQUEindex, which would cost about as much again; writers look a string up before inserting it.TooltipButtonNumbers(id)exists only to pin a fixed, manually-assigned display order when a tooltip has multiple Tier 3 links. (Flagged in the source design doc as something worth redoing without a whole extra table.)
DocumentationDatabaseVersion(major, minor, patch, who, comment, changeTime)— the database's own semver (ADFA-5220), replacing the heuristics that used to infer the format from which tables happened to exist. Exactly one row: the version the file is, whichpopulate_db.pyreplaces rather than appends. Nothing in the DDL enforces that, so both readers (DatabaseVersionResolver.resolveMajorVersionhere,database_major_versionin docdb-studio) take the newest row rather than the one with the highestmajor(the app orders bychangeTime DESC, rowid DESC, so the greatestchangeTimewins androwidonly breaks ties) — a rebuild from an older content set is a downgrade and has to read as one, whichMAX(major)would get wrong. The app's reader additionally logs a warning when it finds more than one row; docdb-studio's does not.populate_db.pycollapses a file that somehow accumulated several back to one, andresolveMajorVersionreturns null for a database predating the table.MAJOR >= 3says the tooltip tables have the shape above and every template is compressed (seeTemplatesbelow).MAJOR >= 2is what tells the app its brotliContentrows are dictionary-compressed; below that,DocumentationContentSourceneither reads nor attachesCompressionDictionary. Gating on the declared version rather than on the table's presence matters in both directions: a database can carry the dictionary table while its content is still plain brotli (every brotli row would then be decoded with a dictionary attached and fail outright, since ADFA-5240 removed the plain-decode retry that used to mask this), and a migrated database that lost the table fails loudly instead of quietly.CompressionDictionary(id, data)— single-row table (id INTEGER PRIMARY KEY CHECK (id = 1)) holding the raw Brotli dictionary every ADFA-5153-migratedcompression = 'brotli'Contentrow is compressed against. First trained from a representative sample across the wholeContenttable byOfflineDocumentationTools'migrate_content_to_dictionary_brotli.py/populate_db.py, and retrained since by itsremint_dictionary.py, which recompresses every brotliContentrow against the new dictionary in the same transaction (from documentation version 3, every template as well: OfflineDocumentationTools #48). A dictionary-compressed row or template is decodable only against the exact dictionary it was compressed with, so a re-mint is safe only when it carries every one of them across; replacing the dictionary alone would silently orphan them all. Shipping the dictionary insidedocumentation.dbitself, rather than as a separate bundled asset, keeps it version-locked to the content compressed against it. Both the reader and the writer load it throughloadCompressionDictionaryin:common, so they cannot disagree about whether a given database's brotli rows carry a dictionary — a row compressed against one the reader won't attach is undecodable, and vice versa.DocumentationContentSourceloads it lazily -- not at database open or swap time, but on the first content lookup after the active database changes, whatever that row's own compression, and only whenDocumentationDatabaseVersiondeclaresMAJOR >= 2(see above) -- and caches it from then on, reloading again only on the next database change (a swap can bring in a database with a different dictionary or none, so it can't stay cached across one). Each row is then decoded exactly once, with the dictionary attached or not according to that version; there is no retry, because there is no longer a second way a row might have been written.Templates(id, name, content)— Pebble template source. The DDL isid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, content BLOB NOT NULL, UNIQUE('name')— worth stating, because the single-quotedUNIQUE('name')reads like a constant rather than a column and has twice been taken for an absent constraint. SQLite resolves the quoted string to the column, so a duplicate name, a null name and a null body are all rejected by the database, and readers here do not re-check them.Content.templateIdnames a page's outermost template by id; every reference between templates is byname(ADFA-5405), which is also howWebServerreaches well-known templates likebookshelf, and how the content source reacheshelpmanual-jstopic.pebfor Help+Manual'sjstopicsfiles. So a template canextends,include,importorembedanother one by writing itsTemplates.nameverbatim ({% include "nav.peb" %}) — the table is a flat namespace, with no directories, prefixes or suffixes applied. A reference to a name with no row fails the request rather than rendering the name; before ADFA-5405 the latter is exactly what happened, silently, because the engine's loader treated the name it was handed as the template body. From documentation version 3 (ADFA-3305), everycontentis the template's UTF-8 text Brotli-compressed againstCompressionDictionary, like a brotliContentrow. No column says so:OfflineDocumentationToolscompresses every template when it builds the database for release, and nothing on the device writesTemplates. Below 3, and in a database declaring no version,contentis plain UTF-8 and is read as it is (expectsCompressedTemplates): an app update never replaces the installed database, so a 2.x file has to keep rendering its templated pages. In a 3.x file a template that does not decode fails the page that uses it, logged with the template's name and the database's path.Bookshelf(contentID, bookCategoryID, title, description)/BookCategories(id, category, description)— the Dynamic Bookshelf: one row per "book" (PDF or similar), linked to its Tier 3 page viacontentID->Content.id. Two DB triggers keepBookshelfin sync when a PDF row is inserted/deleted fromContent;title/descriptiondon't come from those triggers and must be set by hand. Non-PDF books need a separate ingestion path (plugin-provided, e.g. viaPluginDocumentationManager).LastChange(documentationSet, changeTime, who)— audit trail for edits made throughdocdb-studio; not shown to end users.DatabaseVersionResolverreads thedocumentationSet = 'wholedb'row to report the DB's build/edit stamp in debug logging, falling back to the most recent row of any set if'wholedb'is missing.- Misc
ide_tooltip_tableandPUCCtables are historical/example artifacts — not part of the live lookup paths above.
Of the five sites below, only DocumentationContentSource and ToolTipManager open the file, with SQLiteDatabase.openDatabase(..., OPEN_READONLY) — WebServer and the interceptor read through the content source, and PluginDocumentationManager is the one write path, opening OPEN_READWRITE to install plugin content (see ADR 0001 for why raw SQLite is justified here instead of Room).
common/.../documentation/DocumentationContentSource.kt— the one Tier 3 pipeline that reads this database: row lookup (a request target is matched raw first, since storedContent.pathvalues are percent-encoded, with a percent-decoded fallback on a miss — shared by both transports so they agree on which pages exist), chunked-row reassembly, dictionary-aware Brotli decode gated on the declared documentation version, and the database swaps — to a newer sdcard debug database, and reopening the installed file when an asset install rewrites it in place under the cached handle — under a read/write lock so several threads can read while a swap cannot close the handle under them. It also renders the rows that are Pebble template contexts (templateId > 0: thek/andi/pages in a 2.x database, thei/ones being Help+Manual's topic pages (ADFA-6618), and thea/pages too in a 3.x one) — resolving each template's references to other templates againstTemplates.nameas it goes, and decoding each template with the same dictionary as the rows — so both transports below serve finished pages and neither needs the template engine itself. All of that logic exists once (ADFA-5176). One more rule renders a row that isn't stored: Help+Manual's in-page navigation loadsjstopics/X.js, a JavaScript copy of topicX, and once the documentation pipeline stores each Help+Manual topic only as the JSON its pageX.htmlrenders from (ADFA-6618), a request forjstopics/X.jsthat matches no stored row, under either its raw or its percent-decoded path, is rendered from that page's JSON with thehelpmanual-jstopic.pebtemplate and served astext/javascript. It stays a 404 unless the page is one row rendered throughhelpmanual-topic.peband the database hashelpmanual-jstopic.peb; a duplicated page row is reported as ambiguous, like the page itself.common/.../documentation/DocumentationRequestInterceptor.kt— serves Tier 3 in-process for the app's WebViews (HelpActivity, the tooltip fragment,FAQActivity), throughWebViewClient.shouldInterceptRequest, so a page's assets cost a database read instead of a TCP connection each (ADFA-5176). It matches the samehttp://localhost:6174/...URL space, so the strings.xml entries,ToolTipManager's link builder and theDocumentationExtensioncontract need no changes; anything it declines — a/pr/endpoint, an unknown path, a failed read — falls through toWebServerunchanged. Both transports get theirContent-Typecharset from the same place,ContentTypeHeaders(ADFA-5241), so a row does not describe itself differently depending on which one served it. They answerRangedifferently, both validly: the interceptor answers one satisfiable range with a 206 andContent-Range(RangeResponse, ADFA-3305), because the WebView cuts an in-process body to the range itself and a 200 would then start mid-file;WebServerignoresRangeand sends the whole row with a 200. Set/sdcard/Download/CodeOnTheGo.nointerceptto force documentation back onto the server.app/.../localWebServer/WebServer.kt— serves Tier 3 over HTTP on port 6174, for WebViews that are not wired to the interceptor above and for the/pr/developer endpoints. It reads throughDocumentationContentSource, so it holds no database, template engine or decode logic of its own; what remains here is HTTP: request parsing, the/pr/pages, error responses, and the CSS/asset shortcuts. Also serves a Dynamic Bookshelf JSON payload (joiningContent/Bookshelf/BookCategories, rendered through thebookshelftemplate) and debug-only HTML dumps at/pr/db(LastChange, last 20 rows) and/pr/pr(recent projects, from a different database).idetooltips/.../ToolTipManager.kt— serves Tier 1/2, throughreadTooltipinTooltipQuery.kt, which runsqueryTooltipandqueryTooltipButtonsand builds the item shown. Looks up a tooltip by(category, tag): the category's id fromTooltipCategoriesfirst, so the row is found byTooltips' key rather than a scan of the whole table (ADFA-6597), then its text by id. Then its buttons for the Tier 3 links shown at the bottom, byTooltipButtons' key, with their text by id. Refuses a database declaring a version below 3.plugin-manager/.../documentation/PluginDocumentationManager.kt(withTier3AssetWalker.kt, and theDocumentationExtensioncontract inplugin-api) — lets plugins contribute their own help content into the same lookup paths, under the reservedplugin/<pluginId>/...prefix. Its tooltip SQL is inPluginTooltipTables.kt: a plugin's tooltips are replaced or removed by category inside one transaction, each button's text is looked up in or added to the lookup tables, and text no button uses any more is deleted afterwards. Brotli assets are compressed against the sameCompressionDictionarythe rest of the table uses, read from the database being written. When that dictionary can't be read the install is abandoned rather than written plain, and retried on the plugin's next activation.
Not a database reader, but beside them: common/.../documentation/OffDeviceRequests.kt keeps the documentation WebViews (HelpActivity, the tooltip fragment, FAQActivity) and the tooltip popup on the device (ADFA-6589). Each one's shouldInterceptRequest answers an http(s) request to any host but the device itself with an empty 204, after the interceptor and before the network, so requests to localhost still reach WebServer. Tapped off-device links, and an off-device URL passed to HelpActivity, go through UrlManager.openUrl's consent dialog to the browser. keepOnDevice turns Safe Browsing off, which would otherwise have Google Play services send Google hash prefixes of the addresses, charged to the app, even for requests the block answers locally; and it gives service-worker fetches the same block. That service-worker client is process-wide and stays set: from the first documentation WebView on, it covers every WebView in the app, plugins' included, and a plugin that sets its own replaces it only until the next documentation WebView or tooltip popup sets this one again. What the block cannot see: <link rel=preconnect> and dns-prefetch (or a Link: header), WebSockets, WebRTC, and the hops of a redirect. No documentation page uses them, and neither WebServer nor the interceptor redirects or sends Link:. It is not WebSettings.blockNetworkLoads, which would also block WebServer's /pr/ endpoints and the CodeOnTheGo.nointercept fallback.
Schema changes and data edits happen outside this repo, in OfflineDocumentationTools/docdb-studio (a Flet GUI over the master documentation.db). The master stays in documentation version 2: docdb-studio and the other OfflineDocumentationTools scripts read and write the version 2 tooltip tables, and migrate_tooltip_schema.py converts a copy of the master to version 3 for shipping. Conventions enforced there that matter if you're reasoning about data correctness here:
- The schema is locked —
docdb-studio's ownAGENTS.mdsays never change it. If a new column/table is genuinely needed, it's a cross-repo change coordinated with that project, not something to route around in CoGo. - Tooltip uniqueness is
(categoryId, tag); button uris (TooltipButtons.uriin the version 2 master,TooltipButtonUris.urionce converted) are validated there againstContent.path(post?query/#fragmentstripping) before being allowed into the database. - Every edit made through the tool updates
LastChangefor the affected documentation set, which is howDatabaseVersionResolver's debug logging can say what build of the docs is loaded.
Some tickets (e.g. ADFA-5088) ship a one-off .sql script under docs/docdb/ for a docdb-studio maintainer to run against the real database, rather than editing it directly through the tool. They run against the version 2 master, so they use its tooltip columns (Tooltips.summary, TooltipButtons.uri); never run one against a converted version 3 file. Gotchas found writing those scripts:
- The system SQLite has no JSON1 on some devices, and your desktop does.
JSON_OBJECT,JSON_GROUP_ARRAYand friends compile fine under thesqlite3CLI (3.44 ships JSON1 built in) and then fail at runtime withno such function: JSON_OBJECTon real hardware — reproduced on a Galaxy Note 20 Ultra, where the Dynamic Bookshelf was an HTTP 500 until the query was rewritten (ADFA-5179). Nothing on the desktop side of the fence will warn you. Write plain relational SQL and assemble the JSON in Kotlin, and treat any device-only 500 from a new query as this until proven otherwise. Applies to every reader of this database, not justWebServer: adocdb-studioscript, a tooltip query inToolTipManager, andPluginDocumentationManagerare all equally exposed. - Keep each
.systemline simple. The sqlite3 CLI's.systemdot-command can hit a content-dependent shell-parsing failure when a line chains multiple operators (;,&&,||, parentheses) — it reproduces for some input strings and not others, so it won't necessarily show up in a quick test. Stick to one plaincommand | pipe > fileper.systemline. .bail onis required forBEGIN/COMMITto actually mean atomic. Without it, a mid-script SQL error prints to stderr but the script keeps going — including reaching the finalCOMMIT, which then persists whatever succeeded before the error (verified empirically, not just documented behavior)..bailalso can't see.systemshell failures directly, so a failed or empty Brotli payload (which leaves its target file missing or zero-length) needs its own check: insert itsREADFILE()into a throwawayCREATE TEMP TABLEguarded byNOT NULL CHECK (length(content) > 0)immediately before the realContentinsert, turning that failure into a real SQL error.bailwill catch. Seedocs/docdb/ADFA-5088-preference-tooltips.sqlfor the working pattern.- Don't write Brotli payloads to bare
/tmp/*.brfilenames. A fixed, guessable name directly under world-writable/tmplets another local user pre-plant a symlink or race the write/read pair between the.system echo | brotliwrite and theREADFILE()read (CWE-377). Create an owner-only working directory instead —rm -rfit, thenmkdir -m 700it (the mode is set atomically at creation, with no window where it's briefly world-accessible) — write every payload under that directory, and remove it again beforeCOMMIT. See the same script for the working pattern.
- A Tier 3 page that references something off-device is a bug in the content, not the code. Since ADFA-6589 the documentation WebViews answer such a request with an empty local response, so the font, image or player is simply missing, and a tapped off-device link asks before opening in the browser (
OffDeviceRequests). If you see one while working in this area, it's a data problem to report upstream, not aWebServerbug to fix here. Content'sUNIQUE(path)constraint (rather thanUNIQUE(path, languageID)) means a second language for an existing path can't currently be added without a schema change upstream — multi-language content isn't fully wired yet even though theLanguagesdimension anticipates it.TooltipButtonNumbersis a whole table whose only job is pinning a manual sort order; a lighter-weight mechanism (e.g. an ordering column directly onTooltipButtons) would remove a table.