ConvBI / Projas — Ylogx intern BI, from this checkout

0. Lock, labels, docs vs this tree

Every claim below is tagged. FACT = this checkout or a matching architecture/README section. RESUME = Aug 2026 PDF only. DOCS≠CODE = latex/markdown disagrees with the live file — speak the file they opened. MISSING = not in this Nest tree. STD CS = textbook follow-up, not a resume number.

1. How it was built

2. Directory map of this checkout

Nest modules registered in AppModule

Config, Sequelize, Health, Auth, Organization, ModuleRegistry, QueryDB, PolicyManagement, RoleManagement, UserHierarchy, Overview, Reports, FeatureManagement, Chat, Dashboards, FileUpload. JWT+RLS applied to * except health + listed auth POSTs.

Sequelize models (app tables)

From applicationTables.ts and architecture-docs/08: organizations, module_registry, policies, role_policies, roles, user_roles, users, chat_feedbacks, chat_messages, chats, favorite_conversations, favorite_messages, features, feedbacks, reports, user_features. Warehouse fact tables are not Sequelize models — they are queried as SQL under RLS.

Warehouse tables the AI sidecar is allowed to see

extractor.py include_tables: accounting_transaction, bs, cashflow_bank, cashflow_others, inventory_stk_masters, inventory_stk_vouchers, outstanding_payable, outstanding_receivable, pl, sales. Descriptions live in config.py table_descriptions; schema_validator.validate_and_sync_schema(..., auto_sync=True) on extractor init.

3. Request lifecycle (exact files)

Browser React SPA
  App.tsx Router + Sidebar layout
  axiosInterceptor.ts  → Authorization: Bearer (strips/re-adds)
  chatbot.ts fetch     → Authorization: localStorage token as-is
                         (signin returns "Bearer " + jwt, so both work)
        │ REST JSON
        ▼
Nest main.ts
  AllExceptionsFilter
  LoggingInterceptor
  CORS  origin = CORS_ORIGIN || '*'  credentials true
  bodyParser 50mb JSON + urlencoded
  ValidationPipe whitelist + forbidNonWhitelisted + transform
  FinishTransactionMiddleware  (registers res.on('finish'))
  Swagger /api
        │
        ▼  (excluded: health*, auth signup/signin/otp/activate/forgot/reset/update-password/refresh-token)
JwtMiddleware   jwt.verify(JWT_ACCESS_SECRET)
                req.user = { userId: guid, role, email, client_id, organizationId }
        │
        ▼
RLSMiddleware   sequelize.transaction()
                SUPER_ADMIN → RESET ROLE + set_config flags
                ADMIN       → first SYSTEM role.db_role_name or SET ROLE rls_no_access
                USER        → first db_role_name (Postgres one active role)
                              policies → allowedModules → ModuleRegistry.tables → req.user.allowedTables
                req.dbTransaction = tx
        │
        ▼
Controller → Service  queries use req.dbTransaction
        │
        ▼
PostgreSQL RLS policies on current_setting('app.*') + SET ROLE
        │
        ▼
res finish → FinishTransactionMiddleware commit (see docs≠code)

architecture-docs/03 excluded-route table matches app.module.ts. JWT payload names: guid, role, email, client_id, organizationId — not a Postgres role. Org isolation is SET ROLE + GUC + (for raw SQL) injected organizationId AND, not “JWT org_id is RLS.”

4. Auth algorithm

5. RLS algorithm (live use(), not the commented block)

The file still contains a large commented old use() that SET ROLE’d every role name and fell back to RESET ROLE for admins with no SYSTEM role. Do not describe the comment as production. Live path from line ~189:

1. If no req.user, next().
2. const transaction = await this.sequelize.transaction() — one connection held until HTTP finish.
3. SUPER_ADMIN: RESET ROLE plus set_config('app.is_SUPER_ADMIN','true', true) (third arg true = transaction-local). organizationId GUC set empty. Full access. Store tx, next.
4. ADMIN: load org; load UserRole whose Role.scope is SYSTEM. Filter those with db_role_name. If any: SET ROLE "firstName" + admin GUCs + app.admin_hierarchy_filter. If roles exist but none have db_role_name, or zero SYSTEM roles: SET ROLE "rls_no_access". Comment in code: previous RESET ROLE leaked.
5. USER: load all non-deleted roles with RolePolicy→Policy. If a db_role_name exists, SET that first one only (“PostgreSQL can only have one active role”). Collect policy.policyRole, allowedModules, load ModuleRegistry.tables → req.user.allowedTables, roleModuleMapping. Else rls_no_access and noDataAccess=true.
6. On error: rollback that tx, next(error).

