Round-Tripping Mastodon and Saleor Through a Database Diagram: What Broke and What We Fixed

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: We put two real PostgreSQL schemas, Mastodon (Rails) and Saleor (Django), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes PostgreSQL refused that SQL for both, and once patched by hand the copies refused rows the originals accept. After the fixes the rebuilt copies match their originals in every table, key, constraint and index, and behave the same in 16 probes. What a diagram does not hold, Mastodon's own function and Saleor's four extensions, is no longer a hand-written step: Import SQL keeps it and Save runs it first.

A database diagram that reads a schema and writes it back has one job: give you the same database. To test that, we took two real open-source schemas, Mastodon v4.7.3 (Rails, 118 tables) and Saleor 3.23.41 (Django, 148 tables), put each through Schemity both from its pg_dump and from the live database, and rebuilt a database from the SQL Schemity wrote. Before this round of fixes, PostgreSQL refused the SQL Schemity wrote for both schemas. After them, the rebuilt copies match the originals in every table, column, key, constraint and index, apart from spelling, and accept and refuse exactly the same rows in 16 behaviour probes.

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. Reading a database you did not design is how most people start with it: you reverse engineer the PostgreSQL schema to an ERD to understand your schema, and later generate SQL from that diagram. Both directions are only as good as what survives the trip, so this post measures exactly that, including what broke.

Which two schemas, and which two paths?

Two production schemas, each created by its own framework rather than written for the test, and chosen because they use different parts of PostgreSQL.

Rails leans on serial keys and on partial and sorted indexes; Django on identity keys, deferred foreign keys and operator classes for search. Between them they cover most of what a framework writes into a PostgreSQL schema.

Mastodon's 118 tables, imported from its dump and grouped into 17 legends by the models in Mastodon's own source (accounts, statuses, the social graph, moderation, federation and so on), look like this. Open the full-size diagram to read the table names.

The status bar of that diagram says 168 relations, not 156. The extra 12 are relationships Schemity inferred from column names that have no foreign key in the database. They are drawn dashed and are never written out as constraints, so the exported script holds exactly Mastodon's 156 foreign keys. A schema this size is not read by shrinking it: the minimap shows where every legend sits, and search finds a column across all 118 tables. Why the canvas works that way has its own post.

Each went through Schemity on two paths, which run different code:

• Import SQL: the pg_dump --schema-only file read by Schemity's SQL parser, with no database connection.
• Live connection: Schemity connected to the database and read PostgreSQL's catalog.

Both paths end the same way: the diagram's SQL exported and applied to an empty database. The four runs were done in the app, in a development build with the fixes described below. The originals ran on PostgreSQL 17.11 and the rebuilt copies on PostgreSQL 18.3.

Import SQL lists every table it found before anything is added, then what it did with the rest. Mastodon's tables call a function a diagram does not hold, and the summary says it kept the SQL that creates it:

Mastodon's ids come from its own timestamp_id(), which reads seven sequences no column owns. Import SQL keeps the dump's own text for the function and those sequences as setup SQL. It writes it before the tables in View SQL, and when Save migrates to a database that lacks the function, puts it at the top of the migration, with each sequence as CREATE SEQUENCE IF NOT EXISTS so one the database already has is left alone:

Saleor's tables call no function of their own, but its search indexes need four extensions, among them pg_trgm. Import SQL keeps them, and the diagram's SQL creates them first:

None of that existed when the benchmark started. The first runs needed a hand-written prelude on each empty database: the function and its seven sequences copied from Mastodon's dump, and four CREATE EXTENSION lines for Saleor. The comparisons below were measured that way. Once Import SQL kept the setup SQL itself, both dumps went through the app again, imported and migrated to an empty database with Save and nothing run by hand: all 118 of Mastodon's tables and 148 of Saleor's came up, and all 16 probes still match their originals.

How do you prove a rebuilt schema is the same?

Two ways, and they answer different questions: does the database describe itself the same way, and does it behave the same way.

The catalog diff. Each original was created by its own framework, not by us: Mastodon with rails db:schema:load in the official ghcr.io/mastodon/mastodon:v4.7.3 image, Saleor with manage.py migrate in ghcr.io/saleor/saleor:3.23.41, both on PostgreSQL 17.11. The schema then went into Schemity two ways, as a pg_dump --schema-only file through Import SQL and as a live connection, and Schemity's exported SQL was applied to an empty database. A query over PostgreSQL's own catalog then lists, for both databases, one line per fact:

• every column with its type as format_type prints it, NOT NULL, its default and its identity
• every primary key, foreign key, unique and CHECK constraint as pg_get_constraintdef prints it
• every index as pg_indexes prints it, with its own name left out
• every sequence with its type, its start and the column that owns it

The two lists are diffed line by line. A difference is either a real change or the same thing spelled two ways, such as DEFAULT now() and DEFAULT CURRENT_TIMESTAMP, which are the same time, and each one is classified by hand in the results.

The behaviour probes. A catalog can agree while a database still behaves differently, so each copy also gets 16 probes, eight per app, run against the original and every copy. A probe is a few statements in one transaction that is rolled back, so nothing stays: insert an account with its own id, insert two usernames that differ only in case, insert a tax rate before the tax class it points to. Its outcome is "accepted" or "refused at step N" with PostgreSQL's reason, and a copy passes when its outcome is the original's.

Data is not part of this test. Both originals hold almost none: Saleor's migrations insert a default customer type, which one probe has to set aside first, and Mastodon's insert nothing the probes touch.

What failed before the fixes?

Everything below is from Schemity as of 14 October 2026, through Import SQL. Rebuilding the live path at that point would have meant a second full build of the desktop app, so the "before" column has the import path only.

Neither exported script ran. Schemity imported both schemas, but PostgreSQL refused the SQL it wrote for each at the first double precision column, which Schemity had written as DOUBLE, a MySQL name: 5 columns in Mastodon, 9 in Saleor. With that patched by hand, and Saleor's one empty array default, written as the text 'ARRAY[]', patched too, both scripts ran, and the probes showed what the database would have done.

Mastodon's other five probes passed even before the fixes. Saleor's first failure hid the rest, since every probe that gives a row its own id stopped at step one, so its last three rows come from a second run that lets the database pick the ids. Each failure had its own cause.

Why did serial ids come back as GENERATED ALWAYS?

Rails makes a key bigserial, and pg_dump writes that as a plain bigint column plus a separate ALTER TABLE ... ALTER COLUMN id SET DEFAULT nextval('accounts_id_seq'). Import SQL ignored that second statement, so the column arrived with no default, and the export followed its old rule for a lone integer key with none: GENERATED ALWAYS AS IDENTITY. 104 of Mastodon's 106 keys came back that way, and ALWAYS refuses a row that gives its own id, which is exactly what fixtures, seed scripts and copied data do. The other two keys lost their default altogether. Import SQL now reads the SET DEFAULT, and a column on its own sequence is written back as BIGSERIAL, which creates the same sequence, default and ownership. The trade-offs between the two are in PostgreSQL serial vs identity.

On the canvas, a key the database numbers reads auto, whether it is a serial or an identity, and a key the application assigns stays blank. A column that draws from a sequence of its own, such as an invoice number, shows nextval, because it is a default and not a key.

What does Django's BY DEFAULT identity need from a diagram?

To be recorded at all. Since Django 4.1, a key is GENERATED BY DEFAULT AS IDENTITY: the database numbers it, but a row may still bring its own id. pg_dump writes each one as ALTER TABLE ... ADD GENERATED BY DEFAULT AS IDENTITY (SEQUENCE NAME ...), and the SQL parser behind Import SQL cannot read the SEQUENCE NAME option, so all 132 of Saleor's statements went unread, and the export gave 133 keys GENERATED ALWAYS. A diagram now records each key's identity as ALWAYS, BY DEFAULT or none, from the live database and from a dump, writes it back as it was, and shows it in the field dialog as an Auto increment checkbox with the kind beside it.

Why does every Django foreign key say DEFERRABLE INITIALLY DEFERRED?

So a transaction can insert a child row before its parent, the way loaddata and Django fixtures load. 265 of Saleor's 266 foreign keys are declared that way, and before the fixes all 265 came back as ordinary keys, checked by each statement, so the rate inserted before its tax class was refused at once. The diagram now records when each key is checked, and the relationship dialog shows it.

What happens to a partial unique index?

