Database & migrations

Marginalia keeps all its data in one SQLite file. The schema is created and upgraded by Flyway migrations, and Hibernate maps the entities onto it but never changes it. This page explains the setup, the conventions of the schema and how to write a migration. What the tables contain is described in Domain model.

The database file

The beans are in src/main/resources/META-INF/spring/datasources-config.xml:

flowchart LR
    conf["configuration<br/>resolveDb()"] --> ds["dataSource<br/>BasicDataSource"]
    conf --> wds["writeDataSource<br/>BEGIN IMMEDIATE"]
    ds --> fw["flyway<br/>init-method=migrate"]
    ds --> routing["routingDataSource<br/>ReadWriteRoutingDataSource"]
    wds --> routing
    routing --> proxy["ds<br/>TransactionAwareDataSourceProxy"]
    fw -. "depends-on" .-> emf
    proxy --> emf["emf<br/>EntityManagerFactory (PU)"]
    proxy --> jdbc["jdbcTemplate"]
    emf --> em["em<br/>SharedEntityManagerBean"]
    emf --> tm["transactionManager<br/>JpaTransactionManager 'common'"]

• flyway migrates the database when the context starts; emf depends on it, so Hibernate only sees a migrated schema.

• Hibernate and jdbcTemplate use the data source through TransactionAwareDataSourceProxy, so plain JDBC code joins the current JPA transaction.

• Read-write transactions take the write lock when they begin. A deferred transaction reads, takes its snapshot and needs the lock at its first write; when another connection has committed in between, the write fails at once with SQLITE_BUSY or SQLITE_BUSY_SNAPSHOT and busy_timeout does not help. So routingDataSource (ReadWriteRoutingDataSource) gives a read-write transaction a connection of writeDataSource (transaction_mode=IMMEDIATE, writers queue for the busy timeout) and a read-only one (@CommonTxReadOnly) a plain connection of dataSource (never waits for a writer). The transaction manager asks for the connection before it publishes the read-only flag, so the jpaDialect bean (ReadWriteRoutingDataSource.Dialect) notes it while the transaction begins. Flyway and code outside a transaction use dataSource. ConcurrentTransactionsTest covers both sides.

• The persistence unit PU is defined in src/main/webapp/config/persistence.xml: the list of entity classes (exclude-unlisted-classes), the dialect and hibernate.hbm2ddl.auto=validate.

Looking into the database

The file is an ordinary SQLite database. While Marginalia runs, open it read-only so you don't hold locks:

sqlite3 -readonly ~/.marginalia/marginalia.sqlite
sqlite> .tables
sqlite> select version, description, success from flyway_schema_history;
sqlite> select id, name, cast(extendedContent as text) from manuscripts;

extendedContent columns hold UTF-8 JSON, gzipped when it is 256 bytes or more (since app version 6, see Startup). Small ones can be read with cast(... as text); a big one starts with the bytes 1f 8b, unpack it outside of SQLite: sqlite3 -readonly marginalia.sqlite "select hex(extendedContent) from manuscripts where id = 1" | xxd -r -p | gunzip. It holds the extended attributes. Timestamps (creation, modification, lastOpened) are stored as milliseconds since the epoch, booleans as 0 / 1.

Startup: migrate, then validate

1. Before the data source is created, Configuration.resolveDb swaps in a staged database restore, if there is one (see Database backups and restores). Then Configuration.backupBeforeMigration asks DatabaseCheck.pendingMigrationFrom whether the existing file is behind the newest bundled V<n> migration (or has no history table, which Flyway baselines at V1). If so it saves a VACUUM INTO copy as db-backups/pre-migration-<time>-V<from>-marginalia.sqlite and keeps the newest three. A new installation (no file or an empty one) and a current database are left alone, and a copy that fails is logged, not fatal. The check looks at the Flyway history only, so a Java Migration bean without a V<n> file does not trigger a copy.

