The View That Forgets SSN - Withholding Sensitive Columns at the Source

The problem

I built an internal HR policy assistant for a fictional manufacturing company, one I called AskHR, and gave it a job most internal assistants end up doing sooner or later: answer general policy questions and, when someone asks, resolve a question against the live employee table. "How much PTO do I accrue per year" and "what's the SSN on file for Priya Anand" arrive in the same chat surface, and the assistant is supposed to handle both.

The first version I built ingests the employee table wholesale. A seeded SQLite database held 25 synthetic employees, fake names, fake SSNs in the 000-00-0000 pattern, invented salary figures, and the ingestion job flattened every column of every row, SSN and salary included, into one text document per employee. That document got indexed into the vector store right alongside the HR policy handbook, no distinction made between a field that's fine to surface and one that isn't. Indexing happened at the table level, and a table doesn't know which of its own columns are sensitive unless someone tells it.

I ran 20 field-targeting test queries against this version with known ground-truth values for SSN and salary, and measured the percentage of queries that surfaced the exact value in the final answer. It came back above 80%. Ask the assistant a pointed enough question and it hands back a real synthetic SSN or salary figure, because the retrieval layer had already made that value available to itself the moment ingestion ran. The generation step didn't invent anything. It quoted a document that should never have contained what it contained.

The pattern

The fix moves the decision to the data-access layer and makes it structural instead of procedural. I left the orchestration logic in AskHR completely alone and changed what ingestion was allowed to query. A SQL view, employees_safe_view, sits between the raw employee table and the ingestion job, and it withholds the ssn and salary columns entirely, replacing each with a literal [WITHHELD] marker. I restricted the ingestion script to that view as its only permitted source. The withheld fields never make it into a document in the first place, which means they can't become retrievable content for anything downstream to leak. There's no filter running against retrieved chunks or generated text, because there's nothing left in the pipeline that ever saw the real value to begin with.

Running the same 20-query test set against the fixed version dropped exposure to 0% for both fields. I also checked that non-sensitive fields, department and hire date, still retrieved and answered correctly, because a fix that overcorrects into withholding everything just produces a different kind of broken assistant. Both held.

This lines up with what I found to be settled practice in current RAG-security guidance. Elastic Labs and Protecto.ai both published 2026 material on RAG and PII that lands on the same principle AskHR demonstrates directly: mask or exclude sensitive fields before ingestion. The general shape applies past a SQL view too. Whatever your data-access layer looks like, sensitive fields need to disappear before the retrieval layer ever indexes them; a filter bolted onto retrieval after the fact is protecting the wrong stage of the pipeline.

Design considerations

The limitation here is about scope. A view only withholds the columns someone thought to withhold. If a new column gets added to the underlying table, say a home address field introduced for a mailing feature six months from now, the view has no idea to catch it, because nobody told it to. The view's definition is a snapshot of a decision made at one point in time, and the underlying schema keeps moving whether or not anyone updates the view to match.

Joined tables carry the same risk in a sneakier form. If a second table gets joined into the same query path and it happens to carry an equivalent sensitive field under a different name, tax_id sitting next to a table that already withholds ssn, the view's protection does not extend there automatically. Structural withholding only covers the structure you actually defined it against, and every join is a place the definition needs re-checking from scratch.

That makes this pattern a governance discipline as much as a technical one. The mechanism itself is close to free once it's built: a view is cheap to maintain compared to a runtime filter that has to inspect every chunk on every query, but maintaining it takes an owner. Someone has to keep the view's definition current against schema drift, on a cadence tied to how often the underlying schema actually changes. I build this pattern with an explicit owner and a recurring review trigger from day one, because a SQL view left untouched drifts out of sync with a schema nobody is watching.