5. Databases and ER

B+ (Postgres-style index):

        [ 20 | 50 ]
       /     |     \
  [..20]  [21..50]  [51..]     -- internal: keys + child ptrs, no heap rows
     |        |        |
  leaf ↔ leaf ↔ leaf           -- leaves linked; keys + TIDs

Point: root → leaf, O(log_f n) I/Os.
Range: find first leaf, follow next. That is why (org_id, created_at) is B+, not Hash.

A B-tree and a B+ tree are both multiway trees built for disk pages, so one I/O loads many keys instead of one BST comparison. Height is about log_B n, where B is keys per page; a BST can degrade to O(n) and would issue an I/O per key, which is why databases do not use it as the default index. In a classic B-tree, payloads can sit in internal nodes, so a point lookup might finish before the leaf. In a B+ tree every heap pointer lives in a leaf and internals hold separators only, which raises fanout and usually shortens the tree. Leaves are linked, so ORDER BY, BETWEEN, and “rows for this tenant since yesterday” become a descent plus a leaf walk rather than an inorder hop across random pages. Postgres still prints “B-tree” in EXPLAIN; interviewers still say B-tree; the range-scan story is B+. Hash indexes are equality-only, so they are the wrong default for time-ordered bot logs or camera events. I did not implement a B+ tree at internships; I would still choose that shape for query_log(org_id, created_at) and Argus event(camera_id, ts) because those queries are ranges, not hash probes. Kafka in the same slot is unrelated CS: order is per partition, not a global warehouse log, and I did not operate Kafka at Ylogx.

Example: CREATE INDEX query_log_org_time ON query_log (org_id, created_at DESC) is a B+ leaf scan: find the first leaf for that org_id, then walk newer rows to explain the −35% bot-latency slice. A Hash index could not serve “last hour for org X.” Argus (camera_id, ts) is the same pattern for 20+ cameras without storing JPEGs in the index.

If they probe: “Why not BST?” height O(n) and one I/O per key. “B vs B+ for a PK lookup?” B can stop internally; B+ always goes to the leaf, but internals pack more keys so height often matches, and range plus the leaf linked list is the reason to lead with B+. “Did you code B+?” No.

-- session 1: UPDATE dashboard SET spec='a' WHERE id=1;  then id=2
-- session 2: UPDATE dashboard SET spec='b' WHERE id=2;  then id=1
-- PG: ERROR deadlock detected; retry with ids sorted

A transaction is a bundle of reads and writes that must look atomic to everyone else: all of it commits, or none of it does, and the result still satisfies FKs, CHECKs, and RLS. ACID is the interview word; the resume fact is that Ylogx dashboards and Argus events live in Postgres, not in Redis. Atomicity is the WAL plus rollback; consistency here means constraints still hold after commit; isolation is why two report builders must not each think they won the same dashboard.spec; durability is fsync of WAL, which is not the same number as 99.9% service uptime. A database deadlock is a cycle in the row-lock wait-for graph: session one updates dashboard 1 then waits on 2, session two does the reverse, Postgres aborts one with 40P01, and the app retries with ids sorted. Keep transactions short and never hold a row lock while the SQL-RAG bot calls an LLM. OS deadlock is Coffman’s four conditions on mutexes; Banker’s algorithm is avoidance, not something I shipped. Isolation levels are a different question from tenant isolation: RLS is which org’s rows you may see, not whether you can see a dirty write.

Example: Argus inserts an event and an alert in one BEGIN … COMMIT so a crash cannot leave a PPE detection with no alert row. Ylogx SQL RAG shares the warehouse with the report builder under a non-superuser DSN so a half-written dashboard cannot leak across the 3 organizational tiers.

If they probe: “SELECT FOR UPDATE?” serialize alert ack so two operators do not close the same violation. “Lost update?” two builders overwrite spec — a version column or UPDATE … WHERE version =. “Deadlock vs livelock?” cap retries. “SERIALIZABLE everywhere?” not claimed; Postgres default is read committed.

