The Database

Freddy gets tags — and the schema that will power search in Chapter 9. The theme of this chapter: stop letting the agent guess what your database looks like when it can simply ask.

The problem

A codebase describes its database twice: once in migrations (history) and once in models (intent). Neither is the truth. The truth is the actual schema in the actual database file — and agents that only read code routinely drift from it: a column renamed in a later migration, an index that exists in code review but never ran, a pivot table someone created by hand in March.

The hard way

The agent reads four migration files, mentally replays them, and writes a query against the schema it imagines. On drift, you get the worst kind of bug: code that looks right against the codebase and fails against the database.

The Elyra way: install db-tools

/ext

Select db-tools (space, enter), then /reload. Freddy runs on SQLite, which db-tools auto-detects from .env (DB_CONNECTION=sqlite). Two tools matter today:

• get_database_schema — tables, columns, types, and indexes, read from the live database.
• query_sqlite — read-only SQL (SELECT by default), every query shown to you for approval before it runs.

Schema first, code second

Now the habit this chapter exists to teach. Before any migration work, one sentence:

> Check the actual database schema before you plan anything.

The agent calls get_database_schema and gets the truth:

  get_database_schema (sqlite)

  notes: id, title (nullable), content, archived_at (nullable),
         created_at, updated_at
  users: id, name, email, ...
  migrations: id, migration, batch

No guessing, no replaying migrations in its head. Once the agent knows this tool exists it starts using it unprompted — but say it explicitly the first time, and consider adding a line to AGENTS.md: "Check the live schema (get_database_schema) before writing migrations or queries."

Tags and the pivot table

> Add tags per SPEC.md: flat, zero-or-more per note. Migration
  for tags (unique name) and note_tag pivot, Tag model,
  belongsToMany both ways, and extend NoteController@store to
  accept an optional array of tag names - create-or-attach.
  Done means: a note saved with ["ideas","elyra"] has both
  tags in the database.

Same prompt shape as Chapter 4: scope, constraints, definition of done. The edit loop does its thing (one auto-caught diagnostic when the agent typos belongsToMany against a not-yet-imported model — fixed in the same turn). Then verification happens against the database, not against vibes:

  query_sqlite: SELECT n.id, n.title, group_concat(t.name)
    FROM notes n JOIN note_tag nt ON nt.note_id = n.id
    JOIN tags t ON t.id = nt.tag_id GROUP BY n.id;

  Approve query? [y/n] y

  [{"id": 1, "title": null, "group_concat(t.name)": "ideas,elyra"}]

That approval prompt is deliberate: the agent can read your data only when you say so, query by query.

The search foundation (FTS5)

Chapter 9 builds search; today we lay the schema for it while we're in database-land. This is also where Chapter 1's /btw research (SQLite FTS5 over Meilisearch) becomes code:

> Add the FTS5 foundation: a notes_fts virtual table
  (title, content) kept in sync with notes via triggers on
  insert/update/delete. New migration. Verify with a query
  that matches "elyra" after inserting a test note. No UI,
  no controller changes - schema only.

The agent writes the migration, runs it, and proves it works with an FTS query you approve:

  query_sqlite: SELECT title, content FROM notes_fts
    WHERE notes_fts MATCH 'elyra' LIMIT 3;

  [{"title": null, "content": "Try the elyra swarm on..."}]

Schema shipped, proven, and boring — exactly what you want from a foundation. Commit:

> Commit as "feat: tags + FTS5 search foundation"

What you learned

• The live schema is the truth — migrations are history, models are intent. get_database_schema before planning, always.
• Verify against the database, not against the code: query_sqlite with per-query approval.
• Promote habits into AGENTS.md. "Check the schema first" said once becomes behavior forever.
• Lay foundations early, use them later. The FTS5 table costs ten minutes today and makes Chapter 9 a UI exercise instead of a schema scramble.