<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>PA Review Audit Architecture</title> <style> :root{ --bg:#f5f7fb; --card:#ffffff; --ink:#1b2433; --muted:#5b6678; --line:#d5dce7; --brand1:#19376d; --brand2:#2779a7; --ok:#0f9d8a; --ours:#2779a7; --gap:#d64545; --cand:#e0a100; --removed:#7a8796; --box:#eef3fa; --box2:#e8f6f3; --box3:#fdf3e1; --box4:#f3eefa; --code:#f0f3f8; } @media (prefers-color-scheme: dark){ :root:not([data-theme="light"]){ --bg:#0f1622; --card:#172131; --ink:#e4e9f2; --muted:#9aa6b8; --line:#2c3a50; --box:#1c2b42; --box2:#16332f; --box3:#3a2f17; --box4:#2a2340; --code:#111a28; } } :root[data-theme="dark"]{ --bg:#0f1622; --card:#172131; --ink:#e4e9f2; --muted:#9aa6b8; --line:#2c3a50; --box:#1c2b42; --box2:#16332f; --box3:#3a2f17; --box4:#2a2340; --code:#111a28; } *{box-sizing:border-box} body{margin:0;background:var(--bg);color:var(--ink);font:15px/1.55 "Segoe UI",system-ui,-apple-system,Roboto,Arial,sans-serif} header{background:linear-gradient(120deg,var(--brand1),var(--brand2));color:#fff;padding:36px 16px 30px} header .wrap{max-width:1100px;margin:0 auto} header h1{margin:0 0 6px;font-size:28px;letter-spacing:.2px} header p{margin:0;opacity:.9} .pill{display:inline-block;background:rgba(255,255,255,.18);border-radius:99px;padding:3px 12px;font-size:12px;margin:12px 6px 0 0} main{max-width:1100px;margin:0 auto;padding:20px 16px 60px} nav.toc{display:flex;flex-wrap:wrap;gap:8px;margin:-34px 0 22px} nav.toc a{background:var(--card);color:var(--ink);text-decoration:none;border:1px solid var(--line);border-radius:8px;padding:6px 12px;font-size:13px;box-shadow:0 2px 6px rgba(0,0,0,.06)} nav.toc a:hover{border-color:var(--brand2)} section{background:var(--card);border:1px solid var(--line);border-radius:14px;padding:22px 22px 18px;margin:0 0 22px} h2{margin:0 0 4px;font-size:20px;color:var(--brand2)} h2 .n{display:inline-block;width:28px;height:28px;border-radius:50%;background:var(--brand2);color:#fff;text-align:center;line-height:28px;font-size:14px;margin-right:8px} h3{font-size:16px;margin:18px 0 6px} .sub{color:var(--muted);margin:0 0 14px} .grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(170px,1fr));gap:12px;margin:12px 0} .kpi{border:1px solid var(--line);border-radius:12px;padding:12px 14px;background:var(--box)} .kpi b{display:block;font-size:24px} .kpi span{color:var(--muted);font-size:13px} .kpi.ok{border-left:5px solid var(--ok)} .kpi.gap{border-left:5px solid var(--gap)} .kpi.ours{border-left:5px solid var(--ours)} .kpi.cand{border-left:5px solid var(--cand)} .diagram{overflow-x:auto;border:1px solid var(--line);border-radius:12px;background:var(--bg);padding:10px;margin:10px 0} .diagram svg{display:block;min-width:820px;width:100%;height:auto} table{width:100%;border-collapse:collapse;margin:8px 0;font-size:14px} th,td{border-bottom:1px solid var(--line);padding:8px 10px;text-align:left;vertical-align:top} th{background:var(--box);font-weight:600} .tw{overflow-x:auto} code,pre{font-family:Consolas,"SF Mono",Menlo,monospace;font-size:13px} code{background:var(--code);padding:1px 5px;border-radius:4px} pre{background:var(--code);border:1px solid var(--line);border-radius:10px;padding:12px 14px;overflow-x:auto;margin:8px 0} .tag{display:inline-block;font-size:12px;border-radius:99px;padding:1px 9px;font-weight:600} .t-done{background:rgba(15,157,138,.15);color:var(--ok)} .t-next{background:rgba(224,161,0,.18);color:#a87700} .t-later{background:rgba(122,135,150,.18);color:var(--removed)} .cols{display:grid;grid-template-columns:1fr 1fr;gap:16px} @media (max-width:760px){.cols{grid-template-columns:1fr} header h1{font-size:22px}} .tree{white-space:pre;line-height:1.5} .note{border-left:4px solid var(--cand);background:var(--box3);padding:10px 14px;border-radius:8px;margin:10px 0} .good{border-left:4px solid var(--ok);background:var(--box2);padding:10px 14px;border-radius:8px;margin:10px 0}

/* SVG styling uses the same tokens so dark mode works */ svg text{fill:var(--ink);font-family:"Segoe UI",system-ui,Arial,sans-serif} svg .muted{fill:var(--muted)} svg .t-s{font-size:12px} svg .t-m{font-size:14px} svg .t-l{font-size:16px;font-weight:700} svg .zone{fill:var(--card);stroke:var(--line);stroke-width:1.5} svg .zone-win{fill:var(--box4);stroke:#8a72c9;stroke-width:1.5} svg .zone-lnx{fill:var(--box);stroke:var(--brand2);stroke-width:1.5} svg .zone-sf{fill:var(--box2);stroke:var(--ok);stroke-width:1.5} svg .node{fill:var(--card);stroke:var(--line);stroke-width:1.2} svg .node-b{fill:var(--card);stroke:var(--brand2);stroke-width:1.6} svg .node-g{fill:var(--card);stroke:var(--ok);stroke-width:1.6} svg .node-y{fill:var(--card);stroke:var(--cand);stroke-width:1.6} svg .node-r{fill:var(--card);stroke:var(--gap);stroke-width:1.6} svg .arrow{stroke:var(--brand2);stroke-width:2;fill:none} svg .arrow-g{stroke:var(--ok);stroke-width:2;fill:none} svg .arrow-d{stroke:var(--muted);stroke-width:1.6;fill:none;stroke-dasharray:6 5} svg .tunnel{fill:none;stroke:#8a72c9;stroke-width:10;stroke-opacity:.18} svg .life{stroke:var(--line);stroke-width:1.5;stroke-dasharray:4 4} svg .badge{fill:var(--brand2)} svg .badge-t{fill:#fff;font-size:12px;font-weight:700} svg .head{fill:var(--brand1)} svg .head-t{fill:#fff;font-size:13px;font-weight:700} svg .ah{fill:var(--brand2)} svg .ahg{fill:var(--ok)} svg .ahd{fill:var(--muted)} details.tbl{border:1px solid var(--line);border-radius:10px;margin:8px 0;background:var(--card)} details.tbl summary{cursor:pointer;padding:10px 14px;list-style:none;display:flex;flex-wrap:wrap;gap:8px;align-items:center} details.tbl summary::-webkit-details-marker{display:none} details.tbl summary::before{content:"▸";color:var(--brand2);font-weight:700} details.tbl[open] summary::before{content:"▾"} details.tbl .step{background:var(--brand2);color:#fff;border-radius:6px;padding:1px 8px;font-size:12px;font-weight:700} details.tbl .res{color:var(--muted);font-size:13px;margin-left:auto} details.tbl .tbl-body{padding:0 14px 12px} @media print{details.tbl{break-inside:avoid} details.tbl:not([open]) .tbl-body{display:block}} </style>

</head> <body> <header><div class="wrap"> <h1>PA Review Audit: what we built and how it connects</h1> <p>Virtual Auditor · use case "reviewed vs not reviewed" · Privileged Access (PA) · Snowflake DEV (DEVVPS)</p> <span class="pill">AIDEA-UI · Angular 20</span><span class="pill">aidea-backend · FastAPI / Python 3.12</span><span class="pill">Snowflake · IAMARW.AIDEA</span><span class="pill">27 Oct 2026</span> </div></header><main> <nav class="toc"> <a href="#s1">1 · Summary</a><a href="#s2">2 · Architecture</a><a href="#s3">3 · Data pipeline</a><a href="#s3b">3b · Tables &amp; queries</a><a href="#s4">4 · Request flow</a> <a href="#s5">5 · Local setup</a><a href="#s6">6 · Code map</a><a href="#s7">7 · API</a><a href="#s8">8 · Security</a><a href="#s9">9 · Status &amp; next</a> </nav><!-- 1 --> <section id="s1"> <h2><span class="n">1</span>What we have done</h2> <p class="sub">A working, end-to-end PA review audit: Snowflake results tables → read-only API → Angular screen, running on the Linux dev host and viewed in the Windows browser.</p> <div class="grid"> <div class="kpi ours"><b>69,859</b><span>PA assignments in ESOW (base)</span></div> <div class="kpi ok"><b>23,601</b><span>eligible for review (our rules)</span></div> <div class="kpi ours"><b>24,863</b><span>eligible per metric AC02-R3-01</span></div> <div class="kpi gap"><b>1,263</b><span>Tier 0: the <u>only</u> difference (23,601 + 1,263 = 24,864)</span></div> <div class="kpi cand"><b>3 hops</b><span>ESOW → IDNDATA → IDNREVIEW traced</span></div> </div> <div class="tw"><table> <tr><th>Layer</th><th>What exists</th><th>Where</th></tr> <tr><td><b>Data</b> (Snowflake)</td><td>Precomputed result tables: base, eligibility waterfall, reconciliation with the firm's metric, IDNDATA hop, IDNREVIEW hop, findings with their SQL, run log, one-row summary view. Granted read to the app role.</td><td><code>IAMARW.AIDEA.VA_*</code> (DEV)</td></tr> <tr><td><b>Backend</b> (aidea-backend)</td><td>Nine read-only GET endpoints under <code>/va/pa</code>, using the existing pooled Snowflake connection, 60-second timeout, masking, tests.</td><td>branch <code>feature/pa-review-audit</code></td></tr> <tr><td><b>Frontend</b> (AIDEA-UI)</td><td>"PA Review Audit" page with five tabs, Highcharts + AG Grid, "&lt;/&gt; Query" button on every number, dark mode, snapshot / live switch.</td><td>route <code>/#/ops/paReviewAudit</code></td></tr> <tr><td><b>Connection</b></td><td>Browser (Windows) → SSH tunnel → Angular :4200 and FastAPI :8000 on the Linux host → Snowflake. <b>Live calls confirmed.</b></td><td>local dev</td></tr> </table></div> <div class="good"><b>Design principle (matches Ola's guidance, 26 Oct):</b> the heavy audit SQL runs once and stores its results in <code>VA_*</code> tables. The UI only reads those small tables, so five people clicking at the same time never re-run the audit.</div> </section><!-- 2 --> <section id="s2"> <h2><span class="n">2</span>Architecture: the big picture</h2> <p class="sub">Three places: the Windows laptop (only a browser), the Linux dev host (both apps run here), and Snowflake (data).</p> <div class="diagram"> <svg viewBox="0 0 1000 460" role="img" aria-label="Architecture diagram"> <defs> <marker id="a1" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path d="M0,0 L10,5 L0,10 z" class="ah"/></marker> <marker id="a1g" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path d="M0,0 L10,5 L0,10 z" class="ahg"/></marker> <marker id="a1d" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path d="M0,0 L10,5 L0,10 z" class="ahd"/></marker> </defs> <!-- Windows --> <rect x="15" y="20" width="215" height="420" rx="14" class="zone-win"/> <text x="122" y="46" text-anchor="middle" class="t-l">Windows laptop</text> <text x="122" y="64" text-anchor="middle" class="t-s muted">just a browser</text> <rect x="32" y="90" width="181" height="150" rx="10" class="node"/> <text x="122" y="114" text-anchor="middle" class="t-m" font-weight="700">Chrome / Edge</text> <text x="122" y="136" text-anchor="middle" class="t-s">localhost.ms.com:4200</text> <text x="122" y="152" text-anchor="middle" class="t-s">/#/ops/paReviewAudit</text> <text x="122" y="180" text-anchor="middle" class="t-s muted">runs the Angular app</text> <text x="122" y="196" text-anchor="middle" class="t-s muted">(JavaScript)</text> <text x="122" y="222" text-anchor="middle" class="t-s muted">calls localhost:8000</text> <rect x="32" y="300" width="181" height="100" rx="10" class="node"/> <text x="122" y="326" text-anchor="middle" class="t-m" font-weight="700">SSH tunnel</text> <text x="122" y="348" text-anchor="middle" class="t-s">-L 4200:localhost:4200</text> <text x="122" y="366" text-anchor="middle" class="t-s">-L 8000:localhost:8000</text> <text x="122" y="386" text-anchor="middle" class="t-s muted">(or VS Code Ports tab)</text> <!-- tunnel band --> <path d="M213,150 C 260,150 270,150 320,150" class="tunnel"/> <path d="M213,215 C 260,215 270,320 320,320" class="tunnel"/> <!-- Linux --> <rect x="300" y="20" width="400" height="420" rx="14" class="zone-lnx"/> <text x="500" y="46" text-anchor="middle" class="t-l">Linux dev host</text> <text x="500" y="64" text-anchor="middle" class="t-s muted">module load node 20.19+ / python 3.12 · tmux</text> <rect x="325" y="90" width="350" height="120" rx="10" class="node-b"/> <text x="345" y="116" class="t-m" font-weight="700">AIDEA-UI · Angular dev server</text> <text x="345" y="136" class="t-s">npm start → ng serve · port 4200</text> <text x="345" y="156" class="t-s">src/app/useCase/paReviewAudit/</text> <text x="345" y="176" class="t-s muted">serves HTML/JS + snapshot JSON (assets)</text> <text x="345" y="196" class="t-s muted">environment LOCAL → API_URL = http://localhost:8000</text> <rect x="325" y="260" width="350" height="150" rx="10" class="node-b"/> <text x="345" y="286" class="t-m" font-weight="700">aidea-backend · FastAPI (uvicorn)</text> <text x="345" y="306" class="t-s">port 8000 · SYS_ENVIRONMENT=dev</text> <text x="345" y="326" class="t-s">auth_middleware (localhost = local_dev)</text> <text x="345" y="346" class="t-s">routers/paReviewAuditRouter.py → /va/pa/*</text> <text x="345" y="366" class="t-s">services/pa_review_audit/ (fixed SQL)</text> <text x="345" y="386" class="t-s muted">connection_pool.get_connection()</text> <!-- arrows browser to linux --> <line x1="213" y1="150" x2="322" y2="150" class="arrow" marker-end="url(#a1)"/> <circle cx="268" cy="132" r="11" class="badge"/><text x="268" y="136" text-anchor="middle" class="badge-t">1</text> <line x1="213" y1="215" x2="322" y2="318" class="arrow" marker-end="url(#a1)"/> <circle cx="262" cy="250" r="11" class="badge"/><text x="262" y="254" text-anchor="middle" class="badge-t">2</text> <!-- Snowflake --> <rect x="760" y="20" width="225" height="420" rx="14" class="zone-sf"/> <text x="872" y="46" text-anchor="middle" class="t-l">Snowflake DEV</text> <text x="872" y="64" text-anchor="middle" class="t-s muted">DEVVPS</text> <rect x="775" y="90" width="195" height="150" rx="10" class="node-g"/> <text x="872" y="114" text-anchor="middle" class="t-m" font-weight="700">IAMARW.AIDEA</text> <text x="872" y="134" text-anchor="middle" class="t-s">VA_PA_SUMMARY_V</text> <text x="872" y="151" text-anchor="middle" class="t-s">VA_PA_WATERFALL_SUMMARY</text> <text x="872" y="168" text-anchor="middle" class="t-s">VA_PA_RECON</text> <text x="872" y="185" text-anchor="middle" class="t-s" style="font-size:11px">VA_PA_HOP_IDNDATA/_IDNREVIEW</text> <text x="872" y="202" text-anchor="middle" class="t-s">VA_FINDINGS · VA_RUN_LOG</text> <text x="872" y="224" text-anchor="middle" class="t-s muted">precomputed results</text> <rect x="775" y="290" width="195" height="130" rx="10" class="node"/> <text x="872" y="314" text-anchor="middle" class="t-m" font-weight="700">Source schemas</text> <text x="872" y="334" text-anchor="middle" class="t-s">ESOW · METRIC</text> <text x="872" y="351" text-anchor="middle" class="t-s">IDNDATA · IDNREVIEW</text> <text x="872" y="378" text-anchor="middle" class="t-s muted">read-only</text> <text x="872" y="396" text-anchor="middle" class="t-s muted">never touched by the UI</text> <line x1="675" y1="320" x2="772" y2="190" class="arrow-g" marker-end="url(#a1g)"/> <circle cx="722" cy="244" r="11" class="badge"/><text x="722" y="248" text-anchor="middle" class="badge-t">3</text> <line x1="872" y1="290" x2="872" y2="243" class="arrow-d" marker-end="url(#a1d)"/> <text x="880" y="272" class="t-s muted">built by CoCo SQL</text> </svg> </div> <div class="tw"><table> <tr><th>#</th><th>What travels</th><th>From → to</th></tr> <tr><td><b>1</b></td><td>The Angular app itself (HTML, JS, CSS) and, in snapshot mode, the JSON files</td><td>Browser → tunnel → Angular dev server :4200</td></tr> <tr><td><b>2</b></td><td>API calls <code>GET /va/pa/summary</code>, <code>/waterfall</code>, … returning JSON. <b>The browser calls the backend directly</b>; the Angular server is not in the middle.</td><td>Browser → tunnel → FastAPI :8000</td></tr> <tr><td><b>3</b></td><td>Fixed, parameterised <code>SELECT</code> statements on the result tables only</td><td>FastAPI → Snowflake <code>IAMARW.AIDEA.VA_*</code></td></tr> </table></div> </section><!-- 3 --> <section id="s3"> <h2><span class="n">3</span>Data pipeline: how the VA_* tables were built</h2> <p class="sub">Built in Snowflake with CoCo (Cortex Code), one table per step. Every step keeps its SQL, so every number in the UI can show the query behind it.</p> <div class="diagram"> <svg viewBox="0 0 1000 400" role="img" aria-label="Data pipeline"> <defs> <marker id="a2" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0,0 L10,5 L0,10 z" class="ah"/></marker> <marker id="a2d" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0,0 L10,5 L0,10 z" class="ahd"/></marker> </defs> <text x="20" y="28" class="t-m" font-weight="700">Hop 1 · ESOW (what access exists)</text> <!-- row 1 --> <rect x="20" y="45" width="210" height="120" rx="10" class="node"/> <text x="125" y="70" text-anchor="middle" class="t-m" font-weight="700">ESOW sources</text> <text x="125" y="92" text-anchor="middle" class="t-s">FLATTEN_MODEL</text> <text x="125" y="109" text-anchor="middle" class="t-s">FLATTEN_ENTITLEMENT_ALL</text> <text x="125" y="126" text-anchor="middle" class="t-s">INDIVIDUAL</text> <text x="125" y="150" text-anchor="middle" class="t-s muted" style="font-size:10.5px">PRIVILEGE_ACCESS_IND = 'Y'</text> <rect x="275" y="45" width="190" height="120" rx="10" class="node-b"/> <text x="370" y="70" text-anchor="middle" class="t-m" font-weight="700">VA_PA_BASE</text> <text x="370" y="100" text-anchor="middle" font-size="22" font-weight="700" fill="var(--ours)">69,859</text> <text x="370" y="124" text-anchor="middle" class="t-s muted">PA assignments</text> <rect x="510" y="45" width="210" height="120" rx="10" class="node-g"/> <text x="615" y="70" text-anchor="middle" class="t-m" font-weight="700">VA_PA_WATERFALL</text> <text x="615" y="100" text-anchor="middle" font-size="22" font-weight="700" fill="var(--ok)">23,601</text> <text x="615" y="124" text-anchor="middle" class="t-s muted">eligible after rules R1…R11</text> <text x="615" y="142" text-anchor="middle" class="t-s muted">(terminated, no person, Tier 0, …)</text> <rect x="765" y="45" width="215" height="120" rx="10" class="node-y"/> <text x="872" y="70" text-anchor="middle" class="t-m" font-weight="700">VA_PA_RECON</text> <text x="872" y="92" text-anchor="middle" class="t-s">vs METRIC.V_AC02_R3_01</text> <text x="872" y="114" text-anchor="middle" class="t-s">BOTH 23,517</text> <text x="872" y="131" text-anchor="middle" class="t-s" style="font-size:11px">METRIC_ONLY 1,346 · OURS_ONLY 84</text> <text x="872" y="151" text-anchor="middle" class="t-s muted">gap = Tier 0 (1,263)</text> <line x1="230" y1="105" x2="272" y2="105" class="arrow" marker-end="url(#a2)"/> <line x1="465" y1="105" x2="507" y2="105" class="arrow" marker-end="url(#a2)"/> <line x1="720" y1="105" x2="762" y2="105" class="arrow" marker-end="url(#a2)"/> <!-- connector to row 2 --> <path d="M615,165 L615,200 L125,200 L125,232" class="arrow" marker-end="url(#a2)"/> <text x="370" y="194" text-anchor="middle" class="t-s muted">eligible PA assignments flow into the next hops</text>

<text x="20" y="226" class="t-m" font-weight="700" opacity="0">.</text> <!-- row 2 --> <rect x="20" y="235" width="210" height="140" rx="10" class="node-b"/> <text x="125" y="258" text-anchor="middle" class="t-m" font-weight="700">VA_PA_HOP_IDNDATA</text> <text x="125" y="276" text-anchor="middle" class="t-s muted">Hop 2 · is the model in IDNDATA?</text> <text x="125" y="298" text-anchor="middle" class="t-s">OK 6,033 models</text> <text x="125" y="316" text-anchor="middle" class="t-s">NOT_STAMPED ||PA|| 334</text> <text x="125" y="334" text-anchor="middle" class="t-s">MISSING 970</text> <text x="125" y="356" text-anchor="middle" class="t-s muted">(+1,504 missing, no active)</text>

<rect x="275" y="235" width="210" height="140" rx="10" class="node-b"/> <text x="380" y="258" text-anchor="middle" class="t-m" font-weight="700">VA_PA_HOP_IDNREVIEW</text> <text x="380" y="276" text-anchor="middle" class="t-s muted">Hop 3 · was it in a campaign?</text> <text x="380" y="298" text-anchor="middle" class="t-s">RP 776, RP 906</text> <text x="380" y="316" text-anchor="middle" class="t-s">join AUE ↔ CRI on</text> <text x="380" y="334" text-anchor="middle" class="t-s" style="font-size:11px">RP_ID, MSFWID, MODEL, ACCOUNT</text> <text x="380" y="356" text-anchor="middle" class="t-s muted">+ granted before/after load</text> <rect x="530" y="235" width="210" height="140" rx="10" class="node-r"/> <text x="635" y="258" text-anchor="middle" class="t-m" font-weight="700">VA_FINDINGS</text> <text x="635" y="280" text-anchor="middle" class="t-s">G1 Tier 0 in metric · 1,271</text> <text x="635" y="298" text-anchor="middle" class="t-s">G4 not stamped · 334</text> <text x="635" y="316" text-anchor="middle" class="t-s">G5 missing models · 970</text> <text x="635" y="334" text-anchor="middle" class="t-s">G7 not in campaign · 1,929</text> <text x="635" y="356" text-anchor="middle" class="t-s muted">each row stores its SQL_TEXT</text> <rect x="785" y="235" width="195" height="140" rx="10" class="node-g"/> <text x="882" y="258" text-anchor="middle" class="t-m" font-weight="700">VA_PA_SUMMARY_V</text> <text x="882" y="280" text-anchor="middle" class="t-s">one row for the header</text> <text x="882" y="298" text-anchor="middle" class="t-s">match %, counts, data as of</text> <text x="882" y="326" text-anchor="middle" class="t-m" font-weight="700">VA_RUN_LOG</text> <text x="882" y="346" text-anchor="middle" class="t-s">every build step, timed</text> <line x1="230" y1="305" x2="272" y2="305" class="arrow" marker-end="url(#a2)"/> <line x1="485" y1="305" x2="527" y2="305" class="arrow" marker-end="url(#a2)"/> <line x1="740" y1="305" x2="782" y2="305" class="arrow" marker-end="url(#a2)"/> </svg> </div> <p class="sub">Findings in plain words: <b>G1</b> the firm's metric counts Tier 0 PA as needing review, but the approved definition excludes it. <b>G4/G5</b> PA models that IDNDATA does not mark as PA or does not have at all, so they can never reach a review. <b>G7</b> eligible PA not present in campaign 906. G3 (STAGED campaigns, no decisions) is a DEV-environment artefact and shown as INFO only.</p> </section><!-- 3b --> <section id="s3b"> <h2><span class="n">3b</span>The tables in detail: why, built from, query</h2> <p class="sub">All objects live in <code>IAMARW.AIDEA</code> (DEV sandbox). Source schemas (ESOW, METRIC, IDNDATA, IDNREVIEW) are only read, never changed.</p><h3>Why create tables at all?</h3> <div class="grid"> <div class="kpi ours"><b style="font-size:16px">Speed</b><span>Sources hold millions of rows and the metric procedure times out. The heavy joins run once; the UI reads small results in under a second, however many people click.</span></div> <div class="kpi ok"><b style="font-size:16px">Evidence</b><span>Each table freezes the data at build time, and every step and finding stores its SQL and row count, so any number can be re-run and checked.</span></div> <div class="kpi cand"><b style="font-size:16px">Safety</b><span>We cannot write to source schemas. The backend's app role only needs SELECT on our own tables, not on the raw sources.</span></div> </div><h3>Overview</h3> <div class="tw"><table> <tr><th>Step</th><th>Object</th><th>Why we need it</th><th>Built from</th><th>Result</th></tr> <tr><td>1a</td><td><code>VA_PA_BASE</code></td><td>One clean list of every PA assignment, so every later step starts from the same population. Joins three ESOW tables once instead of on every screen.</td><td>ESOW <code>FLATTEN_ENTITLEMENT_ALL</code> (assignments) + <code>FLATTEN_MODEL</code> (PA flag, tier, category) + <code>INDIVIDUAL</code> (employee status)</td><td>69,859 rows · one per PA assignment</td></tr><tr><td>1b</td><td><code>VA_PA_WATERFALL</code></td><td>Decides, for each assignment, whether it <b>should</b> be reviewed, and if not, the first rule that removes it. This is our independent version of the approved eligibility definition.</td><td><code>VA_PA_BASE</code> + ESOW <code>SOURCE_TYPE_STATUS</code> (is the source live?)</td><td>69,859 rows · FIRST_RULE = ELIGIBLE (23,601) or R1…R11</td></tr><tr><td>1b</td><td><code>VA_PA_WATERFALL_SUMMARY</code></td><td>Small table the Eligibility chart draws: how many each rule removed and how many remain. Stores each rule's predicate so the UI can show it.</td><td><code>VA_PA_WATERFALL</code>, grouped by FIRST_RULE</td><td>~10 rows · 69,859 → 23,601</td></tr><tr><td>1c</td><td><code>VA_PA_RECON</code></td><td>Proves our rules against the firm's official metric. Anything in only one side is a difference to explain; this is where the Tier 0 finding (G1) comes from.</td><td><code>VA_PA_WATERFALL</code> (ours) full-joined with <code>METRIC.V_AC02_R3_01</code> (metric AC02-R3-01, eligible PA only). The view is queried directly because <code>SP_IAM_METRIC_DETAIL</code> times out.</td><td>24,947 rows · BOTH 23,517 · METRIC_ONLY 1,346 · OURS_ONLY 84</td></tr><tr><td>1d</td><td><code>VA_PA_HOP_IDNDATA</code></td><td>Hop 2: a PA assignment can only be reviewed if its model reaches IDNDATA <b>and</b> is marked as PA there. This shows which models fall through.</td><td>Eligible PA models from <code>VA_PA_WATERFALL</code> + <code>IDNDATA.ENTITLEMENT</code> (current rows, <code>REVIEW_SCOPE</code>)</td><td>8,841 models · OK 6,033 · NOT_STAMPED 334 · MISSING 970 · MISSING_NO_ACTIVE 1,504</td></tr><tr><td>1g</td><td><code>VA_PA_HOP_IDNREVIEW</code></td><td>Hop 3: was each eligible PA assignment actually put in a review campaign, and was a decision taken? Completes Venkat's ESOW → IDNDATA → IDNREVIEW trace.</td><td>Eligible rows of <code>VA_PA_WATERFALL</code> + <code>IDNDATA.ACCOUNT_USER_ENTITLEMENT</code> (current) + <code>IDNREVIEW.ACCOUNT_USER_ENTITLEMENT</code> (AUE) + <code>IDNREVIEW.CERTIFICATION_REVIEW_ITEMS</code> (RI) + campaign details, for RP 776 and RP 906. Join keys reused from <code>rp_query_engine.py</code>, without its <code>DECISION='NONE'</code> filter.</td><td>One row per (RP_ID, eligible assignment) · zero duplicates · RP 906: 1,929 missing from campaign</td></tr><tr><td>1e</td><td><code>VA_FINDINGS</code></td><td>The findings register Venkat asked for: every finding keeps its count <b>and the exact SQL</b> that produced it, so anyone can re-run it. The UI shows these as cards.</td><td>Counts computed from the tables above (and the metric view); one INSERT per finding</td><td>G1 1,271 · G2 46 · G3 8,332 (DEV only, INFO) · G4 334 · G5 970 · G6 0 · G7 1,929 · G8</td></tr><tr><td>1e</td><td><code>VA_RUN_LOG</code></td><td>Audit trail of the build itself: which step ran, when, how many rows, with what SQL. Gives the UI its "data as of" time.</td><td>Written by each build step (one INSERT after each table is created)</td><td>One row per step per run</td></tr><tr><td>B5</td><td><code>VA_PA_SUMMARY_V</code> <span class="tag t-later">view</span></td><td>One row of headline numbers for the page header, so the backend makes a single tiny query. A <b>view</b>, so it is always in step with the tables.</td><td>Reads <code>VA_PA_BASE</code>, <code>VA_PA_WATERFALL</code>, <code>VA_PA_RECON</code>, <code>VA_FINDINGS</code>, <code>VA_RUN_LOG</code></td><td>1 row</td></tr> </table></div><h3>Queries (click a table to expand)</h3> <div class="note"><b>Source of truth:</b> CoCo checked each column with DESCRIBE before running and adjusted names where needed (for example <code>PRINCIPAL_TYPE</code> → <code>PRINCIPAL_TYPE_ID = 2</code>, current rows <code>TRANSACT_TO = '9999-12-30'</code>). The queries below show the logic as built. The exact text that ran is stored in Snowflake; fetch it with: <pre style="margin-bottom:0">SELECT STEP, TABLE_NAME, ROW_COUNT, ENDED_AT, SQL_TEXT FROM IAMARW.AIDEA.VA_RUN_LOG ORDER BY ENDED_AT;

SELECT FINDING_ID, TITLE, ROW_COUNT, SQL_TEXT FROM IAMARW.AIDEA.VA_FINDINGS ORDER BY FINDING_ID;

SELECT GET_DDL('VIEW', 'IAMARW.AIDEA.VA_PA_SUMMARY_V');</pre></div>

<details class="tbl"> <summary><span class="step">1a</span> <code>IAMARW.AIDEA.VA_PA_BASE</code> <span class="res">69,859 rows · one per PA assignment</span></summary> <div class="tbl-body"> <p><b>Why:</b> One clean list of every PA assignment, so every later step starts from the same population. Joins three ESOW tables once instead of on every screen.</p> <p><b>Built from:</b> ESOW <code>FLATTEN_ENTITLEMENT_ALL</code> (assignments) + <code>FLATTEN_MODEL</code> (PA flag, tier, category) + <code>INDIVIDUAL</code> (employee status)</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_PA_BASE AS SELECT fe.ENTITLEMENT_ID, fe.MODEL_ID, fe.MSFWID, fe.PRINCIPAL_TYPE_ID, -- CoCo fix: column is PRINCIPAL_TYPE_ID (2 = INDIVIDUAL) fe.IS_REVOKED, fe.IS_FULFILLED, fe.GRANT_DATE, fe.DERIVED_EXCLUDED_FROM_REVIEW_CODE AS ENT_EXCL_CODE, fe.DERIVED_EXCLUDED_FROM_REVIEW_INDICATOR AS ENT_EXCL_IND, fm.CATEGORY, fm.TIER, fm.CORE_CLASSIFICATION, fm.ENVIRONMENT_CD, fm.DERIVED_EXCLUDED_FROM_REVIEW_INDICATOR AS MODEL_EXCL_IND, fm.SOURCE_TYPE_ID, i.EMPLOYEE_STATUS, i.WORKER_TYPE, CURRENT_TIMESTAMP() AS BUILT_AT FROM IAMARW.ESOW.FLATTEN_ENTITLEMENT_ALL fe JOIN IAMARW.ESOW.FLATTEN_MODEL fm ON fm.MODEL_ID = fe.MODEL_ID LEFT JOIN IAMARW.ESOW.INDIVIDUAL i ON i.MSFWID = fe.MSFWID WHERE fm.PRIVILEGE_ACCESS_INDICATOR = &#x27;Y&#x27;;</pre> </div> </details><details class="tbl"> <summary><span class="step">1b</span> <code>IAMARW.AIDEA.VA_PA_WATERFALL</code> <span class="res">69,859 rows · FIRST_RULE = ELIGIBLE (23,601) or R1…R11</span></summary> <div class="tbl-body"> <p><b>Why:</b> Decides, for each assignment, whether it <b>should</b> be reviewed, and if not, the first rule that removes it. This is our independent version of the approved eligibility definition.</p> <p><b>Built from:</b> <code>VA_PA_BASE</code> + ESOW <code>SOURCE_TYPE_STATUS</code> (is the source live?)</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_PA_WATERFALL AS SELECT b.*, CASE -- first match wins, in this order WHEN b.IS_REVOKED = &#x27;Y&#x27; OR b.IS_FULFILLED &lt;&gt; &#x27;Y&#x27; THEN &#x27;R1_NOT_ACTIVE&#x27; WHEN b.EMPLOYEE_STATUS = &#x27;T&#x27; THEN &#x27;R2A_TERMINATED&#x27; WHEN b.EMPLOYEE_STATUS IS NULL THEN &#x27;R2B_NO_PERSON_RECORD&#x27; WHEN b.PRINCIPAL_TYPE_ID &lt;&gt; 2 THEN &#x27;R3_NOT_INDIVIDUAL&#x27; WHEN b.ENT_EXCL_CODE &lt;&gt; 0 OR b.MODEL_EXCL_IND = &#x27;Y&#x27; THEN &#x27;R5_EXCLUDED&#x27; WHEN sts.IS_LIVE = &#x27;N&#x27; THEN &#x27;R6_ACCESS_NOT_LIVE&#x27; WHEN b.ENVIRONMENT_CD &lt;&gt; &#x27;PROD&#x27; THEN &#x27;R8_NON_PROD&#x27; WHEN b.TIER = 0 THEN &#x27;R11_TIER0&#x27; ELSE &#x27;ELIGIBLE&#x27; END AS FIRST_RULE FROM IAMARW.AIDEA.VA_PA_BASE b LEFT JOIN IAMARW.ESOW.SOURCE_TYPE_STATUS sts ON sts.SOURCE_TYPE_ID = b.SOURCE_TYPE_ID AND sts.SUBJECT_AREA = &#x27;MODEL&#x27; AND sts.LABEL = &#x27;ENTITLEMENT&#x27;;</pre> </div> </details><details class="tbl"> <summary><span class="step">1b</span> <code>IAMARW.AIDEA.VA_PA_WATERFALL_SUMMARY</code> <span class="res">~10 rows · 69,859 → 23,601</span></summary> <div class="tbl-body"> <p><b>Why:</b> Small table the Eligibility chart draws: how many each rule removed and how many remain. Stores each rule's predicate so the UI can show it.</p> <p><b>Built from:</b> <code>VA_PA_WATERFALL</code>, grouped by FIRST_RULE</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_PA_WATERFALL_SUMMARY AS WITH c AS ( SELECT FIRST_RULE AS RULE, COUNT(*) AS REMOVED FROM IAMARW.AIDEA.VA_PA_WATERFALL GROUP BY FIRST_RULE) SELECT r.RULE_ORDER, r.RULE, COALESCE(c.REMOVED,0) AS REMOVED, (SELECT COUNT(*) FROM IAMARW.AIDEA.VA_PA_BASE) - SUM(COALESCE(c.REMOVED,0)) OVER (ORDER BY r.RULE_ORDER) AS REMAINING, r.SQL_TEXT -- the predicate of that rule FROM (VALUES (1,&#x27;R1_NOT_ACTIVE&#x27;, &#x27;IS_REVOKED=&#x27;&#x27;Y&#x27;&#x27; OR IS_FULFILLED&lt;&gt;&#x27;&#x27;Y&#x27;&#x27;&#x27;), (2,&#x27;R2A_TERMINATED&#x27;, &#x27;EMPLOYEE_STATUS=&#x27;&#x27;T&#x27;&#x27;&#x27;), (3,&#x27;R2B_NO_PERSON_RECORD&#x27;,&#x27;EMPLOYEE_STATUS IS NULL&#x27;), (4,&#x27;R3_NOT_INDIVIDUAL&#x27;,&#x27;PRINCIPAL_TYPE_ID&lt;&gt;2&#x27;), (5,&#x27;R5_EXCLUDED&#x27;, &#x27;ENT_EXCL_CODE&lt;&gt;0 OR MODEL_EXCL_IND=&#x27;&#x27;Y&#x27;&#x27;&#x27;), (6,&#x27;R6_ACCESS_NOT_LIVE&#x27;,&#x27;SOURCE_TYPE_STATUS.IS_LIVE=&#x27;&#x27;N&#x27;&#x27;&#x27;), (8,&#x27;R8_NON_PROD&#x27;, &#x27;ENVIRONMENT_CD&lt;&gt;&#x27;&#x27;PROD&#x27;&#x27;&#x27;), (11,&#x27;R11_TIER0&#x27;, &#x27;TIER=0&#x27;)) r(RULE_ORDER, RULE, SQL_TEXT) LEFT JOIN c ON c.RULE = r.RULE ORDER BY r.RULE_ORDER;</pre> </div> </details><details class="tbl"> <summary><span class="step">1c</span> <code>IAMARW.AIDEA.VA_PA_RECON</code> <span class="res">24,947 rows · BOTH 23,517 · METRIC_ONLY 1,346 · OURS_ONLY 84</span></summary> <div class="tbl-body"> <p><b>Why:</b> Proves our rules against the firm's official metric. Anything in only one side is a difference to explain; this is where the Tier 0 finding (G1) comes from.</p> <p><b>Built from:</b> <code>VA_PA_WATERFALL</code> (ours) full-joined with <code>METRIC.V_AC02_R3_01</code> (metric AC02-R3-01, eligible PA only). The view is queried directly because <code>SP_IAM_METRIC_DETAIL</code> times out.</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_PA_RECON AS WITH ours AS ( SELECT ENTITLEMENT_ID FROM IAMARW.AIDEA.VA_PA_WATERFALL WHERE FIRST_RULE = &#x27;ELIGIBLE&#x27;), metric AS ( SELECT m.ENTITLEMENT_ID, m.INELIGIBILITY_REASON_CODE, m.IS_COMPLIANT, m.COMPLIANCE_REASON_CODE, m.LATEST_REVIEW_DATE FROM IAMARW.METRIC.V_AC02_R3_01 m WHERE m.IS_ELIGIBLE = &#x27;Y&#x27; AND m.ENTITLEMENT_ID IN (SELECT ENTITLEMENT_ID FROM IAMARW.AIDEA.VA_PA_BASE)) SELECT COALESCE(o.ENTITLEMENT_ID, m.ENTITLEMENT_ID) AS ENTITLEMENT_ID, CASE WHEN o.ENTITLEMENT_ID IS NOT NULL AND m.ENTITLEMENT_ID IS NOT NULL THEN &#x27;BOTH&#x27; WHEN o.ENTITLEMENT_ID IS NOT NULL THEN &#x27;OURS_ONLY&#x27; ELSE &#x27;METRIC_ONLY&#x27; END AS BUCKET, w.FIRST_RULE AS OUR_FIRST_RULE, m.INELIGIBILITY_REASON_CODE AS METRIC_INELIG_CODE, m.IS_COMPLIANT AS METRIC_COMPLIANT, m.COMPLIANCE_REASON_CODE AS METRIC_COMPLIANCE_CODE, m.LATEST_REVIEW_DATE, w.TIER, w.CATEGORY, w.CORE_CLASSIFICATION FROM ours o FULL OUTER JOIN metric m ON m.ENTITLEMENT_ID = o.ENTITLEMENT_ID LEFT JOIN IAMARW.AIDEA.VA_PA_WATERFALL w ON w.ENTITLEMENT_ID = COALESCE(o.ENTITLEMENT_ID, m.ENTITLEMENT_ID);</pre> </div> </details><details class="tbl"> <summary><span class="step">1d</span> <code>IAMARW.AIDEA.VA_PA_HOP_IDNDATA</code> <span class="res">8,841 models · OK 6,033 · NOT_STAMPED 334 · MISSING 970 · MISSING_NO_ACTIVE 1,504</span></summary> <div class="tbl-body"> <p><b>Why:</b> Hop 2: a PA assignment can only be reviewed if its model reaches IDNDATA <b>and</b> is marked as PA there. This shows which models fall through.</p> <p><b>Built from:</b> Eligible PA models from <code>VA_PA_WATERFALL</code> + <code>IDNDATA.ENTITLEMENT</code> (current rows, <code>REVIEW_SCOPE</code>)</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_PA_HOP_IDNDATA AS WITH m AS ( SELECT MODEL_ID, ANY_VALUE(CATEGORY) AS CATEGORY, COUNT_IF(FIRST_RULE = &#x27;ELIGIBLE&#x27;) AS ACTIVE_REVIEWABLE_ASSIGNMENTS FROM IAMARW.AIDEA.VA_PA_WATERFALL GROUP BY MODEL_ID), i AS ( SELECT MODEL_ID, MAX(IFF(REVIEW_SCOPE LIKE &#x27;%||PA||%&#x27;, &#x27;Y&#x27;, &#x27;N&#x27;)) AS STAMPED_PA FROM IAMARW.IDNDATA.ENTITLEMENT WHERE TRANSACT_TO = &#x27;9999-12-30&#x27; -- current row GROUP BY MODEL_ID) SELECT m.MODEL_ID, m.CATEGORY, m.ACTIVE_REVIEWABLE_ASSIGNMENTS, IFF(i.MODEL_ID IS NULL, &#x27;N&#x27;, &#x27;Y&#x27;) AS IN_IDNDATA, COALESCE(i.STAMPED_PA, &#x27;N&#x27;) AS STAMPED_PA, CASE WHEN i.MODEL_ID IS NULL AND m.ACTIVE_REVIEWABLE_ASSIGNMENTS = 0 THEN &#x27;MISSING_NO_ACTIVE&#x27; WHEN i.MODEL_ID IS NULL THEN &#x27;MISSING_FROM_IDNDATA&#x27; WHEN i.STAMPED_PA = &#x27;N&#x27; THEN &#x27;NOT_STAMPED&#x27; ELSE &#x27;OK&#x27; END AS HOP_STATUS FROM m LEFT JOIN i ON i.MODEL_ID = m.MODEL_ID;</pre> </div> </details><details class="tbl"> <summary><span class="step">1g</span> <code>IAMARW.AIDEA.VA_PA_HOP_IDNREVIEW</code> <span class="res">One row per (RP_ID, eligible assignment) · zero duplicates · RP 906: 1,929 missing from campaign</span></summary> <div class="tbl-body"> <p><b>Why:</b> Hop 3: was each eligible PA assignment actually put in a review campaign, and was a decision taken? Completes Venkat's ESOW → IDNDATA → IDNREVIEW trace.</p> <p><b>Built from:</b> Eligible rows of <code>VA_PA_WATERFALL</code> + <code>IDNDATA.ACCOUNT_USER_ENTITLEMENT</code> (current) + <code>IDNREVIEW.ACCOUNT_USER_ENTITLEMENT</code> (AUE) + <code>IDNREVIEW.CERTIFICATION_REVIEW_ITEMS</code> (RI) + campaign details, for RP 776 and RP 906. Join keys reused from <code>rp_query_engine.py</code>, without its <code>DECISION='NONE'</code> filter.</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_PA_HOP_IDNREVIEW AS WITH elig AS ( SELECT ENTITLEMENT_ID, MODEL_ID, MSFWID, GRANT_DATE, CATEGORY, TIER FROM IAMARW.AIDEA.VA_PA_WATERFALL WHERE FIRST_RULE = &#x27;ELIGIBLE&#x27;), idn AS ( SELECT USER_ENTITLEMENT_ID, ACCOUNT_ID FROM IAMARW.IDNDATA.ACCOUNT_USER_ENTITLEMENT WHERE TRANSACT_TO = &#x27;9999-12-30&#x27;), lastload AS ( SELECT MAX(TRANSACT_FROM) AS L FROM IAMARW.IDNDATA.ACCOUNT_USER_ENTITLEMENT), camp AS ( SELECT RP_ID, COVERAGE_COLLECTED_ON FROM IAMARW.IDNREVIEW.CAMPAIGN_DETAILS WHERE RP_ID IN (776, 906)) SELECT c.RP_ID, e.ENTITLEMENT_ID, e.MODEL_ID, e.CATEGORY, e.TIER, e.GRANT_DATE, IFF(idn.USER_ENTITLEMENT_ID IS NULL, &#x27;N&#x27;, &#x27;Y&#x27;) AS IN_IDNDATA, IFF(ri.RP_ID IS NULL, &#x27;N&#x27;, &#x27;Y&#x27;) AS IN_CAMPAIGN, ri.DECISION, ri.DECISION_TAKEN_ON, ri.COVERAGE_SOURCE, CASE WHEN e.GRANT_DATE IS NULL THEN &#x27;UNKNOWN&#x27; WHEN e.GRANT_DATE &gt; ll.L THEN &#x27;Y&#x27; ELSE &#x27;N&#x27; END AS GRANTED_AFTER_IDNDATA_LOAD, CASE WHEN idn.USER_ENTITLEMENT_ID IS NULL THEN &#x27;NOT_IN_IDNDATA&#x27; WHEN e.GRANT_DATE &gt; c.COVERAGE_COLLECTED_ON THEN &#x27;GRANTED_AFTER_SCOPING&#x27; WHEN ri.RP_ID IS NULL THEN &#x27;MISSING_FROM_CAMPAIGN&#x27; WHEN COALESCE(ri.DECISION,&#x27;NONE&#x27;) = &#x27;NONE&#x27; THEN &#x27;IN_CAMPAIGN_PENDING&#x27; ELSE &#x27;DECIDED&#x27; END AS HOP3_STATUS FROM elig e CROSS JOIN camp c CROSS JOIN lastload ll LEFT JOIN idn ON idn.USER_ENTITLEMENT_ID = e.ENTITLEMENT_ID LEFT JOIN IAMARW.IDNREVIEW.ACCOUNT_USER_ENTITLEMENT aue -- RP 776 has USER_ENTITLEMENT_ID=&#x27;Not Available&#x27;, ON aue.RP_ID = c.RP_ID AND aue.MSFWID = e.MSFWID -- so match on MSFWID + MODEL + ACCOUNT AND aue.MODEL_ID = e.MODEL_ID AND aue.ACCOUNT_ID = idn.ACCOUNT_ID LEFT JOIN IAMARW.IDNREVIEW.CERTIFICATION_REVIEW_ITEMS ri ON ri.RP_ID = aue.RP_ID AND ri.MS_USER_FWD_ID = aue.MSFWID AND ri.MS_OBJECT_ID = aue.MODEL_ID AND ri.ACCOUNT_ID = aue.ACCOUNT_ID;</pre> </div> </details><details class="tbl"> <summary><span class="step">1e</span> <code>IAMARW.AIDEA.VA_FINDINGS</code> <span class="res">G1 1,271 · G2 46 · G3 8,332 (DEV only, INFO) · G4 334 · G5 970 · G6 0 · G7 1,929 · G8</span></summary> <div class="tbl-body"> <p><b>Why:</b> The findings register Venkat asked for: every finding keeps its count <b>and the exact SQL</b> that produced it, so anyone can re-run it. The UI shows these as cards.</p> <p><b>Built from:</b> Counts computed from the tables above (and the metric view); one INSERT per finding</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_FINDINGS ( FINDING_ID STRING, TITLE STRING, HOP STRING, FINDING_TYPE STRING, ROW_COUNT NUMBER, STATUS STRING, DATA_AS_OF TIMESTAMP_NTZ, SQL_TEXT STRING, DETAIL_TABLE STRING, NOTES STRING);

-- one row per finding; ROW_COUNT is the result of the SQL stored in SQL_TEXT. Examples: -- G1 Tier 0 PA counted eligible by AC02-R3-01 -> 1,271 SELECT COUNT() FROM IAMARW.METRIC.V_AC02_R3_01 m JOIN IAMARW.AIDEA.VA_PA_BASE b ON b.ENTITLEMENT_ID = m.ENTITLEMENT_ID WHERE m.IS_ELIGIBLE = 'Y' AND b.TIER = 0; -- G4 PA model in IDNDATA not stamped ||PA|| -> 334 SELECT COUNT() FROM IAMARW.AIDEA.VA_PA_HOP_IDNDATA WHERE HOP_STATUS = 'NOT_STAMPED'; -- G5 PA model with eligible assignments missing from IDNDATA -> 970 SELECT COUNT() FROM IAMARW.AIDEA.VA_PA_HOP_IDNDATA WHERE HOP_STATUS = 'MISSING_FROM_IDNDATA'; -- G7 eligible PA missing from campaign 906 -> 1,929 SELECT COUNT() FROM IAMARW.AIDEA.VA_PA_HOP_IDNREVIEW WHERE RP_ID = 906 AND HOP3_STATUS = 'MISSING_FROM_CAMPAIGN'; -- G8 granted before the last IDNDATA load but not in IDNDATA SELECT COUNT(*) FROM IAMARW.AIDEA.VA_PA_HOP_IDNREVIEW WHERE RP_ID = 906 AND HOP3_STATUS = 'NOT_IN_IDNDATA' AND GRANTED_AFTER_IDNDATA_LOAD = 'N';</pre>

</div> </details><details class="tbl"> <summary><span class="step">1e</span> <code>IAMARW.AIDEA.VA_RUN_LOG</code> <span class="res">One row per step per run</span></summary> <div class="tbl-body"> <p><b>Why:</b> Audit trail of the build itself: which step ran, when, how many rows, with what SQL. Gives the UI its "data as of" time.</p> <p><b>Built from:</b> Written by each build step (one INSERT after each table is created)</p> <pre>CREATE OR REPLACE TABLE IAMARW.AIDEA.VA_RUN_LOG ( RUN_ID STRING, STEP STRING, TABLE_NAME STRING, ROW_COUNT NUMBER, STARTED_AT TIMESTAMP_NTZ, ENDED_AT TIMESTAMP_NTZ, SQL_TEXT STRING);

INSERT INTO IAMARW.AIDEA.VA_RUN_LOG SELECT :run_id, '1a', 'VA_PA_BASE', (SELECT COUNT(*) FROM IAMARW.AIDEA.VA_PA_BASE), :started_at, CURRENT_TIMESTAMP(), :sql_text_of_step_1a; -- ...same after every step</pre>

</div> </details><details class="tbl"> <summary><span class="step">B5</span> <code>IAMARW.AIDEA.VA_PA_SUMMARY_V</code> <span class="res">1 row</span></summary> <div class="tbl-body"> <p><b>Why:</b> One row of headline numbers for the page header, so the backend makes a single tiny query. A <b>view</b>, so it is always in step with the tables.</p> <p><b>Built from:</b> Reads <code>VA_PA_BASE</code>, <code>VA_PA_WATERFALL</code>, <code>VA_PA_RECON</code>, <code>VA_FINDINGS</code>, <code>VA_RUN_LOG</code></p> <pre>CREATE OR REPLACE VIEW IAMARW.AIDEA.VA_PA_SUMMARY_V AS SELECT (SELECT COUNT(*) FROM IAMARW.AIDEA.VA_PA_BASE) AS BASE_ROWS, (SELECT COUNT(*) FROM IAMARW.AIDEA.VA_PA_WATERFALL WHERE FIRST_RULE=&#x27;ELIGIBLE&#x27;) AS ELIGIBLE_OURS, (SELECT COUNT_IF(BUCKET IN (&#x27;BOTH&#x27;,&#x27;METRIC_ONLY&#x27;)) FROM IAMARW.AIDEA.VA_PA_RECON) AS ELIGIBLE_METRIC, (SELECT COUNT_IF(BUCKET=&#x27;BOTH&#x27;) FROM IAMARW.AIDEA.VA_PA_RECON) AS BOTH, (SELECT COUNT_IF(BUCKET=&#x27;OURS_ONLY&#x27;) FROM IAMARW.AIDEA.VA_PA_RECON) AS OURS_ONLY, (SELECT COUNT_IF(BUCKET=&#x27;METRIC_ONLY&#x27;) FROM IAMARW.AIDEA.VA_PA_RECON) AS METRIC_ONLY, ROUND(100 * BOTH / NULLIF(BOTH + OURS_ONLY + METRIC_ONLY, 0), 1) AS MATCH_PCT, (SELECT COUNT(*) FROM IAMARW.AIDEA.VA_PA_WATERFALL WHERE FIRST_RULE=&#x27;R11_TIER0&#x27;) AS TIER0_REMOVED, (SELECT COUNT(*) FROM IAMARW.AIDEA.VA_FINDINGS) AS FINDINGS_COUNT, (SELECT MAX(ENDED_AT) FROM IAMARW.AIDEA.VA_RUN_LOG) AS DATA_AS_OF;

GRANT SELECT ON VIEW IAMARW.AIDEA.VA_PA_SUMMARY_V TO ROLE AIDEA_313373_READ_WRITE;</pre>

</div> </details><h3>Access for the backend</h3> <pre>-- Naina&#x27;s role owns the tables, so the backend&#x27;s app role was given read access (Prompt B, step 4) GRANT SELECT ON TABLE IAMARW.AIDEA.VA_PA_BASE TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_PA_WATERFALL TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_PA_WATERFALL_SUMMARY TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_PA_RECON TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_PA_HOP_IDNDATA TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_PA_HOP_IDNREVIEW TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_FINDINGS TO ROLE AIDEA_313373_READ_WRITE; GRANT SELECT ON TABLE IAMARW.AIDEA.VA_RUN_LOG TO ROLE AIDEA_313373_READ_WRITE;</pre><h3>Do the tables refresh automatically? <span class="tag t-next">not yet</span></h3> <div class="cols"> <div> <ul> <li>Each table was made <b>once</b> with <code>CREATE OR REPLACE TABLE … AS SELECT</code>: a copy of the sources at that moment, not a live link.</li> <li>If ESOW, IDNDATA or the metric change, our tables stay the same until the build SQL is run again. There is no scheduled job yet.</li> <li>Only the view <code>VA_PA_SUMMARY_V</code> recalculates on every read, and it reads our tables, so it is as fresh as they are.</li> <li>The UI shows <b>"data as of"</b> (latest <code>VA_RUN_LOG.ENDED_AT</code>), so users always see how old the numbers are.</li> <li>In DEV a refresh would change little for now: ESOW has no history, and IDNDATA was last loaded around May 2026.</li> </ul> </div> <div> <p><b>Plan to automate (DEV):</b></p> <ol> <li>Stored procedure <code>SP_VA_PA_REFRESH()</code> re-runs the queries above in order into <code>*_NEW</code> tables, checks row counts, then <code>ALTER TABLE … SWAP WITH</code> so the UI never sees a half-built table.</li> <li>Each run logs to <code>VA_RUN_LOG</code> under one RUN_ID and appends a row to <code>VA_PA_SUMMARY_HISTORY</code> for trends.</li> <li>Snowflake task <code>TSK_VA_PA_REFRESH</code>, daily (cron), calls the procedure. Resume it only after one clean manual run.</li> <li>For UAT/PROD: DDL, procedure and task go through the deployment pipeline (Ola, 26 Oct).</li> </ol> </div> </div> <div class="diagram"> <svg viewBox="0 0 1000 120" role="img" aria-label="Refresh flow"> <defs><marker id="a5" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0,0 L10,5 L0,10 z" class="ah"/></marker></defs> <rect x="10" y="30" width="170" height="64" rx="10" class="node-y"/><text x="95" y="57" text-anchor="middle" class="t-m" font-weight="700">Task (daily)</text><text x="95" y="77" text-anchor="middle" class="t-s muted">TSK_VA_PA_REFRESH</text> <rect x="225" y="30" width="190" height="64" rx="10" class="node-b"/><text x="320" y="57" text-anchor="middle" class="t-m" font-weight="700">SP_VA_PA_REFRESH</text><text x="320" y="77" text-anchor="middle" class="t-s muted">1a → 1b → 1c → 1d → 1g → 1e</text> <rect x="460" y="30" width="170" height="64" rx="10" class="node"/><text x="545" y="57" text-anchor="middle" class="t-m" font-weight="700">*_NEW tables</text><text x="545" y="77" text-anchor="middle" class="t-s muted">check counts &gt; 0</text> <rect x="675" y="30" width="140" height="64" rx="10" class="node-g"/><text x="745" y="57" text-anchor="middle" class="t-m" font-weight="700">SWAP</text><text x="745" y="77" text-anchor="middle" class="t-s muted">live VA_* tables</text> <rect x="860" y="30" width="130" height="64" rx="10" class="node-g"/><text x="925" y="57" text-anchor="middle" class="t-m" font-weight="700">VA_RUN_LOG</text><text x="925" y="77" text-anchor="middle" class="t-s muted">+ history row</text> <line x1="180" y1="62" x2="222" y2="62" class="arrow" marker-end="url(#a5)"/> <line x1="415" y1="62" x2="457" y2="62" class="arrow" marker-end="url(#a5)"/> <line x1="630" y1="62" x2="672" y2="62" class="arrow" marker-end="url(#a5)"/> <line x1="815" y1="62" x2="857" y2="62" class="arrow" marker-end="url(#a5)"/> </svg> </div> </section><!-- 4 --> <section id="s4"> <h2><span class="n">4</span>Request flow: what happens when you open a tab</h2> <p class="sub">Example: the user opens the Summary tab in live mode (<code>?source=api</code>).</p> <div class="diagram"> <svg viewBox="0 0 1000 520" role="img" aria-label="Sequence diagram"> <defs> <marker id="a3" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0,0 L10,5 L0,10 z" class="ah"/></marker> <marker id="a3g" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0,0 L10,5 L0,10 z" class="ahg"/></marker> </defs> <!-- heads --> <g> <rect x="15" y="15" width="130" height="44" rx="8" class="head"/><text x="80" y="42" text-anchor="middle" class="head-t">User</text> <rect x="175" y="15" width="150" height="44" rx="8" class="head"/><text x="250" y="35" text-anchor="middle" class="head-t">Tab component</text><text x="250" y="51" text-anchor="middle" class="head-t" font-weight="400" font-size="11">Angular</text> <rect x="355" y="15" width="160" height="44" rx="8" class="head"/><text x="435" y="35" text-anchor="middle" class="head-t">PaReviewAudit</text><text x="435" y="51" text-anchor="middle" class="head-t" font-weight="400" font-size="11">Service + HttpClient</text> <rect x="545" y="15" width="150" height="44" rx="8" class="head"/><text x="620" y="35" text-anchor="middle" class="head-t">FastAPI :8000</text><text x="620" y="51" text-anchor="middle" class="head-t" font-weight="400" font-size="11">middleware + router</text> <rect x="725" y="15" width="130" height="44" rx="8" class="head"/><text x="790" y="35" text-anchor="middle" class="head-t">Service</text><text x="790" y="51" text-anchor="middle" class="head-t" font-weight="400" font-size="11">conn. pool</text> <rect x="875" y="15" width="115" height="44" rx="8" class="head" style="fill:var(--ok)"/><text x="932" y="42" text-anchor="middle" class="head-t">Snowflake</text> </g> <line x1="80" y1="59" x2="80" y2="505" class="life"/> <line x1="250" y1="59" x2="250" y2="505" class="life"/> <line x1="435" y1="59" x2="435" y2="505" class="life"/> <line x1="620" y1="59" x2="620" y2="505" class="life"/> <line x1="790" y1="59" x2="790" y2="505" class="life"/> <line x1="932" y1="59" x2="932" y2="505" class="life"/> <!-- messages --> <line x1="80" y1="95" x2="246" y2="95" class="arrow" marker-end="url(#a3)"/> <text x="90" y="88" class="t-s">① opens Summary tab</text> <line x1="250" y1="135" x2="431" y2="135" class="arrow" marker-end="url(#a3)"/> <text x="260" y="128" class="t-s">② getSummary()</text> <rect x="440" y="150" width="170" height="40" rx="6" class="node-y"/> <text x="525" y="167" text-anchor="middle" class="t-s">source = 'api'?</text> <text x="525" y="183" text-anchor="middle" class="t-s muted">else read assets JSON</text> <line x1="435" y1="215" x2="616" y2="215" class="arrow" marker-end="url(#a3)"/> <text x="445" y="208" class="t-s">③ GET localhost:8000/va/pa/summary</text> <rect x="626" y="228" width="155" height="54" rx="6" class="node-b"/> <text x="703" y="247" text-anchor="middle" class="t-s">④ auth_middleware</text> <text x="703" y="263" text-anchor="middle" class="t-s muted">localhost → local_dev</text> <text x="703" y="277" text-anchor="middle" class="t-s muted">router → service</text> <line x1="620" y1="305" x2="786" y2="305" class="arrow" marker-end="url(#a3)"/> <text x="630" y="298" class="t-s">⑤ get_summary()</text> <line x1="790" y1="345" x2="928" y2="345" class="arrow" marker-end="url(#a3)"/> <text x="798" y="330" class="t-s">⑥ SELECT … FROM</text> <text x="798" y="342" class="t-s">VA_PA_SUMMARY_V</text> <line x1="932" y1="380" x2="794" y2="380" class="arrow-g" marker-end="url(#a3g)"/> <text x="800" y="398" class="t-s">⑦ 1 row</text> <line x1="790" y1="420" x2="624" y2="420" class="arrow-g" marker-end="url(#a3g)"/> <text x="630" y="413" class="t-s">⑧ {data, sql, dataAsOf, env}</text> <line x1="620" y1="450" x2="439" y2="450" class="arrow-g" marker-end="url(#a3g)"/> <text x="445" y="443" class="t-s">⑨ HTTP 200 JSON</text> <line x1="435" y1="475" x2="254" y2="475" class="arrow-g" marker-end="url(#a3g)"/> <text x="260" y="468" class="t-s">⑩ typed model</text> <line x1="250" y1="498" x2="84" y2="498" class="arrow-g" marker-end="url(#a3g)"/> <text x="90" y="491" class="t-s">⑪ KPI cards + "&lt;/&gt; Query"</text> </svg> </div> <h3>The response envelope (every endpoint)</h3> <pre>{ "data": [ ...rows... ], // what the tab draws "sql": "SELECT ... FROM IAMARW.AIDEA.VA_PA_SUMMARY_V", // shown by "&lt;/&gt; Query" "dataAsOf": "2026-10-2xTxx:xx:xx", // latest VA_RUN_LOG ENDED_AT "environment": "dev" // badge in the header }</pre> <p>The same envelope is used in snapshot mode (the JSON files) and live mode (the API). That is why one switch changes the source without changing any screen.</p> </section><!-- 5 --> <section id="s5"> <h2><span class="n">5</span>How the two apps are connected locally</h2> <div class="cols"> <div> <h3>Snapshot mode vs live mode</h3> <div class="tw"><table> <tr><th></th><th>Snapshot</th><th>Live (api)</th></tr> <tr><td>Data comes from</td><td><code>src/assets/data-dump/pa-review-audit/*.json</code> (export of the VA_* tables)</td><td><code>http://localhost:8000/va/pa/*</code> → Snowflake</td></tr> <tr><td>Needs backend?</td><td>No</td><td>Yes</td></tr> <tr><td>Calls in Network tab</td><td>only <code>*.json</code> from :4200</td><td><code>/va/pa/...</code> to :8000</td></tr> <tr><td>Use for</td><td>offline demo, screenshots</td><td>real testing, DEV deploy</td></tr> <tr><td>How to choose</td><td colspan="2"><code>…/#/ops/paReviewAudit?source=api</code> or <code>?source=snapshot</code>; header badge shows which</td></tr> </table></div> </div> <div> <h3>Why it works on localhost</h3> <ul> <li>AIDEA-UI decides its environment from the hostname. <code>localhost.ms.com</code> = <b>LOCAL</b>, so <code>API_URL = http://localhost:8000</code>.</li> <li>The SSH tunnel makes Linux ports 4200 and 8000 appear as <code>localhost</code> on Windows, so both addresses resolve.</li> <li>The backend's <code>auth_middleware</code> treats localhost requests as <code>local_dev</code>, so no SSO is needed locally.</li> <li>The backend reads Snowflake with its own configured connection; the browser never sees credentials.</li> </ul> </div> </div> <h3>Start-up checklist (Linux host)</h3> <pre># once per shell (or put in ~/.bashrc) module load &lt;node 20.19+ module&gt; ; module load &lt;python 3.12 module&gt;

terminal 1 (inside tmux): backend

cd ~/aidea-backend/aidea && source <venv>/bin/activate SYS_ENVIRONMENT=dev SYS_LOC=dev python -m uvicorn main:app --host 127.0.0.1 --port 8000 --workers 1

terminal 2 (inside tmux): UI (npm ci only the first time)

cd ~/AIDEA-UI && npm start

Windows PowerShell: tunnel (keep open)

ssh -L 4200:localhost:4200 -L 8000:localhost:8000 <user>@<linux-host>

Windows browser

http://localhost.ms.com:4200/#/ops/paReviewAudit?source=api http://localhost:8000/va/pa/health # quick backend check http://localhost:8000/docs # Swagger: all /va/pa routes</pre>

</section><!-- 6 --> <section id="s6"> <h2><span class="n">6</span>Code map: what was added where</h2> <div class="cols"> <div> <h3>AIDEA-UI · <code>feature/pa-review-audit</code></h3> <pre class="tree">src/app/useCase/ ├─ useCase.routes.ts + route 'paReviewAudit' ├─ (nav config) + "PA Review Audit" item └─ paReviewAudit/ ├─ models/ TypeScript shapes per tab ├─ services/ PaReviewAuditService │ (snapshot | api switch) ├─ pa-review-audit page header, KPIs, tabs └─ tabs/ ├─ summary/ ├─ eligibility/ Highcharts waterfall ├─ reconcile/ donut: BOTH / METRIC / OURS ├─ hops/ IDNDATA + IDNREVIEW, RP selector ├─ findings/ cards → drawer, sample rows └─ query dialog "&lt;/&gt; Query" (SQL + copy) src/assets/data-dump/pa-review-audit/ snapshot JSON</pre> </div> <div> <h3>aidea-backend · <code>feature/pa-review-audit</code></h3> <pre class="tree">aidea/ ├─ server.py + include_router(prefix="/va/pa") ├─ routers/ │ └─ paReviewAuditRouter.py 9 GET routes, require_login ├─ services/pa_review_audit/ │ ├─ __init__.py │ ├─ sql_queries.py fixed SELECTs, 60s timeout, │ │ DETAIL_QUERIES per finding │ └─ service.py get_connection(), bind params, │ masking, envelope ├─ data_service/schemas.py + Pydantic response models ├─ config/…inference_config + VA_SCHEMA = IAMARW.AIDEA ├─ test/test_paReviewAuditRouter.py └─ docs/PA-REVIEW-AUDIT-HANDOFF.md</pre> </div> </div> <p><b>Reused, not reinvented:</b> the <code>/ops</code> shell, HttpClient interceptor and theme of AIDEA-UI; the Snowflake connection pool, app role, auth middleware and router pattern (copied from the TCM reviewer) of aidea-backend; the IDNREVIEW join keys from <code>rp_query_engine.py</code>.</p> </section><!-- 7 --> <section id="s7"> <h2><span class="n">7</span>API reference: <code>/va/pa</code></h2> <div class="tw"><table> <tr><th>GET</th><th>Reads</th><th>Feeds</th></tr> <tr><td><code>/health</code></td><td>no Snowflake; environment + schema</td><td>connection check</td></tr> <tr><td><code>/summary</code></td><td><code>VA_PA_SUMMARY_V</code></td><td>header KPIs</td></tr> <tr><td><code>/waterfall</code></td><td><code>VA_PA_WATERFALL_SUMMARY</code></td><td>Eligibility tab</td></tr> <tr><td><code>/recon</code></td><td><code>VA_PA_RECON</code> (grouped)</td><td>Reconcile tab</td></tr> <tr><td><code>/hop</code></td><td><code>VA_PA_HOP_IDNDATA</code></td><td>Hops tab: IDNDATA</td></tr> <tr><td><code>/hop-review?rp_id=</code></td><td><code>VA_PA_HOP_IDNREVIEW</code></td><td>Hops tab: campaign</td></tr> <tr><td><code>/findings</code></td><td><code>VA_FINDINGS</code></td><td>Findings cards</td></tr> <tr><td><code>/findings/{id}/rows?limit=</code></td><td>fixed detail query for that finding (max 100, masked)</td><td>Finding drawer</td></tr> <tr><td><code>/runs</code></td><td><code>VA_RUN_LOG</code></td><td>"data as of", audit trail</td></tr> </table></div> </section><!-- 8 --> <section id="s8"> <h2><span class="n">8</span>Security and guardrails</h2> <div class="grid"> <div class="kpi ok"><b style="font-size:16px">No credentials in the UI</b><span>The browser only talks to our API. Snowflake access stays in the backend's existing pool.</span></div> <div class="kpi ok"><b style="font-size:16px">Read-only</b><span>GET routes only; no POST/PUT/DELETE; no auto-commit helper used.</span></div> <div class="kpi ok"><b style="font-size:16px">No dynamic SQL</b><span>Fixed queries in code with bound parameters; SQL text in VA_FINDINGS is displayed, never executed.</span></div> <div class="kpi ok"><b style="font-size:16px">Masked people</b><span>IDs / names shown as first 2 characters + ***; sample rows capped at 100.</span></div> <div class="kpi ok"><b style="font-size:16px">Small tables</b><span>UI reads precomputed results, not the multi-million-row sources.</span></div> <div class="kpi cand"><b style="font-size:16px">DEV only</b><span>VA_* tables live in DEV; UAT/PROD need the deployment pipeline.</span></div> </div> </section><!-- 9 --> <section id="s9"> <h2><span class="n">9</span>Status and next steps</h2> <div class="tw"><table> <tr><th>Step</th><th>What</th><th>Status</th></tr> <tr><td>Data</td><td>VA_* tables built, R2 split, G3 relabelled INFO, timing split + G8, grants, summary view, export</td><td><span class="tag t-done">done</span></td></tr> <tr><td>UI</td><td>Five tabs with real DEV numbers (snapshot), nav label fixed</td><td><span class="tag t-done">done</span></td></tr> <tr><td>Run</td><td>Node/Python on Linux host, both apps running, viewed from Windows</td><td><span class="tag t-done">done</span></td></tr> <tr><td>Connect</td><td>UI calls the backend on :8000 (live mode)</td><td><span class="tag t-done">done</span></td></tr> <tr><td>Prompt D</td><td>Backend returns the exact field names the screens expect; detail queries G1, G4, G5, G7, G8; pagination + filters on detail endpoints</td><td><span class="tag t-next">next</span></td></tr> <tr><td>Verify</td><td>Every tab: live numbers = snapshot numbers; screenshots of all five tabs</td><td><span class="tag t-next">next</span></td></tr> <tr><td>Align</td><td>Same app as Mulugeta's live AC02 screens: one AIDEA-UI, one backend, separate <code>/ops</code> routes</td><td><span class="tag t-next">next</span></td></tr> <tr><td>Deploy</td><td>PRs for both repos → DEV at <code>aidea-ui.webfarm-dev.ms.com/#/ops/paReviewAudit</code></td><td><span class="tag t-later">later</span></td></tr> <tr><td>Promote</td><td>VA_* DDL through the pipeline; nightly refresh; UAT/PROD campaigns</td><td><span class="tag t-later">later</span></td></tr> </table></div><h3>After deployment (target picture)</h3> <div class="diagram"> <svg viewBox="0 0 1000 150" role="img" aria-label="DEV deployment"> <defs><marker id="a4" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0,0 L10,5 L0,10 z" class="ah"/></marker></defs> <rect x="10" y="35" width="190" height="80" rx="10" class="node-b"/> <text x="105" y="65" text-anchor="middle" class="t-m" font-weight="700">Any MS browser</text> <text x="105" y="87" text-anchor="middle" class="t-s muted">SSO login</text> <rect x="250" y="35" width="230" height="80" rx="10" class="node-b"/> <text x="365" y="60" text-anchor="middle" class="t-m" font-weight="700">aidea-ui.webfarm-dev.ms.com</text> <text x="365" y="82" text-anchor="middle" class="t-s">/#/ops/paReviewAudit</text> <text x="365" y="100" text-anchor="middle" class="t-s muted">built Angular files</text> <rect x="530" y="35" width="230" height="80" rx="10" class="node-b"/> <text x="645" y="60" text-anchor="middle" class="t-m" font-weight="700">aidea-backend (DEV)</text> <text x="645" y="82" text-anchor="middle" class="t-s">/api/va/pa/*</text> <text x="645" y="100" text-anchor="middle" class="t-s muted">service user aidea_d · app role</text> <rect x="810" y="35" width="180" height="80" rx="10" class="node-g"/> <text x="900" y="65" text-anchor="middle" class="t-m" font-weight="700">Snowflake</text> <text x="900" y="87" text-anchor="middle" class="t-s">IAMARW.AIDEA.VA_*</text> <line x1="200" y1="75" x2="247" y2="75" class="arrow" marker-end="url(#a4)"/> <line x1="480" y1="75" x2="527" y2="75" class="arrow" marker-end="url(#a4)"/> <line x1="760" y1="75" x2="807" y2="75" class="arrow" marker-end="url(#a4)"/> </svg> </div> <div class="note">Same code, no tunnel: the UI detects the DEV hostname and calls <code>/api/va/pa/*</code> on the same domain; the backend uses the DEV service user, which already has SELECT on the VA_* tables (granted in Prompt B).</div> </section> </main> </body> </html>