# ConvBI code mechanisms — provenance dump (not spoken)

Notes for chapter 14 (why-cards) and chapter 15 (Ylogx live). HTML is the rehearsal book. This file exists so a claim can be traced to a path. Job **10454435** still none. No secrets copied from task definitions or `.env`. Java is Live Code. This intern is Python FastAPI + TypeScript NestJS/React.

**Checkout:** `/home/adarsh/Documents/Work/Ylogx/ConvBI/December25`  
**Resume PDF remains the only metric source.** Redis **−35%**, **30** dashboards, **sub-210 ms**, **99.9%**, SQL RAG **+65%**, reports **40%** faster, RLS+RBAC **3** tiers — speak those numbers from the resume, not from this dump.

**Ylogx LP spread (do not break):** Ownership, Frugality, Dive Deep only. Dive Deep lead = Redis + RLS on the resume; in this snapshot add honesty that Nest has no Redis client.

---

## 1. Docs vs code (speak the split)

| Topic | Docs / markdown say | Code in this checkout does |
| --- | --- | --- |
| Cookie auth | `COOKIE_AUTH_IMPLEMENTATION_COMPLETE.md`: HTTP-only cookies, `cookie-parser`, `req.cookies.accessToken` first, CORS specific origins, frontend `withCredentials`, no localStorage token. Lists `src/config/cookie-options.ts` as NEW. | **No** `cookie-options.ts`. `jwt.middleware.ts` only reads `Authorization` Bearer. `auth.service.ts` still returns `accessToken: Bearer …` in JSON. Frontend `axiosInterceptor.ts` and `api/chat.ts` read `localStorage.token`. Nest CORS is `process.env.CORS_ORIGIN \|\| '*'`. |
| CI/CD | `backend-docs/chapters/07-docker.tex` section “CI/CD: AWS CodeBuild”, figure Git push → development branch → CodeBuild → Docker Build → backend.projas.in | Runnable deploys are GitHub Actions: `convbi-backend/.github/workflows/convbi-qa.yml` (push `qa`) and `convbi-prod.yml` (push `main`). |
| Redis | Resume: Redis −35% bot DB latency. Writer lock: Dive Deep = Redis + RLS. | **No** `redis` / `ioredis` in Nest or FastAPI Python trees. In-process `Map` `fieldsCache` in `queryDB.service.ts`. |
| 30 dashboards | Resume: 30 KPI dashboards +60% ops, sub-210 ms | `architecture-docs/06_dashboard_flow.tex`: **9** predefined types (some multi-page). Controller matches those 9. Page counts in that tex: overview 1, sales 6, receivables 5, payables 3, stock 2, collections 3, P&L 1, BS 1, cashflow 2 (**24** pages). Do not invent a tile census. Keep resume **30** as outcome; keep **9** as this enum. |
| Recharts | Resume / older notebook: React/Recharts | Predefined KPI pages: **Recharts**. Report builder + chatbot `ElementRenderer`: **Chart.js**. `frontend-docs/chapters/10-packages.tex` lists both. `package.json` has `recharts` and Chart.js (via lock / react-chartjs-2). |
| Gemini | `frontend-docs/chapters/10-packages.tex`: `@google/generative-ai` 0.24.0 “Google Gemini AI SDK”. `convbi-frontend/package.json` lists the same. | **No** import of `@google/generative-ai` under `convbi-frontend/src`. SQL extractor is `langchain_openai.ChatOpenAI` in `convbi-ai/extractor.py`. |
| FinishTransaction | architecture-docs/03 and backend-docs/03: COMMIT on success, ROLLBACK on error | `finish-transaction.middleware.ts`: on `res.on('finish')` **always commit**, rollback only if commit throws. No status-code check. |
| Session GUC names | architecture-docs/08: `app.user_guid`, `app.organization_id`, `app.client_id`, `app.allowed_tables` | `rls.middleware.ts`: `app.current_user_id`, `app.organizationId`, `app.is_SUPER_ADMIN`, `app.is_admin`, `app.user_role`, `app.admin_hierarchy_filter`. |
| Base git branch | `convbi-ai/GUIDELINES.md`: `dev` is the base branch | Workflows trigger on `qa` and `main`. Nest `.git/HEAD` in this workspace was `developement` (typo). Do not invent a third environment name as an incident. |
| Pool 20 | backend-docs/07 table: max 20, min 0, acquire 30000, idle 10000 | `convbi-backend/src/dbModules/sequelize.module.ts` same numbers. **FACT.** |
| USER node | backend-docs/07 Dockerfile walkthrough | `convbi-backend/Dockerfile` `USER node` then `CMD ["node", "dist/main"]`. **FACT.** |
| Prod approval | (not in latex as a timeout) | `convbi-prod.yml`: `trstringer/manual-approval@v1`, `timeout-minutes: 3`, `minimum-approvals: 1`. **FACT.** Do not invent war-room hours. |
| FastAPI CORS | Cookie MD: Nest CORS specific origins | `convbi-ai/main.py`: `allow_origins=["*"]`, `allow_credentials=True`. |