This slot on that IE was a thin cloud vocabulary list, not a warehouse design interview, so I answer in one breath and sit down. S3 holds objects: PDFs, model weights, camera frames if anyone asked for pixels, not RLS-protected KPI rows. NoSQL in this resume means “I do not need SQL joins for this key,” which is Redis for hot bot answers, not a Mongo system of record for 30 dashboards. Sharding is how you split a primary when one machine cannot hold the tenants; I did not shard Ylogx, and inventing a shard key would be a lie. What shipped is one Postgres with RLS plus Redis as a cache in front of the bot path. REST, Docker, and ECS are how the app ran; CloudFront is the edge; I did not run EC2 by hand and I will not upgrade Kubernetes from the skills list into production ownership. Facts stay in Postgres so joins, transactions, and row policies still work. Redis cut bot database latency by 35 percent because it is a cache, not because it became the warehouse.

Example: a Ylogx dashboard spec jsonb row lives in Postgres under org_id so SQL RAG can join it to query_log under the same RLS GUC. The PDF bytes of an IQVIA 200+ page BRD would be an object, not a Postgres cell. Redis key (org_id, tier, qhash) may hold a hot NL answer; deleting Redis must not delete the dashboard.

If they probe: “When would you shard?” when one primary cannot take tenant growth — I did not. “Mongo for events?” Argus compliance joins stay in Postgres. “Is Redis NoSQL?” yes as a data structure server; still not the source of truth.

                 ┌─────────┐
                 │   org   │
                 └────┬────┘
          ┌───────────┼────────────┐
          │1:N        │1:N         │1:N
     ┌────▼────┐ ┌────▼─────┐ ┌────▼──────┐
     │  role   │ │dashboard │ │ query_log │
     │ tier 1..3│ └────▲─────┘ └────▲──────┘
     └────┬────┘      │            │
          │1:N        │N:1 owner   │N:1
     ┌────▼────┐      │            │
     │  user   ├──────┴────────────┘
     └─────────┘

Redis: cache schema + hot answers keyed by (org_id, tier, qhash). Not a 4th table.
99.9% = ECS/ALB/CloudFront uptime, not WAL / ACID.

