A schema Sigma's AI built from a picture

I'm a software architect. I've been building RBAC systems for over 30 years.

RBAC is Role-Based Access Control — the machinery that decides who's allowed to see and do what. Users get roles, roles carry permissions on resources, and grants can be scoped to a slice of the business rather than all of it. It's the basis for most multi-tenant systems: that scope is usually the tenant boundary, and it's the thing standing between one customer's data and another's. Nobody notices it until it's wrong, and then it's the only thing anyone notices.

I was curious what Sigma Assistant would do if I attached a mermaid ERD to the prompt. I was pretty amazed at the result.

Ten tables, ten foreign keys — built from a PNG and two prompts. No CSVs, no DDL, no column list.

Prompt 1  (with the PNG attached)
  "re create this ERD of a RBAC system as editible input tables,
   preserving join relationships, and populate it with sample data."

Prompt 2
  "create an expanded join table for users that shows all related information,
   and the resources that they have access to"

So I checked it. I transcribed its output into SQLite and ran the resolution independently: which user reaches which resource, at what scope, view or edit. 11 rows. Matching row for row. Zero referential integrity violations.

$ python3 oracle/verify.py

oracle    11 rows
sigma     11 rows

  Alice Johnson   Editor         Engineering Org  Financial Report    view+edit
  Bob Smith       Administrator  Global           Customer Database   view+edit
  Carol Davis     Viewer         Sales Org        Sales Dashboard     view
  …

  Alice Johnson   oracle=2  sigma=2  ok
  Bob Smith       oracle=3  sigma=3  ok
  Carol Davis     oracle=2  sigma=2  ok
  David Lee       oracle=2  sigma=2  ok
  Emma Wilson     oracle=2  sigma=2  ok

foreign key violations: 0

PASS - the AI's schema resolves correctly

The tell is in the foreign keys

Seven of the ten share a name with the key they point at — user_id → user_id. Any string matcher finds those. Three don't:

Edge	Why a name match misses it	Result
assignments.granted_by → users.user_id	the column isn't called user_id	✅ wired
sessions.acting_as → users.user_id	no name overlap whatsoever	✅ wired
assignments.scope_id nullable	optional is drawn, never named	see below

It wired both of the name-invisible ones. The assignments table came back with two separate joins to users — one for who holds the grant, one for who issued it. Nothing about a column called granted_by says it points at a person. It read that off a line in a raster image.

Then it disagreed with me

My diagram annotated the scope relationship null = global — a grant with a null scope applies everywhere. It read that correctly, and then designed the null away, inventing an explicit Global Scope row so every assignment points at something real.

Not a misreading. A modelling opinion, applied silently. Same meaning, different mechanism — and it removes the need for the LEFT join the original encoding requires.

It made a similar call in the join layer, presenting can_view / can_edit as a single Access Level of Edit / View — a tidier way to show it, and the right one for most readers. This model happens to use the two-flag form deliberately, so that's a simplification I'd unwind; in almost any other schema I'd keep it.

Which is the thing worth knowing: it understands your model well enough to have opinions about how you expressed it. That's a higher bar than reproducing a diagram, and it's the reason this worked at all. Just worth knowing which of your decisions were load-bearing before you accept them.

What's here

erd/
  rbac-erd.png        the image it was given — the only input
  rbac-erd.mmd        mermaid source
  datapackage.json    the original schema, machine-readable (Frictionless Table Schema);
                      the ERD is generated from this, so the two can't drift
oracle/
  schema.sql          THE SCHEMA IT BUILT, as runnable SQL
  data.sql            the sample data it invented, transcribed from the workbook
  resolution.sql      the resolution query — the SQL twin of what it built in Sigma
  verify.py           runs the above and diffs it against the workbook's output
evidence/
  ...-assistant-erd-to-tables.md      the full account, with counts and caveats

oracle/schema.sql is not a reference implementation the AI was graded against. It is its output, transcribed out of the workbook and written down in a form you can run. An AI reproducing a schema is easy to claim and hard to check. These files make it checkable.

