29. Bar Raiser technicalities — Adarsh Vishwakarma

Adarsh Vishwakarma · SDE I AUTA APJ · Job 10454435. This page is for when the bar raiser stops asking "what number" and starts asking "show me how." Two systems most likely to get deep probing: IQVIA BRD RAG pipeline and Ylogx RLS+RBAC. Every config, every architectural decision, every cross-question.

Part 1 — IQVIA BRD RAG Pipeline · Apr 2026–present

Resume hook: Hybrid RAG Agent using Azure AI Search (Hybrid + Semantic), GraphDB, and LangGraph on 200+ page BRD/PDF documents. LangSmith is the eval sentence. DeepQuery is the CLI research tool — separate product, separate pipeline. Do not mix 857 URLs into the BRD chunk index.

1. Ingestion pipeline — PDF to chunks

How ingestion actually works

PDF extraction. pdfplumber opens the file, extracts text page by page. Empty pages (whitespace-only after strip) are dropped. len(non_empty_pages) is the page count I report — 180+ on the resume, 200+ in older banks. Same BRDprocessor product, I flag the wording difference.
DOCX handling. If the input is DOCX, I create synthetic page boundaries every 50 paragraphs. Tables are marked with [TABLE] delimiters so the chunker does not split a row across two chunks.
Upload cap. 100 MB per file. If pages > 500 I log a warning but extraction still runs — a 600-page BRD is unusual, not impossible in regulated programs.
Chunking. Recursive character splitter with cl100k_base tokenizer. Chunk size 1500 tokens, overlap 200 tokens. Overlap ratio: 200/1500 ≈ 0.133 (13%). This means each chunk shares about 200 tokens with the next, so a requirement that straddles a boundary is captured in both chunks.
Worked example. A section with 10,000 tokens, stride 1300 (1500 − 200) → ceil(10000/1300) ≈ 8 chunks plus edge effects. A 180-page BRD is not 180 chunks — it depends on text density per page. Empty pages are already gone. A dense BRD might produce 300–500 chunks.
Metadata per chunk. Each chunk carries: document_id, page_number(s), section_title (extracted from headings), chunk_index, full content text. This metadata is indexed alongside the embedding so I can filter by document and display section context in the answer.

Config	Value	Why this value
Chunk size	1500 tokens	Large enough to hold a full requirement paragraph + its context. Small enough that 5 chunks fit in a prompt without exceeding useful context.
Overlap	200 tokens	Catches requirements split at boundaries. 200 ≈ 2–3 sentences of regulatory text.
Tokenizer	cl100k_base	Same tokenizer family as the embedding model (OpenAI). Counting in a different tokenizer would make 1500 a lie.
Upload cap	100 MB	pdfplumber loads into memory. Larger files need streaming extraction.
Page warn	>500	Warning, not rejection. Regulated BRDs can be long.

Why 1500 not 512?

A BRD requirement paragraph is often 300–800 tokens including its context clause and dependency references. At 512 I split requirements across chunks and the retriever needs to reassemble them. At 1500 a full requirement fits in one chunk, and 5 chunks × 1500 = 7,500 tokens in the prompt — well within the model's useful window. I trade chunk count for chunk quality.

Why not split by headings instead of tokens?

Some BRD sections are 5,000+ tokens. Heading-based splitting would create huge chunks that waste prompt space. I use heading-aware metadata (section_title on each chunk) so the retriever knows which section a chunk belongs to, but the split is by token count for uniform chunk size.

2. Embedding — why 3072 dimensions

The embedding decision

Model. text-embedding-3-large from Azure OpenAI. Output: float[3072]. This is the full dimensionality of the model — I am not truncating.
Why 3072 not 1536. text-embedding-3-small produces 1536-d vectors. text-embedding-3-large produces 3072-d vectors. The Azure AI Search index field is defined as Collection(Edm.Single) with dimensions=3072. If I embed at 1536 and upload to a 3072 field, the upload fails with a dimension mismatch. If I use the small model, I lose the additional semantic resolution that matters when two BRD clauses use nearly identical regulatory language but refer to different requirements. In a life-sciences BRD, "data retention for 7 years" and "data retention for 5 years per FDA 21 CFR Part 11" are semantically close — the larger model separates them better.
Batch size. 16 chunks per API call. Rate-limit sleep 0.2 seconds between batches. For a 300-chunk BRD: ceil(300/16) = 19 embed calls, 19 × 0.2 = 3.8s of sleep, plus API latency.
Poison pill. If an embed batch fails (timeout, rate limit), the fallback must not insert a zero vector. A zero vector indexed as meaning would be retrieved for any query near the origin and poison the results. I retry the failed batch or skip those chunks with a logged warning — never insert [0.0] × 3072.
Cost driver. Embedding is cheap relative to the extract-per-chunk LLM calls. The sequential bottleneck is requirement extraction, not embedding.

Config	Value	Why
Model	`text-embedding-3-large`	Azure OpenAI managed endpoint. Resume says Azure AI Search.
Dimensions	3072	Full model output. Must match Azure field definition.
Batch	16	Azure rate limits. Larger batches risk 429.
Sleep	0.2s	Polite rate limiting. 5 calls/sec max.
Env var	`AZURE_OPENAI_EMBEDDING_DIMENSIONS=3072`	Config match. Wrong value = broken index.