CREATE TABLE org (
  org_id uuid PRIMARY KEY
);
CREATE TABLE role (
  role_id uuid PRIMARY KEY,
  org_id  uuid NOT NULL REFERENCES org(org_id),
  tier    smallint NOT NULL CHECK (tier IN (1, 2, 3))
);
CREATE TABLE app_user (
  user_id uuid PRIMARY KEY,
  org_id  uuid NOT NULL REFERENCES org(org_id),
  role_id uuid NOT NULL REFERENCES role(role_id)
  -- no email/name on the whiteboard
);
CREATE TABLE dashboard (
  dashboard_id uuid PRIMARY KEY,
  org_id       uuid NOT NULL REFERENCES org(org_id),
  owner_id     uuid NOT NULL REFERENCES app_user(user_id),
  spec         jsonb NOT NULL DEFAULT '{}'
);
CREATE TABLE query_log (
  id          bigserial PRIMARY KEY,
  org_id      uuid NOT NULL REFERENCES org(org_id),
  user_id     uuid NOT NULL REFERENCES app_user(user_id),
  sql_hash    char(64) NOT NULL,  -- hash, not a warehouse dump
  ms          int NOT NULL,       -- −35% was measured here, not in Redis
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX query_log_org_time ON query_log (org_id, created_at DESC);
CREATE INDEX query_log_user_time ON query_log (user_id, created_at DESC);
CREATE INDEX dashboard_org ON dashboard (org_id);

ALTER TABLE dashboard ENABLE ROW LEVEL SECURITY;
ALTER TABLE dashboard FORCE ROW LEVEL SECURITY;
ALTER TABLE query_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY dashboard_tenant ON dashboard
  USING (org_id = NULLIF(current_setting('app.org_id', true), '')::uuid);
CREATE POLICY query_log_tenant ON query_log
  USING (org_id = NULLIF(current_setting('app.org_id', true), '')::uuid);
-- SET app.org_id / app.tier / SET ROLE after JWT→RBAC, BEFORE SQL RAG.
-- Never BYPASSRLS / superuser DSN for the bot.

Ylogx is a multi-tenant BI warehouse, so the spoken ER is org, role, user, dashboard, and query_log, not a dump of production tables. An org has many roles, users, dashboards, and log rows; each user has one role in this simpler sketch because the resume does not name a bridge table. Primary keys are stable UUIDs; every fact table carries org_id as a foreign key so joins stay tenant-scoped and so RLS has a column to predicate on. Role holds tier in {1,2,3} rather than inventing shipped titles, which keeps the “3 organizational tiers as role” bullet in third normal form instead of copying a tier name onto every dashboard. Dashboards store a jsonb spec owned by a user; query_log stores a SQL hash and a millisecond duration, which is how −35% bot database latency was measured, not a Redis-internal counter. NestJS RBAC decides which route a JWT may hit; Postgres RLS decides which rows even a generated SELECT may see, including when the LLM forgets WHERE org_id. Redis is a cache of schema and hot answers keyed by org, tier, and query hash; it is not a fourth entity and not the source of truth. Ninety-nine point nine percent uptime is ECS, ALB, and CloudFront, not WAL. I did not shard; one primary plus RLS is what I can defend. Sub-210 ms is the API path with the LLM off the KPI GET.

Example: after JWT mapping, the request does SET app.org_id and SET ROLE before SQL RAG. Policy USING (org_id = current_setting('app.org_id')::uuid) on dashboard and query_log means a join SELECT d.spec, avg(q.ms) FROM dashboard d JOIN query_log q USING (org_id) cannot leak another tenant even if the model omits the filter. Redis GET for that answer runs only after that query succeeds, and the cache key includes org and tier so a tier-1 hit cannot serve tier-3 rows.

If they probe: “Why hash SQL?” audit without retaining a prompt of business numbers. “Why Redis if RLS?” populate cache after a successful RLS query. “Sharding?” did not. “Many-to-many user-role?” resume does not specify; one role per user is the board sketch.

camera 1────────N event 1────────0..1 alert

-- 20+ cameras. Insert on detection/heartbeat, NOT per frame.
-- 20 × 24 FPS ≈ 480 frames/s would drown Postgres. Log events, not JPEGs.

CREATE TABLE camera (
  camera_id  uuid PRIMARY KEY,
  label      text NOT NULL,          -- CAM-07, not an IP
  fps_target int NOT NULL CHECK (fps_target > 0)  -- 24 on resume
);
CREATE TABLE event (
  event_id  bigserial PRIMARY KEY,
  camera_id uuid NOT NULL REFERENCES camera(camera_id),
  ts        timestamptz NOT NULL,
  kind      text NOT NULL CHECK (kind IN ('ppe', 'attendance', 'heartbeat')),
  cls       text,
  conf      real CHECK (conf IS NULL OR (conf >= 0 AND conf <= 1)),
  x int, y int, w int, h int         -- bbox, not the JPEG
);
CREATE INDEX event_cam_time ON event (camera_id, ts DESC);
CREATE TABLE alert (
  alert_id  bigserial PRIMARY KEY,
  event_id  bigint NOT NULL UNIQUE REFERENCES event(event_id),
  status    text NOT NULL CHECK (status IN ('open', 'acked', 'closed')),
  opened_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX alert_open ON alert (event_id) WHERE status = 'open';

BEGIN;
INSERT INTO event (camera_id, ts, kind) VALUES ($1, $2, 'ppe') RETURNING event_id;
INSERT INTO alert (event_id) VALUES ($id);
COMMIT;  -- retry on 40P01

Argus is a PPE and attendance detector on more than twenty camera feeds, so the ER is camera, event, and alert, not a video warehouse. Each camera has a stable id, a label that is not an IP, and a target of 24 FPS on the resume. An event is one detection or heartbeat, with kind in ppe, attendance, or heartbeat, optional class and confidence, and a bounding box of integers rather than a JPEG. Twenty cameras at 24 FPS is about 480 frames per second; inserting a row per frame would drown Postgres, so we log events, not pixels. An alert is zero-or-one per event that crossed a threshold, with status open, acked, or closed, and a unique event_id so the same detection cannot open two tickets. The insert of event plus alert is one transaction so a crash cannot leave a detection without an alert. The B+ index is (camera_id, ts) for “what happened on CAM-07 just now,” plus a partial index on open alerts. Postgres is the compliance log that supports joins; object storage is where frames would go if they ask. I will not invent README routes that 404, and I will not put worker names or RTSP passwords on the board. YOLOv9 moved from 73 percent to 89 percent mAP on 15,000+ images; those numbers are model quality, not a table name.

Example: BEGIN; INSERT INTO event (camera_id, ts, kind, cls, conf) VALUES ($cam, now(), 'ppe', 'no_helmet', 0.91) RETURNING event_id; INSERT INTO alert (event_id, status) VALUES ($id, 'open'); COMMIT; Then SELECT a.status, e.ts, e.cls FROM alert a JOIN event e ON e.event_id = a.event_id JOIN camera c ON c.camera_id = e.camera_id WHERE c.label = 'CAM-07' AND a.status = 'open' is the floor-staff join. Retry on 40P01.

If they probe: “Partition by day?” possible; not on resume. “Mongo for events?” structured compliance joins stay in Postgres. “Double insert at 24 FPS?” unique (camera_id, ts, kind) for heartbeats, not a unique on every bbox. “RLS like Ylogx?” skip unless they ask; this was logging, not a public multi-tenant stream.

document 1────N chunk
run      1────N span
span     N────M chunk          -- retrieval set
         \──── GraphDB         -- requirement → section → depends_on
Azure AI Search: lexical + vector. FAISS is skills / GiftedBooks-scale, not this primary.

CREATE TABLE document (
  doc_id     uuid PRIMARY KEY,
  title      text NOT NULL,          -- filename / BRD id, not a person
  page_count int NOT NULL CHECK (page_count > 0),
  source     text NOT NULL CHECK (source IN ('brd', 'pdf'))
);
CREATE TABLE chunk (
  chunk_id    uuid PRIMARY KEY,
  doc_id      uuid NOT NULL REFERENCES document(doc_id) ON DELETE CASCADE,
  section     text,                  -- heading, not a prose dump
  ordinal     int NOT NULL,
  azure_key   text,                  -- id in Azure AI Search
  UNIQUE (doc_id, ordinal)
);
CREATE TABLE run (
  run_id    uuid PRIMARY KEY,
  started_at timestamptz NOT NULL DEFAULT now(),
  eval_pass boolean                  -- LangSmith-shaped stop
);
CREATE TABLE span (
  span_id uuid PRIMARY KEY,
  run_id  uuid NOT NULL REFERENCES run(run_id) ON DELETE CASCADE,
  node    text NOT NULL,             -- retrieve | generate | eval
  note    text                       -- error class, not document text
);
CREATE TABLE span_chunk (
  span_id  uuid NOT NULL REFERENCES span(span_id) ON DELETE CASCADE,
  chunk_id uuid NOT NULL REFERENCES chunk(chunk_id),
  rank     int NOT NULL,
  PRIMARY KEY (span_id, chunk_id)
);

IQVIA work is Hybrid RAG over long BRDs, so the ER is document, chunk, run, span, and a retrieval bridge, with no patient, email, or MRN entity on the board. A document is a filename and page count, sourced as brd or pdf, including 200+ page BRDs; Deep Research’s 200+ websites is a crawl-rank bullet, not this table. Chunks belong to a document with an ordinal and an Azure Search key; the unique pair (doc_id, ordinal) keeps section order without stuffing prose into Postgres. A run is one traced execution; spans hang off the run as retrieve, generate, or eval nodes; span_chunk records which chunks that retrieve step actually used. GraphDB, named on the resume, holds requirement-to-section hops that cosine similarity will miss; Neo4j is on the skills list, and I will not claim the intern GraphDB was Neo4j. Vectors live in Azure AI Search hybrid plus semantic ranking, not an invented pgvector column and not FAISS as the IQVIA primary. FAISS is skills and GiftedBooks-scale; GiftedBooks sub-300 ms and 99.5 percent uptime is a different product, and its README currently describing AegisAI is a mismatch I will not mix into this sketch. Traces store error class, not full BRD text. This schema has no PII columns on purpose.

Example: SELECT c.section, sc.rank FROM span s JOIN span_chunk sc USING (span_id) JOIN chunk c ON c.chunk_id = sc.chunk_id JOIN document d ON d.doc_id = c.doc_id WHERE s.run_id = $run AND s.node = 'retrieve' ORDER BY sc.rank reconstructs what the Hybrid RAG step saw without dumping the PDF. Azure Search holds the lexical-plus-vector index keyed by azure_key; GraphDB answers “which section does requirement R12 depend on?” as a hop, not a cosine neighbor.

If they probe: “Where is vector(1536)?” Azure Search, not pgvector. “Why GraphDB?” hops are not cosine. “PII in chunks?” BRDs may still contain names — redaction is process; the sketch has no patient entity. “Was it Neo4j?” skills vs bullet — do not claim it.

// Study models for sketch 1. Not a claim this schema.prisma shipped.

model Org {
  id         String      @id @default(uuid())
  users      User[]
  roles      Role[]
  dashboards Dashboard[]
  logs       QueryLog[]
}
model Role {
  id     String @id @default(uuid())
  orgId  String
  tier   Int    // sketch 1..3 — do not invent titles
  org    Org    @relation(fields: [orgId], references: [id])
  users  User[]
  @@index([orgId])
}
model User {
  id     String @id @default(uuid())
  orgId  String
  roleId String
  org    Org    @relation(fields: [orgId], references: [id])
  role   Role   @relation(fields: [roleId], references: [id])
  logs   QueryLog[]
  @@index([orgId])
}
model Dashboard {
  id    String @id @default(uuid())
  orgId String
  org   Org    @relation(fields: [orgId], references: [id])
  @@index([orgId])
}
model QueryLog {
  id        String   @id @default(uuid())
  orgId     String
  userId    String
  sqlHash   String
  ms        Int
  createdAt DateTime @default(now())
  org       Org      @relation(fields: [orgId], references: [id])
  user      User     @relation(fields: [userId], references: [id])
  @@index([orgId, createdAt(sort: Desc)])
  @@index([userId, createdAt])
}

Prisma is a TypeScript schema that emits migrations and a typed client, which fits NestJS CRUD, and it is on the skills list. Ylogx bullets name PostgreSQL, not Prisma, so I will not claim Prisma shipped the intern warehouse. Even if a sketch uses Prisma models for org, role, user, dashboard, and query_log, findMany({ where: { orgId } }) is still an application filter: miss one query and you leak a tenant. Prisma does not emit row-level security policies; those stay as SQL. SQL RAG must run parameterized SQL as the request’s database role after SET LOCAL app.org_id, not as a superuser datasource. Live Code is Java, so Hibernate is the same idea if they ask, and neither ORM will appear in a DSA editor. Use include for the 30 KPI dashboards instead of looping findUnique, which is the N+1 trap. Raw SQL remains for timeout, catalog deny, and the RLS GUC. The sketch models are language-agnostic study ER, not a claim that schema.prisma was committed at Ylogx.

Example: a Prisma Dashboard model with @@index([orgId]) is fine for NestJS CRUD. The bot path still does $executeRaw`SET LOCAL app.org_id = ${orgId}` then a parameterized SELECT. If one admin report uses prisma.dashboard.findMany() with no where, RLS in Postgres is what still hides other orgs — the ORM will not save you if the DSN bypasses RLS.

If they probe: “Prisma vs Hibernate?” same typed-client idea; Live Code will not need either. “Does Prisma generate RLS?” No. “Did Ylogx use Prisma?” bullets say PostgreSQL; do not claim it.

// Live Code Java shape — cache-aside. TTL number is NOT a Ylogx claim.
String get(String key, Supplier<String> dbLoad, int ttlSec) {
  String v = redis.get(key);
  if (v != null) return v;
  v = dbLoad.get();                 // Postgres as RLS role
  redis.setex(key, ttlSec, v);
  return v;
}
// write: db.update(...); redis.del(key);
// key MUST contain orgId + tier

Pick the store from the access pattern, not from a blog ranking. Postgres is the system of record for Ylogx facts and Argus event/alert logs because I need joins, foreign keys, transactions, and row-level security; 99.9 percent uptime is the service path around that primary, not a reason to move KPI rows into Redis. Mongo is on the skills list and is the wrong warehouse for SQL RAG, tenant joins, and RLS. Redis cut bot database latency by 35 percent as cache-aside: GET the key, on miss load Postgres as the RLS role, then SET; on write, update Postgres first and delete the key. A Redis key without org and tier would scale a leak at the same 35 percent. FAISS is a vector index for GiftedBooks-style PDF RAG; IQVIA’s primary retrieval is Azure hybrid search, and neither FAISS nor a vector neighbor is an authorization or money total. GraphDB on the IQVIA bullet holds BRD relationships; Neo4j is skills, and I will not stamp it on Ylogx’s three-tier org tree or the 30 KPI dashboards. MySQL, Firebase, and Supabase are skills only; Firebase is not an RLS warehouse, and Supabase’s hosted-Postgres-plus-RLS idea is not what shipped — self-managed Postgres on ECS is. GiftedBooks sub-300 ms is embed-once top-k, not stuffing a PDF, and the README currently describing AegisAI is not this product. I did not shard. Cache-aside is the CS name; I will not invent a TTL, stampede lock, or Redis Cluster that is not on the resume. Frugality is Redis plus CloudFront before a larger RDS.

Example: bot asks “what was last week’s fill rate for my org?” Cache key orgA:tier2:sha256(sql). Miss → SET app.org_id → SELECT avg(ms) FROM query_log WHERE created_at > now() - interval '7 days' under RLS → SET Redis. A Mongo document per dashboard could not join that average to RBAC tiers. CAP in one sentence: Postgres primary leans CP for the tenant row; the Redis copy can be stale; source of truth stays Postgres.

If they probe: “Postgres vs MySQL?” RLS is why I lead Postgres for Ylogx. “FAISS vs pgvector?” skills say FAISS; IQVIA says Azure; I will not invent pgvector in prod. “CAP?” Postgres row is CP; Redis/CloudFront are AP-ish for hot reads. Don’t lecture Dynamo unless they name it.

An entity is a thing we store as a table: org, camera, document. A relationship is a verb: a user owns a dashboard, a camera emits an event, a document contains chunks. An attribute is a column. Cardinality decides where the foreign key goes: one-to-many puts the FK on the many side; many-to-many needs a bridge table with a composite primary key, which is what span_chunk is. One-to-one is rare; Argus alert-to-event is the honest 1:1 via unique event_id. A weak entity cannot be identified without its owner: an event without camera_id is meaningless. A primary key is unique, not null, and stable, so we use a surrogate uuid or serial and never email, which changes and is PII. A foreign key references a primary or unique key so the join cannot dangle. Restrict deleting an org that still has users; cascade delete chunks when their document goes. A unique pair like (org_id, slug) is per-tenant uniqueness, not a global slug. Putting org_id on every Ylogx fact table is both the FK for joins and the RLS predicate, which is why we do not denormalize it away.

Example: query_log.user_id REFERENCES app_user(user_id) and query_log.org_id REFERENCES org(org_id) let SELECT u.user_id, avg(q.ms) FROM app_user u JOIN query_log q ON q.user_id = u.user_id AND q.org_id = u.org_id GROUP BY u.user_id stay inside one tenant. IQVIA span_chunk(span_id, chunk_id) is the M:N bridge. Argus alert.event_id UNIQUE REFERENCES event is 1:1.

If they probe: “Why UUID?” unguessable org ids on a public JWT mapping. “Why serial?” smaller B+ leaves for high-ingest Argus events. Ylogx was one primary — either is fine on the board. “Identifying vs non-identifying?” event without camera is identifying.

First normal form means atomic cells: one value per column, no comma-separated lists pretending to be a set. A dashboard that stores kpi_ids = 'a,b,c' fails 1NF and should become a dashboard_kpi bridge; a jsonb object of chart settings on the dashboard row is still one typed column, while a json list of users is the smell. Argus stores one event row per detection, not a string of twenty camera ids. Second normal form is 1NF plus no partial dependency on a composite key: if query_log were keyed by (user_id, created_at) and also stored org_name that depends only on the user, that name belongs on org or user, not on every log row. Third normal form forbids transitive dependencies: user.org_name when an org table already exists fails; the resume’s “three tiers as role” puts tier on role, not copied onto every dashboard. We still denormalize on purpose for speed: Redis holds a copy that bought −35 percent bot latency, query_log.ms is a snapshot of duration, and a KPI snapshot keeps 30 Recharts charts from running eight joins. The source of truth stays in 3NF. Never denormalize org_id away, because RLS predicates on it. BCNF is a one-sentence follow-up if they push: every determinant is a candidate key; I will not rewrite the intern schema on the board.

Example: bad 1NF: event.cameras_csv = 'CAM-01,CAM-07'. Good: twenty camera rows and events that each reference one camera_id. Bad 3NF: dashboard.tier_name = 'executive' copied from role. Good: role.tier SMALLINT CHECK (tier IN (1,2,3)) and join when the UI needs a label — labels are not invented on the resume. Redis key (org_id, tier, qhash) is a denormalized copy of a 3NF answer, not a fourth warehouse.

If they probe: “Is jsonb a 1NF violation?” a single typed document column is acceptable; a json array of other entities is the list smell. “Why keep org_id everywhere?” RLS and joins, not because 3NF forbids it. “BCNF vs 3NF?” BCNF is stricter on determinants; 3NF is the board default.

An index is a B+ tree from key to tuple id, so the engine can find rows without a sequential scan. Composite indexes use a leftmost prefix: (org_id, created_at) serves org_id alone and the pair, not created_at alone. I would run EXPLAIN ANALYZE before adding a second index, because every extra tree slows writes. On Ylogx I would defend primary keys, foreign-key org_id, and query_log(org_id, created_at DESC) because that slice is how −35 percent bot latency was investigated; reports 40 percent faster is the report path, not a claim that we added twenty indexes. On Argus, (camera_id, ts) and a partial index on open alerts are enough; do not B-tree bounding-box json, and do not pay ingest cost of extra trees on more than twenty streams. On IQVIA, unique (doc_id, ordinal) and run_id on span are relational; the vector index lives in Azure Search or, at GiftedBooks scale, FAISS — not a Postgres B+ on a text column. Refuse an index on every json key, boolean-only indexes with no selectivity, and one row per Argus frame. Hash indexes are not the default for time ranges. A standalone index on tier is three values and will not be selective.

Example: EXPLAIN ANALYZE SELECT avg(ms) FROM query_log WHERE org_id = $1 AND created_at > now() - interval '1 day' should show an index range scan on query_log_org_time, not a seq scan of every tenant. Argus CREATE INDEX alert_open ON alert (event_id) WHERE status = 'open' keeps the floor-staff query small. Do not CREATE INDEX ON event ((bbox::jsonb)).

If they probe: “Index on tier?” three values — poor selectivity; second column after org_id or a GUC, not standalone. “GIN?” arrays and full-text, not the KPI path. “Covering INCLUDE?” only if a query is proven hot. “Why not index every FK?” write amplification at 24 FPS ingest.

Transaction isolation answers whether concurrent sessions can see dirty, non-repeatable, or phantom rows. Read uncommitted allows dirty reads in the textbook; Postgres will still not return uncommitted data even at that name. Read committed, the Postgres default, never returns a dirty row but a second SELECT in the same transaction can see a committed change, and a new row can appear as a phantom. Repeatable read freezes a snapshot of committed data so non-repeatable reads go away; Postgres snapshot isolation already blocks many phantoms, and I will say textbook versus Postgres if they care. Serializable in Postgres is SSI and may throw 40001, which the app retries. Most Ylogx reads are fine at read committed; keep the transaction to SET RLS GUC, SELECT, and INSERT log on one connection. Tenant isolation is a different word: RLS plus RBAC for three organizational tiers, not an isolation level. WAL durability is not 99.9 percent uptime. Redis persistence is optional; losing Redis makes the bot slower by giving back the 35 percent, and it must not change which rows RLS returns. I did not claim SERIALIZABLE everywhere, which would contend on thirty dashboards.

Example: two builders load dashboard 12, both UPDATE spec, last write wins — that is a lost update under read committed, fixed with a version column. SQL RAG: one transaction, same role, SET LOCAL app.org_id, a row cap, and a timeout, so a concurrent report builder’s uncommitted INSERT is invisible. Redis miss after a crash reloads those committed rows; it must not serve a cached answer from another tier.

If they probe: “Serializable everywhere?” write contention on 30 dashboards; not claimed. “Read committed for SQL RAG?” yes for reads; one tx, same role, row cap, timeout. “Phantom vs non-repeatable?” non-repeatable is the same row changed; phantom is a new row matching the predicate. “Is RLS isolation?” no — tenant isolation, not a level.