How do I know a table keeps versions?

Look for columns like valid_from and valid_to, effective_from, is_current, version, snapshot_date, or a table name ending in _history, _versions or _snapshots. If a customer id appears in more than one row, it keeps versions.

Is this the soft-delete problem?

No. A soft delete flags a row as removed. A versioned table keeps every row on purpose, and the current one is marked by an open end date, a flag or the newest timestamp. Both need a filter, but a different one.

Is this the same as picking the latest row per group?

It is related. When the table has an is_current flag or an open valid_to, filter on it. When it only has timestamps, you need a latest-row-per-group query, which has its own traps.

Why does COUNT(DISTINCT customer_id) look right, then?

It fixes how many customers there are. It does not fix which version you read. 'Customers by plan' with DISTINCT can still put one customer in three plans, one per past version.

What about 'as of last March'?

Use the validity window: valid_from the date). The current-row filter answers 'now', not 'then'.

History Table Counts Every Version | valid_to IS NULL

You ask: "How many customers are on each plan?" The AI answers Free 1,900, Starter 1,400, Pro 600. That's 3,900 customers. You have 1,100.

The customer_plans table keeps one row per change. A customer who went Free, then Starter, then Pro has three rows. COUNT(*) counted all of them.

History tables are not "one row per thing"

-- every version of every customer
SELECT plan, COUNT(*) FROM customer_plans GROUP BY plan

-- only the current version
SELECT plan, COUNT(*) FROM customer_plans
WHERE valid_to IS NULL
GROUP BY plan

Many databases keep history on purpose. A row has a valid_from and a valid_to, and the current one has valid_to set to NULL. Others use an is_current flag, a version number, or a daily snapshot with one row per customer per day. The SQL runs and the numbers look plausible. They are just too big.

This is not the soft-delete filter, where a row is flagged as gone. Here every row is meant to be there, and you pick the right one.

Pick the right version

-- flag or open end date: the current row
WHERE is_current = true            -- or: valid_to IS NULL

-- snapshot table: the newest day
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM customer_snapshots)

-- as of a past date
WHERE valid_from <= DATE '2026-03-01'
  AND (valid_to IS NULL OR valid_to > DATE '2026-03-01')

COUNT(DISTINCT customer_id) alone does not rescue you. It fixes the number of customers, but a plan breakdown can still assign one customer to several plans, one per old version. When the table only has timestamps, the current row is the latest row per group, which has its own traps. And a quick DISTINCT is the band-aid that hides the real problem.

How to catch it

SELECT COUNT(*) AS rows_in_table,
       COUNT(DISTINCT customer_id) AS customers
FROM customer_plans
-- if rows_in_table is much larger than customers,
-- the table keeps more than one row per customer

Compare customers with the count you already trust, such as the row count of the main customers table. And when the AI first reads the schema, have it name the columns that mark the current row.

The prompt that heads this off

Before counting or summing from a table, check whether one entity can have
several rows (history, versions, snapshots). If it can, filter to the current
row (is_current, valid_to IS NULL, or the latest snapshot) and tell me which
filter you used. Compare the result with COUNT(DISTINCT id).

The short version

If an id shows up in more than one row, the table is keeping history. COUNT(*) counts every version. Filter to the current row first, or to the validity window if you mean a past date.

Read-only MCP gateway: mcpserver.design.

Related reading: