# Hermes Chat → Data access (the assistant reading PETA)

**Portal:** none (machine API) · **Routes:** `webhooks.hermes-lab.data.{whoami,tables,describe,query}`
(`POST /webhooks/hermes-lab/data/*`) · **Parent:** [Hermes Chat](/docs/modules_handbook/agent-app/hermes-chat/readMe.md)

## What it does

A person's Hermes assistant answers questions from live PETA data ("which of my leads went quiet this
week?", "cost per lead for the AI 买房 campaign"). It reads **with the PETA permissions of the person it
belongs to** (Lee Jie, 2026-10-31: "follow back petav3 user and permission"):

- **Which tables:** the ones that person's PETA pages show. Meta ad tables need `view-marketing`,
  payments `view-sales`, bookings `view-projects`, and so on. Super admins pass every permission, as
  they do in PETA.
- **Which rows:** someone who sees only some leads (own / team / group, `LeadVisibility`) reads only
  those leads' rows in every lead table: leads, comments, tasks, WhatsApp / Messenger / LINE
  conversations and messages, calls, Zoom, appointments, bookings, the users behind the leads.
- **Never, for anyone:** passwords, tokens, report/join links, Zoom passwords, WhatsApp bridge keys, IC
  numbers and dates of birth, credential stores, other people's assistant chats, and the CEO's private
  WhatsApp (PETA limits that channel to its owners, even among super admins).

Until 2026-10-31 the box connected to the database directly as `hermes_ro`, with 31 hand-picked tables
for everyone. The box now holds **no database login at all**.

## How it works

**The path.** The box's database tool (`/usr/local/lib/hermes-lab/peta-db/peta_db_mcp.py`, one per
profile) keeps its four tools (`whoami`, `list_tables`, `describe_table`, `run_query`). Each one is
a signed POST to PETA:

- Signature: HMAC-SHA256 over `hermes-lab-data\nPOST\n<path>\n<unix time>\n<body>`, keyed with **that
  profile's own API key** (`HermesClient::apiKey`), the profile named in `X-Lab-Profile`. At most five
  minutes old (`HermesLabDataRequest`).
- The profile must be a ready, switched-on assistant of an active staff member. The request then runs
  as that user. 403 otherwise; a refusal or a failed query is a 200 with `data.error`, which the
  assistant reads and acts on.
- Throttle `hermes-data`: one bucket per profile (`hermes.data.per_minute`, 60), since every call
  comes from the box's one IP.

**One query** (`Src\Hermes\Services\HermesDataService::query()`):

1. **Shape:** one `SELECT` (or `WITH … SELECT`), no `;`, no `INTO`, no locking read.
2. **What it touches:** `EXPLAIN` on PETA's own connection, which plans the query without running it,
   then `SHOW WARNINGS`. MySQL hands back the query as it rewrote it, with every table spelled out
   (aliases, subqueries, CTEs). Both go over the plain text protocol: `SHOW WARNINGS` fails through
   prepared statements (error 1295). Only errors about the SQL itself are passed back, so a failing
   `EXPLAIN` on the privileged connection cannot be used to probe data. A plan step that writes, or a
   table in any other schema (`information_schema` shows up as `mysql`), is refused.
3. **Permissions:** each table against `Src\Hermes\Support\HermesDataTables`, below. A secret column
   *named* in the query is refused by name.
4. **Rows:** for someone who sees only some leads, each lead table the query touches is replaced, for
   this one connection, by a `TEMPORARY` table of the same name holding only their rows. A temporary
   table hides the real one even when the query names its schema, so there is no way round it. The
   copies are made under `READ COMMITTED`, a consistent read: under MySQL's default
   `REPEATABLE READ`, `CREATE TABLE … SELECT` holds shared locks on every source row, which made PETA's
   own writes to those leads wait (found while testing).
5. **The query** runs on the `hermes_data` connection: at most `hermes.data.max_rows` (200) rows,
   `hermes.data.statement_ms` (10 s), unbuffered. Afterwards the connection is purged, so its session
   and temporary tables go with it.

Every call is logged to `storage/logs/hermes-data-*.log`: profile, user, SQL, tables, rows, ms, and
why it was refused. The rows themselves are never logged.

**The table map** (`HermesDataTables`). Every table resolves to:

- **a permission**, from its exact entry in `PERMISSIONS`, else its longest matching prefix: `STAFF`
  (any staff member), `SUPER`, a permission, or a list where any one suffices. A table with no entry
  is not readable, so a new table stays closed until someone maps it.
  `php artisan hermes:data-schema --missing` lists those.
