Keep only the newest row

Week 3 · Data audits & API trust boundary · about 3 minutes

What you will be able to do

Deduplicate ticket history with ROW_NUMBER so the AI layer sees one current row per ticket.

The idea

Legacy ticket systems often store every update as a new row. Feeding that straight to retrieval means the model sees closed and open at once.

ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY updated_at DESC) ranks versions. Keep rn = 1. That is the current truth for the API.

SELECT *
FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER (
      PARTITION BY ticket_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM ticket_history t
) ranked
WHERE rn = 1;

Deduplicate before embed. The vector store should not argue with itself.

Key takeaways

• History tables need a window to pick the newest row.
• PARTITION BY the business key, ORDER BY updated_at DESC.
• Filter to rn = 1 before the AI or the API sees the data.

Part of the Forward Deployed Engineer roadmap. New to the role? Start with what a Forward Deployed Engineer does.