It used to become a full one, which is the dangerous direction: the copy refuses rows the original accepts. Saleor allows one default tax rate per country with CREATE UNIQUE INDEX ... ON tax_taxclasscountryrate (country) WHERE (tax_class_id IS NULL). Without its condition that index makes country unique across the whole table, so a second rate for the same country, under a different tax class, was refused. Saleor's one-default customer type went the same way, and a UNIQUE NULLS NOT DISTINCT rule went the other, losing the clause that makes NULL count as a duplicate. How a unique index differs from a unique constraint in the first place is in unique constraint vs unique index.

How do you keep lower(username) and text_pattern_ops?

By keeping the index's key list as SQL when its columns alone do not describe it. Mastodon makes usernames unique regardless of case with UNIQUE (lower(username), COALESCE(lower(domain), '')), an index on expressions, and the old diagram held only column names, so the rule was dropped and the copy accepted Alice beside alice. Seven other Mastodon indexes use an operator class, text_pattern_ops, which the parser could not read either, so all seven were lost, three of them unique, which is why the copy accepted two statuses with the same URI. Saleor has 62 indexes with an operator class, among them 19 trigram GIN indexes for search. The diagram now keeps the expression, the operator class, a sort order such as id DESC, the WHERE condition and NULLS NOT DISTINCT, and the index dialog shows them.

The keys read lower((username)::text) rather than lower(username) because that is how PostgreSQL stores the expression, and the diagram keeps what the database reports.

Which bugs did only real dumps find?

The ones no hand-written test schema had:

• Newer versions of pg_dump wrap the file in psql's \restrict command, which is not SQL and has no semicolon, so it joined the statement after it.
• Saleor writes extension operator classes with their schema, public.gin_trgm_ops, which needed its own handling.
• The live reader reported a nullable column with no default as DEFAULT NULL, so the export wrote DEFAULT NULL on 171 of Saleor's columns.
• Import SQL turned every CREATE UNIQUE INDEX into a UNIQUE constraint, so 69 of Mastodon's unique indexes came back as constraints. It is the same rule, so no probe noticed, but not the same object: a later Rails migration that runs remove_index on one would fail, because PostgreSQL will not drop an index a constraint owns. A unique index now stays an index, as the live reader already kept it; on the canvas both still show as unique.
• A diagram imported from Mastodon's dump and then refreshed from the database it built reported 70 columns as changed in the database, because the two paths spell false and FALSE, or DECIMAL and NUMERIC, differently. Comparing the meaning instead of the spelling took the planned migration from 71 statements to none.

None of these broke a test, because every test schema had been written by hand to cover a feature. A framework's real output was the first input that had all of them at once.

One more turned up later, while taking the screenshots for the SQLite post, which the scripts here 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, so a copy built from the diagram met a SELECT * or an INSERT without a column list with its columns in another order. 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, 102 of Mastodon's 118 tables and 125 of Saleor's 148 came back in a different column order before this fix.

What does the round trip give you now?

The same database, on both paths, for both schemas. Each line below is one run in the app: the schema went in through one path, Schemity wrote the SQL, and the rebuilt copy was compared with the original.

A first version of this table gave Mastodon 92 differences, all timestamp(6) columns written back as timestamp. On PostgreSQL those are one type, because 6 digits is the default, so nothing was lost. The MySQL round trip showed the same gap is not harmless there: a bare MySQL timestamp keeps whole seconds, so timestamp(6) written back that way rounds every microsecond away. Schemity now keeps a time column's precision as it was declared, on every database, and Mastodon's 92 columns come back as timestamp(6). That rerun went through Schemity's core on the development build, not through the app.

Both paths start from the original database Rails created. A first version of this post read Mastodon's live path from the Import SQL copy instead, which made its number a repeat of the import's rather than its own; the line above is from the original.

All 132 identity keys come back GENERATED BY DEFAULT AS IDENTITY, all 106 serial keys BIGSERIAL, all 265 deferred foreign keys DEFERRABLE INITIALLY DEFERRED, and every partial, expression and sorted index with its condition, key list and operator class.

What still differs, and why?

Two kinds of difference are left: the same thing spelled another way, and objects the diagram does not hold.

Spelled differently, behaving the same:

Not held by the diagram as objects:

• Functions. Mastodon numbers seven tables with its own timestamp_id() function, and the column defaults that call it are kept. Without the function PostgreSQL stops at the first such table with function public.timestamp_id(text) does not exist. From a dump, Import SQL now keeps the function and the seven sequences it reads as setup SQL and runs them first, as shown above. A function read from a live database is only named: View SQL warns that the script calls a function it does not create.
• Views. A CREATE VIEW statement names its columns' types nowhere, so Import SQL skips Mastodon's two views and lists them as skipped. A live connection reads them, as read-only tables in the diagram, and they are not exported as SQL.
• A sequence's options. A sequence a column default draws on, or one a column owns with OWNED BY, comes back bare: bigint, starting at 1. Saleor's three such sequences, among them order_order_number_seq on order_order.number, match their originals because those were bare too.

The import summary counts what it skips: 12 statements for Mastodon (seven sequences timestamp_id() reads, two views, the function and two indexes on the materialized view), all but the indexes named, and 1 for Saleor, a function no table calls. Saleor's four extensions and their comments are kept, not skipped; before this round they were 8 of 9 skipped statements.

One gap was found only on the way to this post: Saleor's three owned sequences have no column default, nothing in the schema reads them, and the first version of the catalog diff did not list sequences, so they were missing from every rebuilt copy without a line of difference. Reading the skipped statements caught it. The diagram now keeps a sequence a column owns, and the catalog diff lists sequences.

What this benchmark does not show

• Other databases. Everything here is PostgreSQL. MySQL, SQL Server, SQLite and Snowflake each get their own schema and their own post. Converting a diagram from PostgreSQL to one of them leaves identity kinds, DEFERRABLE and partial and expression indexes out, and the converted script's notes name each one.
• Data. The originals are empty, and no rows were copied. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
• Prisma, and other frameworks. Rails and Django are covered; a schema from Prisma, Laravel or a hand-written monolith may use features neither of these uses.
• The live path before the fixes. The "before" column covers Import SQL only.
• Triggers. Neither schema has one, so nothing here tests them. A trigger is not a table either, and one left behind fails nothing: the rebuilt database simply behaves differently, which is the case this benchmark exists to catch. A schema that leans on triggers, such as an audit table or an updated_at trigger, is the next one to test.
• Schemas this size are not a stress test. 118 and 148 tables are typical, not large; how Schemity handles a schema of a thousand tables is in a separate post.

How to rerun it

The method needs nothing from us. Create each schema with its own framework in its official Docker image, take pg_dump --schema-only --no-owner --no-privileges, put it through Schemity on either path, export the SQL, and apply it to an empty database. Through the app, Save does that and runs the setup SQL first; by hand, View SQL's script already starts with it. Then list both catalogs with a query like this one and diff the output:

SELECT line FROM (
  SELECT format('column %s.%s %s%s%s', c.table_name, c.column_name,
         format_type(a.atttypid, a.atttypmod),
         CASE WHEN c.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
         COALESCE(' DEFAULT ' || c.column_default, '') ||
         CASE WHEN c.is_identity = 'YES' THEN ' IDENTITY ' || c.identity_generation ELSE '' END) AS line
  FROM information_schema.columns c
  JOIN pg_attribute a ON a.attrelid = format('public.%I', c.table_name)::regclass AND a.attname = c.column_name
  JOIN information_schema.tables t ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE'
  WHERE c.table_schema = 'public'
  UNION ALL
  SELECT format('constraint %s %s %s', conrelid::regclass, contype, pg_get_constraintdef(oid))
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace AND contype IN ('p', 'u', 'f', 'c')
  UNION ALL
  SELECT format('index %s %s', tablename, regexp_replace(indexdef, '^CREATE (UNIQUE )?INDEX \S+ ON ', 'CREATE \1INDEX ON '))
  FROM pg_indexes WHERE schemaname = 'public'
  UNION ALL
  SELECT format('sequence %s %s start %s%s', s.relname, format_type(q.seqtypid, NULL), q.seqstart,
         COALESCE((SELECT ' owned by ' || t.relname || '.' || a.attname FROM pg_depend d
                   JOIN pg_class t ON t.oid = d.refobjid
                   JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
                   WHERE d.objid = s.oid AND d.deptype IN ('a', 'i')), ''))
  FROM pg_class s JOIN pg_sequence q ON q.seqrelid = s.oid
  WHERE s.relnamespace = 'public'::regnamespace
) lines ORDER BY 1;

For behaviour, write a few statements per rule you care about, run each inside BEGIN and ROLLBACK on the original and on the copy, and compare which ones PostgreSQL accepts and at which step it refuses the rest.