2. Flyway runs every pending migration from classpath:migration (src/main/resources/migration/) and records it in flyway_schema_history. baselineOnMigrate=true with baseline version 1 makes databases created before Flyway was introduced (V1 schema without a history table) start at V1 and get V2 onwards.

3. Hibernate builds the EntityManagerFactory and validates the schema against the entities: every mapped table and column must exist with a compatible type. It never creates or alters anything.

If either step fails, the Spring context doesn't start and Marginalia doesn't serve any page; the cause is in the log. Typical messages:

There is also a second, Java-level version check: ApplicationInitializer compares AppSettings.dbVersion / appVersion with the versions set in container-config.xml and runs registered Migration beans for data changes that are easier in Java than in SQL. It runs after Hibernate is up, so a migration can use jdbcTemplate or services. Each migration gets the current version and returns the new one (or the same one when it has nothing to do), so a migration is written for one version step and ignores the others.

To add one: implement Migration, register the bean in the migrations list of applicationInitializer and raise appVersion (or dbVersion). A fresh database starts at version 1, so it goes through all migrations too.

Migrations

Rules

• Never change a migration that has been released. Flyway stores a checksum of each applied file and refuses to start when it changes. Fix mistakes with a new migration.

• Name files V<next number>__<what_it_does>.sql (two underscores). Versions are plain integers here.

• One migration per change, together with the entity change in the same commit.

• Keep data. Users upgrade in place - every migration must turn any existing database into the new schema without losing data. Convert old values in SQL (V5 is an example).

• Only SQL that SQLite understands. See SQLite limitations.

• Prefer extended attributes. A field stored in extendedContent needs no migration at all; add a column only when the value is used in queries (see Domain model).

Adding a column

-- V10__manuscript_archived.sql
ALTER TABLE manuscripts
    ADD COLUMN archived boolean not null default false;

ADD COLUMN with not null needs a default, otherwise existing rows can't get a value. Then map it in the entity:

@Column(nullable = false)
private boolean archived = false;

Adding a table

A new entity needs its table, its sequence table with a first row, and its indexes. Hibernate uses GenerationType.AUTO, which on SQLite means a table-backed sequence named <table>_SEQ with a pooled optimizer (ids are reserved in blocks of 50 - gaps in ids are normal):

-- V10__bookmarks.sql
create table bookmarks
(
    id              bigint      not null primary key,
    creation        timestamp,
    is_deleted      boolean     not null,
    modification    timestamp,
    uuid            varchar(36) not null unique,
    extendedContent blob,
    _fulltext       clob,
    userId          bigint,
    message_id      bigint
);

create index ix_bookmarks_user on bookmarks (userId, is_deleted);
create index ix_bookmarks_message on bookmarks (message_id);

create table bookmarks_SEQ
(
    next_val bigint
);

INSERT INTO bookmarks_SEQ (next_val) VALUES (1);

The columns of BaseEntity / OwnedEntity / ExtendableEntity are always the same: id, creation, is_deleted, modification, uuid, userId (owner), extendedContent and _fulltext. Then list the class in persistence.xml. For joined inheritance (a subtype of AI or Protocol), the subtype table has only id plus its own columns. A subtype of Setting is single table (@DiscriminatorValue, the settings table): StylesSetting and ExportSettings are only listed in persistence.xml and keep everything in extendedContent, so they need no migration.

Changing or removing a column

SQLite can rename a column (ALTER TABLE ... RENAME COLUMN) and, since 3.35, drop a simple one (ALTER TABLE ... DROP COLUMN), but it can't change a column's type, nullability, default or constraints. For those, rebuild the table - V3 and V5 show the pattern:

CREATE TABLE protocols_new ( ... new definition ... );

INSERT INTO protocols_new (id, ...)
SELECT id, ... FROM protocols;          -- convert values here

DROP TABLE protocols;

ALTER TABLE protocols_new RENAME TO protocols;

