15. Ylogx live — ConvBI blast radius, RLS/RBAC how, and issue classes from this repo

Situation. Operators who are not SQL experts needed custom reports, ad-hoc natural-language analysis, and live KPI tiles over shared warehouse facts at three organizational scopes.

Task. I had to make analysis useful without one tier reading another, keep known KPI reads deterministic and fast, and deploy repeatably.

Action. I shipped React at the browser; NestJS for JWT, RBAC, report CRUD, dashboard REST, and the SQL executor; FastAPI for LangChain SQL generation; Postgres for facts and RLS; Docker images on ECS behind ALB and CloudFront.

Action — hard part. FastAPI proposes SQL and never executes warehouse reads as the caller. NestJS opens a per-request Sequelize transaction, runs SET ROLE to the mapped Postgres role, and executes SELECT under that transaction. Dashboard GETs use the same transaction and never call the model.

Result. The resume records 40% faster report generation, 99.9% uptime, +65% analysis productivity, −35% bot database latency, 30 dashboards with +60% operations efficiency, and sub-210 ms. I do not invent a baseline sample.

Mechanism flow

1. Browser loads the Vite React SPA (nginx in the frontend image).
2. Axios sends Authorization: Bearer from localStorage.
3. CloudFront serves static assets; dynamic HTTP hits ALB → healthy ECS tasks.
4. JwtMiddleware verifies the access token; RLSMiddleware begins a transaction and SET ROLE.
5. Dashboard/report routes run fixed SQL on req.dbTransaction.
6. Chat path: Nest POST /queryDB/chat-query calls FastAPI POST /chatbot; FastAPI returns eventQuery; Nest runs it through executeRawSelectQuery.
7. SELECT-only parse, table allow-list, optional org predicate, then Postgres RLS.
8. FinishTransactionMiddleware commits on HTTP finish.

Situation. In dev I can break my own user and still see a plausible chatbot answer.

Task. Practice production-safe habits before they become a tenant problem.

Action. Run generated SQL through Nest as a non-owner role; reject non-SELECT; keep KPI GETs off FastAPI; do not treat a single-account click as three-tier isolation.

Result. Dev catches path mistakes cheaply. A successful single-user click is not evidence of SUPER_ADMIN / ADMIN / USER isolation.

Situation. A feature that passed for one account could leak or over-deny another tier.

Task. Prove permission and denial, then promote an image that ALB will only send to healthy tasks.

Action. Cross-tier fixtures, denied JOINs, golden NL → expected SQL shape, rls_no_access for unassigned roles, dashboard GETs without generation. QA workflow rolling-replaces ECS after image push.

Result. Promotion required role-correct behavior. I do not invent a QA pass-rate percent.

Situation. Blast radius became every authorized user across three organizational tiers.

Task. Preserve isolation, availability, and deterministic KPI latency while live tasks were replaced.

Action. CloudFront → ALB → healthy ECS; role-bound SQL; no LLM on dashboard GET; prod workflow blocks deploy until the approval action completes or times out.

Result. 99.9% uptime and sub-210 ms on the resume. No missed target is invented.

Problem. A logo dump cannot identify latency, authorization, or rollback ownership.

Checks. Classify static asset, deterministic dashboard/report API, or ad-hoc chatbot request.

Change / mechanism.

1. Hostname: registrar (GoDaddy on the resume) delegates to Route 53.
2. CloudFront serves the nginx-built SPA from convbi-frontend/Dockerfile.
3. API HTTP → ALB → ECS task listening on container port 5000 (task definition family convbi-backend, cpu 1024, memory 3072 — no secret values).
4. main.ts: CORS, 50mb body parser, ValidationPipe whitelist, FinishTransaction listener, Swagger at /api.
5. AppModule applies Jwt then RLS on all routes except health and public auth.
6. Dashboard: DashboardsController passes req.dbTransaction into DashboardsService.
7. Chat SQL: QueryDBService.executeChatQuery fetches CHATBOT_API_URL (FastAPI /chatbot), then executeRawSelectQuery.
8. Health: GET /health uses Terminus sequelizeHealth.pingCheck('postgresDB'). FastAPI has its own /health plus a Docker HEALTHCHECK.