---

## 2. Process graph (algorithms)

### 2.1 Nest request pipeline

**Files:** `src/main.ts`, `src/app.module.ts`, `src/middleware/jwt.middleware.ts`, `src/middleware/rls.middleware.ts`, `src/middleware/finish-transaction.middleware.ts`.

1. Bootstrap: ValidationPipe (`whitelist`, `forbidNonWhitelisted`, `transform`), 50mb body parser, CORS, global exception filter, logging interceptor, `app.use(FinishTransactionMiddleware)` (registers `finish` listener **before** route middleware).
2. `AppModule.configure`: `JwtMiddleware` then `RLSMiddleware` on `*`, excluding `health`, `healthcheck`, `health/simple`, and public `auth/*` (signup, signin, OTP, activate, forgot/reset/update password, refresh-token). **Logout is not in the exclude list** (cookie MD claimed it was).
3. JWT algorithm:
   - Require `Authorization` header.
   - Split; require `Bearer` + token.
   - `jwt.verify(token, process.env.JWT_ACCESS_SECRET)`.
   - Set `req.user = { userId: decoded.guid, role, email, client_id, organizationId }`.
4. RLS algorithm (live `use()`, not the large commented block):
   - If no `req.user`, `next()`.
   - `transaction = await sequelize.transaction()`.
   - Branch on `RoleEnum`.
   - Store `req.dbTransaction = transaction`.
   - On error: `rollback` and `next(error)`.
5. Controllers that need isolation pass `req['dbTransaction']` into services (dashboards, queryDB).
6. On HTTP finish: commit (see docs vs code).

**RoleEnum:** `SUPER_ADMIN | ADMIN | USER | GUEST` (`src/enums/userRole.enum.ts`). Resume “3 tiers” maps to SUPER_ADMIN / ADMIN / USER. GUEST is extra; dashboards forbid guests.

### 2.2 SET ROLE resolution

**File:** `rls.middleware.ts` live `use()`.

| JWT role | Postgres action | Extra |
| --- | --- | --- |
| SUPER_ADMIN | `RESET ROLE` + `app.is_SUPER_ADMIN=true` | Full access by design |
| ADMIN + SYSTEM roles with `db_role_name` | `SET ROLE "<first db_role_name>"` | `app.is_admin=true`, `app.organizationId` |
| ADMIN + roles but none have `db_role_name` | `SET ROLE "rls_no_access"` | Fail closed |
| ADMIN + zero SYSTEM roles | `SET ROLE "rls_no_access"` | Comment: previously RESET ROLE leaked |
| USER + `db_role_name` | `SET ROLE "<primary db_role_name>"` | Collect `policyRoleNames`, `allowedModuleGuids`, `allowedTables` via ModuleRegistry |
| USER otherwise | `SET ROLE "rls_no_access"` | `noDataAccess=true` |

Postgres can have only one session role: code uses **first** `db_role_name` only.

`set_config(name, value, true)` is transaction-scoped (third arg `is_local`).

### 2.3 Policy DDL algorithm

**File:** `policyManagement.service.ts` (create policy path ~line 430+).

For each table in allowed modules:

1. `RESET ROLE` on privileged tx for DDL.
2. Build `USING` from policy conditions.
3. If table has `organizationId` column: AND `"organizationId" = current_setting('app.organizationId')::uuid`.
4. If table not in allowed modules: `USING (false)` (zero rows, not error).
5. `DROP POLICY IF EXISTS`, `CREATE POLICY ... FOR SELECT TO <policyRole> USING (...)`, `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`.

Dashboards comment (`dashboards.service.ts` header): access control is **purely** Postgres RLS once SET ROLE ran; empty result if no table grant.

When `dbRoleNames` is empty, dashboards may apply `getClientFilter` app SQL (`rlsHandlesAccess` flag ~line 700). That is a second path — not a reason to drop RLS.

### 2.4 SQL RAG (propose vs execute)

**FastAPI files:** `convbi-ai/main.py`, `extractor.py`, `engine.py`, `prompts.py`, `config.py`, `schema_validator.py`.