CREATE INDEX protocol_is_deleted_idx ON protocols (is_deleted);   -- indexes are dropped with the old table
CREATE INDEX protocol_user_id_ix ON protocols (userId);

Recreate every index of the old table. Removing an entity field without removing the column is harmless - validation only checks that mapped columns exist.

Generating the SQL

com.github.enerccio.tools.GenerateFlywayDiff (in src/main/java/com/github/enerccio/tools/) asks Hibernate what it would change to make an existing database match the entities, and prints the SQL. Run its main from the repository root (it reads marginalia/src/main/webapp/config/persistence.xml), with -Duser.home pointing to a development home whose database is at the current schema version. Treat the output as a draft:

• Hibernate only creates and adds; it never drops or changes columns.
• It doesn't know about data conversion, defaults for existing rows or the table rebuilds SQLite needs.
• It may print Hibernate's temporary tables (HT_*, HTE_*) again - leave them out if they already exist.

Testing a migration

db/FlywayMigrationTest checks, without the rest of the application:

• an empty database migrates through all versions and the schema validates against persistence.xml,
• migrating twice is a no-op,
• a database at V1 with data upgrades to the latest version, keeping the data,
• a pre-Flyway database (V1 schema, no history table) is baselined and upgraded.

New migrations are picked up automatically by the first two tests. When a migration converts data, add rows to insertV1Data() and assertions to assertUpgradedData(), like the existing ones for V2-V5 and V9 (the description of three manuscripts: only in the column, merged into other JSON keys, and in both). A Java migration (Migration bean) needs the Spring context: see db/FulltextMigrationTest. Run it with mvn test -Dtest=FlywayMigrationTest.

Schema conventions

Names. Tables are plural (manuscripts, messages, entries), set by @Table(name = ...). Columns use the Java field name (maxTokens, lastOpened); relations end in _id (lorebook_id, parentScript_id), except the owner, which is userId, and the soft delete flag, is_deleted. Indexes exist only in the migrations, see Indexes.

No foreign key constraints. The tables have no REFERENCES clauses (V2's summary_id declares one, but SQLite doesn't enforce it because PRAGMA foreign_keys is off). Integrity is kept by the application: rows are soft deleted, so references stay valid, and the admin Cleanup follows the cleanup references to decide what may be purged. Code that hard deletes must clear or move references itself (see ChatMessageService.deleteNodeAndMigrateChildren).

Enums. AI.aiType and Protocol.protocolType are stored as ordinals (tinyint), all other enums by name. SaneSQLiteDialect turns off the CHECK constraints that Hibernate would otherwise generate for enum columns, so a new enum value doesn't need a table rebuild.

Large values. Text that can be long (name, uri, apiKey...) is @Lob → clob; extendedContent is a blob with JSON (gzipped when big, see ExtendableEntity). SQLite doesn't enforce lengths, so varchar(255) is only documentation.

Encrypted columns. Secrets are stored encrypted, see Encrypted columns.

Hibernate's temporary tables. V1__initial.sql contains HT_* and HTE_* tables. Hibernate uses them for bulk updates and deletes on entities with joined inheritance (AI, Protocol) and for inserts into such hierarchies. They are part of the schema - don't drop them.

Indexes

Indexes are created only by Flyway migrations; the entities don't declare any (@Table(indexes = ...)), because Hibernate only validates the schema and never creates them. Name them ix_<table>_<what>.

Index what the queries filter on, in the order of the WHERE clause, with the equality columns first and the ORDER BY column last:

A single-column index on is_deleted alone helps only the Cleanup (few rows are deleted); don't add new ones. Check a query with EXPLAIN QUERY PLAN in SQLite - SCAN <table> means a full table scan, SEARCH ... USING INDEX means the index is used. FlywayMigrationTest.tagRelationLookupsUseIndexes does that for the main lookups.

Encrypted columns

OpenAICompatible.apiKey is mapped with @Convert(converter = EncryptedStringConverter.class). The converter calls Configuration.encrypt / decrypt:

• On the first start Configuration writes a random UUID to <data folder>/secret.key; the AES-256 key is the SHA-256 of that UUID. The file is outside the database, so a database backup alone doesn't reveal the secrets.

• A stored value is enc: + Base64 of a random 12-byte IV and the AES/GCM ciphertext. Values without the prefix are read as they are (plain values from before the migration); a value that can't be decrypted (a database from another installation, a different secret.key) is logged and read as null - the user enters the key again.

• The converter is a Spring bean: emf sets hibernate.resource.beans.container to a SpringBeanContainer, so Hibernate asks Spring for converters (and entity listeners) and @Autowired fields are injected. Use the same converter for any new secret column.

• Code outside JPA (jdbcTemplate, migrations) sees the encrypted value and must use Configuration itself, as EncryptApiKeysMigration does.

SQLite limitations

Database backups and restores

DatabaseBackupServiceImpl backs up the live database with SQLite's VACUUM INTO '<file>', which writes a consistent, compacted copy without stopping the application. Backups go to <data folder>/db-backups/ as marginalia-<time>.sqlite (manual) or marginalia-scheduled-<time>.sqlite; scheduled ones are rotated (keep last N). Configuration writes two more kinds into the same folder, pre-restore-* (the database a restore replaced) and pre-migration-* (see Startup); the service lists every .sqlite file there, and only marginalia-scheduled-* counts as scheduled. The methods the UI calls (create, list, download, upload, delete, restore, schedule) call AdminGuard.requireAdmin(); the scheduled run (createScheduledBackup) and the rotation need no logged-in user.

A database can't be replaced while it's open, so restoring is done in two steps:

sequenceDiagram
    participant A as Admin UI
    participant B as DatabaseBackupService
    participant C as Configuration (next start)
    A->>B: scheduleRestore(backup)
    B->>B: DatabaseCheck.check(backup)
    B->>B: copy backup → marginalia.sqlite.restore (tmp file + atomic move)
    Note over A,C: restart
    C->>C: DatabaseCheck.check(marginalia.sqlite.restore)
    C->>C: move marginalia.sqlite (+ -wal, -shm) → db-backups/pre-restore-<time>-marginalia.sqlite
    C->>C: move marginalia.sqlite.restore → marginalia.sqlite
    C->>C: Flyway migrates the restored database
    Note over C: datasource, Flyway bean (nothing left to migrate), Hibernate validates

DatabaseCheck.check(File) is run on Upload Backup (importBackup), on scheduleRestore and on start. It opens the file read-only and refuses it (InvalidDatabaseException, an IllegalArgumentException with the reason) when:

• it doesn't start with the SQLite header,

• PRAGMA integrity_check reports problems,

• core tables (users, settings, manuscripts, messages, lorebooks, entries, ais, protocols, tags, t2e) are missing - another application's database,

• flyway_schema_history has a failed migration or a version newer than the newest bundled migration/V*__*.sql - a database of a newer Marginalia version. A database without the history table predates Flyway and is baselined at V1 as usual.

Configuration.applyPendingRestore() migrates the restored database itself, before the datasource is created, with the same Flyway configuration as the flyway bean (DatabaseCheck.flywayConfiguration(DataSource), used by datasources-config.xml too). If the check fails, the staged file is moved to db-backups/rejected-restore-<time>-marginalia.sqlite and the current database stays. If the migration fails, the restored file is moved there as well and the pre-restore-* database is moved back. Either way the reason is logged as an error and Marginalia starts with the previous database. A backup from an older version is upgraded on start.

API keys in a backup are encrypted with the installation's secret.key (see Encrypted columns). A backup restored on another installation loses them unless secret.key is copied along.

Tests and the database

Tests don't touch your data: MarginaliaTestBase starts the Spring configuration with the data folder in target/test-home/ctx-*, so every test context gets a fresh database migrated by the real Flyway configuration. See Testing.