What is in this database?

The symptom

You add the commerce sample and connect. The explorer lists six schemas. billing alone has invoices, invoice_lines, payments, events, invoice_events and event_attachments, and nothing says which of them the others hang off, which column has no index, or which key is closest to the end of its integer range.

When the brief opens

The brief opens the first time you connect to a database, once per connection. Saving a new connection, connecting from the sidebar and opening something on an offline connection all count. Reconnects at launch or after a drop don't, and neither do the connections compare and drift open. It waits for the table list, then opens on public if that has tables, else on the schema with the most. The commerce sample has no public, so you get Brief · billing.

To open it again, right-click a schema and choose Database brief, or pick Database brief in the command palette. Open the brief of a new database, in Settings › General, turns the first opening off.

The brief is per schema. A foreign key into another schema, like billing.invoices.customer_id to crm.customers, is not part of any area.

The masthead

While it builds, three stations run across the top, catalog, stats and brief, each with its time. Then come the build time, Refresh, a Page and Ledger switch, and Export. The title is the database name, with tags for the schema, the server version and any framework or extensions found. Below are the vitals: tables, on disk, rows, areas, islands and links. On billing, tables is 6 and links is 2 with +3: two declared foreign keys inside the schema, three links inferred from names. rows carries ≈ because it is the planner's estimate.

Areas

Areas is a metro map. Each line is an area: tables that link to each other more than to the rest. Solid track is a declared foreign key and dashed track is inferred. A large ring marks a hub: the table an area is laid out around, and any table that at least five others point at. A numbered badge is read first: up to six tables, the most pointed-at table of each area first. Tables with no link to another table in the schema are islands, counted in the vitals.

billing splits into two areas, ringed at invoices and events. invoices has four tables pointing at it, two by name only, and gets the badge 1: start there. Click a station to open its table.

Weight and tables

Weight is a waffle of 100 squares over the bytes on disk. The six largest tables get a colour and a legend line, and the rest share a hatched n others. Tables below it is one dot per table, by area, hollow when dormant. With twelve tables or fewer the dots become names.

House style

House style writes a CREATE TABLE in the schema's own conventions: table names, the primary key, foreign key names, timestamps, JSON. Each line carries the share of tables that follow it and a count of outliers, whose names show on hover.

Smells

Smells is a matrix. Each row is one kind of problem, with its glyph coloured by severity, most severe first. Each column is an area, then all. A dot's size is the count. Click a row to list its objects.

On billing it raises four, each a known flaw of the sample:

• FK without index: payments.invoice_id, → invoices.id. Critical, because invoices has 8,100,000 rows. This is the three-minute DELETE.
• Key-named, no FK: invoice_lines.invoice_id, invoice_events.invoice_id and event_attachments.event_id. Each looks like a foreign key and has no constraint.
• Duplicate index: invoices.invoices_customer_id_idx, covered by invoices_customer_created_idx.
• Sequence near its limit: events.id, 71% of integer.

Every smell the brief can raise, and what it reads:

Fixing what it flags

A row with a script ends in Fix. Fix scripts, the orphan check and Open as design are Pro. The smells and their objects are not.

Fix opens the row with every fixable object ticked and the script for them on the right, headed 1 chosen. For FK without index on billing it reads:

-- payments.invoice_id · → invoices.id
CREATE INDEX CONCURRENTLY payments_invoice_id_idx
  ON billing.payments (invoice_id);

The scripts are written for a live server. Indexes are built and dropped CONCURRENTLY. An inferred foreign key is added NOT VALID, then validated, with an index first when it needs one. A retype warns in a comment that it rewrites the table under an exclusive lock. Nothing runs from the brief: Copy takes the script, and Open in SQL editor puts it in a new editor for you to read and run. For the two key smells, Open as design starts a schema design instead.

Before you add a key the brief inferred, press Check orphans. It counts the rows whose value has no match in the target, and stops at 10,000 (10000+ orphans). A key with orphans would fail to validate, so the brief unticks it. Each check runs in a read-only transaction and gives up after 5 seconds with timed out.

For indexes beyond foreign keys, see Which indexes are missing?.

The ledger

Ledger lists every table under its area, islands last, with Role, ≈Rows, Size, In · Links · Out, Writes as a mix of ins, upd and del, Time cols and Smells. A role is hub, entity, join, lookup, log or island, read from links, size and writes. Filters along the top pick a role, has smell or no updated_at. No billing table has an updated_at, so that last one keeps all six.

Export copies the brief as Markdown or agent context, or saves it as Markdown or HTML.

When the brief has told you where to start, A schema you did not write walks the rest of the first hour.