# Round-Tripping Campfire and Jellyfin Through a Database Diagram: What SQLite Lost and What We Fixed

> We put two real SQLite schemas, Campfire (Rails 8) and Jellyfin (Entity Framework Core 10), through Schemity and rebuilt a database file from the SQL it wrote. Before this round of fixes Campfire's columns came back as an affinity name instead of their declared type, so Rails read `created_at` as a decimal and Campfire could not create an account; Jellyfin's partial and expression indexes were lost. After the fixes Jellyfin comes back with no difference, Campfire differs only in its FTS5 search table, Campfire's own code works on the copies, and all 18 behaviour probes across both schemas match the originals.

Source: https://schemity.com/blog/sqlite-round-trip-campfire-jellyfin/

A database diagram that reads a SQLite schema and writes it back should give you the same database file, and before this round of fixes Schemity did not. We took two real open-source schemas, Campfire v1.5.2 (Rails 8, 23 tables) and Jellyfin 12.2 (Entity Framework Core 10, 33 tables), put each through Schemity from a `.schema` script and from the live database file, and rebuilt a new file from the SQL Schemity wrote. SQLite stored the same values in the copies, so most row-level checks passed, but Campfire's columns came back as an affinity name instead of their declared type, and Campfire's own code could not create an account on the copy. After the fixes, Jellyfin comes back with no catalog difference on either path, Campfire differs only in its FTS5 search table, and both behave like their originals.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. This is the fourth post in a series that tests the round trip on each database it supports, after [Mastodon and Saleor on PostgreSQL](https://schemity.com/blog/postgres-round-trip-mastodon-saleor/), [Invoice Ninja and Sylius on MySQL](https://schemity.com/blog/mysql-round-trip-invoice-ninja-sylius/) and [Bitwarden and Smartstore on SQL Server](https://schemity.com/blog/sqlserver-round-trip-bitwarden-smartstore/). Reading a schema you did not write is how you understand your schema before changing it, and SQLite tested something the other three never did: a database that accepts any type name and keeps it, while enforcing almost none of it.

## Which two schemas, and which two paths?

Two schemas built by two frameworks that treat SQLite differently. Campfire, 37signals' self-hosted chat app, comes from Rails migrations, which declare types the way Rails reads them back: `datetime(6)`, `varchar`, `bigint`, `json`. Jellyfin, the media server, comes from Entity Framework Core migrations, which declare only SQLite's own names (`INTEGER`, `TEXT`, `REAL`) but use partial and expression indexes.

| | Campfire v1.5.2 | Jellyfin 12.2 |
| --- | --- | --- |
| What it is | Group chat | Media server |
| Built by | Rails 8 migrations, `bin/rails db:prepare` | Entity Framework Core 10 migrations, on first start |
| Tables | 23: 17 of its own, an FTS5 virtual table and the 5 tables SQLite keeps its data in | 33 |
| Foreign keys | 10 | 28, 25 of them `ON DELETE CASCADE` |
| `AUTOINCREMENT` keys | 15 | 12, and one plain `INTEGER PRIMARY KEY` |
| Declared types beyond SQLite's own | 33 `varchar`, 1 `varchar(16)`, 31 `datetime(6)`, 6 `bigint`, 1 `json` | none |
| Partial / expression indexes | 0 / 0 | 4 / 1 |

Each schema went through Schemity on two paths:

- **Import SQL:** the sqlite3 shell's `.schema` output, read by Schemity's SQL parser, with no connection.
- **Live connection:** Schemity's SQLite reader opened the original database file and read SQLite's `PRAGMA` functions and `sqlite_master`.

Both paths end with the diagram's SQL applied to an empty file with the sqlite3 shell. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window. The fixes described below are in that build and ship in the next release; until then, the released app has the type and index losses in the "before" column.

## How do you prove a rebuilt SQLite schema is the same?

Three checks, one more than the earlier posts, because on SQLite the database itself barely notices the difference that mattered most.

**The catalog diff.** A script lists one line per fact for the original file and for the copy: every table with `WITHOUT ROWID`, `STRICT` and `AUTOINCREMENT`; every column with its declared type, nullability, default and key position; every foreign key with its actions; every index with its keys, `WHERE` and expressions. The lists are diffed line by line. A declared type is compared without regard to case, as SQLite and Rails read it.

**The behaviour probes.** A probe is a few statements in one transaction that is rolled back, run with `PRAGMA foreign_keys = ON` as both frameworks open their connections: delete an account and insert another to see which id it gets, store `'12'` in the `json` column and ask SQLite what type it kept, ask SQLite how it plans a lookup by `lower("Name")`. The outcome is "accepted" with what the `SELECT` read back, or "refused at step N" with SQLite's message.

**The framework itself.** Campfire's Docker image ran `bin/rails runner` against each copy: print the type Rails reads for three columns of `accounts` (`created_at`, `settings`, `join_code`), then create an account through Campfire's own `Account` model and read it back.

| Check | What it compares | What it catches |
| --- | --- | --- |
| Catalog diff | Tables, columns, keys, indexes, as SQLite reports them | Anything missing, added or changed in the schema |
| Probes | What each file accepts, refuses, stores and how it plans a query | A key, index or affinity that behaves differently |
| Rails runner | What the application reads from the schema | A declared type the application reads differently |

The originals hold a few rows the frameworks wrote (Campfire's `schema_migrations` and `ar_internal_metadata`, Jellyfin's `__EFMigrationsHistory` and 4 rows of library items); every probe uses rows of its own.

## What failed before the fixes?

Everything below is from Schemity's code just before these fixes (commit `cb4b7b28`), on both paths.

| | Campfire, Import SQL | Campfire, live | Jellyfin, Import SQL | Jellyfin, live |
| --- | --- | --- | --- | --- |
| Catalog lines that differ | 198 | 200 | 37 | 37 |
| Declared types written as an affinity name | 72 | 72 | 0 | 0 |
| `INTEGER PRIMARY KEY` without its `NOT NULL` (spelling only) | 15 | 15 | 13 | 13 |
| FTS5 table and its data tables | data tables read as Campfire's | all 6 read as plain tables | - | - |
| Partial indexes that lost `WHERE` | - | - | 4 of 4 | 4 of 4 |
| Expression index | - | - | left out | left out |
| `AUTOINCREMENT` added where there was none | 0 | 0 | 1 | 1 |
| Probes that match the original | 10 of 10 | 10 of 10 | 5 of 8 | 5 of 8 |
| Campfire's `Account.create!` | fails | fails | - | - |

Cascades, unique indexes, defaults, `NULL` handling in unique columns, composite keys and foreign keys all survived. The failures:

| Check | Original | Before the fixes | After |
| --- | --- | --- | --- |
| Campfire: Rails reads `accounts.created_at` as | `datetime`, precision 6 | `decimal` | `datetime`, precision 6 |
| Campfire: `Account.create!` | creates the account | `NoMethodError` | creates the account |
| Jellyfin: plan for `WHERE lower(Name) = 'x'` | `SEARCH Peoples USING INDEX IX_Peoples_NameLower` | `SCAN Peoples` | the original's |
| Jellyfin: plan for `WHERE OwnerId IS NULL` | `SCAN BaseItems` | `SEARCH ... USING INDEX IX_BaseItems_OwnerId` | the original's |
| Jellyfin: a deleted migration lock's id | handed out again: 1 | not reused: 2 | 1 |

### Why does a declared type matter if SQLite ignores it?

Because SQLite does not ignore it; it keeps it and reads only part of it. A column's declared type sets its affinity by [five substring rules](https://www.sqlite.org/datatype3.html): a name containing `INT` gets `INTEGER`, one containing `CHAR`, `CLOB` or `TEXT` gets `TEXT`, and so on, down to `NUMERIC` for anything else, including `datetime` and `json`. The length in `varchar(16)` is ignored. Schemity's SQLite support modelled only the five affinity names, so both paths turned `varchar` into `TEXT`, `datetime(6)` into `NUMERIC` and `bigint` into `INTEGER`. Each pair has the same affinity, so SQLite stores the same values in the copy, and every SQL probe passed.

Rails reads the declared text. On the copy, `bin/rails runner` printed `created_at: NUMERIC -> decimal`, `settings: NUMERIC -> decimal` and `join_code: TEXT -> text`, where the original says `datetime(6) -> datetime, precision 6`, `json -> json` and `varchar -> string`. Campfire's `Account` model keeps its settings in that `json` column, and creating an account failed with `NoMethodError: undefined method 'reverse_merge!' for nil`. Campfire deployed against a schema rebuilt by the diagram would have stopped at its first account, and any Rails app would have read its times as decimals.

The diagram now keeps each column's declared type on both paths, with its length or precision, and writes it back. The [field editor](https://schemity.com/doc/tables-and-fields/) offers the declared types frameworks write (`VARCHAR`, `CHAR`, `BIGINT`, `INT`, `SMALLINT`, `BOOLEAN`, `DECIMAL`, `DOUBLE`, `FLOAT`, `DATE`, `TIME`, `DATETIME`, `JSON`) beside the five affinity names. A diagram drawn before this change holds `TEXT` where the database says `varchar`; saving it does not rebuild the table for that, since the two have the same affinity, and a rebuild for another reason keeps the database's declared type. Lint compares foreign key types by affinity on SQLite, so a `bigint` column pointing at an `integer` key is not reported as a mismatch.

![Part of the Campfire diagram read from the live database file, with the Accounts & Users and Rooms & Messages legends in view and the minimap in the corner: accounts shows join_code and name as VARCHAR, settings as JSON and created_at as DATETIME(6), rooms.creator_id is BIGINT and boosts.content VARCHAR(16); the status bar reads Entities: 17, Relations: 10 (+2 virtual hidden)](https://schemity.com/images/blog/sqlite-round-trip-campfire-jellyfin/campfire-minimap.webp)

### What happened to Jellyfin's partial and expression indexes?

They lost what made them different. Jellyfin has four partial indexes, such as `IX_BaseItems_OwnerId ON BaseItems (OwnerId) WHERE OwnerId IS NOT NULL`, and one on an expression, `IX_Peoples_NameLower ON Peoples (lower("Name"))`. Both paths wrote the partial ones without `WHERE` and left the expression index out; the live reader had kept them only to replay during a table rebuild.

None of the five is unique, so no row is refused either way. The query plan shows the difference. On the copy without the expression index, SQLite planned a lookup by `lower(Name)` as a full scan of `Peoples`; on the original it searches the index. And `IX_BaseItems_OwnerId`, written as a plain index, holds every item instead of only those with an owner, so SQLite keeps it up to date on every write to an ownerless item and plans `WHERE OwnerId IS NULL` through it, where the original scans the table. The diagram now keeps a SQLite index's key expressions and `WHERE`, as it already did for PostgreSQL, and reads them through the same parser on both paths.

![The Indexes tab of Jellyfin's BaseItems in Schemity: IX_BaseItems_OwnerId on OwnerId WHERE "OwnerId" IS NOT NULL, IX_BaseItems_ExtraType_OwnerId WHERE "ExtraType" IS NOT NULL, IX_BaseItems_PrimaryVersionId WHERE "PrimaryVersionId" IS NOT NULL and IX_BaseItems_TopParentId_Type_IsVirtualItem WHERE "PrimaryVersionId" IS NULL AND ("OwnerId" IS NULL OR "ExtraType" IS NOT NULL), followed by the plain indexes](https://schemity.com/images/blog/sqlite-round-trip-campfire-jellyfin/baseitems-partial-indexes.webp)

![Schemity's index dialog for Jellyfin's IX_Peoples_NameLower, with Keys lower("Name") and Unique unticked, over the Peoples index list showing it beside the plain IX_Peoples_Name](https://schemity.com/images/blog/sqlite-round-trip-campfire-jellyfin/peoples-expression-index.webp)

### Why did a lock table start skipping ids?

Because `AUTOINCREMENT` changes which ids a table hands out, and the writer added it to every table. [SQLite's documentation](https://www.sqlite.org/autoinc.html) puts it plainly: a plain `INTEGER PRIMARY KEY` gives a new row the largest id plus one, so a deleted last id comes back, while `AUTOINCREMENT` never reuses one and records the highest in `sqlite_sequence`. Jellyfin declares it on 12 tables and leaves it off `__EFMigrationsLock`. The copy's lock table gave a new row id 2 after id 1 was deleted, where the original gives 1 again. Schemity now records `AUTOINCREMENT`, `WITHOUT ROWID` and `STRICT` for each table on both paths and writes them as declared; neither schema has a table of its own with the last two, so only unit tests cover them.

### What is an FTS5 virtual table to a diagram?

Not a table it can hold. Campfire's message search is `CREATE VIRTUAL TABLE message_search_index USING fts5 (body, tokenize=porter)`, and SQLite creates five tables of its own to keep the index in: `message_search_index_data`, `_idx`, `_content`, `_docsize` and `_config`. Import SQL could not parse the virtual table and imported the five as if Campfire had written them; the live reader read all six as plain tables, so the copy got a `message_search_index` table with a `body` column and no search. Import SQL now names the virtual table in its summary and leaves it out with its five data tables, and the live reader skips both kinds.

![Schemity's Import SQL summary for Campfire's .schema on an empty diagram: the last rows of the table list, each marked New, the note that 1 virtual table is not imported, nor the tables SQLite keeps its data in, and the diagram's SQL does not create it: message_search_index, and the button Import (17 new, 0 updated)](https://schemity.com/images/blog/sqlite-round-trip-campfire-jellyfin/campfire-import-summary.webp)

### Which bugs did only a refresh find?

Import a script, connect to the file it came from, and [Refresh & merge](https://schemity.com/doc/resync-database/) should find nothing. Through the app's own merge code, it planned 152 statements for Campfire and 503 for Jellyfin, mostly table rebuilds. Foreign key names differed on 9 of Campfire's tables and 22 of Jellyfin's. SQLite keeps a foreign key's name only in the table's DDL: Import SQL read `fk_rails_c3b3935057` and `FK_Devices_Users_UserId` from the script, while the live reader named each key after its columns. The live reader now takes a name the DDL declares, the writer writes it as `CONSTRAINT "name" FOREIGN KEY`, and both refreshes list nothing, as do the six schemas from the earlier posts.

Taking the screenshots for this post turned up one more, which the scripts could not see because they skip a step the app takes when it reads a table: the app lists a table's fields key first, foreign keys next and timestamps last, and wrote `CREATE TABLE` in that order. A copy of Campfire's `sessions` built from the diagram put `user_id` second instead of last, so a `SELECT *` or an `INSERT` without a column list would read and write its columns in a different order, and a SQLite table rebuild writes the same `CREATE TABLE`. The diagram now remembers each column's position as the table declares it and writes that order, while the canvas keeps its own; a column added in the diagram goes last, where `ADD COLUMN` puts it. Read and written back the way the app does it, 14 of Campfire's 17 tables and 20 of Jellyfin's 33 came back in a different column order before this fix, and none do now.

![Schemity's Entity SQL dialog for Campfire's sessions: CREATE TABLE "sessions" with "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, then created_at, ip_address, last_active_at, token, updated_at, user_agent and user_id in the order Campfire declares them, DATETIME(6) and VARCHAR types, CONSTRAINT "fk_rails_758836b4f0" FOREIGN KEY ("user_id") REFERENCES "users" ("id"), and its two indexes; on the canvas behind it the same table lists id, user_id and the rest in Schemity's own order](https://schemity.com/images/blog/sqlite-round-trip-campfire-jellyfin/sessions-view-sql.webp)

## What does the round trip give you now?

The same file, on both paths, for both schemas, apart from the search table.

| | Import SQL | Live connection |
| --- | --- | --- |
| Campfire: catalog differences | the FTS5 table and its 5 data tables | the same |
| Campfire: probes that match | 10 of 10 | 10 of 10 |
| Campfire: Rails types and `Account.create!` | as the original | as the original |
| Jellyfin: catalog differences | none | none |
| Jellyfin: probes that match | 8 of 8 | 8 of 8 |

All 72 of Campfire's declared types, the 28 `NOT NULL` rowid keys, Jellyfin's 4 partial indexes and its expression index, and every table's `AUTOINCREMENT` come back as they were.

In the app's window, Import SQL of Campfire's script onto the diagram read from its live file finds nothing to change.

![Schemity's Import SQL summary for Campfire's .schema on the diagram read from the live database file: the last rows of the table list, each marked Unchanged, the note that the virtual table message_search_index is not imported, and the button Import (0 new, 0 updated) disabled](https://schemity.com/images/blog/sqlite-round-trip-campfire-jellyfin/campfire-import-unchanged.webp)

## What still differs, and why?

**Virtual tables.** FTS3, FTS4, FTS5 and R*Tree tables belong to a SQLite module, not to columns a diagram can hold, so the diagram's SQL does not create Campfire's `message_search_index`. Run its `CREATE VIRTUAL TABLE` from the script after the diagram's SQL and SQLite creates the five data tables itself; with that one statement added, Campfire's copy matches the original line for line.

**Spelling.** A declared type is written in upper case (`DATETIME(6)` for `datetime(6)`), which SQLite and Rails read the same way. An `INTEGER PRIMARY KEY` declared without `NOT NULL` gets it, which a rowid key is anyway; both schemas already declare it.

## What this benchmark does not show

- **The app's window.** These runs called Schemity's import, reader, export and comparison code from scripts on a development build. The screenshots are from the app's window on the same build, and the column-order check above went through the app's own read path.
- **Data.** The originals hold only bookkeeping rows. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
- **Triggers, views and `CHECK` constraints.** Neither schema has one.
- **Snowflake.** The last post in the series puts [TPC-DS and a Snowflake feature schema](https://schemity.com/blog/snowflake-round-trip-tpcds/) through the same round trip.
- **Other frameworks.** Django and Prisma declare SQLite types their own way; this tested Rails and EF Core.

## How to rerun it

Create each schema with its framework against an empty file, take `sqlite3 <file> .schema` for the script, put the script or the file through Schemity, export the SQL and apply it to an empty file. Then list both catalogs with this query, a shorter form of the benchmark's, and diff the output. It lists a primary key or `UNIQUE` constraint's index by origin only and leaves collations out, which the benchmark's script compares:

```sql
SELECT line FROM (
  SELECT 'table | ' || l.name || ' | ' || l.type
         || CASE WHEN l.wr THEN ' | without rowid' ELSE '' END
         || CASE WHEN l.strict THEN ' | strict' ELSE '' END
         || CASE WHEN m.sql LIKE '%AUTOINCREMENT%' THEN ' | autoincrement' ELSE '' END AS line
    FROM pragma_table_list l JOIN sqlite_schema m ON m.name = l.name
   WHERE l.schema = 'main' AND l.name NOT LIKE 'sqlite_%'
  UNION ALL
  SELECT 'column | ' || m.name || ' | ' || c.name || ' | ' || lower(c.type)
         || ' | ' || CASE c."notnull" WHEN 1 THEN 'not null' ELSE 'null' END
         || ' | ' || ifnull('default ' || c.dflt_value, 'no default') || ' | pk ' || c.pk
    FROM sqlite_schema m, pragma_table_xinfo(m.name) c
   WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
  UNION ALL
  SELECT 'fk | ' || m.name || ' | ' || f."from" || ' | ' || f."table" || ' | ' || f."to"
         || ' | on delete ' || f.on_delete || ' | on update ' || f.on_update
    FROM sqlite_schema m, pragma_foreign_key_list(m.name) f
   WHERE m.type = 'table'
  UNION ALL
  SELECT 'index | ' || m.name || ' | ' || i.name || ' | unique ' || i."unique" || ' | '
         || ifnull(replace(x.sql, '"', ''), i.origin)
    FROM sqlite_schema m, pragma_index_list(m.name) i
    LEFT JOIN sqlite_schema x ON x.name = i.name
   WHERE m.type = 'table'
) ORDER BY line;
```

It needs SQLite 3.37 or later for `pragma_table_list`. For behaviour, run each probe between `BEGIN` and `ROLLBACK` with `PRAGMA foreign_keys = ON`, end it with a `SELECT` of what was stored or an `EXPLAIN QUERY PLAN`, and compare the outputs. And if a framework reads your schema, ask it: on SQLite the declared type is for the application more than for the database.

## Frequently asked questions

### How do you generate an ERD from a SQLite database without losing column types?

Keep each column's declared type, not only its affinity. SQLite gives a `datetime(6)` column `NUMERIC` affinity, but it keeps the declared text and frameworks read it back: Rails takes the type and precision from it. Schemity now keeps the declared type from a live database file and from a `.schema` script and writes it back, so a `varchar` column comes back `VARCHAR` and a `datetime(6)` column `DATETIME(6)`, which SQLite and Rails read the same way.

### Does SQLite AUTOINCREMENT matter in a schema diagram?

Yes. With `AUTOINCREMENT` an id from a deleted row is never handed out again, tracked in `sqlite_sequence`; a plain `INTEGER PRIMARY KEY` gives the next row the largest id plus one, so a deleted last id comes back. Schemity records whether each table declares it, from a database or a script, and writes it as declared; a table drawn in the editor gets it.

### Can a database diagram keep SQLite partial and expression indexes?

Schemity now does, on both paths. A partial index keeps its `WHERE` and an index on `lower("Name")` keeps its expression, and both are written back. Without them SQLite plans a lookup by `lower("Name")` as a full table scan, and an index meant only for rows with an owner grows and is kept up to date for every row without one.