Result. Same serving story as the resume. I will not claim this Nest process contains a Redis client.

Problem. Patching one mutable host creates drift.

Change. CI builds a Docker image, pushes ECR, renders the task definition container image, (prod) waits for manual approval, deploys the ECS service, waits for stability.

Definitions. A task is one running copy. A service maintains desired count and replaces unhealthy tasks. ALB is L7 HTTP plus target health. CloudFront is edge/static delivery.

Rollback. Bad image → previous known image/task definition. Wrong RLS → stop privileged path and restore tested policy. DNS → delegation/Alias, not app rollback.

Situation. Users at three scopes shared warehouse facts; handwritten and generated SQL could join shared tables.

Task. Control both what action a caller may perform and which rows that allowed action may return.

Action — RBAC. JWT payload carries role. Nest route middleware requires a valid Bearer token. Feature flags and admin modules further gate UI. That is necessary and not sufficient.

Action — RLS.

1. RLSMiddleware opens sequelize.transaction().
2. SUPER_ADMIN: RESET ROLE plus set_config('app.is_SUPER_ADMIN','true', true).
3. ADMIN with a SYSTEM role that has db_role_name: SET ROLE "<db_role_name>" (first such role only — Postgres has one active role).
4. USER with roles: same SET ROLE on the primary db_role_name, then resolve policies → allowedModules → ModuleRegistry.tables onto req.user.allowedTables.
5. ADMIN or USER with no usable db_role_name: SET ROLE "rls_no_access" and, for users, noDataAccess=true. Comment in code: previously RESET ROLE caused a data leak.
6. set_config writes app.current_user_id, app.organizationId, app.is_admin, app.user_role as transaction-local settings.
7. Policy DDL: CREATE POLICY ... FOR SELECT TO <policyRole> USING (conditions AND "organizationId" = current_setting('app.organizationId')::uuid), then ENABLE ROW LEVEL SECURITY.
8. Dashboard SQL uses that same open transaction so policies apply.

Result. Three-tier analysis stays trustworthy while reports became 40% faster and analysis productivity improved 65%.

-- mechanism sketch matching policyManagement.service.ts
SET ROLE "db_role_name";
SELECT set_config('app.organizationId', :organizationId, true);
-- CREATE POLICY ... USING (
--   <module rules> AND
--   ("organizationId" = current_setting('app.organizationId')::uuid)
-- );
-- generated SELECT runs on req.dbTransaction

Problem. A prompt can request catalog dumping, invent a table, or generate a broad JOIN.

Checks. FastAPI allow-listed tables in SQLDatabase(include_tables=...). Nest rejects non-SELECT, unknown tables, then RLS.

Change. Treat the model as an untrusted SQL proposer. Deterministic Nest controls execute a bounded query or fail closed.

Result. Prompt text cannot promote privilege; +65% means useful analysis, not blind acceptance of fluent SQL.

Situation. Uncached SQL RAG repeated Postgres work; the resume records Redis on that bot DB path.

Task. Reduce repeat DB latency without crossing tier boundaries.

Action — resume. Redis cache-aside on the bot path, key must include tier/role; miss executes under RLS; Redis-down falls back to Postgres as the same role.

Action — this snapshot. Dataset field metadata is cached in a process-local Map without org or role in the key. Warehouse answers are not cached there. Dashboards are not served from this Map.

Failure class. A Map keyed only by table name is shared across every tenant on that Nest task. Even if values are column names, it is the wrong isolation shape, and it is not Redis.

Result. I keep the resume −35% as the intern outcome. I will not invent an ioredis module that is not in this tree.