1. `POST /chatbot` body `{ query, options }` — **no JWT**.
2. `SQLQueryAndDetailsExtractor.get_llm_chain_object`:
   - `ChatOpenAI(model="gpt-4.1", temperature=0)`.
   - `SQLDatabase(engine, schema="public", include_tables=[accounting_transaction, bs, cashflow_bank, cashflow_others, inventory_stk_masters, inventory_stk_vouchers, outstanding_payable, outstanding_receivable, pl, sales])`.
   - `validate_and_sync_schema` (may auto-sync config).
   - Prompt partial: `table_info`, `table_descriptions`, dialect.
   - `prompt | llm | PydanticOutputParser(SQLQueryAndDetails)`.
3. `extract_data`: if `eventQuery` is `WITH ...` without SELECT, wrap as `SELECT * FROM (<cte>) q`. Strip trailing semicolon. Whitespace normalize.
4. `engine.py`: SQLAlchemy `create_engine` to Postgres **as DB_USER** — used for **schema introspection**, not the Nest execute path.
5. CORS `*` + credentials True.
6. Dockerfile: Python 3.12, gunicorn 8 Uvicorn workers, bind `0.0.0.0:80`, timeout 180, HEALTHCHECK curl `/health`.

**Nest execute:** `QueryDBService.executeChatQuery` (`queryDB.service.ts` ~1751):

1. `fetch(process.env.CHATBOT_API_URL || 'https://chatbot.projas.in/chatbot')` with `{ query, options }` only.
2. Parse JSON; if `eventQuery`, call `executeRawSelectQuery(eventQuery, user, dbTransaction)`.
3. On SQL error: return empty `data`, chatbot `chatMessage`, `showChart: false`, `error` message — fail closed for the chart, still return prose.
4. Strip `eventQuery` from the client payload.

**SELECT allow-list algorithm** (`executeRawSelectQuery` ~1325):

1. `trim.toLowerCase().startsWith('select')` else 400 “Only SELECT queries are allowed”.
2. Regex `(?:from|join)\s+((?:"?[a-zA-Z0-9_]+"?\.)?"?[a-zA-Z0-9_]+"?)` extract table names (schema stripped).
3. For each table: `hasTableAccess`:
   - Chat tables: allow all authenticated.
   - SUPER_ADMIN: all non-system tables (`isSystemTable` list).
   - ADMIN: `getAdminAllowedTables` via user_roles → role_policies → policies.allowedModules → module tables.
   - USER: deny system tables; else `getTables` policy list.
4. Non-SUPER_ADMIN: `getOrganizationFilter` → if table has `organizationId`, string-inject `"alias"."organizationId" = '<organizationId>'` into WHERE (or before GROUP BY/ORDER BY/LIMIT). **Bound replacements are not used for this inject** — UUID from JWT/user row.
5. `sequelize.query(modifiedSql, { type: QueryTypes.SELECT, transaction: dbTransaction })`.

`getAllowedTables` queries `pg_policies` **without** the RLS transaction (comment: SET ROLE may block catalog). That is intentional catalog read as login role.

### 2.5 Dashboard path (no LLM)

**Files:** `dashboards.controller.ts`, `dashboards.service.ts`.

Endpoints: `GET /dashboards/{overview,payables,collections,sales,cashflow,balance-sheet,stocks,receivables,profit-loss}` plus `last-updated`, `detailed-update-info`.

Each GET reads `req.user` and `req.dbTransaction`, parses month(s)/year(s) query strings, runs **fixed** SQL (`QueryTypes.SELECT` with that transaction).

Frontend: `DashboardFiltersContext` (localStorage `dashboardFiltersState_v2`). Predefined pages call `dashboardAPI`. Custom dashboards persist layout in localStorage (`dashboardState`) — not warehouse facts.

Chart split: Recharts on predefined pages (e.g. `SalesPage1.tsx`); Chart.js on `ElementRenderer.tsx` and chatbot `App.tsx` dynamic import.

### 2.6 Chat persistence bypass

**File:** `chat.service.ts`.

```ts
private async executeWithoutRLS<T>(callback): Promise<T> {
  const transaction = await this.sequelize.transaction();
  await this.sequelize.query('RESET ROLE;', { transaction });
  const result = await callback(transaction);
  await transaction.commit();
  ...
}
```

New transaction, **not** `req.dbTransaction`. Intended for `chats`, `chat_messages`, favorites, feedback. Org isolation: `getOrganizationFilter` (SUPER_ADMIN → null filter).

