Design: A Document Q&A Assistant

The second design prompt you should rehearse: "Our staff spend hours digging through policy manuals. Design an internal assistant that answers questions from our documentation." Lower stakes than the transcript analyzer, but it has its own frequently missed requirement — security trimming — and interviewers use it to see whether you notice.

Users, risk, data — then the pipeline

Users are internal staff; the risk is a confidently wrong policy answer propagating into real casework, so answers must be cited and the system must be willing to say "I don't know." The data is the interesting part: policy manuals, procedure guides, memos — living documents in a CMS or SharePoint, with access levels (some HR and legal content isn't for everyone).

Sync, don't snapshot. An ingestion job (a .NET hosted service is fine) pulls changed documents from the CMS on a schedule or webhook, so the assistant never answers from a stale manual. Each document carries its permissions metadata and version through the pipeline.

Chunk and embed into SQL Server. Split documents into chunks that respect structure — section boundaries, not blind character counts — because a chunk that half-covers two policies retrieves badly. Store chunk text, embedding vector, source document id, section heading, and ACL metadata. On this stack, SQL Server is a defensible vector store (native vector support in SQL Server 2025 / Azure SQL; a well-indexed nearest-neighbor pattern before that) — one database, one backup story, EF Core access, no new infrastructure to govern. Say that trade-off aloud: a dedicated vector database buys scale you may not need yet.

Retrieval, citations, and security trimming

Use hybrid retrieval: keyword/full-text search plus embedding similarity, merged. Policy questions are full of exact terms of art ("CL-777", "Ed Code 44259") that pure semantic search fumbles, while embeddings catch paraphrases keywords miss. Feed the top chunks to the model with instructions to answer only from the provided context and cite each claim back to document and section — the UI renders citations as links into the CMS.

Now the critical requirement: security trimming. Retrieval must filter by the asking user's permissions before chunks reach the prompt — filter at query time against the ACL metadata, ideally re-checked against the source system. If a restricted memo lands in the context window, the model will leak it to someone who couldn't have opened the file. Nailing this unprompted is the moment interviewers remember.

Finish with refusal + escalation: when retrieval finds nothing strong, the assistant says so and offers a handoff — link the policy team, open a ticket — rather than improvising. Log questions that hit the refusal path; they're a ranked list of documentation gaps.

Red flags

• One-time bulk import with no sync — the assistant confidently quotes last year's policy.
• No security trimming, or trimming applied after generation.
• Answers without citations, or no refusal behavior at all.
• Jumping to a new vector database without justifying it against the SQL Server they run.

Practice prompts

1. Run the full design in ten minutes: sync → chunking → hybrid retrieval → trimming → refusal.
2. "A staffer got an answer quoting a memo they can't open. Walk me through the failure."
3. Defend SQL-Server-as-vector-store to a colleague pushing for a dedicated vector DB.