# this repo (queryDB.service.ts) — NOT Redis
key = "fields:" + tableName
Map.get(key)  # no org, no role, 5 min TTL

# resume Redis sketch — not a file in this checkout
key = tier | role | fingerprint(schema, question)
miss → Postgres as caller role → SET → return

Problem. Syntactically valid JOIN returns a good-looking chart but includes another organization through a shared mapping path.

Checks. Executing role; whether the query used req.dbTransaction; RLS on joined tables; whether Nest also string-injected "alias"."organizationId" = '<id>' only on tables that have that column.

Change. Caller-mapped non-owner role, RLS at row access, table allow-list before execute, RBAC at route.

Result. Trustworthy three-tier analysis supports +65% productivity without inventing a leak event.

Problem. Different tiers type identical English or hit the same table metadata. A key without identity is a cross-tenant hit.

Checks. Key identity; fill role; Redis-down (resume) or process-local Map (this repo); denied/empty result handling.

Change. Resume: tier/role + fingerprint; fill after RLS. This snapshot: do not treat fields:${tableName} as tenant-safe; do not put warehouse result sets in that Map.

Result. −35% remains a win only if reuse does not undo isolation.

Problem. A fixed tile becomes variable, slower, costlier, and capable of inventing a KPI when it calls a model per refresh.

Checks. Route file: dashboards.controller.ts has no FastAPI call. Chat lives at queryDB/chat-query.

Change. Identity → RLS transaction → grouped SQL → JSON → Recharts (predefined pages) or Chart.js (report builder / chatbot ElementRenderer). Optional explanation stays off path.

Result. 30 dashboards / +60% / sub-210 ms / 99.9% on resume.

Problem. A task can bind the port, fail the DB ping, or loop on startup, and still be “deployed” if nobody waits on health.

Checks. /health vs /health/simple (simple does not ping Postgres). ALB target state. ECS service events. Do not debug RLS when the name does not resolve or the target is draining.

Change. Keep health off JWT (AppModule excludes health, healthcheck, health/simple). ALB only forwards to healthy tasks. Deploy waits for service stability.

Result. 99.9% is a serving result, not a claim that every failed task was a named outage.

Problem. Model emits nonexistent/unapproved table or a wrong-grain JOIN that runs and lies.

Checks. eventQuery present; starts with SELECT; FROM/JOIN names in hasTableAccess; execute on RLS transaction; inspectable SQL on failure returns chatbot message with showChart: false.

Change. Fail closed on unknown table; execute accepted SQL as caller role; do not let FastAPI’s privileged SQLDatabase engine run the user query (it is for schema get_table_info() only).

Result. +65% analysis refers to useful analysis, not confident prose over a guessed FROM.

Problem. Hostname fails before app, points at the wrong environment, has host/TLS mismatch, or reaches ALB with unhealthy targets.

Checks. Registrar NS; hosted zone/Alias; CloudFront/ALB target; host/certificate; target health; origin.

Change. Correct only delegation, Alias, host route, or health. Keep app/RLS diagnosis separate.

Result. Configured path makes 99.9% and sub-210 ms reachable; no downtime duration invented.

Problem. An ADMIN JWT without an assigned Postgres role looks “logged in.” RESET ROLE returns the connection’s login role — typically the table owner — so RLS does not apply.

Checks. db_role_name present? SYSTEM scope for ADMIN? noDataAccess on USER? Guest hitting dashboards?

Change. Fail closed to a role with zero table grants. Empty dashboard is correct; full warehouse is not.

Result. Unassigned users do not inherit owner reads. I will not invent a customer who saw the leak.

Problem. Chat metadata tables may not be granted to the restricted warehouse role, so the happy path needs a bypass. The same helper on sales / outstanding_receivable would skip RLS.

Checks. Which tables sit inside the callback? Is this chat_* or warehouse facts? Is the filter SUPER_ADMIN → no org predicate?

