Database schema reference

This page lists every MySQL table the internal AI assistant can read, along with what each table is for in business terms.

Two sections:

1. Business-domain groupings (this section) — human-curated. Why each table exists and how to think about it.
2. Auto-generated column reference (below the markers) — regenerated by npm run sync:schema from INFORMATION_SCHEMA. Don’t edit by hand.

Audience

• Engineers writing migration code or new features
• The assistant — this page is inlined into its system prompt’s cached block, so it can pick the right table + columns on its first tool call

Access boundary

The assistant connects as the assistant_app MySQL user. That user has:

• SELECT only on the curated tables listed below
• Column-level grants on members — password and salt are NOT visible to the assistant. Any SELECT against them returns a permission error.
• Plus full SELECT on INFORMATION_SCHEMA.{columns,statistics} for the mysql_describe_table tool.

A separate set of GRANTs (INSERT/UPDATE/SELECT/DELETE on assistant_* tables) covers the assistant’s own data — that side is not documented here.

Common query shapes

Patterns that recur across the admin SQL we built up over time. Use these as starting points when generating ad-hoc queries — they encode invariants the LLM can’t infer from column names.

Q-1 — Real revenue (“money actually received”)

WHERE fees.status = 'succeeded' AND fees.amount > 0

fees.status is webhook-updated by Stripe. Other values ('failed', 'pending', 'cancelled') appear in the table but should NOT count as revenue. For deeper analytical queries (renewal-segmentation, monthly revenue summaries) also exclude coupon-discounted rows:

WHERE fees.status = 'succeeded'
  AND fees.amount > 0
  AND (fees.coupon IS NULL OR fees.coupon = '')

Q-2 — Active subscription as of a specific date

SELECT DISTINCT member_id
FROM fees
WHERE status = 'succeeded'
  AND payment_date <= :asOfDate
  AND next_payment_date > :asOfDate
  AND NOT EXISTS (
    -- exclude rows that have been superseded by a later payment before :asOfDate
    SELECT 1 FROM fees f2
    WHERE f2.member_id = fees.member_id
      AND f2.payment_date > fees.payment_date
      AND f2.payment_date <= :asOfDate
  );

A member is “active” at a point in time if their subscription window covers that point AND there’s no later payment that supersedes it.

Q-3 — Epoch to year-month

Almost every date column in the legacy schema is a UNIX epoch (int), NOT a TIMESTAMP. To bucket by month:

EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(epoch_col))   -- returns "YYYYMM"
DATE_FORMAT(FROM_UNIXTIME(epoch_col), '%Y-%m')      -- returns "YYYY-MM"

Epoch columns observed: members.join_date, members.last_visit, fees.payment_date, fees.next_payment_date, fees.created_at, channel_logbook.entry_date, channel_currency_*.entry_date, channel_currency_*_checkboxes.entry_date.

Q-4 — Rolling 12-month calendar scaffold

To make “last N months with zeros for empty months” queries, build a date scaffold via UNION ALL then LEFT JOIN counts against it:

WITH calendar AS (
  SELECT EXTRACT(YEAR_MONTH FROM LAST_DAY(now()) + interval 1 day - interval 12 month) AS yearMonth
  UNION ALL SELECT EXTRACT(YEAR_MONTH FROM LAST_DAY(now()) + interval 1 day - interval 11 month)
  UNION ALL ...
)
SELECT calendar.yearMonth, IFNULL(num.count, 0) AS count
FROM calendar
LEFT JOIN (...counts grouped by yearMonth...) num ON calendar.yearMonth = num.yearMonth
ORDER BY calendar.yearMonth;

The 12-row UNION is verbose but it’s the canonical pattern in the legacy admin reports.

Q-5 — Currency aggregation pivot (per-tunnel role breakdowns)

For “members per role per tunnel” with active/inactive split:

SELECT
  CT.title AS tunnel_name,
  SUM(CASE WHEN role.name = 'Flyer' THEN 1 ELSE 0 END) AS Flyer,
  SUM(CASE WHEN role.name = 'Flyer' AND (M.currency_instructor != 0 OR M.currency_instructor IS NULL) THEN 1 ELSE 0 END) AS FlyerActive,
  SUM(CASE WHEN role.name = 'Flyer' AND M.currency_instructor = 0 THEN 1 ELSE 0 END) AS FlyerInactive,
  -- repeat for Instructor, Trainer, Examiner, Tunnel Manager, Airflow Controller
  ...
