13. The database form and the bundle

13.1 Portability#

sql/doc.sql defines the document tables and sql/store.sql a team store's tables. Both run unchanged on SQLite, Dolt and PostgreSQL, under these rules:

No foreign keys; references are checked by validators, not by the database.
Enumerations are VARCHAR with CHECK (… IN (…)).
JSON values are canonical JSON in TEXT columns; booleans are INTEGER 0 or 1.
Every table has a primary key, and key columns are VARCHAR with a length, as MySQL-compatible engines require.
No table, column or index is named with a word reserved in SQLite, PostgreSQL or MySQL; so the formats' key, index, at, by, before and after are doc_key, idx, made_at, signer, before_hash and after_hash.

13.2 Tables#

Table	One row per	Notes
`works`	work	Its key and `same_as`.
`documents`	document	Every property of `document.json` as a column, plus `provenance`, `roster` and `package_hash`.
`witnesses`	witness	With `ord`, its position in `witnesses.json`.
`nodes`	node	With `seq`, its position in document order.
`pages`, `locations`, `refs`	page, location, reference
`corrections`	correction	The common properties as columns; the rest in `details`.
`node_ids`	retired ID or alias
`receipts`, `revocations`	receipt, revocation
`suspects`	suspect
`package_files`	asset, dataset file or derived file	Its path, SHA-256, size and whether its bytes were present. Bytes go to the asset store.
`citations`	cite run and reference	Derived on load.
`receipt_nodes`	node a receipt lists	Derived on load.

A 30-page paper is about 3,000 nodes, so 500 documents are about a million and a half rows, which SQLite and Dolt hold comfortably.

13.3 Load and unload#

Loading a package is a pure function of the package: each record becomes a row, an optional property absent from the record is NULL, and JSON-valued properties are stored as canonical JSON. Unloading writes each file back from its rows in the file's defined order, omitting optional properties that are NULL and optional files that have no rows, copies the other files from the asset store, and writes the manifest. Unloading gives the package back byte for byte, and its manifest hash with it; the suite checks this for every fixture.

13.4 The store tables#

store.sql adds what a team adds to its documents: roster_keys, projects, members, project_documents, reading_paths, layers, annotations, annotation_targets (derived, so a reader finds the annotations on a node), datasets, dataset_values, digitizations, views, policies, submissions and grades. Each keeps its record whole in a record or descriptor column, with the columns a query needs beside it.

13.5 The bundle#

A bundle, scienting-bundle/1, is one SQLite file that a reader or a server queries. It holds the tables of doc.sql and store.sql, then those of sql/bundle-sqlite.sql:

Table	Holds
`bundle_meta`	`name` and `value` pairs: at least `format` (`scienting-bundle/1`), `store`, `built_from` (the store revision) and `spec` (the version of these specifications).
`search`	An FTS5 index with one row per sentence, cell, equation outside a sentence and figure-text item, in document order: the document, the node, the unit, the context and the text. Withheld text is never indexed.
`embeddings_meta`	Reserved for embeddings with `sqlite-vec`: the model, its dimensions, and the rights use they were made under.

A search row's unit is sentence, cell, equation or figure-text. For a node, context is the kind of its parent, such as paragraph, heading, caption or row, and text is its normalized plain projection (3.6). An equation inside a sentence is searched as part of that sentence, so it has no row of its own. A figure-text item names its panel as the node, its role as the context and its normalized text as the text.

At team scale this is all the search a team needs. A team can download its bundle and use it offline. Because a bundle holds text, it is served only under the rights of 14, checked again whenever text is served.

Source: spec/13-database.md · raw