JodGig AI Database (jodgig-ai)
This page is the source of truth for the jodgig-ai database.
Status (last updated 2026-08-29): the clean room is live.
- Done: the manifest and the CI drift gate —
driftis a required status check onmerge-prod. - Done: the generator and
nightly.sh, merged intojodgig-api. - Done: the
jodgig-aiinstance (MySQL 8.4.11), its parameter group, and the three database users. - Done: the first manual run published
jodgig_ai— all 87 validation checks returned 0, and a 10-minute browse test found no real name, phone number, NRIC, or bank account. - Done: the schedule on
jodgig.prod.cron(03:30 SGT) and the missing-success alarm (clean-room-nightly-missing→ SNS topicjodgig-clean-room-alarms→ Slack channel email). - Changed 2026-08-29: the job runs from a systemd timer, not a crontab entry, and
install.shnow sets up a server in one command. The crontab entry sent its output to a file the account could not create, so the job never ran for three nights and left no log to say why. See jodgig-api#2704 and the setup runbook. - Open: the missing-success alarm did go off during those three nights, and it reached nobody. Find out why before trusting it again.
- Done: the AI Metabase (
metabase-aionjod.prod.metabase, memory verified: ~4.4 GB still free with all three containers up), public athttps://mcp.teamjod.app, MCP server on, single data connection. Tested end to end with Claude Desktop on 2026-08-27. - Not done: AI Metabase accounts for the rest of the team, and deleting the old manual PII dump files.
Update this list as phases complete. The build steps live in the setup runbook, which opens with a picture of every file and folder the clean room uses. Team members: see Connect your AI agent.
What this is
jodgig-ai is a new, small RDS instance. It holds a copy of the JodGig production database. A nightly job rebuilds the copy and removes all PII on the way in.
AI agents read from this copy through a small, separate Metabase we call the AI Metabase. Agents never read from production.
Our main Metabase is not part of this system. It keeps its production connections and its PII dashboards, unchanged (decision: August 2026).
Words used on this page
| Word | Meaning |
|---|---|
| PII | Personal data that can identify a person. Examples: name, NRIC, bank account, phone number. |
| Scrub | Replace or remove PII in the copy. Production data never changes. |
| Manifest | A CSV file in jodgig-api that lists every column and its scrub rule. |
| Allow-list | The list of tables we copy with rows. A table not on the list ships with 0 rows. |
stage | A schema on jodgig-ai. The raw copy lands here during the nightly job. It still has PII. Agents cannot read it. |
jodgig_ai | A schema on jodgig-ai. It holds the scrubbed data. This is the only schema agents can read. |
| AI Metabase | A second, small Metabase container (metabase-ai). Its only data connection is jodgig_ai. It exists to serve the MCP endpoint for AI agents. |
| Main Metabase | Our existing Metabase. Humans use it for dashboards. It connects to production (jodapp, jodgig.prod.rds-replica). It never gets the MCP server. |
Why we build it
We are solving three separate problems:
- AI agents need database access to make good decisions.
- Agents must never see PII. An agent copies what it reads into prompts and logs. We cannot take a read back.
- Agent queries must never slow down production. The
jodgiginstance already peaks near 100% CPU.
A read replica alone does not solve this. A replica is an exact copy, PII included. We already have one (jodgig-replica-1), and it is as full of NRICs as the writer.
So we build a derived database. A nightly job copies the data and scrubs it before it lands. The job rebuilds the copy from zero every night. A failed night means old data, never broken data. The next night repairs it with no manual work.
The instances and containers
| Name | What it is | Role here |
|---|---|---|
jodgig | RDS MySQL 8.0.45, db.m7g.2xlarge | Production writer. The nightly job never connects to it. |
jodgig-replica-1 | RDS read replica, db.t4g.medium | The nightly job reads from here. It holds full PII. Never give agents or people analytics access to it. |
jodgig-ai | New. RDS MySQL 8.4.11, db.t4g.small, single-AZ, 30 GB gp3, subnet group prod-rds.private-subnets | Holds the stage and jodgig_ai schemas. Cost is about US$40 per month at on-demand list price (instance + storage). |
jodgig.prod.cron | EC2, t4g.medium, private subnet | Runs nightly.sh from a systemd timer at 03:30 SGT. |
jod.prod.metabase | EC2, t3a.large | Runs three docker containers: the main Metabase (unchanged), the AI Metabase, and CloudBeaver. Watch memory on this box after adding the third container — it has 8 GB. |
metabase-ai | New. A second Metabase docker container on jod.prod.metabase. Public at https://mcp.teamjod.app through haproxy. Stores its own settings in a new database on the existing metabase Postgres RDS. | The bridge between Claude and the database, using Metabase's built-in MCP server at /api/metabase-mcp. Its only data connection is jodgig_ai as ai_read_only. Members' agents sign in with their own AI Metabase account. See the Setup section. |
Notes on the instance choice:
jodgig-aiis a copy that we rebuild every night. Losing it costs nothing. So we skip Multi-AZ and keep backups at the minimum.- The real data is only about 2 GB. The large production tables (
notifications,audits) ship with 0 rows. db.t4g.small is enough. Resizing later takes one reboot. jodgig-airuns MySQL 8.4, not 8.0 like production. RDS ended standard support for 8.0 in July 2026 — a new 8.0 instance now costs extra every month (Extended Support). The nightly copy is plain SQL text, so dumping from 8.0 and loading into 8.4 works. Verified end to end before the instance was created.- The parameter group
jodgig-ai-mysql84sets two values:max_execution_time = 30000— MySQL kills any SELECT that runs longer than 30 seconds. Safe because only this workload runs on the instance.restrict_fk_on_non_standard_key = 0— MySQL 8.4 normally refuses two of our legacy foreign keys (they point at a non-unique column, see jodgig-api#2695). This switch keeps the old 8.0 behaviour so the schema loads. Production must fix those keys properly before its own 8.4 upgrade; this switch is only acceptable here because the copy is rebuilt nightly.
The nightly job
The scripts live in jodgig-api under database/clean_room/. The manifest generates them. One bash script drives the whole job:
The job is not application code
The job runs in neither the Laravel app nor the Rails app. It is a systemd timer on the jodgig.prod.cron instance. It only uses bash, mysqldump, and the mysql client. No framework starts. No deploy pipeline is involved. The script runs git pull on its own folder first, so merging a change to jodgig-api updates the job by itself.
Why it is set up this way:
- The code lives in
jodgig-apibecause the manifest must sit next to the migrations it classifies. When a PR adds a column, CI in the same repo can demand a scrub rule in the same PR. A manifest in another repo cannot see that PR. - The job runs on an instance, not in an app, so it cannot break when we deploy, upgrade a framework, or restart queues. A 15-minute data-moving job is also a bad fit for Sidekiq or Laravel queues.
database/clean_room/install.shsets a server up in one command, and it is safe to run twice, so the setup is written down instead of remembered. - App database access does not matter here. Any private-subnet instance can reach the replica and
jodgig-aion port 3306. The job signs in asetl_read_onlyandetl_admin— never with the Laravel or Rails app credentials, which have more rights than the job should have.
Points that matter:
- The dump reads from the replica, never the writer. Production does no extra work.
- The dump uses
--single-transaction. It gets a consistent copy without locking any table. - The dump also uses
--no-tablespacesand--column-statistics=0. These flags avoid extra privileges on RDS. - Step 3 always starts with
DROP DATABASE stage. A job that died halfway leaves nothing to clean up. The job is safe to run twice. - Step 6 uses one
RENAME TABLEstatement for all tables. Readers see yesterday's data or today's data. They never see a mix. - If anything fails, nothing is published. Old data is a safe failure. Leaked data is not.
Scrub rules
Every column gets exactly one of five rules. The manifest (database/clean_room/manifest.csv in jodgig-api) is the source of truth for these per-column choices.
| Rule | Meaning | Example |
|---|---|---|
| KEEP | Copy as-is. | id, created_at, status, amounts |
| FAKE | Replace with a value built from the row id. Unique indexes and joins keep working. | email → user4812@scrubbed.jod |
| NULL | Set to NULL. The default for free text and secrets. | contact_number, bank_account_number, password |
| GENERALIZE | Keep the shape, drop the precision. | date_of_birth → Jan 1 of the birth year |
| EMPTY | Ship the whole table with 0 rows. The table still exists, so queries return empty instead of failing. | audits, notifications |
A short way to remember the defaults:
- IDs, foreign keys, flags, and amounts stay.
- Text that a human typed becomes NULL.
- Identifiers become fakes built from the row id.
- File paths and documents become NULL.
- Log tables ship empty.
Worked example
User 4812 in production users:
| Column | Production | In jodgig_ai |
|---|---|---|
id | 4812 | 4812 |
name | Tan Wei Ming | Jodder 4812 |
email | weiming@gmail.com | user4812@scrubbed.jod |
unique_id | S1234567A | SCRUB-4812 |
date_of_birth | 1998-06-14 | 1998-01-01 |
contact_number | +65 9123 4567 | NULL |
bank_account_number | 123-456-789 | NULL |
created_at | 2023-02-01 10:15:00 | 2023-02-01 10:15:00 |
Every payment, slot, and badge row still joins to user 4812. Age and cohort queries still work. No query can return who the person is.
Tables that ship empty
notifications(all generations of it),email_sms_notifications— message bodies contain names and phone numbers. These are also the largest tables. Shipping them empty keeps the copy near 2 GB.audits— holds old and new values of every user field as JSON. That includes NRIC and bank numbers. Scrubbinguserswhile shippingauditswould remove nothing.oauth_*(all six),password_resets— live login tokens and client secrets.failed_jobs,jobs,job_batches— job payloads contain full user records.ukg_api_logs,ukg_job_histories,ukg_command_logs,eber_points_logs— raw third-party payloads tied to workers.qr_code_slot_users,slot_user_transaction_logs,files,configurations— clock-in tokens, document paths, config values that can hold keys.
Traps to know about
users.unique_idis the NRIC / FIN. The column name does not say so. It has a unique index, so the fake value must differ per row.- Bank details live in three tables:
users,payments(asbank_accountandcardholder), andpayment_adjustment_approvals. users.tfa_code_urlcontains the 2FA secret.token,otp_code, anddevice_keyare also secrets.metabase_dateswas created by hand, not by a migration. Metabase date filters depend on it. Keep it with data.
Database users
| User | Created on | Rights | Used by |
|---|---|---|---|
etl_read_only | jodgig writer (grants copy to the replica) | SELECT on jodgig.* only | nightly.sh, connecting to the replica endpoint |
etl_admin | jodgig-ai | All rights on stage and jodgig_ai | nightly.sh only |
ai_read_only | jodgig-ai | SELECT on jodgig_ai.* only, max 20 connections | The AI Metabase container, CloudBeaver |
ai_read_only has no grant on stage. Agents cannot read the raw copy, even while the nightly job runs.
Team members never receive the ai_read_only password. Only the AI Metabase and CloudBeaver containers hold it. Members sign in with their own AI Metabase account instead — see the Setup section.
Who connects to what
| Reader | Connects to | With |
|---|---|---|
| Team members' AI agents | the AI Metabase MCP endpoint at mcp.teamjod.app/api/metabase-mcp | their own AI Metabase login |
| AI Metabase container | jodgig-ai, schema jodgig_ai | ai_read_only |
| Main Metabase container | production (jodapp, jodgig.prod.rds-replica) — unchanged, humans only, never MCP | its existing read-only users |
| CloudBeaver, normal browsing | jodgig-ai, schema jodgig_ai | ai_read_only |
| CloudBeaver, admin writes to production | jodgig writer | separate admin user, on purpose |
| Boss / local analysis | dump of jodgig_ai — it is scrubbed, so a local copy is fine | ai_read_only |
The old workflow — a manual mysqldump of production for local analysis — stops. Those dump files contain full PII. Delete the old ones.
Setup: connect your AI agent to jodgig_ai
This section is for every team member. It works with Claude on the web, Claude Desktop, and Claude Code. There is nothing to install. There is no password to store. There is no API key anywhere.
We use the MCP server that is built into Metabase — but not our main Metabase. We run a second, small Metabase container just for AI access: the AI Metabase. Your Claude talks to it over HTTPS, and it runs the queries:
The main Metabase you already use for dashboards is a different app with a different login. Nothing changes there.
Where the cost goes
- Claude does the thinking. That usage is covered by each member's own Claude subscription.
- Metabase only runs queries and returns rows. That costs nothing extra.
- We configure no Anthropic API key in Metabase. We do not use Metabot (Metabase's own chat). The MCP server works without any AI provider.
How access control works
- You sign in once with your own Metabase account. Metabase shows its normal login page.
- Your agent then gets a token limited to your Metabase permissions.
- Each person has their own account. We can remove one person without touching anyone else.
- Metabase logs every query per person.
- Metabase reads the database as
ai_read_only. That user can only read scrubbed data. It cannot write to the database. This safety lives in the database grant. A broken setup on your side cannot leak anything.
Agents can only reach one database
The MCP server can only query databases that its Metabase has a connection to. The AI Metabase has exactly one data connection: jodgig_ai.
- Agents cannot add database connections. Only a Metabase admin can.
- Even the
jodgig_aiconnection cannot see more. Theai_read_onlygrant covers one schema. - Rule for the future: never add a second data connection to the AI Metabase. If some workflow needs another database, it belongs on the main Metabase or somewhere else — never here.
Why a second Metabase, and not group permissions
The main Metabase's Permissions page can remove "Create queries" per database per group. That is not enough, for three reasons:
- "Create queries: No" only stops new queries. People in that group can still open saved questions and see their results. Fully hiding a database from a group ("Blocked") is a paid Metabase feature. We run the free version.
- The agent signs in as you. It gets your exact permissions. So every person who can see a PII dashboard would have an agent that can read PII.
- Permissions add up across groups, and every person is in All Users. One wrong edit in the future re-opens access, silently.
A separate container has none of these problems. The AI Metabase cannot show what it is not connected to. The guarantee comes from the architecture, not from settings staying correct forever.
Member steps
The step-by-step guide for team members lives on its own page, written for everyone: Connect your AI agent. In short: add a custom connector named JodGig Data with the URL https://mcp.teamjod.app/api/metabase-mcp, leave every other field empty, and sign in with your AI Metabase account.
Two technical notes that stay on this page:
- Claude has a built-in "Metabase" connector. It only works for Metabase Cloud addresses. Ours is self-hosted, so we use a custom connector.
- The connector dialog has optional OAuth client ID / secret fields. Leave them empty: our Metabase advertises a dynamic registration endpoint (
/oauth/register), so Claude registers itself. Those fields are only for servers that require pre-registered clients. - For Claude Code, one command, then
/mcpto sign in:
claude mcp add --scope user --transport http jodgig-data https://mcp.teamjod.app/api/metabase-mcp
Removing one person's access
| Situation | Action |
|---|---|
| One person's access is compromised | Deactivate their account on the AI Metabase (Admin → People). Their agent's token stops working. Nobody else is affected. |
| A person leaves the company | Deactivate their AI Metabase account. Make this part of normal offboarding. |
| The whole setup looks compromised | Change the ai_read_only password and update the AI Metabase's connection. All reads stop until you finish. |
If something stops working
The troubleshooting table lives on the member page: Connect your AI agent.
Build notes (for whoever sets this up)
The AI Metabase was built on 2026-08-27. The exact steps, with the checks after each and the mistakes we already made once, live in the setup runbook (steps 11 and onward). The short version, in order: create the metabase_ai app database on the metabase Postgres RDS before starting the container (Metabase does not create it, the container just restarts forever) → run metabase-ai on host port 3001 (the main Metabase holds 3000) with a pinned image version, a restart policy, and MB_SITE_URL set → complete the first-boot admin wizard through an SSH tunnel before the DNS record exists (a fresh Metabase hands the admin seat to its first visitor) → security group rule, haproxy route, and a grey-cloud (DNS only) Cloudflare record → one data connection (jodgig_ai as ai_read_only), delete the Sample Database → Admin → AI: features on, no provider, MCP server on.
One open decision: the MCP server also has tools that create and edit Metabase questions and dashboards (see the agent:*:create scopes it advertises). Database writes stay impossible (ai_read_only). Decide in Admin → Permissions whether members' agents may edit that content.
Fallback if the Metabase MCP server disappoints: host our own small MCP server (FastMCP with Google sign-in). Ask Ali for the earlier design notes.
How we keep it safe over time
The biggest risk is a new column. Someone adds users.emergency_contact_number next year. A scrubber with a fixed column list would let it through without warning. We prevent this in four ways:
| Guard | What it does |
|---|---|
| Manifest check in CI | A PR in jodgig-api that adds a column fails CI until the column has a rule in the manifest. The author adds one CSV line. Implemented in .github/workflows/clean-room-drift.yml + database/clean_room/check_drift.sh. CI rebuilds the schema in about 1 second: it loads database/schema/mysql-schema.dump (Laravel's version of Rails structure.sql), then replays only migrations newer than the dump — so the PR's new migration is always included. |
| Nightly drift check | The generator (generate.sh) compares the real schema against the manifest every night, before writing any SQL. An unknown column or table stops the job before publish. This catches hand-made tables — it found jobs_quarantine_20260817 (an incident leftover CI had never seen) on the very first day. |
| Canary queries | After the scrub, validate.sql hunts for real-looking data. Examples: an email not ending in @scrubbed.jod, a value matching the NRIC pattern, a non-NULL bank account. Any hit stops the job. |
| Missing-success alarm | The job sends a success signal to CloudWatch at the end. An alarm fires when no signal arrives for 36 hours. This catches a dead server. A failure alert alone cannot, because a dead host sends nothing. |
| Alert on a failed start | clean-room-nightly.service names an OnFailure= unit. systemd starts that unit whenever the job fails, including when the job cannot start at all. The script's own Slack alert cannot cover that case, because a script that never runs cannot send anything. Added after jodgig-api#2704. |
What this does not protect
- The scrubbed data is not anonymous. It still shows behavior, and it still holds all our business data: client names, margins, payouts. Keep
jodgig-aiin the private subnets. Never make it publicly accessible. - Uploaded files are out of scope. We NULL the columns that point at NRIC scans. The scan files themselves stay in storage. That is a separate security task.
- The jodapp Postgres database also holds jodder PII. It is not covered. Build the same pattern for it later.
- The main Metabase still shows production data, with PII, to all its users. We kept this on purpose (August 2026) because its dashboards are in daily use. Tightening it is separate work. Until then: no AI agent may ever be pointed at the main Metabase.