FROM members M
JOIN channel_tunnels CT ON CT.entry_id = M.tunnel
JOIN member_roles role ON M.role_id = role.role_id
GROUP BY CT.title, CT.entry_id, CT.manufacturer
ORDER BY CT.title;

Q-6 — Cumulative running total

For “cumulative member count over time”:

-- Modern (MySQL 8+)
SELECT yearMonth, SUM(monthly_count) OVER (ORDER BY yearMonth) AS cumulative
FROM (...);
 
-- Legacy session-variable pattern (used in admin code)
SELECT @running := IFNULL(@running, 0) + monthly_count AS cumulative
FROM ( ... ordered by yearMonth ... ) T1, (SELECT @running := 0) T2;

Q-7 — Exclude banned members

-- members.role_id = 4 means BANNED — the member asked to be removed.
-- Most analytical queries should exclude them. Admin role is role_id = 1.
WHERE members.role_id NOT IN (1, 4)

Business-domain groupings

Members and identity

• members — the source of truth for member accounts. Joined date, last visit, currency flags per role (flyer/coach/instructor/military/trainer), approval levels, home tunnel. Excludes password and salt — those columns are not in the assistant’s grant.
• member_roles — lookup table mapping role_id → human role name (admin, member, instructor, etc.).
• member_logins — append-only login event log. Useful for “when did Alice last log in” or “logins this month” questions.
• countries — country lookup, joined from members.country (numeric id).

Relationships:

• members.role_id → member_roles.role_id
• members.tunnel → channel_tunnels.entry_id (column is named tunnel, NOT tunnel_id)
• members.country → countries.id
• members.member_id ← member_logins.member_id (logins are append-only events)

Business definitions:

• role_id = 4 means BANNED — members who asked to be removed from the system. Rarely used in practice but they stay in the table for audit reasons. Most analytical queries should exclude role_id IN (1, 4) (admin + banned).
• Email is NOT unique — family members (parents + children) often share the parent’s email address. Don’t use email as a join key.
• Username uniqueness is not guaranteed — historically may have duplicates. Use member_id for joins, never username.
• members.currency_instructor is the universal “is current?” flag despite the name. The column is reused across roles — for Flyer, Trainer, Examiner, Tunnel Manager, etc. The role-specific currency tables (channel_currency_*) carry the canonical per-role currency state; members.currency_instructor is a denormalised cached flag for fast queries. Values: 1 or NULL = active/current, 0 = inactive/expired.

Payments and subscription state

• fees — line items per member per period. Represents subscription state, NOT invoices.
• fees_mapping — maps fee types to their pricing / metadata.
• ad_costs — paid-ads cost tracking. Used by the financial report’s cost-side queries. Standalone (no FKs to other tables).

Relationships:

• fees.member_id → members.member_id (the owner — the person whose subscription this is)
• fees.payer_id → members.member_id (the payer — the person who actually paid; can differ from owner for corporate / sponsored / family payments)
• fees.group_id → member_roles.role_id (the payment is for membership in this role)
• fees_mapping is joined to fees implicitly via fee-type fields (fees.member_status / similar — check the schema for the exact column)

The owner vs payer split matters: for “how much money did each member spend” use payer_id; for “which subscriptions are active” use member_id.

Business definitions:

• Real revenue — see Q-1 above. Always filter fees.status = 'succeeded' AND fees.amount > 0. Other values exist in the table but don’t count as revenue.
• Active subscription as of date X — see Q-2 above. Three predicates required: payment_date <= X, next_payment_date > X, and “no later payment supersedes this one before X” via the NOT EXISTS subquery.
• Active member (payment-time) — COUNT(DISTINCT member_id) FROM fees WHERE status='succeeded' AND payment_date BETWEEN :from AND :to. This counts “members who paid in the period”, different from Q-2’s as-of definition.
• Renewal (simple) — used by dashboard.getTotalMembersRenewed. A payment counts as a renewal if the member had a previous succeeded fee whose next_payment_date had already passed: f1.payment_date > f2.next_payment_date (self-join on member_id).
• Renewal (analytical, three-way split) — used by financial.getFee for monthly stats. Splits into renewed_same_month (paid before previous expiry — continuous coverage) and renewed_after_expiration (lapsed then returned — reactivation). Used for churn/retention analysis where the distinction matters.
• Non-renewal in period [X, Y] — derive from negation: a member who had a succeeded fee with next_payment_date in [X, Y] and has NO succeeded fee with payment_date >= X. They lapsed during the window without coming back.
• Never-paid member — used by dashboard.getTotalMembersNeverPaid. A member with ≥8 of the Flyer Level 1 skill IDs logged (see “Skill ID reference” below) AND no fees row ever. These are trial accounts that engaged with logbook but never converted to payment.