Can you truncate 3072 to 1536 for storage?

text-embedding-3-large supports Matryoshka representation — you can truncate to 1536 or even 256 and it still works reasonably. But the Azure index field is defined at 3072. Changing the field requires reindexing. I kept 3072 because the full resolution helps with near-duplicate regulatory clauses, and storage cost for a few hundred chunks is negligible.

Why not a fine-tuned domain embedding?

I did not fine-tune an embedding model. The resume claim is Azure AI Search Hybrid + Semantic, not a custom embedder. Fine-tuning on BRD text would need labeled pairs I did not have. The managed model plus hybrid retrieval (BM25 catches exact IDs, vectors catch paraphrase) was sufficient. I would not invent a fine-tuning story.

3. Azure AI Search index — the three retrieval layers

Index architecture

Index name. brd-documents. One index per deployment. Documents from multiple BRDs share the index, filtered by document_id at query time via OData.
Lexical field. BM25 on content. This is the keyword layer — it catches exact tokens like "REQ-1044", "FR-214", section numbers, table names. BM25 does not need vectors.
Vector field. HNSW on embedding. m=4 (connections per node), efConstruction=400 (build-time search width). m=4 is conservative — smaller graph, less memory. efConstruction=400 is high to build a better graph upfront; query-time efSearch defaults to Azure's setting.
Semantic config. brd-semantic-config applied to content + section_title. This is Azure's built-in cross-encoder reranker — it reorders the hybrid candidates for semantic relevance. I did not train this model. It is a managed Azure feature.
Upload. 100 documents per batch, retry 3 on failure. Each document in the index has: id, document_id, content, embedding, section_title, page_number, chunk_index, metadata JSON.

Azure AI Search Index: brd-documents
├── Lexical (BM25)
│   └── content: Edm.String, searchable
│       "REQ-1044 mandates data retention..." → exact token match
│
├── Vector (HNSW)
│   └── embedding: Collection(Edm.Single), dimensions=3072
│       m=4, efConstruction=400
│       paraphrase: "how long must records be kept?" → cosine neighbor
│
├── Semantic Config: brd-semantic-config
│   └── content + section_title → Azure cross-encoder rerank
│       reorders hybrid candidates for meaning, not just token/vector overlap
│
└── Filter: document_id (Edm.String, filterable)
    OData: $filter=document_id eq 'BRD-2026-IQVIA-001'

Why m=4 not m=16?

m controls connections per HNSW node. Higher m means better recall but more memory and slower indexing. For a few hundred chunks per BRD, m=4 is sufficient — the graph is small. On a million-document index I would increase m. efConstruction=400 compensates by building a higher-quality graph at index time.

What is hybrid vs semantic — are they the same thing?

No. Hybrid is the retrieval stage: one query fires both BM25 keyword search and vector kNN simultaneously, results are fused by reciprocal rank fusion (RRF). Semantic is the reranking stage: Azure's cross-encoder model reorders the fused candidates based on deeper language understanding. Hybrid solves recall (don't miss REQ-1044 or the paraphrase). Semantic solves precision (put the best match first).

4. Hybrid retrieval — how a question becomes evidence

The retrieval path

Query construction. User asks: "What does REQ-1044 require?" I embed the question at 3072-d with the same model and issue one call to Azure AI Search with query_type=semantic, which internally does: (a) BM25 on the text, (b) kNN on the vector, (c) RRF fusion, (d) semantic reranking.
kNN k=5. I ask for 5 nearest neighbors from the vector field. This is the candidate pool — the reranker may reorder them but does not add new candidates. If k is too large (k=50), I pay for reranking noise. k=5 is the sweet spot: small enough for a focused prompt, large enough to catch the right chunk even if BM25 misses it.
OData filter. $filter=document_id eq '...' ensures I only retrieve chunks from the current BRD, not from a different document in the same index.
Top 5 contents. After reranking, I take the top 5 content strings. These become the evidence set for the writer. 5 chunks × 1500 tokens = 7,500 tokens of evidence — well within the model's window with room for the prompt and output.
Why not vector-only. "REQ-1044" is an exact token. BM25 matches it perfectly. A vector search might return REQ-1043 or REQ-1045 because their embeddings are close (similar surrounding text). The hybrid ensures the exact ID survives. Conversely, "how long must records be kept?" is a paraphrase that BM25 might miss if the BRD says "retention period" — the vector search catches the semantic match.

What if k=5 misses the right chunk?

If the answer is in the 6th chunk, I miss it. The trade-off is prompt quality vs recall. I could increase k to 10, but then the writer sees more irrelevant text and may cite the wrong section. My mitigation is the graph hop: if REQ-1044 depends on REQ-880, the graph adds REQ-880's text even if it was not in the top 5 vector results.

Is Azure's semantic reranker a cross-encoder?

Azure describes it as a language understanding model that scores query-document pairs. Functionally it behaves like a cross-encoder — it sees the query and the candidate together, unlike a bi-encoder that scores them independently. I use it as a managed feature. I did not train or configure it beyond specifying which fields to rerank on (content + section_title).

5. Neo4j knowledge graph — why cosine cannot replace edges

