Sealed Archive SQL Schema Reference

SALT's central claim is that anyone can verify a sealed archive without proprietary tools. That claim is only meaningful if the schema is documented and freely available — otherwise a reviewer would still depend on Sealed Ledger to interpret what's inside.

So we publish it. Every table, column, index, and full-text search definition that's part of the query contract. A SALT-sealed bundle is a SQLite 3 database; a reviewer with sqlite3, openssl, this page, and the schema file has everything they need.

Schema versions are recorded in the _schema table inside every bundle so a reviewer can always tell which version a particular archive uses.

The schema is organized into eight logical groups:

• Identity & self-documentation — 7 tables

  Capture metadata, integrity checks, and the in-database self-documentation tables (per-table narratives and per-column provenance). The cryptographic seal record itself lives in the bundle's manifest.json, not in the .db.

  _schema · _schema_doc · _company · _capture · _qbo_diagnostics · _integrity_checks · _field_provenance
• Master data — 15 tables

  Reference entities carried forward from the source system. Each table mirrors the source-system shape with provenance columns added.

  accounts · contacts · items · tracking_categories · departments · payment_methods · terms · tax_agencies · tax_codes · tax_rates · customer_types · company_currencies · exchange_rates · preferences · projects
• Transactions — 20 tables

  One table per source-system transaction type. Reviewers can query a single transaction type directly, or use the unified transaction_index to query across all types.

  invoices · bills · bill_payments · budgets · credit_memos · deposits · estimates · inventory_adjustments · journal_entries · payments · bank_transactions · purchase_orders · refund_receipts · sales_receipts · time_activities · transfers · vendor_credits · credit_card_payments · attachments · recurring_transactions
• Lines, linkage & index — 4 tables

  Polymorphic line items, linked-transaction relationships, the journal-line spine (debits and credits at the line level), and the unified transaction index that lets a reviewer query every transaction regardless of type.

  transaction_lines · linked_txns · journal_lines · transaction_index
• Pre-computed analytics — 5 tables

  Materialized at capture time so reviewers don't have to recompute them: open AR/AP with aging, duplicate clusters, full source-system report payloads, and the migration-plan workspace.

  open_ar_items · open_ap_items · duplicate_clusters · report_snapshots · migration_plan
• Audit log — 2 tables

  Outside the capture boundary. QuickBooks Online does not expose its audit log through the API, so Sealed Ledger cannot retrieve it and does not attest to it. These tables exist to hold an audit-log export the customer supplies themselves (typically a QBO CSV), cross-checked against the captured data. In a bundle where no such file was supplied — which is the default — both tables are empty.

  When a customer-supplied log is loaded, the seal covers the file as received. It does not attest that the file is complete, authentic, or unaltered before it reached Sealed Ledger. audit_log_meta records the source format, the file's hash, parseable counts, and the customer's attestation so a reviewer can weigh it accordingly. Every other table in the archive carries API-captured provenance; these two do not, and a reviewer should treat them differently for that reason.

  audit_log_entries · audit_log_meta
• Extension tables — 6 tables

  Platform- and region-specific fields kept out of the base tables so the core schema stays portable across source systems. Each extension's primary key matches its base table's primary key one-to-one, and extensions never duplicate columns that already exist in the base table. A bundle captured from a source system that has no such fields simply leaves the corresponding extension table empty.

  qbo_preferences · qbo_entitlements · accounts_qbo_extensions · bank_transactions_qbo_extensions · employee_extensions · contacts_us_tax_extensions
• Full-text search — 3 virtual tables

  FTS5 virtual tables that let reviewers search transactions, master data, and line items by free text. SQLite generates internal storage tables behind each virtual table automatically — those are implementation details, not part of the query contract.

  search_transactions · search_lists · search_lines

A SALT bundle's snapshot.db file is a standard SQLite 3 database. Open it with any SQLite-compatible tool — sqlite3 on the command line, DB Browser for SQLite, DBeaver, or your tool of choice. No special drivers or libraries required.

A few orientation queries

-- Identify the archive
SELECT * FROM _company;
SELECT * FROM _capture;

-- Inspect the seal record. Nothing seal-related lives inside the .db
-- (the .db is what the seal signs; writing the seal back in would
-- change the .db's hash). Open the bundle's manifest.json instead:
--   jq '.captureSeal' manifest.json
-- And confirm the materializer-stamped schema version + canonical DDL:
SELECT schema_version, materialized_at FROM _schema;
SELECT length(canonical_ddl) AS ddl_bytes FROM _schema;

-- Read what each table is for, and where each column came from
SELECT table_name, category, description FROM _schema_doc ORDER BY category, table_name;
SELECT table_name, column_name, source, source_path FROM _field_provenance LIMIT 20;

-- Surface any failed integrity checks
SELECT check_name, status, severity, actual_value
FROM _integrity_checks
WHERE status != 'pass';

-- See every transaction in one query, regardless of type
SELECT entity_type, txn_date, doc_number, total_cents
FROM transaction_index
ORDER BY txn_date DESC
LIMIT 100;

-- Open AR by aging bucket
SELECT age_bucket,
       COUNT(*) AS items,
       SUM(balance_cents) / 100.0 AS total_dollars
FROM open_ar_items
WHERE balance_cents > 0
GROUP BY age_bucket
ORDER BY MIN(days_overdue);

-- Verify a sample row's provenance hash hasn't been tampered with
-- (the recomputed SHA-256 must match _raw_json_sha256)
SELECT id,
       _raw_json_sha256        AS recorded_hash,
       _source_record_hash     AS canonical_hash
FROM invoices
LIMIT 5;

Free-text search

Three FTS5 virtual tables make it easy to search the archive without knowing exact field names:

-- Find any transaction mentioning "banjo" anywhere in
-- doc number, party name, or memo
SELECT * FROM search_transactions
WHERE search_transactions MATCH 'banjo'
LIMIT 20;

-- Find any master-data entity (account, customer, vendor, item)
-- mentioning "shipping"
SELECT * FROM search_lists
WHERE search_lists MATCH 'shipping'
LIMIT 20;

-- Find any line item (transaction line) mentioning "warranty"
SELECT * FROM search_lines
WHERE search_lines MATCH 'warranty'
LIMIT 20;

Reviewer notes for schema diffs

If you compare the live snapshot.db schema (via sqlite3 snapshot.db .schema) against the published schema.sql, you will see additional objects in the live database that are not defined in the published file. These are auto-generated by SQLite and are not part of SALT's contract:

• search_transactions_data, search_transactions_idx, search_transactions_content, search_transactions_docsize, search_transactions_config — and the equivalents for search_lists and search_lines. These are FTS5's internal storage tables, generated automatically by each CREATE VIRTUAL TABLE … USING fts5(…) statement. They contain inverted indexes and segment metadata, not accounting data, and they are not queryable in any meaningful sense. Use the three named virtual tables instead.
• sqlite_stat1 and sqlite_stat4 — query-planner statistics populated by SQLite's ANALYZE command. They affect query performance but contain no data.
• sqlite_sequence — internal counter for AUTOINCREMENT columns.

A clean diff that ignores those should produce no output. The published schema is the contract; the auto-generated artifacts are SQLite's implementation.

For full cryptographic verification of the seal itself — recomputing the database hash and validating the RFC 3161 timestamp — see the verification procedure on the SALT white paper page.