Logbook, skills, tunnels

• channel_logbook — flight session records. One row per logged session; author_id is the member who flew.
• channel_skills — the catalogue of trainable skills. Multi-lang: filter WHERE lang = 'en' for reporting queries.
• channel_skills_categories — skill groupings, hierarchical via parent_id self-reference. Multi-lang: filter WHERE lang = 'en'.
• channel_skills_category_posts — junction table mapping skills to categories (a skill can be in multiple categories).
• channel_tunnels — wind tunnels in the system. title is the human-readable name; manufacturer and status ∈ {Announced, Open, Construction, Closed}.

Relationships:

• channel_logbook.author_id → members.member_id (the column is author_id, NOT member_id — this trips up everyone)
• channel_logbook.skill → channel_skills.entry_id
• channel_skills.entry_id ← channel_skills_category_posts.entry_id
• channel_skills_category_posts.cat_id → channel_skills_categories.cat_id
• channel_skills_categories.parent_id → channel_skills_categories.cat_id (self-join for sub-categories)
• channel_tunnels.entry_id ← members.tunnel (and many other tables)

Business definitions:

• channel_logbook.status enum: 'open' (active, current), 'suspended', 'not_current' (was open, currency expired), 'requested' (in-flight, awaiting approval). The standard “completed/credited skill” filter is status IN ('open', 'suspended', 'not_current') — this excludes 'requested' (still pending). See docs/app/business-logic/logbook/skill-flow/page.mdx for the state machine.
• Skill completion — a row in channel_logbook with author_id = member_id, skill = X, and status IN ('open', 'suspended', 'not_current') means the member completed skill X. The MIN(entry_date) for that (member, skill) is the first completion.
• Level completion (instructor / coach / trainer / examiner) — based on completing a specific set of “open” skills in channel_skills mapped via channel_skills_categories. For Instructor Level 1, the canonical skill is 361. For Coach, skills 362, 364, 365. For Trainer / Examiner / other levels, the skill set is defined in the category mapping — query channel_skills joined through channel_skills_category_posts and channel_skills_categories to find the skill list for a given level/category. (Curated tools landing in Phase 3.5d will encode the canonical sets so the LLM doesn’t have to discover them every time.)
• Skill category completion — a member has “completed” a category when their completed skills cover EVERY status='Open' skill in that category. The pattern is a HAVING clause: HAVING COUNT(DISTINCT logged_skill_ids) >= total_open_skills_in_category. Used by logbook.getCustomSkillCatMembers.

Currency rates (per role)

The currency tables track how recent each role’s certifications are. After Phase 3.5c the assistant has access to all five role tables and their matching checkbox tables:

• channel_currency_flyer — flyer-role currency data.
• channel_currency_coach — coach-role currency data.
• channel_currency_instructor — instructor-role currency data.
• channel_currency_trainer — trainer-role currency data. (granted in migration 0006)
• channel_currency_military — military-role currency data. (granted in migration 0006)
• channel_currency_flyer_checkboxes, channel_currency_coach_checkboxes, channel_currency_instructor_checkboxes — individual sign-off events per currency row. Each row records which instructor signed off, at which tunnel, on which date. (granted in migration 0006)

Relationships:

• channel_currency_<role>.author_id → members.member_id (the member whose currency this tracks)
• channel_currency_<role>.entry_id ← channel_currency_<role>_checkboxes.entry_id (one currency row has many checkbox sign-offs)
• channel_currency_<role>_checkboxes.instructor → members.member_id (who did the sign-off)
• channel_currency_<role>_checkboxes.tunnel → channel_tunnels.entry_id (where the sign-off happened)

Business definitions:

• Current vs expired — see B-7 / members.currency_instructor note in the Members section above. The cached flag on members is the cheap source of truth for “is X currently current?”; the per-role currency tables are the canonical source for “when did X’s currency last change, and which instructor signed it off?”.
• Sign-off lineage — to answer “what sign-offs did Instructor 12345 make in June”, query the checkbox tables with instructor = 12345 AND entry_date BETWEEN :from AND :to. Saved report #23 (“What has Instructor approved or updated since set date”) is the canonical implementation.
• Cache freshness caveat — the cached members.currency_* flags are kept in sync by cron jobs. For analytical queries (which mostly run after the daily cron) the cache is reliable. For real-time admin tasks (e.g. “an instructor just lost currency mid-day”) consult the per-role tables and their checkbox tables directly.

Notifications

• channel_notifications — push/in-app notifications sent to members. Multi-lang: filter WHERE lang = 'en' for reporting.
• channel_notifications_categories — notification category lookup.
• channel_notifications_category_posts — junction mapping notifications to categories.
• channel_notifications_read — sparse log of which members read which notification.

Relationships:

• channel_notifications.entry_id ← channel_notifications_category_posts.entry_id (junction)
• channel_notifications_category_posts.cat_id → channel_notifications_categories.cat_id
• channel_notifications.entry_id ← channel_notifications_read.entry_id
• channel_notifications_read.member_id → members.member_id

Operational

• op_cron_logs — log of api/ cron job executions. NOT the assistant’s own cron logs (those live in assistant_cron_logs, which is granted separately).
• reports — the saved-queries catalog (40+ rows). NOT the assistant’s own data — these are SQL queries that admins built up over years for one-off analytical needs. Columns: id, label, query (the SQL text), status (1 = active, 0 = archived), created_at, updated_at. The assistant’s mysql_list_saved_reports + mysql_run_saved_report tools (Phase 3.5d) read from here. (granted in migration 0006.)
• op_custom_reports_skill_categories, op_custom_reports_skills — admin-defined groupings for the custom-skill reports (logbook module). Lookup-only.

Skill / role / status reference

The legacy schema uses lots of magic numbers (skill IDs, role IDs, status codes) whose meaning isn’t obvious from the column. This section captures the ones the LLM needs to answer Rusty-class questions.

Member roles (members.role_id / member_roles.role_id)

For “active member” counts, the standard filter is role_id IN (6, 8, 9, 10, 11, 12, 13) — i.e. everyone except admin and banned.

Skill IDs (subset — extend as needed)

For other levels (Trainer Level 1–4, Examiner, Tunnel Manager), the canonical skill set lives in channel_skills joined through channel_skills_category_posts and channel_skills_categories. Use channel_skills_categories.cat_name as the level marker.

channel_logbook.status enum

The standard “completed/credited skill” filter is status IN ('open', 'suspended', 'not_current') — excludes 'requested' which hasn’t been approved yet. See /business-logic/logbook/skill-flow for the state machine.

fees.status (Stripe-driven)

How to extend

To add a new table to the assistant’s read access:

1. Write a new migration: sql/migrations/000N_grant_assistant_app_select_<table>.sql
2. Use column-level grants if the table has any sensitive columns
3. Apply locally: npm run migrate
4. Regenerate this doc’s auto-section: npm run sync:schema
5. Add a paragraph to the appropriate “Business-domain groupings” section above
6. Commit migration + regenerated doc + curated paragraph together

Full column reference (auto-generated)

For every column, type, default, nullability, and index on each table the assistant_app user can SELECT, see the auto-generated reference file in the assistant repo:

assistant/docs/db-columns.md

The file is regenerated by cd assistant && npm run sync:schema against INFORMATION_SCHEMA. Run that after any migration that adds or removes a table the assistant can see (any change to its GRANT list).

Why is this in the assistant repo and not inlined here? Nextra v4 with Turbopack can’t render this many markdown tables on a single page — empirically it 500s around 8+ tables. Keeping the column reference in the assistant repo lets the docs site stay fast while the column details remain version-controlled and viewable on GitHub.