- **its rows** (`rows()`):
  - a row rule in `SCOPES`, or a `lead_id` column, means **scoped** (only the person's leads). This
    always wins over OPEN.
  - `OPEN` means read in full: reference data with no customer in it (catalogue, market, Meta ads,
    templates, staff directory…).
  - anything else is readable only by someone who sees every lead.
- Row rules: `leads` (the table itself), `lead` (a lead id column), `lead_or_owner` (Zoom meetings,
  recordings, calls: the lead is visible, or there is no lead and the person owns it), `lead_users`
  (users / profiles of visible leads, plus every staff member), `conversations`
  (`LeadVisibility::applyToConversations`), `contacts`, and `via` (belongs to a visible row of a
  parent, e.g. `whatsapp_messages` via its conversation).
- `DENY_TABLES` / `DENY_PREFIXES` and `SECRET_COLUMNS` apply to everyone.

**The view schema** (production). `php artisan hermes:data-schema` prints SQL that builds
`<database>_hermes`:

- one `SQL SECURITY DEFINER` view per mapped table, listing only its non-secret columns;
- on every WhatsApp table, a filter that leaves out the CEO channel (`WhatsappChannel::PURPOSE_CEO`);
  a contact known only through that channel is left out too;
- a `DROP VIEW` for every denied or unmapped table.

The `hermes_data` login holds only `SELECT, CREATE TEMPORARY TABLES` on that schema. The database itself
refuses a secret column or a base table (`SELECT command denied … for table 'users'`), whatever SQL
arrives. **Run the command again after a migration adds a table or a column:** a view lists its
columns when it is made, so new ones stay closed until then. Locally `HERMES_DATA_SCHEMA` is empty: the
service reads PETA's own tables and strips secret column names from the rows instead (this is how the
tests run).

**The box's own jobs read PETA the same way** (2026-11-02). The Morning brief and Nightly
WhatsApp capture jobs run `/usr/local/lib/hermes-lab/lab_peta.py` on the box before the
assistant wakes. It is stdlib Python, and it signs with the profile's key exactly like the tool,
so PETA applies the same permissions and rows:

- `snapshot` writes `_System/chief-of-staff/peta-today.md`. That holds the person's counts, the
  buyers waiting on their WhatsApp reply, their follow-ups due, new leads, appointments, and a
  per-colleague table for anyone who sees more than their own leads.
- `whatsapp --date` writes `08. Sources/whatsapp/<date>.md`. That is one day of the person's
  WhatsApp, kept out of the brain's git and pruned after 30 days.
- "The person's WhatsApp" means a conversation whose contact is their lead, or one on their own
  line whose contact is nobody else's lead.
- Emails, phone numbers, IC numbers and income are stripped from every message line.
- The box log records these calls as `job:query`.
- Message rows from the buyer have no `sent_at`, so the jobs date them by `created_at`.

**Limits worth knowing.**
- Someone who sees only some leads can use each lead table once per query (MySQL: "Can't reopen
  table" on a temporary table): no self-join, and not in a subquery as well as the main query. The
  assistant is told to split the query.
- A derived table (`FROM (SELECT …) x`) is refused as "not PETA's own tables (not x)". `inspect()`
  reads the derived table's `x`.`column` references as a schema named `x`. Count in two queries,
  or group in the caller, until `inspect()` learns derived and CTE names.
- Views are owned by the admin login that ran the SQL. Deleting that login breaks them.
- The CEO's own assistant cannot read his private WhatsApp either. That is stricter than PETA, on
  purpose.

## Setup on production

1. Deploy, then generate the SQL on the server:
   `sudo -u www-data php artisan hermes:data-schema > /tmp/hermes-data.sql`.
2. In Cloud SQL Studio, **as an admin login**, run that file. Then, once, run the two commented lines
   at its end with this server's IP and a new password: `CREATE USER 'hermes_data'@'<ip>' …` and
   `GRANT SELECT, CREATE TEMPORARY TABLES ON petav3_hermes.* …`.
3. `.env`: `HERMES_DATA_SCHEMA=petav3_hermes`, `HERMES_DATA_DB_USERNAME=hermes_data`,
   `HERMES_DATA_DB_PASSWORD=…`, then `config:cache`.
4. On the box, swap the tool for the new one. It reads `~/.hermes/lab-peta-data.json` =
   `{"url": "https://app.propertylab.com.my", "secret_file": "/home/hermes/.hermes/lab-api-secret"}`.
   Then run `sudo hermes-lab peta-db test`, and restart the gateway so the running assistants start the
   new tool. Afterwards drop the old `hermes_ro` login, and remove its login fields from
   `~/.hermes/lab-peta-db.json` (`host`, `port`, `user`, `password`, `database`, `ssl_ca`). **Keep the
   file itself** with `enabled` and `excluded`: `lab.py` reads the on/off switch and the excluded list
   from it, so deleting it switches the data tools off for everyone.

## Reference usage

```php
// What the box's tool calls; also usable from tinker to check what someone's assistant sees.
$result = app(HermesDataService::class)->query($user, 'their-profile', 'SELECT COUNT(*) AS n FROM leads');
// ['rows' => [['n' => 2]], 'row_count' => 1]   or   ['error' => 'This person\'s PETA access does not open: …']
```

## Related files

- `src/Hermes/Services/HermesDataService.php`: one query, end to end
- `src/Hermes/Support/HermesDataTables.php`: the table map
- `src/Hermes/Services/HermesLabAdmin.php`: `PURPOSE_DATA`, `profileSignature()`
- `app/Http/Controllers/Webhooks/HermesLabDataController.php`
- `app/Http/Requests/Webhooks/HermesLabData/` (`HermesLabDataRequest`, `QueryRequest`, `DescribeRequest`)
- `app/Console/Commands/HermesDataSchema.php`: `hermes:data-schema`
- `config/hermes.php` (`data`), `config/database.php` (`hermes_data`), `config/logging.php`
  (`hermes-data`), `app/Providers/AppServiceProvider.php` (the `hermes-data` limiter)
- `routes/main.php`
- `tests/Feature/Hermes/HermesLabDataTest.php`