How the graph works

Node types. Requirement (REQ-1044), Section (3.2.1 Data Retention), Entity (extracted business terms). Each node has properties: id, title, description (truncated to ~300 chars), source_page, document_id.
Edge types. DEPENDS_ON (REQ-1044 depends on REQ-880), CONTAINS (Section 3.2 contains REQ-1044), INVOLVES (REQ-1044 involves entity "PatientRecord").
How edges are created. During ingestion, the LLM extracts requirements and their dependency references from each chunk. If chunk text says "This requirement depends on REQ-880 for the base retention policy," I create a DEPENDS_ON edge from REQ-1044 → REQ-880. MERGE ensures idempotency — re-ingesting the same BRD does not duplicate nodes.
Query: 1–2 hop bounded walk. Cypher pattern: MATCH (r:Requirement {id: $req_id})-[:DEPENDS_ON*1..2]->(dep) RETURN dep LIMIT 50. I take the first 3 paths for the prompt. Why bounded: loading an entire connected component into the prompt recreates the 200-page context problem I was solving.
Why graph not bigger vector k. "What breaks if REQ-1044 is removed?" is a relationship query. Increasing vector k from 5 to 50 returns more similar prose but does not guarantee it returns REQ-880, which may use completely different language. DEPENDS_ON is an explicit directed edge that no embedding captures. The graph answers "which requirements are structurally connected," while Azure Search answers "which text can support the answer."
Graph + search combined. Graph hop returns neighbor node IDs. I then fetch those nodes' full text from Azure Search (not from the graph — the graph stores truncated descriptions). This way the writer gets the full chunk text for citation, not a 300-char summary.

REQ-1044 ──DEPENDS_ON──▸ REQ-880
    │                         │
    │ Azure full text     Azure full text
    │                         │
    ▼                         ▼
    ┌─────────────────────────────────┐
    │  Writer prompt:                 │
    │  • 5 hybrid chunks (top-k)     │
    │  • 3 graph neighbor texts      │
    │  • question                    │
    │  → cited answer or abstain     │
    └─────────────────────────────────┘

Why not store full text in Neo4j?

Neo4j is optimized for traversal, not full-text retrieval. Storing 1500-token chunks as node properties would make the graph unnecessarily large and slow traversals. Azure Search already has the full text indexed and searchable. I use Neo4j for structure (edges) and Azure for content (text). Each tool does its job.

What about cycles in the dependency graph?

DEPENDS_ON*1..2 with LIMIT 50 bounds the walk. If REQ-A depends on REQ-B depends on REQ-A, the Cypher query hits the depth limit and stops. I do not need cycle detection because I cap the traversal. For a 200-page BRD, dependency depth rarely exceeds 2 — a requirement depends on another requirement, which may depend on a policy section, and that is the deepest practical chain.

6. LangGraph orchestration — why not a linear chain

The graph-as-workflow

Why LangGraph. BRD processing is not one model call. It has parallel extraction, bounded retry, conditional routing (dependency question → graph, factual question → search only), and checkpointing. A linear LangChain chain cannot fan out, cannot retry a failed branch without restarting, and cannot checkpoint mid-run.
Node sequence. entry → chunker → embedder → indexer → requirement_extractor → graph_writer → (per query) retriever → graph_walker → writer → citation_grader → (loop back to retriever if grading fails, max 3 iterations, else abstain).
Typed state. LangGraph state is a typed dictionary: query, chunks, embeddings, requirements, graph_edges, retrieved_context, graph_paths, answer, citations, errors. Nodes return partial updates (e.g., retriever adds retrieved_context) rather than mutating a global dictionary.
Checkpoint. MemorySaver, thread_id = document_id. If embedding completes but requirement extraction crashes, I resume from the checkpoint without re-embedding. This is not a distributed queue — it is in-process persistence via LangGraph's built-in checkpointer.
Bounded cycle. citation_grader → retriever loop has max_iterations=3. If after 3 retrieval attempts the answer still has no supporting span, the system abstains rather than looping forever. This is an explicit edge condition, not a prompt that says "try again."
Semaphore(3). Test-case generation for multiple requirements runs concurrently with a semaphore of 3. This caps the API call concurrency so I do not hit rate limits while still parallelizing the slowest phase.

LangGraph BRD Processing Pipeline

  ┌─────────┐     ┌──────────┐     ┌─────────┐     ┌───────────────┐
  │ Chunker │────▸│ Embedder │────▸│ Indexer │────▸│ Req Extractor │
  └─────────┘     └──────────┘     └─────────┘     └───────┬───────┘
                                                            │
                                                    ┌───────▼───────┐
                                                    │ Graph Writer  │
                                                    │ (Neo4j MERGE) │
                                                    └───────┬───────┘
                                                            │
  Per query:                                                │
  ┌───────────┐     ┌──────────────┐     ┌────────┐   ┌────▼────┐
  │ Retriever │────▸│ Graph Walker │────▸│ Writer │──▸│ Grader  │
  │ (Azure k5)│     │ (Cypher 1..2)│     │        │   │cite?    │
  └───────────┘     └──────────────┘     └────────┘   └────┬────┘
        ▲                                                   │
        │              max 3 iterations                     │
        └───────────────── no cite ──────────────────────────┘
                           │
                      cite found → answer
                      3 fails   → abstain

Why not just use LangChain?

LangChain provides tools and chain patterns — SequentialChain, RouterChain. But this pipeline needs: (a) parallel requirement extraction across chunks, (b) conditional routing between graph-walk and search-only paths, (c) bounded retry loops on citation grading, (d) checkpoint/resume for long BRDs. A SequentialChain is a linked list of side effects — one failure aborts or leaves half-mutated state. LangGraph made state and transitions explicit. Ylogx stayed LangChain because SQL RAG is a single propose→execute path with no graph or retry loop.

What is MemorySaver?

LangGraph's built-in checkpointer that persists state after each node completion. Thread_id = document_id means I can resume a crashed run on the same document. It is not Redis, not Celery, not a distributed queue. Redis and Celery are in requirements.txt but not on the routes — jobs sit in memory in this implementation.

7. Test-case generation — from requirements to cases

How test cases are generated

Input. For each testable requirement extracted in the ingestion phase. Worked example from the doc: 80 chunks / 40 requirements / 30 testable → 30 test-case generation calls.
Concurrency. Semaphore(3) — 3 parallel generation calls at a time. Each call: embed the requirement text, retrieve context from Azure, retrieve graph neighbors, generate test case with the LLM.
Cost math. 80 chunks: ~5 embed batches (80/16). 80 extract chats (sequential per chunk, BATCH_SIZE 10 groups). 1 analysis call. 30 testable: ~30 embed + 30 gen with Semaphore(3) → ~10 batches of 3 = 10 sequential waits. Extract-per-chunk is the cost driver, not embedding.
Analyzer. Truncates requirement descriptions to ~300 chars for the graph node. Full text stays in Azure Search.
Generated case structure. Each test case includes: requirement ID it traces to, test description, expected result, the BRD section it cites, and the retrieval path (which chunks were used as evidence).

Can the LLM generate a plausible test from the wrong requirement?

Yes. That is the exact failure mode. A fluent test case that cites Section 3.2 but was generated from Section 4.1's context is wrong. The cite-or-abstain gate checks that the test case's cited section matches the retrieved evidence. LangSmith traces which chunks reached the writer so I can audit whether the right evidence was used.

8. Cite-or-abstain + LangSmith — the quality gate

How I know the output is grounded

Cite-or-abstain rule. If the generated answer or test case has no overlapping span in the retained chunks, it fails. This is the quality gate, not a percent on the resume. The system does not return "I think the answer is..." without evidence.
LangSmith role. LangSmith separated observability from evaluation. Tracing tells me what the graph did — which nodes ran, what retrieval returned, whether the writer received empty context. A gold set tells me whether that path produced supported output. My gate is not "same wording as the reference." It is that the expected section was retrieved and the answer has a supporting span.
Empty retrieval detection. If Azure returns 0 chunks (document not indexed, or filter mismatch), LangSmith exposes that the writer received empty evidence. Without tracing, a fluent answer from an empty scrape looks correct.
What I rejected as evaluation methods. (a) "It sounds good" — fluency accepts wrong citations. (b) BLEU — rejects correct paraphrases, accepts polished wrong text. (c) Manual review without gold references — no baseline. (d) Logging full BRD bodies — data leak risk.
Suggested SLOs in the doc are not implemented. The codebase has commented SLO targets. I do not recite them as shipped. Resume gives no pass-rate percentage.

What is the difference between LangSmith tracing and LangSmith evaluation?

Tracing records what happened — node order, tool inputs/outputs, latency, token counts. Evaluation compares the output against a gold set — did the answer cite the right section? did the test case trace to the right requirement? I used tracing on every run and evaluation on a subset of known question-answer pairs. They are complementary, not the same feature.

Do you have a pass rate?

No. The resume gives no pass-rate percentage. I describe the gate mechanism — cite-or-abstain — and refuse to invent a number. If they push, I say: "the gate exists, I can describe how it works, but I do not have a published pass rate to defend."

9. BRD probe bank — every question they might ask

Walk me through what happens when I upload a 200-page BRD.

pdfplumber extracts text per page, drops empties. Chunker splits at 1500 tokens with 200 overlap using cl100k_base. Embedder calls text-embedding-3-large in batches of 16 at 3072 dimensions. Azure indexer uploads 100 per batch with retry 3 to the brd-documents index. LLM extracts requirements from each chunk sequentially. Extracted requirements and their dependency references get written to Neo4j as nodes with DEPENDS_ON edges. Checkpoint saves state after each phase. Total: if 300 chunks, about 19 embed calls + 300 extract calls + graph writes.

Why Azure AI Search and not Pinecone or Qdrant?

The resume says Azure AI Search. The IQVIA internship used Azure cloud. Pinecone and Qdrant are vector-only — they do not have built-in BM25 + semantic reranking in one query. I would need a separate keyword index and write my own fusion. Azure does hybrid + semantic in one API call. I would not replace the resume stack with a tool I did not ship.

How do you handle a BRD update — re-ingest everything?

Currently yes — delete the document's chunks from Azure (filter by document_id), clear Neo4j nodes for that document, re-ingest. Incremental diff-based ingestion would be a 10× improvement: hash each chunk, compare with existing, only re-embed changed chunks. I did not implement incremental ingest.

What if two BRDs have the same requirement ID?

document_id is part of the Azure filter and the Neo4j node key. REQ-1044 in BRD-A and REQ-1044 in BRD-B are different nodes and different chunks. The OData filter ensures retrieval is scoped to one document at a time.

What is the latency of a query?

No IQVIA latency percentage is on the resume. I do not borrow Ylogx sub-210 ms or GiftedBooks sub-300 ms. The components: Azure hybrid search ~100-300ms, graph hop ~50-100ms, LLM generation ~2-5s. Total user-facing is dominated by the LLM. I would not invent a p90.

Is this multi-tenant?

The resume does not say multi-tenant BRD SaaS. I do not import Ylogx's JWT and RLS model into IQVIA. Document isolation is by document_id filter, not by org-level row policies.

DeepQuery vs BRD — are they the same pipeline?

No. DeepQuery is the CLI research agent that scrapes 200+ websites — DuckDuckGo, Bing, Firecrawl fan-out. BRDprocessor ingests uploaded PDF/DOCX into Azure + Neo4j for requirement extraction and test-case generation. Different data sources, different indexes, different products. I never mix 857 URLs into the BRD chunk index.

Part 2 — Ylogx/ConvBI RLS + RBAC · Nov 2024–Oct 2025

Resume hook: 3-tier RLS+RBAC. Product is ConvBI/Projas — folders named after the intern, resume row is Ylogx. This is TypeScript NestJS, not IQVIA Python. Three tiers: (1) app role SUPER_ADMIN/ADMIN/USER/GUEST, (2) Postgres db_role_name + RLS policies, (3) Feature routes in the SPA. Do not invent a fourth tier.

10. Request lifecycle — exact middleware order

Browser React SPA (localStorage Bearer token)
         │ REST JSON
         ▼
Nest main.ts (port 5000)
  ┌─ AllExceptionsFilter
  ├─ LoggingInterceptor (method, URL, user, IP, duration, status)
  ├─ CORS  origin = CORS_ORIGIN || '*', credentials: true
  ├─ bodyParser 50mb JSON + urlencoded
  ├─ ValidationPipe whitelist + forbidNonWhitelisted + transform
  └─ FinishTransactionMiddleware  ← registers res.on('finish')
         │
         ▼  (excluded: health*, auth signup/signin/otp/activate/forgot/reset)
  JwtMiddleware
    jwt.verify(JWT_ACCESS_SECRET)
    req.user = { userId: guid, role, email, client_id, organizationId }
         │
         ▼
  RLSMiddleware
    const tx = await sequelize.transaction()
    switch (req.user.role):
      SUPER_ADMIN → RESET ROLE + set_config('app.is_SUPER_ADMIN','true',true)
      ADMIN       → SET ROLE first SYSTEM db_role_name or rls_no_access
      USER        → SET ROLE first db_role_name or rls_no_access
    set_config('app.organizationId', jwtOrg, true)  ← transaction-local
    req.dbTransaction = tx
         │
         ▼
  Controller → Service  ← all queries use req.dbTransaction
         │
         ▼
  PostgreSQL
    RLS policies: USING (tenant = current_setting('app.organizationId'))
    SET ROLE restricts GRANT-level access
         │
         ▼
  res.on('finish') → FinishTransactionMiddleware → tx.commit()
    (live code always commits; rollback only if commit throws)
    (architecture docs say ROLLBACK on error — docs lag the file)

Docs ≠ code alert. architecture-docs/08 says ROLLBACK on error. Live FinishTransactionMiddleware always commits on res.on('finish'). If they open the file, speak the file. Would-do: inspect statusCode before commit.

Why per-request transactions?

SET ROLE and set_config with the third arg true are scoped to the transaction. Without a transaction, the role change would leak to the next request on the same connection from the pool. A per-request transaction ensures that user A's SET ROLE never bleeds into user B's query on the same Postgres connection.

Does that not hold a connection for the entire request?

Yes. Pool max is 20, each request holds one connection from JwtMiddleware until res.on('finish'). Under 20 concurrent requests, the pool is fine. Above 20, requests queue on acquire (30s timeout). This is a known scaling limitation — the honest 10× move is shorter handlers or read-only connection pooling, not ignoring the transaction.

11. RLS algorithm — the live code path

Three role paths