**Misuse class:** wrapping warehouse SELECT in this helper.

### 2.7 Dataset field Map (not Redis)

**File:** `queryDB.service.ts` fields:

- `fieldsCache: Map<string, { etag, data, expiresAt }>`
- `fieldsTtlMs = 5 * 60 * 1000`
- Key: ``fields:${tableName}`` — **no org, no role, no user**
- Hit returns cached column metadata even though `getTableColumnsWithTypes` would have used the RLS transaction on a miss

This is the code-accurate “cache/Map without tier” class. It is not the resume Redis claim.

### 2.8 Tally zip → S3, not DB blobs

**Files:** `fileUpload.service.ts`, `fileUpload.controller.ts`, `fileUpload/utils/file-validation.util.ts`, `backend-docs/chapters/06-file-upload.tex`.

Algorithm:

1. Multer memory storage; max 2 zips.
2. Validate extension `.zip`, MIME, size (service `MAX_FILE_SIZE_MB` default 500; util default 200MB — docs say 200MB).
3. Magic bytes PK (`0x50 0x4b …`), AdmZip integrity, nonempty.
4. `sanitizeFilename`; key `tally-uploads/<timestamp>-<name>`.
5. AWS SDK v3 `Upload` to S3; return `s3://bucket/key`.
6. POST Tally Gateway with `zip_files.current_year` / `previous_year` URIs plus credentials object.

Do not paste gateway keys or AWS secrets from source or task definition.

### 2.9 Health / sick task

**Nest:** `healthCheck/health.controller.ts`

- `GET /health` and `/healthcheck`: Terminus + `sequelizeHealth.pingCheck('postgresDB')`
- `GET /health/simple`: `{ status: 'ok' }` without DB ping

**FastAPI:** `GET /health` → `{ status: "ok" }`; Docker HEALTHCHECK 30s / timeout 10s / start 40s / retries 3.

**Frontend:** nginx wget `/` every 30s.

**ECS:** workflows `wait-for-service-stability: true`. Task definition (non-secret): family `convbi-backend`, container port 5000, cpu 1024, memory 3072, memoryReservation 1024. Do not quote secret env values from that JSON.

### 2.10 Sequelize module

**File:** `dbModules/sequelize.module.ts`

- dialect postgres, `autoLoadModels: true`, **`synchronize: true`**
- pool max 20 / min 0 / acquire 30000 / idle 10000
- `application_name: convbi_app`
- SSL when `POSTGRES_SSL === 'true'` (`rejectUnauthorized: false`)
- SQL logging when `SQL_LOGGING=true` or `NODE_ENV=development`

`synchronize: true` is a prod-shaped risk (schema drift from models). 10× / Highest Standards would-do: migrations, not auto-sync. Not a named incident.

### 2.11 Auth (what actually runs)

**Files:** `auth.service.ts`, `auth.controller.ts`.

Signup domain check `ORGANISATION_EMAIL`; OTP; bcrypt salt 10; access + refresh JWTs; signin returns Bearer access token in body. Matches backend-docs chapter 9, not the cookie markdown.

### 2.12 Logging

`LOGGING_IMPLEMENTATION_SUMMARY.md` / `LOGGING_GUIDE.md`: `http-exception.filter.ts`, `logging.interceptor.ts`, `logger.util.ts` (`sql`, `transaction`, `performance`), slow-query warning >1s, redaction of passwords/tokens. Not LangSmith.

---

## 3. Deploy files (no secrets)

| File | What it is |
| --- | --- |
| `convbi-backend/.github/workflows/convbi-qa.yml` | On push `qa`. ECR repo `convbi-backend`. ECS service `convbi-backend-service`, cluster `convbi-cluster`. Tags SHA + `qa`. No approval job. |
| `convbi-backend/.github/workflows/convbi-prod.yml` | On push `main`. Same cluster/service. Tags SHA + `latest`. **Manual approval** then deploy. |
| `convbi-backend/Dockerfile` | node 18.18.2-alpine3.18, `USER node`, port 5000 |
| `convbi-ai/Dockerfile` | python 3.12-slim, gunicorn 8, port 80, HEALTHCHECK |
| `convbi-frontend/Dockerfile` | node 20 build → nginx stable-alpine, HEALTHCHECK wget |
| `convbi-backend/docker-compose.yml` | local Nest on 5000, env from compose — do not dump values |
| `convbi-backend/.aws/task-definition.json` | ECS task shape. **Contains secrets in this snapshot — never quote them in HTML or speech.** Class: secrets in task JSON vs runtime secret store. |

