MySQL / DB tools

The mysql connector contributes 20 tools — the largest surface in the assistant today. They’re organised into three layers that the model is prompted to try in order: curated first, saved reports next, generic SQL last.

Architecture: three layers


┌──────────────────────────────────────────────────────────────┐
│  Layer 1 — Curated query tools                               │
│  18 hand-crafted tools for the questions we know we get      │
│  every week. Highest priority — pick these when the          │
│  question matches.                                           │
└──────────────────────────────────────────────────────────────┘
              ↓ fallback if no curated tool fits
┌──────────────────────────────────────────────────────────────┐
│  Layer 2 — Saved reports catalog                             │
│  Admin's pre-validated SQL reports from the `reports` table  │
│  (dozens, accumulated over years). Use mysql_list_saved_     │
│  reports to discover, then mysql_run_saved_report to fire.   │
└──────────────────────────────────────────────────────────────┘
              ↓ fallback for truly novel questions
┌──────────────────────────────────────────────────────────────┐
│  Layer 3 — Generic escape hatches                            │
│  mysql_run_read_sql + mysql_describe_table. Read-only,       │
│  SELECT-only, capped row count. Last resort when the         │
│  curated and saved-report layers don't cover the question.   │
└──────────────────────────────────────────────────────────────┘

The model is told to prefer earlier layers via the connector’s system-prompt block (MysqlConnector.ts → connectorSystemPrompt). The bot wraps generic-SQL calls with a “look — I’m using the escape hatch” framing so reviewers can see when curated coverage is missing.

Access boundary

Every query runs as the assistant_app MySQL user. That user has:

SELECT only on the curated table list (no INSERT/UPDATE/DELETE/DDL anywhere)
Column-level grants on members — password and salt are NOT visible. Any query against them returns a permission error.
SELECT on INFORMATION_SCHEMA.{columns,statistics} for mysql_describe_table

The full grant list is in migrations 0002-0006 under assistant/sql/migrations/. The list of visible tables is in /tech/db-schema (and the column-level reference at assistant/docs/db-columns.md).

Layer 1 — Curated query tools (18)

Each tool wraps one or more carefully-tuned SQL queries that encode IBA business definitions (active member, renewal, level completion, etc.). The model is given the description below as the JSON-Schema description for that tool.

Members and identity

Tool	Purpose
`mysql_count_members`	Count members in the database. Optional filters: `joinedSince` (ISO date), `roleId`.
`mysql_recent_signups`	Most recent N member signups, ordered by `join_date` desc. Returns username, screen_name, email, country, role, join date.
`mysql_members_by_country`	Count members grouped by country, sorted descending. Joins to `countries`.
`mysql_members_by_role`	Count members grouped by `role_id` (joined to `member_roles` for the role name).
`mysql_registrations_in_period`	Count registrations between `from` and `to` (YYYY-MM-DD inclusive), optionally grouped by month, tunnel, or role. Excludes admins (role_id=1) + banned (role_id=4) by default.
`mysql_active_members_count`	Count members who logged in within the last `withinDays` days (defaults 30). Uses `members.last_visit`.
`mysql_active_inactive_by_role`	Count active vs inactive members by level for instructor / trainer roles. Active = `members.currency_<role>` is 1 or NULL.

Payments and revenue

Tool	Purpose
`mysql_revenue_summary`	Sum revenue from `fees` (status=‘succeeded’, amount>0) in a date range, optionally grouped by tunnel / role / country. Returns total, distinct paying members, payment count, per-group breakdown. Preferred over saved reports for revenue questions.
`mysql_non_renewals_in_period`	Count members whose subscription lapsed in `from`–`to`. Returns count + sample of up to 100 lapsed members.
`mysql_dashboard_headlines`	Headline metrics for a date range — revenue, cost, distinct paying members, never-paid trial members, renewed members. Mirrors the admin dashboard’s financial cards.

Logbook and skills

Tool	Purpose
`mysql_top_logbook_contributors`	Top N members by `channel_logbook` entry count.
`mysql_logbook_signoffs_by_instructor`	List sign-offs by a specific instructor between `from` and `to`. Covers all three currency-checkbox tables.
`mysql_level_completions_in_period`	Count level completions (first achievement of a level) between `from` and `to`. Currently supports `role='instructor'` (level 1, skill 361) and `role='coach'` (skills 362/364/365).

Currency

Tool	Purpose
`mysql_currency_holders_by_role`	Count members with active vs expired currency for a given role (`flyer` / `coach` / `instructor` / `trainer` / `military`). Uses the cached `members.currency_<role>` flag.

Operational

Tool	Purpose
`mysql_recent_cron_runs`	Most recent N entries from `op_cron_logs` (api/‘s cron job log — NOT the assistant’s own crons).

Layer 2 — Saved reports catalog (3 tools, dozens of reports)

The admin team has built dozens of SQL reports in the reports table over the years. Each encodes business definitions (renewal, level completion, etc.) that would otherwise have to be reconstructed. The assistant accesses them via three tools:

Tool	Purpose
`mysql_list_saved_reports`	List the saved reports admins have built. Returns `[{id, label, status, createdAt}]`. Defaults to active reports only; `includeArchived=true` to see retired ones.
`mysql_describe_saved_report`	Return the SQL text + parsed default params of a report without running it. Use to inspect a candidate report before executing. Accepts `id` (number) or `label` (string).
`mysql_run_saved_report`	Execute a report by `id` or `label`. Optional `params` overrides values bound from the report’s `SET @var = ...` prefix. Returns rows + the SQL that ran + bound params for transparency.

The agent’s system prompt tells it: before writing custom SQL via mysql_run_read_sql, check this catalog. List → describe → run is the canonical flow.

Layer 3 — Generic escape hatches (2)

For truly novel questions that neither curated tools nor saved reports cover:

Tool	Purpose
`mysql_describe_table`	Column types, nullability, defaults, indexes for a table. Only tables the `assistant_app` user has SELECT on are visible (others return “not found”). Use when planning a `mysql_run_read_sql` query against an unfamiliar table.
`mysql_run_read_sql`	Run a read-only SQL query. Only SELECT and WITH … SELECT are allowed. Results capped at a row limit (currently 500). Audit-logged with the SQL text.

When the assistant picks the wrong tool

The model occasionally favours mysql_run_read_sql when a curated tool would have answered correctly. Usually this is because the curated tool’s description field didn’t trigger keyword-match for the user’s question. Fix: edit the curated tool’s description in its *Tool.ts file to include the missing wording. The description is the only thing the model sees about each tool — invest in it.

Adding a curated tool

The recipe (~30 minutes for a simple one):

New file under assistant/src/infrastructure/connectors/mysql/curated/<Question>Tool.ts. Export a single defineTool({…}).
Write the SQL using :named placeholders (project convention — see assistant/docs/CONVENTIONS.md). Use executeMainDbQuery for IBA-schema reads.
Test fixture in savedReports.test.ts or a dedicated <Question>Tool.test.ts if param handling is non-trivial.
Register the tool in MysqlConnector.ts → getTools(). Order doesn’t matter for the agent (the model sees all tools) but keeping the list grouped by domain helps human review.
Re-run the migration grants if the tool reads a new table (assistant/sql/migrations/000N_grant_assistant_app_select_<table>.sql).
Regenerate the column reference: cd assistant && npm run sync:schema.
Update the table above on this page so the documented tool surface matches what the assistant actually exposes.