SUPER_ADMIN. RESET ROLE — runs as the connection owner (typically the Postgres login role that owns the tables). Plus set_config('app.is_SUPER_ADMIN', 'true', true). organizationId GUC set empty. Full access to all tables. This is the intended bypass — admin tools need cross-org visibility.
ADMIN. Load the user's organization. Load UserRole records whose Role.scope is SYSTEM. Filter those with a non-null db_role_name. If found: SET ROLE "${db_role_name}" — this switches the Postgres session to a non-owner role that has RLS policies applied. If roles exist but none have db_role_name, or zero SYSTEM roles found: SET ROLE "rls_no_access". The code comments: "previous RESET ROLE leaked" — this was a bug fix. Old code did RESET ROLE for admins with no SYSTEM role, which ran queries as the table owner and bypassed RLS.
USER. Load all non-deleted roles with RolePolicy → Policy. If a db_role_name exists, SET ROLE to the first one — "PostgreSQL can only have one active role" (code comment). Collect policy.policyRole, allowedModules, load ModuleRegistry.tables → req.user.allowedTables, req.user.roleModuleMapping. If no db_role_name exists: rls_no_access and noDataAccess = true.
GUEST. Enum value exists. Guests are blocked from dashboards, query engine, report creation at the service level. They never reach the RLS middleware's SET ROLE path for warehouse queries.
Why first role only. Postgres allows one active role per session (SET ROLE is not additive). If a user has multiple roles mapped, I take the first one. Union of permissions would require running the query multiple times under different roles and merging results — I did not implement that.
Transaction-local GUCs. set_config('app.organizationId', jwtOrg, true) — third argument true means this setting is local to the current transaction. When the transaction commits/rolls back, the setting disappears. A different transaction on the same connection gets a clean slate. This is why per-request transactions are essential: without them, the GUC persists and org A's organizationId leaks to org B.

Role	SET ROLE	GUCs	Result
SUPER_ADMIN	RESET ROLE (owner)	is_SUPER_ADMIN=true, orgId empty	Full access, RLS bypassed (owner bypasses unless FORCE)
ADMIN + SYSTEM role	SET ROLE db_role_name	is_admin=true, orgId=jwt.org, admin_hierarchy_filter	RLS policies apply on this role
ADMIN no SYSTEM role	SET ROLE rls_no_access	—	Permission denied on any warehouse table
USER + db_role	SET ROLE first db_role_name	orgId=jwt.org	RLS policies apply, allowedTables populated
USER no db_role	SET ROLE rls_no_access	noDataAccess=true	No warehouse access
GUEST	(blocked before SET ROLE)	—	403 at service level

Why not just use JWT organizationId as the RLS filter?

JWT organizationId is a claim from the application token. It is not a Postgres session variable. RLS policies use current_setting('app.organizationId') which is set via set_config in the transaction. The JWT is the source of truth at the app layer; the GUC is how that truth reaches Postgres. If I wrote USING (org_id = 'jwt-value') in the policy, the policy would need to parse the JWT — Postgres cannot do that. SET ROLE + GUC is the bridge.

What happens if someone sends a WHERE clause targeting another org?

The query runs. But RLS policy USING (organizationId = current_setting('app.organizationId')) filters rows before the WHERE clause sees them. So WHERE organizationId = 'ORG_B' under ORG_A's transaction returns zero rows — the RLS policy already hid ORG_B's rows. The WHERE cannot override USING. JOIN cannot resurrect a hidden row. This is database-level enforcement, not application-level string matching.

12. Policy generator — how CREATE POLICY is automated

The generator

Role creation. CREATE ROLE policy_${scope}_${slug}_${Date.now()} NOLOGIN, sliced to 63 characters (Postgres identifier limit). NOLOGIN means this role cannot be used to connect to the database directly — it is only used via SET ROLE.
GRANT. GRANT SELECT ON dashboard_tables TO role_name. The specific tables come from ModuleRegistry. Only SELECT is granted — no INSERT, UPDATE, DELETE on warehouse fact tables.
ENABLE RLS. ALTER TABLE table_name ENABLE ROW LEVEL SECURITY. This is required — without it, policies are defined but not enforced.
CREATE POLICY. CREATE POLICY policy_name ON table_name FOR SELECT USING (organizationId = current_setting('app.organizationId')::uuid). The USING clause is the row filter — it runs on every row and returns true/false.
Module not in policy. If a table is not in the policy's module list: USING (false) → 0 rows, but HTTP 200 and success. Not 403. This is by design — the policy exists but deliberately returns no rows for that role.
No FORCE ROW LEVEL SECURITY. The generator does ENABLE but not FORCE. Table owners bypass RLS by default unless FORCE is applied. This is why SET ROLE is critical — it switches from the owner to a non-owner role that has RLS applied. RESET ROLE (SUPER_ADMIN path) goes back to owner, which bypasses RLS intentionally.
Identifier interpolation. SET ROLE "${dbRoleName}" is not a bind parameter — it is string interpolation. This is safe because role names are admin-created identifiers from the policy management UI, not user input. If user input could control dbRoleName, this would be an injection vector.

Why not FORCE ROW LEVEL SECURITY?

FORCE makes RLS apply even to the table owner. If I added FORCE, the SUPER_ADMIN path (RESET ROLE → owner) would also be filtered by RLS policies, which would break admin tools that need cross-org visibility. The design relies on SET ROLE to downgrade from owner to a restricted role. SUPER_ADMIN stays as owner intentionally.

Is SET ROLE injection-safe?

The role name is string-interpolated, not parameterized. It is safe because dbRoleName comes from the admin-created Policy table, not from user input. If a malicious admin could create a role named rls_no_access"; DROP TABLE sales;--, that would be an injection. Mitigation: the generator slices to 63 chars and uses a controlled slug. But yes, the proper fix is format('%I', role_name) in a Postgres function — I flag that as a would-do if they grind Highest Standards.

13. Three failure classes — know which one you are in

The three responses and what they mean

