If the AI queries the real database, why is the number still wrong?

Valid SQL can encode the wrong business meaning — ignoring deleted_at, using status = 'active' when you use 'Active', or UTC vs local week boundaries. Live access removes invented columns; it doesn't define your metrics.

What's the fastest way to catch this?

Known-answer checks: compare the chat number to SQL or a dashboard you trust. Ask the model for DISTINCT status values and whether a deleted_at column exists before you accept 'active users.'

Should I put metric definitions in the prompt?

Yes for anything you'll reuse. Or create a SQL view that encodes 'active' and only allow that view. Don't rely on the model to remember your meaning across threads.

Is this a reason not to connect AI at all?

No — it's a reason to verify and to keep fought-over board metrics in BI. Use AI for exploration and drafts; lock definitions where the company already argues about them.

Does Synra fix metric definitions?

No. Synra runs read-only SQL and logs calls. Definitions stay yours — views, prompts, or dashboards.

AI Active Users Wrong | Metric Traps with Live SQL

Live access kills a lot of invented tables. It does not make “how many active users?” automatically right.

The model can query real data and still count the wrong population: soft deletes, the wrong status spelling, the wrong week boundary, or an “active” definition your product never used.

Common traps — with prompts that force clarity. Also: verify AI answers, prompts for talking to your database.

Trap 1 — Soft deletes

You ask for users. The table still has everyone who “left,” with deleted_at set.

The model writes SELECT COUNT(*) FROM users and you get a number that includes ghosts.

What to ask instead:

Before counting users, check for deleted_at, is_deleted, discarded_at,
or similar. Default to excluding soft-deleted rows unless I say otherwise.
Show the filter in the SQL.

Trap 2 — Status enums the app doesn’t use

You say “paid orders.” The app stores complete, settled, or Paid.

The model filters status = 'paid' and returns zero — or a quiet undercount.

What to ask instead:

List DISTINCT status values on orders (or whatever table).
Then count "paid" using the values that actually mean paid in this data.
Don't invent enum members.

Trap 3 — “Active” means three different things

Product: logged in within 7 days.
Growth: completed activation.
Finance: unpaid invoice is still “active” subscription.

The model picks one. You meant another.

What to ask instead:

I need weekly active users. First propose 1–2 definitions that this schema
can support (cite columns). Then compute the one I pick.
If we can't support WAUs from this DB, say what's missing.

Or encode the definition in a view and only allow that view on the connector — table allowlists.

Trap 4 — Timezones and “this week”

created_at is timestamptz. “This week” in Bangkok ≠ UTC week.

Monday Claude Code reports drift if the prompt never names a timezone.

What to ask instead:

Use timezone Asia/Bangkok (or mine: ___).
Define "this week" as Mon 00:00 to next Mon 00:00 in that zone.
State the TZ in the answer.

Trap 5 — Joins that double-count

Orders ↔ line items. Count “orders” via order_items without DISTINCT and the number inflates.

What to ask instead:

Count orders, not line items. If you join order_items, use DISTINCT order_id
or aggregate carefully. Show SQL.

Trap 6 — Staging data treated as production truth

The SQL is perfect. The connection is last week’s staging refresh. You paste the number into a board update.

Check the credential name in Usage. Name connections staging-… and prod-replica-… on purpose.

A short “definition” block for recurring threads

Save this above metric questions:

For every metric:
1. Inspect columns (deleted flags, status, timestamps)
2. State your definition in one sentence
3. Show SQL
4. If ambiguous, ask me — don't pick silently

When to stop using chat for the metric

If the company already fights about the definition in Looker or Hex, keep the fight there. AI is fine for exploration and “roughly how many.” Board MRR needs a governed source — when not to connect / when BI wins.

Live SQL isn’t enough. Definitions still need a human or a view.

Practice on staging: mcpserver.design (7-day Solo trial). Known-answer check “active” before you trust the chat.

Related reading:

How to Verify AI Answers About Your Database — smoke tests
Why AI Hallucinates Your Database Schema — invented tables vs wrong metrics
Weekly Database Reports with Claude Code — recurring counts need stable definitions
Prompts for Talking to Your Database — paste-ready asks
Where AI Database Query Results Go — privacy side of wide pulls