/* 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 & 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 & 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, "</> 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 = 'Y';</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 = 'Y' OR b.IS_FULFILLED <> 'Y' THEN 'R1_NOT_ACTIVE' WHEN b.EMPLOYEE_STATUS = 'T' THEN 'R2A_TERMINATED' WHEN b.EMPLOYEE_STATUS IS NULL THEN 'R2B_NO_PERSON_RECORD' WHEN b.PRINCIPAL_TYPE_ID <> 2 THEN 'R3_NOT_INDIVIDUAL' WHEN b.ENT_EXCL_CODE <> 0 OR b.MODEL_EXCL_IND = 'Y' THEN 'R5_EXCLUDED' WHEN sts.IS_LIVE = 'N' THEN 'R6_ACCESS_NOT_LIVE' WHEN b.ENVIRONMENT_CD <> 'PROD' THEN 'R8_NON_PROD' WHEN b.TIER = 0 THEN 'R11_TIER0' ELSE 'ELIGIBLE' 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 = 'MODEL' AND sts.LABEL = 'ENTITLEMENT';</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,'R1_NOT_ACTIVE', 'IS_REVOKED=''Y'' OR IS_FULFILLED<>''Y'''), (2,'R2A_TERMINATED', 'EMPLOYEE_STATUS=''T'''), (3,'R2B_NO_PERSON_RECORD','EMPLOYEE_STATUS IS NULL'), (4,'R3_NOT_INDIVIDUAL','PRINCIPAL_TYPE_ID<>2'), (5,'R5_EXCLUDED', 'ENT_EXCL_CODE<>0 OR MODEL_EXCL_IND=''Y'''), (6,'R6_ACCESS_NOT_LIVE','SOURCE_TYPE_STATUS.IS_LIVE=''N'''), (8,'R8_NON_PROD', 'ENVIRONMENT_CD<>''PROD'''), (11,'R11_TIER0', 'TIER=0')) 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 = 'ELIGIBLE'), 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 = 'Y' 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 'BOTH' WHEN o.ENTITLEMENT_ID IS NOT NULL THEN 'OURS_ONLY' ELSE 'METRIC_ONLY' 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 = 'ELIGIBLE') AS ACTIVE_REVIEWABLE_ASSIGNMENTS FROM IAMARW.AIDEA.VA_PA_WATERFALL GROUP BY MODEL_ID), i AS ( SELECT MODEL_ID, MAX(IFF(REVIEW_SCOPE LIKE '%||PA||%', 'Y', 'N')) AS STAMPED_PA FROM IAMARW.IDNDATA.ENTITLEMENT WHERE TRANSACT_TO = '9999-12-30' -- current row GROUP BY MODEL_ID) SELECT m.MODEL_ID, m.CATEGORY, m.ACTIVE_REVIEWABLE_ASSIGNMENTS, IFF(i.MODEL_ID IS NULL, 'N', 'Y') AS IN_IDNDATA, COALESCE(i.STAMPED_PA, 'N') AS STAMPED_PA, CASE WHEN i.MODEL_ID IS NULL AND m.ACTIVE_REVIEWABLE_ASSIGNMENTS = 0 THEN 'MISSING_NO_ACTIVE' WHEN i.MODEL_ID IS NULL THEN 'MISSING_FROM_IDNDATA' WHEN i.STAMPED_PA = 'N' THEN 'NOT_STAMPED' ELSE 'OK' 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 = 'ELIGIBLE'), idn AS ( SELECT USER_ENTITLEMENT_ID, ACCOUNT_ID FROM IAMARW.IDNDATA.ACCOUNT_USER_ENTITLEMENT WHERE TRANSACT_TO = '9999-12-30'), 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, 'N', 'Y') AS IN_IDNDATA, IFF(ri.RP_ID IS NULL, 'N', 'Y') AS IN_CAMPAIGN, ri.DECISION, ri.DECISION_TAKEN_ON, ri.COVERAGE_SOURCE, CASE WHEN e.GRANT_DATE IS NULL THEN 'UNKNOWN' WHEN e.GRANT_DATE > ll.L THEN 'Y' ELSE 'N' END AS GRANTED_AFTER_IDNDATA_LOAD, CASE WHEN idn.USER_ENTITLEMENT_ID IS NULL THEN 'NOT_IN_IDNDATA' WHEN e.GRANT_DATE > c.COVERAGE_COLLECTED_ON THEN 'GRANTED_AFTER_SCOPING' WHEN ri.RP_ID IS NULL THEN 'MISSING_FROM_CAMPAIGN' WHEN COALESCE(ri.DECISION,'NONE') = 'NONE' THEN 'IN_CAMPAIGN_PENDING' ELSE 'DECIDED' 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='Not Available', 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='ELIGIBLE') AS ELIGIBLE_OURS, (SELECT COUNT_IF(BUCKET IN ('BOTH','METRIC_ONLY')) FROM IAMARW.AIDEA.VA_PA_RECON) AS ELIGIBLE_METRIC, (SELECT COUNT_IF(BUCKET='BOTH') FROM IAMARW.AIDEA.VA_PA_RECON) AS BOTH, (SELECT COUNT_IF(BUCKET='OURS_ONLY') FROM IAMARW.AIDEA.VA_PA_RECON) AS OURS_ONLY, (SELECT COUNT_IF(BUCKET='METRIC_ONLY') 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='R11_TIER0') 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's role owns the tables, so the backend'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 > 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 + "</> 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 "</> 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 <node 20.19+ module> ; module load <python 3.12 module>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
cd ~/AIDEA-UI && npm start
ssh -L 4200:localhost:4200 -L 8000:localhost:8000 <user>@<linux-host>
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 "</> 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>