HTTP 200 + empty rows = USING hide. The query ran. RLS policy filtered all rows because the caller's organizationId GUC did not match any row's organizationId. Or the module policy is USING(false). This is not an error — it is correct behavior. The user is not allowed to see those rows, so they get an empty result.
SQLSTATE 42501 (permission denied) = missing GRANT. The SET ROLE succeeded, but that role has no GRANT on the requested table. Postgres rejects the query before RLS even runs. This happens when the policy generator did not GRANT SELECT on that table, or when the role is rls_no_access.
HTTP 403 = Nest hasTableAccess. Before the query reaches Postgres, Nest checks hasTableAccess(user, tableName). If the table is not in the user's allowedTables (from Policy → ModuleRegistry), Nest returns 403 without executing any SQL. This is the application-level gate — defense in depth above the database-level RLS.

Response	Layer	Cause	What the user sees
200 + []	Database (RLS)	USING clause filtered all rows	Empty table / chart
SQLSTATE 42501	Database (GRANT)	Role has no SELECT on table	Permission denied error
HTTP 403	Application (Nest)	hasTableAccess returned false	Forbidden response

Is 200 + empty the same as 403?

No. 403 means Nest blocked you before the query ran. 200 + empty means Postgres ran the query, RLS filtered all rows, and returned nothing. From the user's perspective, both mean "no data." But from a debugging perspective, 403 is a Nest/RBAC issue, and empty is an RLS/policy issue. I keep these distinct because the fix is different: 403 → check allowedTables/policy mapping. Empty → check the USING clause and the organizationId GUC.

14. executeWithoutRLS — the controlled bypass

When and why RLS is bypassed

What it does. ChatService.executeWithoutRLS creates a new transaction, runs RESET ROLE inside it, executes the callback, and commits. This runs as the table owner, bypassing RLS.
Why it exists. Chat messages, favorites, and feedback are app-level data, not warehouse facts. The SET ROLE used for warehouse queries may have no GRANT on chat tables. If chat CRUD ran under the warehouse role, users could not save messages. So chat operations use a separate transaction as the owner.
What uses it. chats, chat_messages, chat_feedbacks, favorite_conversations, favorite_messages. All app tables.
What must NEVER use it. sales, bs, pl, receivables, payables, stock, cashflow — warehouse fact tables. If executeWithoutRLS ran on these, it would bypass org isolation. The query would return all orgs' data. This is the leak class.
Architecture docs vs code. Docs say "new Sequelize instance." Live code is this.sequelize.transaction() + RESET ROLE on the same pool. Speak the file.

What if someone calls executeWithoutRLS on the sales table?

That would bypass RLS and return all organizations' sales data in one query. It is a leak. The design prevents this by only calling executeWithoutRLS from ChatService on chat-related tables. There is no runtime check that blocks it on warehouse tables — the protection is code-level discipline, not a Postgres constraint. If I wanted a hard gate: create a separate Postgres role for chat-only that has GRANT only on chat tables, and use that role in executeWithoutRLS instead of RESET ROLE.

15. SQL RAG safety chain — how the chatbot cannot leak another org

The five-layer safety chain

Layer 1: FastAPI allow-list. extractor.py has include_tables: 10 warehouse table names (accounting_transaction, bs, cashflow_bank, cashflow_others, inventory_stk_masters, inventory_stk_vouchers, outstanding_payable, outstanding_receivable, pl, sales). The LLM can only propose SQL against these tables. schema_validator.validate_and_sync_schema(auto_sync=True) verifies the tables exist.
Layer 2: SELECT-only prompt. Prompt instructions: SELECT only, no INSERT/UPDATE/DELETE/DDL, no trailing semicolon. Temperature 0. Pydantic parser enforces the output schema (eventQuery, chartType, axes, etc.). WITH CTE is wrapped as SELECT * FROM (with...) q so Nest's startsWith('select') check passes.
Layer 3: FastAPI does not execute. FastAPI returns the SQL string and chart metadata. It never executes tenant SQL. It never sees the JWT. Nest is the trust boundary.
Layer 4: Nest executeRawSelectQuery. (a) Trim, lower, must startsWith('select'). (b) Regex (?:from|join)\s+... extracts table names. (c) hasTableAccess checks each table against the user's allowedTables. (d) If tableHasOrganizationId, inject WHERE organizationId = '${jwt.orgId}' — application-level belt. (e) Execute on req.dbTransaction (which has SET ROLE + RLS active).
Layer 5: Postgres RLS. Even if layers 1–4 had a gap, RLS policies USING(organizationId = current_setting('app.organizationId')) filter rows at the database level. The WHERE inject in layer 4 is defense-in-depth, not the primary filter.

SQL RAG Safety Chain

User: "Show me APAC sales last quarter"
         │
         ▼
Layer 1: FastAPI allow-list ── table in include_tables? ──No──▸ reject
         │ Yes
         ▼
Layer 2: Prompt ── SELECT only, temp 0, Pydantic parse
         │
         ▼
Layer 3: FastAPI returns SQL string (never executes, no JWT)
         │
         ▼
Layer 4: Nest executeRawSelectQuery
         ├─ startsWith('select')? ──No──▸ reject
         ├─ regex extract tables
         ├─ hasTableAccess(each table)? ──No──▸ 403
         ├─ inject WHERE organizationId = jwt.org (if column exists)
         └─ execute on req.dbTransaction (SET ROLE + RLS active)
         │
         ▼