Live workbooks

	Sigma Public
The writeup, with prompts and outcomes	1W1wI0dNbHhA9mko9E0ont
Tables only, before relationships	7ELVW8RD2edKdw4apJ27BT
Tables + joins	183oqTkgZ8StPDZOEHfUus

I've arranged these, but I haven't fixed anything. The elements are laid out across tabs so they can be read in order; nothing has been renamed, rebuilt, corrected or hidden. The staging tables are there, Invalid Query: Column … does not exist errors and all. The final table is clean; the road there isn't. That's what you'll see if you try it, so that's what I'm showing.

The one thing I changed is navigation. The assistant split its work across two pages — raw tables on one, outcome tables on the other, which is a sensible separation. I consolidated them onto tabs in a single page so they can be stepped through in order.

Worth knowing if you re-create this: you'll get those two pages rather than tabs. Same tables, same joins, same output — but on Sigma Public a viewer of a published app gets no page navigation unless something explicitly links there, so a second page is easy to miss. Tabs are simply easier to walk someone through.

Those errors are worth understanding rather than hiding. They come from the AI attempting to use Sigma's internal row identifiers by default — ID, SEQ_NUM, ROW_VERSION, UPDATED_AT — and carrying those references through the joins. The columns don't exist on those elements, so every reference reports Column … does not exist.

That instinct runs against the idea of an ERD, where the declared keys are the contract and reaching past them for the storage engine's internal identity is the layer the diagram exists to sit above.

But it's the right default almost everywhere else. Most people don't define their schema with explicit keys at all — they load a table, the platform assigns row identity, and the internal ID is genuinely the only stable handle available. Defaulting to it is correct for the common case. This ERD is the uncommon one: GUID-keyed throughout, every key named and declared, precisely because the model is meant to be portable rather than tied to whatever engine happens to be underneath.

Especially given who the assistant is for. Its job is to help people who aren't steeped in SQL build SQL-based systems — and those users are exactly the ones who will never declare a key, never think about referential integrity, and never want to. Reaching for a row identifier that always exists is the right instinct when the person you're helping doesn't know they were supposed to supply one.

So this isn't the model getting it wrong. It's the model applying a sensible default to a schema that had already answered the question — completely understandable, and the errors it leaves behind are cosmetic.

And it does no harm. The final output is exactly what the ERD described — the declared keys are what the joins actually resolve on, the relationships are the ones drawn, and the resolution verifies against SQL row for row. The internal-ID references are noise in the staging layer that never reaches the result.

It's simply the seam where the model's platform instinct and the diagram's intent pull in different directions, and it's visible in the workbook if you scroll.

Column naming sits in the same bucket — some tables came back in Title Case (Department ID, User Name), others in snake_case (platform_role_id, acting_as), from a source that was snake_case throughout. Cosmetic, and invisible to the result.

Ask it yourself

The workbook has a fifth tab: an agent grounded on the tables it built. You can interrogate the result directly — who can reach what, which grants are scoped where, who issued them.

It introduces itself with this:

Role assignments — which users have which roles and scopes Permissions — what resources users can view or edit Assignments — when access was granted and by whom

That last line is the granted_by edge again, described as audit semantics — by a different AI surface, grounded on tables that were themselves reconstructed from a picture. Nothing in the schema says that column means "who issued this grant." It's inferred, twice, independently.

Which is the part that impresses me most about the platform rather than the model: the whole path — image to tables to joins to a grounded agent that can answer questions about them — is a couple of prompts inside one product.

Honest limits

• This validates the joins and the resolution, not the modelling decisions. The Global Scope sentinel and the Access Level collapse don't produce wrong answers on this data.
• res-004 has no permission rows, so no user reaches it. Oracle and workbook agree — but the fixture never exercises a case with all four resources in play.
• Sample data was read out of the rendered workbook by hand — Sigma's data grid exposes no per-cell nodes to the accessibility tree, so values can't be queried, only seen.
• Two prompts, not one. The relationships were asked for; which relationships was not.