backend-docs still drawing CodeBuild is stale relative to `.github/workflows`.

---

## 4. Frontend chat / dashboard wiring

| File | Role |
| --- | --- |
| `convbi-frontend/src/api/chat.ts` | Nest `/chat` CRUD, Bearer from localStorage |
| `convbi-frontend/src/modules/chatbot/App.tsx` | UI; Chart.js dynamic import for bot charts |
| architecture-docs/05 | `sendChatMessage` → `POST /queryDB/chat-query` |
| `convbi-frontend/src/utils/axiosInterceptor.ts` | Bearer from localStorage; 401 clears storage |
| `convbi-frontend/src/modules/auth/services/authService.tsx` | Exists; cookie MD claimed a full rewrite — verify against localStorage still used by interceptors |

---

## 5. What not to steal across products

| Product | Mechanism | Not Ylogx |
| --- | --- | --- |
| IQVIA | LangGraph, Azure Hybrid + Semantic, GraphDB, LangSmith, 200+ sites/pages | Do not put LangGraph on ConvBI chatbot |
| Horizon | ROS2, GStreamer, ZED 2, costmap | — |
| Argus | YOLOv9 mAP, cameras | — |
| GiftedBooks | PDF RAG sub-300 ms | Do not merge with SQL RAG |

Ylogx chatbot = LangChain SQL RAG (`ChatOpenAI` + parser) + Nest executor + Postgres RLS.

---

## 6. Spoken honesty lines (keep)

- “FastAPI proposes SQL. Nest executes SELECT under SET ROLE. FastAPI’s engine is for schema info.”
- “This Nest snapshot has no Redis module. Resume −35% is Redis. This file’s Map is `fields:${tableName}` with a 5 minute TTL.”
- “Resume 30 KPI dashboards; this repo’s controller has 9 dashboard types.”
- “Cookie markdown is not what `jwt.middleware.ts` does.”
- “backend-docs CodeBuild is not what `.github/workflows` does.”
- “Prod workflow waits for a 3-minute manual approval action. I do not invent war-room hours.”
- “Pool 20 and `USER node` are in the files.”
- “I will not quote secrets from `task-definition.json`.”
- “FinishTransaction commits on finish, including error responses.”
- “Unassigned ADMIN used to RESET ROLE; live code sets `rls_no_access`.”

---

## 7. File index (primary)

```
architecture-docs/01_system_overview.tex
architecture-docs/02_auth_flow.tex
architecture-docs/03_middleware_pipeline.tex
architecture-docs/04_report_builder.tex
architecture-docs/05_chatbot_flow.tex
architecture-docs/06_dashboard_flow.tex
architecture-docs/07_admin_rbac.tex
architecture-docs/08_data_layer.tex
backend-docs/chapters/03-system-flow.tex
backend-docs/chapters/06-file-upload.tex
backend-docs/chapters/07-docker.tex
backend-docs/chapters/09-security-system.tex
frontend-docs/chapters/09-docker.tex
frontend-docs/chapters/10-packages.tex
COOKIE_AUTH_IMPLEMENTATION_COMPLETE.md
LOGGING_IMPLEMENTATION_SUMMARY.md
convbi-backend/src/main.ts
convbi-backend/src/app.module.ts
convbi-backend/src/middleware/jwt.middleware.ts
convbi-backend/src/middleware/rls.middleware.ts
convbi-backend/src/middleware/finish-transaction.middleware.ts
convbi-backend/src/dbModules/sequelize.module.ts
convbi-backend/src/queryDB/queryDB.service.ts
convbi-backend/src/queryDB/queryDB.controller.ts
convbi-backend/src/dashboards/dashboards.controller.ts
convbi-backend/src/dashboards/dashboards.service.ts
convbi-backend/src/chat/chat.service.ts
convbi-backend/src/policyManagement/policyManagement.service.ts
convbi-backend/src/fileUpload/fileUpload.service.ts
convbi-backend/src/healthCheck/health.controller.ts
convbi-backend/src/common/applicationTables.ts
convbi-backend/Dockerfile
convbi-backend/.github/workflows/convbi-qa.yml
convbi-backend/.github/workflows/convbi-prod.yml
convbi-ai/main.py
convbi-ai/extractor.py
convbi-ai/engine.py
convbi-ai/prompts.py
convbi-ai/Dockerfile
convbi-frontend/Dockerfile
convbi-frontend/src/utils/axiosInterceptor.ts
```

End of provenance. Do not treat this markdown as a spoken answer.