Layer 5: Postgres RLS policies
         USING (organizationId = current_setting('app.organizationId'))
         │
         ▼
Only this org's rows returned

Is the prefix check a real SQL parser?

No. It is string startsWith('select'). Comments like /* */ SELECT, stacked queries, or function calls could bypass it. The FROM/JOIN table regex can also miss some shapes. These are hardening items I would flag under Highest Standards. The practical defense is that the LLM is prompted to emit clean SELECT, and Postgres RLS is the last gate regardless.

What if the model generates a DELETE disguised as a SELECT?

The model's temperature is 0 and the prompt explicitly says SELECT only. But if it somehow emitted SELECT * FROM sales; DELETE FROM sales, Nest's regex would extract "sales" twice, hasTableAccess would pass, and sequelize.query(sql, { type: SELECT }) would execute it. The mitigation: Sequelize query type SELECT may reject non-SELECT in some drivers, and the SET ROLE has SELECT-only GRANT. A stacked DELETE would fail on the GRANT (no DELETE permission on the warehouse role). But yes, a proper SQL parser is the real fix.

16. RLS probe bank — every question they might ask

Is this database-level or app-level security?

Both. Postgres RLS policies and SET ROLE are database-level — they filter rows regardless of what the application sends. Nest hasTableAccess and the org-inject WHERE are app-level — defense in depth. If the app layer has a gap, the database layer still filters. If the database policies are misconfigured, the app layer's WHERE catches it. Belt and suspenders.

What is set_config third argument?

true = local to the current transaction. When the transaction commits or rolls back, the setting disappears. false = session-level, persists until the connection is returned to the pool. I use true because the connection pool shares connections across requests. Transaction-local ensures org A's setting does not leak to org B's next request on the same connection.

Why rls_no_access instead of just rejecting the request?

Old code did RESET ROLE for admins with no SYSTEM role. That ran queries as the table owner and bypassed RLS — a leak. The fix: SET ROLE to a role that has no GRANTs on anything. Any query as rls_no_access fails with SQLSTATE 42501 (permission denied). Fail closed instead of fail open.

Why first db_role_name only?

Postgres SET ROLE is not additive. You can only be one role at a time. If a user has two roles mapped, taking the first is a simplification. The alternative — running the query under each role and merging results — is complex and raises questions about what happens when two roles have different USING clauses. I took the first one and documented the limitation.

What is the pool size and why does it matter?

Sequelize pool: max 20, min 0, acquire timeout 30s, idle timeout 10s. Each request holds one connection for the entire request lifecycle (JWT → finish). At 20 concurrent requests, the pool is full. Request 21 waits up to 30 seconds. This is the scaling bottleneck — not CPU, not memory, but Postgres connections. The honest 10× is shorter handlers, read-only SET ROLE pooling, or PgBouncer transaction-mode pooling.

Does the fieldsCache leak across orgs?

fieldsCache is keyed by table name only: fields:${tableName}, 5-minute TTL, in-process Map. It caches column metadata (names, types), not row data. Column schemas are the same across orgs — the table structure does not change per tenant. So this cache is safe. If it cached row data by table name without org, that would be a leak. Resume Redis −35% is a different hop: bot execute path with bot:{orgId}:{role}:{sha256(sql)} key — org is in the key.

What if they open the checkout and there is no Redis?

This Nest package.json has no ioredis. The fieldsCache is an in-process Map, 5 minutes. Resume −35% is Redis cache-aside on the bot execute path. I do not point at the Map and call it the Redis metric. I say both if they open the file.

Walk me through what happens when user from Org A asks "show me Org B's sales."

JwtMiddleware extracts organizationId = OrgA from the JWT. RLSMiddleware SET ROLEs to OrgA's db_role, sets GUC app.organizationId = OrgA. FastAPI proposes SELECT * FROM sales WHERE organization_name = 'OrgB'. Nest executeRawSelectQuery injects WHERE organizationId = 'OrgA' (app-level belt). The query runs on the RLS transaction. Even without the app-level inject, Postgres RLS USING(organizationId = current_setting('app.organizationId')) = 'OrgA' filters out all OrgB rows. Result: 200 with empty array. Not 403. Not an error. The RLS simply makes OrgB invisible.

Is executeWithoutRLS the same as disabling RLS?

Functionally yes for that transaction — RESET ROLE goes back to owner, which bypasses ENABLE ROW LEVEL SECURITY (unless FORCE is set, which it is not). But it is a scoped bypass: new transaction, specific callback, commit. It is not ALTER TABLE DISABLE ROW LEVEL SECURITY, which would affect all sessions. The key discipline is: only chat tables go through this path. Warehouse facts stay on req.dbTransaction.

How is the policy generator tested?

Cross-tier fixture: same SELECT text, two JWTs (OrgA and OrgB), verify OrgA gets OrgA rows, OrgB gets OrgB rows, and OrgA query for OrgB returns empty (not OrgB's data). GUEST → 403. rls_no_access → SQLSTATE 42501. I run this fixture, not a pen-test theater. No automated test suite in this repo — would-do.

29. Bar Raiser technicalities — how the systems actually work