FACT Identifier interpolation: SET ROLE "${dbRoleName}" is not a bind parameter. Role names must be trusted admin-created identifiers.

FACT Pool in sequelize.module.ts: max 20, min 0, acquire 30000, idle 10000. backend-docs warn: each request holds a tx until finish, so Postgres max_connections must cover concurrency × open transactions. synchronize: true is on — schema auto-sync; speak as a would-do to turn off in prod if they grind Highest Standards.

FACT App-level org AND in QueryDB is defense in depth. RLS is still the row filter. JWT organizationId is not a Postgres policy by itself.

6. Query engine algorithms

Controller: queryDB.controller.ts. Service: queryDB.service.ts. Every data path takes req.dbTransaction.

hasTableAccess

1. Chat tables → true for authenticated.
2. SUPER_ADMIN → all non-system tables.
3. ADMIN → tables from current policy assignment helper.
4. System tables → false for normal users.
5. Else table must appear in getTables() for that user.

executeRawSelectQuery

1. Trim, lower, must startsWith('select') — WITH at the front is rejected here; extractor wraps WITH as SELECT * FROM (… ) q first.
2. Regex (?:from|join)\s+… collect table names (schema stripped, quotes stripped).
3. hasTableAccess each table.
4. If org filter needed, inject WHERE (alias."organizationId" = '…') AND before GROUP/ORDER/LIMIT/HAVING/UNION or append.
5. sequelize.query(modifiedSql, { type: SELECT, transaction: dbTransaction }).

STD CS Prefix check is not a SQL parser. Comments, stacked queries, or functions can still be a later hardening item. Table regex can miss some FROM shapes. Org id is interpolated, dates in execute-query are bound.

7. SQL RAG — Nest proposes nothing; FastAPI proposes; Nest executes

SPA chatbot.ts  fetch POST /queryDB/chat-query  { query, options }
QueryDBService.executeChatQuery
  fetch CHATBOT_API_URL || https://chatbot.projas.in/chatbot
  body { query, options }   ← no JWT forwarded
FastAPI main.py chatbot_function
  SQLQueryAndDetailsExtractor.extract_data(input=query)
    SQLDatabase(engine, include_tables=[10 warehouse tables])
    validate_and_sync_schema(auto_sync=True)
    prompt | ChatOpenAI(gpt-4.1, temperature=0) | PydanticOutputParser(SQLQueryAndDetails)
    If eventQuery starts WITH and not SELECT: wrap SELECT * FROM (sql) q
    Strip trailing semicolon, collapse whitespace
Nest executeRawSelectQuery(eventQuery, user, req.dbTransaction)
Return rows + chatMessage + chartType + xAxis/yAxis/series + showChart + dataPeriod
SPA prepareChartData → ElementRenderer (same as report builder) Chart.js

FACT FastAPI CORS allow_origins=["*"] with credentials True. Nest default CORS also * unless CORS_ORIGIN set.

FACT Prompts in prompts.py: SELECT-only, no INSERT/UPDATE/DELETE/DDL, no trailing semicolon, chartType enum, showChart false ⇒ chartType table, series only for stacked/striped/multiLine/barLine.

FACT On SQL failure Nest still returns the chatbot message with showChart: false and error — user sees a sentence, empty data.

FACT AI Dockerfile: python:3.12-slim, gunicorn 8 UvicornWorker, bind 0.0.0.0:80, timeout 180, healthcheck curl /health. FastAPI itself does not authenticate the caller — Nest is the trust boundary.

Chart-type normalize table (architecture-docs/05, copy): stackedcolumn→stackedColumn, line/multiline→line, pie→pie, donut→hollowPie, unknown→bar.

8. Dashboards — 9 types, fixed SQL, no LLM

architecture-docs/06 registry (copy of the table):

Also GET last-updated and detailed-update-info. Query params: month/months, year/years (comma lists). Filters context: selectedMonths, selectedYears, currencyUnit lakhs|actual, persisted dashboardFiltersState_v2.

Overview algorithm (pattern for the rest)

