Application IDs, user_version, schema_version, and Database-as-File-Format Design

Learning outcomes

SQLite's stable file format makes a database attractive as an application document: one portable file can contain relational data, indexes, constraints, and metadata. But “this is a valid SQLite file” is not the same as “this is a compatible FieldNotes file.” Applications need their own identity and schema-version contract.

Three “version-ish” values with different owners

Keeping ownership clear prevents one of the most damaging migration anti-patterns: manually adjusting internal metadata to make an incompatible schema “look current.”

Assign an application ID intentionally

For the course lab we use decimal 1179537236, whose bytes correspond to the memorable marker FNOT. A real project should choose and document its identifier according to its file-format governance.

PRAGMA application_id = 1179537236;PRAGMA application_id;

This marker does not provide cryptographic authenticity. It is a fast format-identification field. A malicious or corrupted file can contain the same number, so still validate schema/integrity and trust boundaries appropriate to your application.

user_version is a migration contract you define

PRAGMA user_version = 12;PRAGMA user_version;

SQLite will happily store the integer but does not know that “12” means Chapter 12 or migration 12. Your migration code must define transitions such as 10→11 and 11→12, test them, and decide which older/newer versions can be opened read-only, migrated, or rejected.

schema_version is not your application migration number

PRAGMA schema_version;CREATE TABLE IF NOT EXISTS schema_cookie_probe(id INTEGER PRIMARY KEY);PRAGMA schema_version;DROP TABLE schema_cookie_probe;PRAGMA schema_version;

Schema changes alter SQLite's schema cookie so prepared statements can detect that their compiled schema assumptions may be stale. The current PRAGMA documentation warns that direct manipulation can cause incorrect behavior. Use DDL to change schema and user_version to record your migration level.

Unknown file workflow: validate before writes

open candidate file READ-ONLY
          |
          v
is it readable SQLite? ---- no ---> reject / report
          |
          yes
          v
application_id matches? --- no ---> wrong application file
          |
          yes
          v
user_version supported? --- newer ---> reject or read-only compatibility mode
          |
       older/current
          v
required schema objects + columns present?
          |
          +--- no ---> reject / repair / migration failure
          |
          yes
          v
integrity / foreign-key checks as policy requires
          |
          v
handoff to migration or normal application open

Validate the schema, not only the version integer

PRAGMA application_id;PRAGMA user_version;PRAGMA schema_version;SELECT name,typeFROM sqlite_schemaWHERE name IN ('site','device','maintenance_note','device_summary')ORDER BY type,name;PRAGMA table_xinfo('device');PRAGMA foreign_key_check;PRAGMA integrity_check;

A stale/corrupted/manually edited file can claim user_version=12 while missing required objects. Version metadata should select an expected schema; validation should confirm the expectation when opening untrusted or operationally important files.

Forward and backward compatibility are product decisions

Python file-identification script

from __future__ import annotationsimport sqlite3from pathlib import PathEXPECTED_APP_ID = 1179537236MAX_SUPPORTED_USER_VERSION = 12REQUIRED_TABLES = {"site", "device", "maintenance_note"}def inspect_fieldnotes(path: str) -> dict:    p = Path(path)    uri = p.resolve().as_uri() + "?mode=ro&cache=private"    con = sqlite3.connect(uri, uri=True)    try:        app_id = con.execute("PRAGMA application_id").fetchone()[0]        user_version = con.execute("PRAGMA user_version").fetchone()[0]        schema_version = con.execute("PRAGMA schema_version").fetchone()[0]        tables = {            row[0] for row in con.execute(                "SELECT name FROM sqlite_schema WHERE type='table'"            )        }        integrity = con.execute("PRAGMA integrity_check").fetchone()[0]        return {            "application_id": app_id,            "user_version": user_version,            "schema_version": schema_version,            "identity_ok": app_id == EXPECTED_APP_ID,            "version_supported": user_version <= MAX_SUPPORTED_USER_VERSION,            "required_tables_ok": REQUIRED_TABLES <= tables,            "integrity": integrity,        }    finally:        con.close()print(inspect_fieldnotes("fieldnotes.db"))

This script deliberately opens the candidate read-only. A later migration layer can decide whether to create a backup, acquire a write connection, migrate, and update user_version.

Lab: create one valid and one misleading file

-- Valid lab file metadata after creating the FieldNotes schema:PRAGMA application_id = 1179537236;PRAGMA user_version = 12;-- In a separate disposable file, create no FieldNotes tables but set the same metadata.PRAGMA application_id = 1179537236;PRAGMA user_version = 12;-- A robust inspector must still detect the missing required schema objects.

The point is the data contract: identity + supported version + expected schema + health checks. Any one field by itself is too weak.

Designing SQLite as an application document format

A well-designed SQLite-backed document format gains transactions, constraints, indexing, queryability, and portability. It also creates responsibilities: migration policy, backward compatibility, backup/recovery, concurrent access rules, extension requirements, and opening untrusted files safely.

Verification checkpoint

Production judgment and Chapter 13 bridge

Chapter 12 closes the schema-level automation layer: stable read interfaces, controlled triggers, multi-file workflows, connection-open policy, and explicit file identity/versioning. Chapter 13 moves into semi-structured data. The same discipline will apply there: JSON is a representation choice inside SQLite, not an excuse to abandon relational constraints or application contracts.

Authoritative references

• SQLite — PRAGMA application_id.
• SQLite — PRAGMA user_version.
• SQLite — PRAGMA schema_version.
• SQLite — Database File Format.
• SQLite — URI Filenames.