Change. Keep RESET ROLE only for application tables the restricted role cannot touch. Warehouse reads stay on req.dbTransaction.

Result. Chat history works; generated analytics still hit RLS. Mixing those is the class.

Problem. HTTP 4xx/5xx still finishes the response. The listener commits the open RLS transaction, including any writes that already ran.

Checks. Did the handler write before throwing? Did the client see an error while the DB committed? Did a pooled connection return with a leftover role if commit failed oddly?

Change. The intended design is: success commit, error rollback, always RESET ROLE before release. The file as checked in does not inspect status codes.

Result. I describe the class and the would-do. I do not invent a corrupt-row incident.

// finish-transaction.middleware.ts — actual
res.on('finish', async () => {
  const tx = req.dbTransaction;
  if (!tx) return;
  try { await tx.commit(); }
  catch (e) { await tx.rollback(); }
});

Problem. A browser from any origin can call the FastAPI proposer. FastAPI has no JWT. Nest is supposed to be the only caller, but CORS * plus an exposed chatbot URL is a generation endpoint on the public internet.

Checks. Is FastAPI reachable without going through Nest? Does Nest attach user identity to that POST? (It does not.) Spec: * + credentials is not a valid browser CORS combination.

Change. Lock FastAPI to the Nest origin or keep it private; never let the browser talk to the generator; keep execution on Nest under SET ROLE.

Result. Isolation still depends on Nest executing SQL. CORS is not RLS, but it is how an unauthenticated client reaches the proposer.

Situation. 10× raises concurrent connections, cache misses, role-policy combinations, task demand, and harm from isolation mistakes.

Task. Scale after measuring the current mechanism.

Action.

1. Expand protected-table/role deny matrix including JOIN fixtures.
2. Test pooled role set/reset; do not rely on commit-on-finish.
3. Stop treating a process Map as a distributed cache.
4. Remove dashboard N+1; keep the model off tiles.
5. Size the pool against desired task count (20 × tasks can exhaust RDS max_connections).
6. Increase ECS desired count behind ALB health.
7. Turn off synchronize: true in prod-shaped deploys.
8. Re-measure before new architecture.

Result. More of the mechanism that supports current outcomes. I explicitly did not shard Ylogx.

Java. Live Code language on Zoom. Ylogx was not Spring/JVM production.

Kubernetes. Listed knowledge. Ylogx shipped Docker on ECS. I was not a production cluster owner and do not claim EKS.

FAISS / Qdrant. Not this intern. Ylogx used Postgres + SQL RAG.

Auth0. Listed skill. This snapshot is JWT Bearer from Nest jsonwebtoken.

Gemini. Frontend dependency and docs package table. Extractor is ChatOpenAI. No GoogleGenerativeAI import in frontend src.

Cookies. A completed-migration markdown exists. Running middleware is Bearer.

Redis. Resume −35%. Not in this Nest tree.

Problem. Went live meant blast radius moved from my user to three organizational tiers on one warehouse.

Checks. Classes: wrong-org JOIN; Map/cache without tier; LLM on KPI tile; sick ECS task; hallucinated SQL; DNS; rls_no_access; executeWithoutRLS misuse; FinishTransaction commit-on-finish; FastAPI CORS *.

Change. Caller-role RLS + NestJS RBAC; no LLM on dashboards; constrained SQL failing closed; hop-by-hop DNS/health; fail closed when no db role.

Result. 40% faster reports, 99.9% uptime, +65% analysis, −35% bot DB latency, 30 dashboards +60% operations, sub-210 ms.

Stop. Let the interviewer choose one class. Do not restart the architecture or invent a ticket.

A generic Amazon-style outline would invent a PagerDuty page, a timestamp, a customer name, and a manager quote, then claim you personally rolled DNS for three hours. That is exactly what this chapter forbids. Ylogx answers stay at failure classes, file names, and resume numbers.