1. GUEST → 403. SUPER_ADMIN → no app clientFilter.
2. If user.dbRoleNames length > 0, treat RLS as the row filter and skip application clientFilter; else getClientFilter(user).
3. Parse month/year lists; default current month/year.
4. Date predicates: EXTRACT(MONTH FROM col) IN (…) and year IN. Balance-sheet “as of” uses max year/month.
5. Parallel-ish SQL: SUM sales.total_amount; P&L; accounting_transaction; outstanding_receivable / payable aging to month-end; cashflow_bank; bs assets/liabilities. All on the RLS transaction.

Sales extra query params: topCustomerCount, bottomCustomerCount. Growth queries compare target year vs previous year with EXTRACT.

RESUME 30 KPI dashboards, +60% ops, sub-210 ms. FACT nine controller types. Do not invent p95.

9. Report builder — five phases (architecture-docs/04)

1. Data source: Utilitybar → GET queryDB/tables (RLS-visible).
2. Joins + columns: INNER/LEFT/RIGHT/FULL OUTER, GET columns-with-types.
3. Fetch: single table getTableData or POST execute-query (join SQL, LIMIT 1000–5000).
4. Chart building: react-dnd DraggableColumn → ChartDropZone x/y → ElementRenderer → Chart.js (bar/line/pie/area/doughnut).
5. Canvas: Canvas.tsx react-rnd, 15px grid, PowerBICustomizationPanel, save JSON via POST /reports { name, data }. Load GET /reports/:id. Body limit 50mb in Nest for large report JSON. Export: frontend-docs/07 (jspdf / file-saver).

Slicers: SlicerPanel + dataset field values API. Saved report is canvas JSON, not a warehouse materialized view. Guest cannot create reports.

10. Chat CRUD vs warehouse

ChatController: create/list/get/update/delete chat; add/update messages; favorites; feedback. ChatService.executeWithoutRLS: new transaction, RESET ROLE, run callback, commit. Used for chats / messages / favorites / feedback so SET ROLE on warehouse policies does not hide app chat rows.

Warehouse facts stay on req.dbTransaction via chat-query. executeWithoutRLS on sales/bs would skip isolation — that is a leak class, not the implemented path.

architecture-docs/05 says executeWithoutRLS is a “new Sequelize instance”; live code is this.sequelize.transaction() + RESET ROLE on the same pool. Speak the file.

11. Tally ingest

1. POST /file-upload/tally multipart. Multer memoryStorage, max 2 files, 200MB each, .zip name + mime.
2. validateFile: extension, size, mime, ZIP magic bytes PK (0x50 0x4b 03 04 / empty / spanned), AdmZip integrity, not empty.
3. S3 Upload to tally-uploads/${Date.now()}-sanitizedName, URI s3://bucket/key. Region default ap-south-1.
4. Build TallyUploadDto: client_id, uploaded_by, period_in_years, credentials current/previous year, inventory flags. Gateway POST TALLY_GATEWAY_URL (default tallygw.projas.in/instance/create) header x-api-key, timeout 30s.

Zips are not stored as bytea in Postgres. Gateway is a separate service this Nest does not implement.

12. Admin RBAC graph

User ──< UserRole >── Role (scope SYSTEM|ORG, db_role_name)
                          └──< RolePolicy >── Policy (allowedModules[], policyRole)
                                                └── ModuleRegistry (tables[])
User ──< UserFeature >── Feature (route)
User ── belongs → Organization (client_id)
UserHierarchy module: org tree / manager relations (UserHierarchyModule)
OverviewModule: admin stats
FeatureManagement: assign routes; frontend FeatureAccessProvider
  SUPER_ADMIN hasFeatureAccess always true
  others: feature.route must be assigned (including ADMIN)
  fetch cache 30s; refreshFeatures on login custom event

Admin UI routes: /dashboard/admin, roles, policies, users, tables, features, custom-expressions. Policy form has expression-builder + module-tree.

3-tier resume mapping to speak: (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.

13. Frontend map

UI libraries present: Tailwind/Radix, MUI, Antd, Mantine, Redux Toolkit, TanStack Query, DOMPurify, Chart.js + Recharts. Resume bullet is React + Recharts — do not claim you rewrote tiles in all four UI kits as the intern metric.

Frontend Docker: multi-stage node:20-alpine build → nginx:stable-alpine port 80, gzip, SPA try_files, /health 200, X-Frame-Options SAMEORIGIN. frontend-docs claim ~30–40MB image — that number is the latex book, not a resume metric.

14. How it is deployed

Three images

GitHub Actions (this repo — speak this, not the CodeBuild latex)

QA (convbi-qa.yml): push branch qa → ubuntu-latest, environment qa, AWS credentials from GitHub secrets, ECR login, docker build+push :sha and :qa, render task def, deploy ECS, wait-for-service-stability: true. No manual approval.

Prod (convbi-prod.yml): push main, environment production, tags :sha and :latest, then trstringer/manual-approval@v1 timeout 3 minutes, approver Arjun-ylogx, minimum-approvals 1, then ECS deploy + wait-for-service-stability.

Shared env in YAML: AWS_REGION ap-south-1, ECR_REPOSITORY convbi-backend, ECS_SERVICE convbi-backend-service, ECS_CLUSTER convbi-cluster, CONTAINER_NAME convbi-backend.

RESUME 99.9% via CloudFront → ALB → ECS + Docker + CI/CD + Route 53. This backend workflow is the Actions→ECR→ECS slice. CloudFront/ALB/Route 53 are resume edge; they are not defined inside these two YAML files. Do not claim you owned EKS. Do not invent desired count or Fargate vs EC2 unless they open the cluster in AWS.

Local compose

convbi-backend/docker-compose.yml service convbi-backend port 5000:5000, env from Postgres/JWT/SMTP/FRONTEND_* (no secrets in this book).

Health

Terminus: GET /health and /healthcheck ping Postgres. GET /health/simple returns process-up JSON without DB. Use /health for “sick task”; /health/simple can stay green if RDS is down — that is the class.

15. Logging

From LOGGING_IMPLEMENTATION_SUMMARY (matches code): global HttpException filter; LoggingInterceptor (method, URL, user, IP, duration, status, redacts passwords/tokens); CustomLogger sql() + slow >1s; sequelize benchmark + SQL_LOGGING or NODE_ENV=development; auth.service example logs. No Datadog / CloudWatch dashboard in this repo — do not invent one. Speak interceptor + Terminus + ECS wait-for-stability.

16. Security slice (what you actually built)

• Bearer JWT verify on almost every route; bcrypt passwords; org-domain signup.
• RLS SET ROLE fail-closed rls_no_access; transaction-scoped GUCs.
• SQL RAG: SELECT prefix + table allow-list + RLS tx + optional org AND. FastAPI allow-listed tables + SELECT-only prompt + Pydantic.
• Tally: magic bytes + zip integrity, not trust MIME alone.
• ValidationPipe whitelist. Swagger Bearer.
• Would-do if they grind: Secrets Manager; CORS not *; parser not prefix; bind org id; FinishTransaction rollback on 5xx; cookie/HttpOnly if they want XSS story — cookie MD is not this file; turn synchronize off; Redis still resume-only in this Nest package.json.

17. One-request walks

18. Failure classes (no invented tickets)

19. Honest 10×

• I did not shard Postgres. First: pool vs max_connections, indexes on filter columns, stop SELECT *.
• Per-request transactions × 10× QPS: shorter handlers or read-only SET ROLE pooling — I did not build PgBouncer in this repo.
• fieldsCache is one process: 10× tasks = 10× maps. Resume Redis is the shared cache story, not this Map.
• ChatOpenAI on every NL question: cost and latency. Tiles stay SQL. I would not “scale” by putting the model on dashboards.
• ECS: this YAML deploys one service name. I do not invent autoscaling numbers.
• AI 8 gunicorn workers is the Dockerfile, not a capacity study.

20. Spoken cards (one project per answer)

21. Question bank — how / what / where / logic / algo

Resume-derived unless noted. Other candidates’ loops may differ. Not a Job 10454435 list.

Deeper spoken STAR + 80 more rehearsal rows: interview-book/r4/12-convbi-codebase.html and Q1–Q125 in 26-convbi-full-codebase.html.

22. Keep straight

Tighten: one intern per spoken block. If they already heard Ylogx RLS, backup LP story is Horizon 17th (Deliver) or GiftedBooks 3–10 min (Customer) — not a fourth Ylogx trophy. Do not rank internships. Dates: Horizon Feb–Jun 2024, Ylogx Nov 2024–Oct 2025, IQVIA Apr 2026–present.

Built from architecture-docs 01–08, backend-docs (including Docker ch.7), frontend-docs (Docker ch.9, chatbot, report builder), COOKIE/LOGGING markdown, and the live TypeScript/Python/YAML/Dockerfiles in December25. Latex diagrams copied as English tables where they still match; disagreements called out in §0.