Twelve systems, rebuilt here so you can click them.

Architecture, what was hard, what was done about it, and what shipped. Written for the person who has to decide whether to rebuild something.

The law-firm cases were delivered in-house by our principal as platform architect for a 2,000-user US firm and are described at the pattern level only: no client names, data, identifiers or internal systems. Cases marked composite are representative builds from that engagement type, with agencies, programs and figures changed. Every replica is synthetic.

Power Apps code apps

React and TypeScript on the Power Apps SDK, over live SQL Server and SharePoint, with the flows and security around them. Production systems at a 2,000-user law firm, described at the pattern level.

Year-end collections forecastingCode app · Law firm · 2026 Reporting request intakeCode app · Law firm · 2026 Annual partner certificationCode app · Law firm · 2026 Power BI permission audit and access lookupCode app + PowerShell · Law firm · 2026

Year-end collections forecasting

A code app over live finance data, with a campaign engine the business runs itself.

The situation

A 2,000-user law firm needed several hundred partners to enter net-collection estimates for the year-end push, against inventory figures that finance rebuilds nightly, with rollups for executives by department and practice group and a reminder campaign that tracked who had responded. It had been spreadsheets and email.

What was built

A Power Apps code app in React and TypeScript on the Power Apps SDK, reading and appending directly to an on-premises SQL Server through the data gateway. Three SharePoint lists hold campaign settings, a send log and a command queue. Two Power Automate flows do all the sending: a command flow that fires when the app writes a command row, and a daily engine that reads the settings and handles cadence reminders, the final-notice window and a weekday digest to leadership. Access is a firm-managed Entra security group; the mobile story is an Intune web-link icon, because code apps don't run in the mobile player. One source tree is deployed to three environments, with an environment chip in the header built from the folder's own config so nobody mistakes test for production.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://collections · synthetic dataOpen full size ↗

A working replica of Collections Forecast, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system.

Stack

Power Apps code app (React 19, TypeScript, Vite, Power Apps SDK)
SQL Server via on-premises data gateway
SharePoint Online
Office 365 Users
Power Automate (two flows)
Office 365 Outlook
Entra ID security group
Intune managed web link
Dev, Test and Prod environments, pay-as-you-go billing

What was hard, and what was done about it

The SQL connector is not a database driver

In this SDK version it pages silently, in practice caps filter trees at about 100 nodes, has no COUNT, GROUP BY or DISTINCT, works only with single-column keys, and its data-source binding refused one complex view. Every read walks a keyset. Long IN lists are chunked into ten ORed comparisons. Aggregation happens client-side. The estimates table got a surrogate identity key. The flows read that view through the same connector without trouble, so they handle the audience query.

Code apps cannot call a flow

At build time the SDK had no Power Automate connector and the app has no user token for Graph. A custom connector could wrap an HTTP-triggered flow, but that is one more endpoint to secure and one more component to deploy per environment, for one job. The pattern chosen: the app writes a command row to a list and a flow triggers on it. Every send in the system is a row first, which also gives an audit trail for free.

Nothing may leak while testing

Test mode is one setting. When it's on, every partner-facing send is redirected to the developer's inbox with a prefix and a cap of one. The campaign was rehearsed end to end against real data without a partner seeing it.

Workflow expressions have opinions

Numerics from SQL must be cast or comparisons silently fail. The leadership digest's 'new since last run' cut compared a local timestamp to UTC and missed records; the fix was explicit Eastern conversion and a 72-hour window on Mondays. Long expression-bearing HTML is kept in Compose actions, because the designer reflows it.

Phones

iOS Safari zooms on any control under 16px and never zooms back, and the desktop emulator won't show it. Inputs are 16px under pointer: coarse, layouts use 100dvh, and the home-screen icon is rendered from one master for both platforms' safe areas.

Knowing who actually uses it

The platform's analytics do not cover code apps by user or device, so the app logs its own usage to a SharePoint list: launch, landing, screen opened and estimate written, with device class, operating system, browser, viewport, how the person arrived (icon, email, digest, direct), the build stamp and the platform's own time-to-interactive. Labels only, never a figure. Writes are fire-and-forget and a failure is swallowed, so nothing on the partner path waits on the log. A dashboard in the app draws it as hand-built SVG, every chart with a table twin, and the feature is gated per environment by a flag so the same source stays silent in production until it is switched on.

Security and governance

Four tiers see four different slices: administrators, executives, department heads, and the people under them. Row access is derived from identity columns on the data itself, filtered server-side and confirmed on the client. The SQL connection is shared, so this is a visibility rule for low-sensitivity projection data, not a security boundary; anything that had to be enforced against a determined user would sit in Dataverse security roles or a per-role SQL view. An in-code admin allowlist outranks the role table. Recording an estimate on someone's behalf re-derives authority on every write and fails closed; previewing as a partner severs writes entirely. Every important write reads the row back by ID before the UI says it saved.

Outcome

The campaign system is live and administrators run it from an email center inside the app. Pay-as-you-go billing was proven with a controlled test before anyone committed: one unlicensed user, one launch per app, exactly the rows predicted on the next month's report.

Reporting request intake

The first code app, and the one every later app was cloned from.

The situation

A finance reporting team took requests through a canvas app over a SharePoint list. It was slow, had no attachments, no admin view, and the history of who asked for what lived in people's heads.

What was built

A code app with a request form, a people picker for sponsors and assignees, an attachment drop zone, and an admin dashboard with KPIs and CSV export. A notification flow sends a branded email with a deep link back to the request. The legacy list was migrated with PnP PowerShell and then backfilled twice so every record kept its real submitter and its original submission date rather than the date it was migrated.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://intake · synthetic dataOpen full size ↗

A working replica of Reporting Requests, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system.

Stack

Power Apps code app
SharePoint Online
Office 365 Users
Power Automate
PnP PowerShell on PowerShell 7
Entra app registration (delegated, write-scoped)

What was hard, and what was done about it

Attachments drop their bodies

The SDK's typed attachment call discarded binary content. A node_modules patch, persisted with patch-package so it survives every install, fixed it; the patch is pinned to the SDK version it targets and retires at the first version that fixes the bug. App-side cap 10 MB; connector cap 90 MB.

'Created By' is always the service account

Every item is written by a service account, so the system author field can't identify the requester. A Submitter person field is the real key, and users are matched on UPN, SMTP address and bare login, because different columns store different ones.

SharePoint column types have exact shapes

Choice columns want an object, not a string; a string is dropped without an error. Person columns want the full expanded user with a claims string. Flipping a person column to multi-select changes its shape from one record to a table and silently breaks every existing read and write; the fix is a new column, a scripted backfill, and the old column kept as a fallback until nothing reads it.

Deep links come from the SDK, not the URL

window.location shows the player, not the app. Parameters are read from the SDK context, and three parameter names are reserved by the player.

Migration scripts and stale objects

A list item's field values are invalidated after the query executes. Snapshot them into plain objects first, then update. That lesson is in every backfill script since.

Security and governance

Admin gating from a list of administrators, service-account writes, and an app registration deliberately capped at item-write scope: it can create, update and delete items, and cannot create a list or column even for a site owner. Lists are created in the UI; scripts fill them.

Outcome

In production, and the business owner declared it finished and froze it, which is the best outcome an intake app can have. Its skeleton became the starter template for every app that followed.

Annual partner certification

One-click certify by email, with an executive admin portal behind it.

The situation

Once a year every partner completes an annual compliance acknowledgment. The old canvas app emailed a link and wrote a row. The rebuild keeps that one-click path and adds what executives actually asked for: who has certified, by department, recent activity, the email content, send and schedule, and recording on someone's behalf.

What was built

A code app whose boot runs the admin check, the acknowledgment check and the roster check in parallel; admins land on the portal, everyone else lands on the certify path, and the email deep link always takes the certify path. Six SharePoint lists: status, roster, administrators, email content, scheduled sends, settings. Three flows: a send engine every ten minutes, a confirmation on each acknowledgment, and a weekday digest inside the campaign window. Flow definitions are built by scripts and deployed with dated baselines.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://certification · synthetic dataOpen full size ↗

A working replica of Annual Certification, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system.

Stack

Power Apps code app
SharePoint Online
Office 365 Users
Power Automate (three flows)
Office 365 Outlook

What was hard, and what was done about it

Duplicate prevention keyed on the right field

Keyed on the acknowledgment person field, not the system author, for the same service-account reason as the intake app. When an admin records on someone's behalf, a separate field carries the admin and the confirmation email says so and copies them.

A send engine that cannot over-send

Each scheduled row is checked again at send time and cancelled if the recipient already certified this year. In test mode the engine samples at most three rows to the developer and cancels the rest, so nothing can leak.

Admin-authored email bodies

Administrators compose the campaign email in the app with a live preview. Whether to sanitize or constrain the HTML they can write is a deliberate open decision, not an accident.

Security and governance

Roster-driven eligibility: the roster list is the denominator for the dashboard, the audience for the campaign, and the gate for who can certify. Admin gating from the shared administrators list.

Outcome

In test mode ahead of the deadline, with the full campaign loop verified end to end: on-behalf entry, roster eligibility, confirmations copying the admin, and branded templates.

Power BI permission audit and access lookup

Who can see this report? Nobody could answer that from the portal, so now a script can.

The situation

A reporting owner asked a simple question: type a name, see which Power BI app audiences and pages that person can reach. The Power BI REST API doesn't expose app audiences, and group membership hides the real answer behind nested groups.

What was built

Two pieces. A read-only audit in PowerShell 7 that runs as a service principal: it calls the Power BI metadata scanner for every report, semantic model and dashboard in a workspace, pulls row-level-security roles, separates direct from group access, and expands every group to actual people through Microsoft Graph. Output is a nine-tab workbook, plus a preflight script that prints PASS or FAIL against tenant settings before anything runs. And a small code app for the lookup itself, with the audit's index embedded in the bundle.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://access-lookup · synthetic dataOpen full size ↗

A working replica of Access Lookup, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system.

Stack

PowerShell 7
Power BI Metadata Scanner API
Microsoft Graph
Entra app registration with client secret, read-only
Power Apps code app
Office 365 Users

What was hard, and what was done about it

The access needed a change request, so it got one

The scanner needs a tenant admin setting that allows service principals to use read-only admin APIs, scoped to a group containing the principal, plus one Graph application permission. Both went through the firm's change board with the purpose, the read-only scope and the identity spelled out, and were approved before the first run.

The player won't serve a JSON file

A standalone data asset returns 404 inside the Power Apps player. The 4 MB index is compiled into the JavaScript bundle as a string. The folder has a note explaining why, so the next person doesn't spend a day on it.

People are spelled three ways

Source data has 'Last, First', email local parts and bare tokens. A normalizer handles all three before matching.

Security and governance

Read-only by construction. As a service principal the registration holds only read application permissions and runs only the audit; the scanner APIs are authorised by the tenant setting for a security group, not by a scope. The outputs are the audit trail.

Outcome

Working. Outputs exist for the production reporting workspace, and the question that started it can be answered in a search box.

Canvas apps

Apps for people in the field and forms that replaced InfoPath, from federal and public-sector engagements. Composite builds: the patterns and problems are real, the agencies and figures are changed.

Site-visit capture for a federal grant oversight programCanvas app · Composite · Big Four federal advisory work Field inspections for a state transportation agencyCanvas app · Composite · public-sector work at a global engineering firm Retiring InfoPath for a DHS componentCanvas + model-driven · Composite · Big Four federal advisory work

Site-visit capture for a federal grant oversight program

A canvas app that works in a basement with no signal, and a checklist the program office edits itself.

The situation

A federal program office sent reviewers to grantee sites across several regions. Findings came back on paper checklists, photos arrived by email, and it took about three weeks for a visit to show up in the tracking spreadsheet leadership actually looked at. Corrective actions were followed up by memory.

What was built

A canvas app for phone and tablet, built offline-first. The reviewer downloads their assigned visits and the checklist before leaving the office; the app saves every answer, photo and GPS fix locally and syncs when it sees a network again. The checklist itself is a SharePoint configuration list, so program staff add or reword a question without a developer. Photos land in a document library with the visit, question and timestamp as metadata. Submitting a visit starts a Power Automate approval to the regional supervisor; an approved visit with findings creates corrective-action items with due dates. A Power BI report gives leadership findings by region and program, open corrective actions, and days-to-close.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://site-visits · synthetic dataOpen full size ↗

A working replica of Site Visit Capture, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system. Composite example: a representative build from that engagement type, with agencies, programs and figures changed.

Stack

Power Apps canvas (phone + tablet layouts)
SaveData / LoadData offline pattern
Component library
SharePoint Online lists and library
Power Automate approvals
Power BI
Section 508 accessibility review
GCC tenant

What was hard, and what was done about it

Delegation is the first wall every canvas app hits

Lists blew past the 2,000-row cap on non-delegable queries within a year and the 5,000-item view threshold not long after. Every gallery filter was rewritten against delegable operators, the big lists got indexed columns, lookups were moved to the server side, and anything that had to be non-delegable ran only on the reviewer's own pre-filtered slice. The delegation warning is treated as a build error, not a hint.

Offline means conflicts

Two reviewers can touch the same visit. The app stamps each local record with the item's version at download; before each sync it re-reads the item and, if the version moved, stops and shows the reviewer exactly which answers differ. SharePoint is last-write-wins, so the check lives in the sync step. The sync queue retries with backoff and never drops a record silently, because a lost finding is worse than a duplicate.

Photos are heavy and the camera control is blunt

Full-resolution photos blew through mobile data and SharePoint quotas. The camera control is set to a sane resolution, images are uploaded to the library with the visit, question and timestamp as metadata, and the app shows a thumbnail from local cache until the upload confirms.

Federal means accessible

Section 508 was a gate, not a nice-to-have. Tab order, accessible labels on every control, contrast ratios, and no information carried by color alone. The component library made that consistent instead of per-screen.

GCC is not commercial

Not every connector exists in the government cloud and some arrive late. The design was checked against the GCC connector list before a line was built, which ruled out one shortcut and saved a rebuild.

Security and governance

Reviewers see only the visits assigned to their region, enforced by item-level permissions the flow sets on creation (the region filter only keeps the download small), kept well under the unique-permissions ceiling by archiving closed visits yearly, not by hiding things in the UI. Supervisors approve only their region. Leadership gets the report, not the lists.

Outcome

Visits appeared in the leadership report the same day instead of three weeks later. Corrective actions got owners and due dates. The program office changed the checklist twice in the first quarter without a change request.

Field inspections for a state transportation agency

Tablets in trucks, forty thousand assets, and work orders that create themselves.

The situation

Inspectors rated culverts, signs and pavement segments on paper, photographed them on personal phones, and typed it all in days later. Photos went missing, ratings drifted between crews, and maintenance found out about a failed culvert when it flooded.

What was built

A tablet canvas app over Dataverse for the asset registry, built by a three-developer team our principal led. The inspector scans or searches the asset tag, sees its history and last photos, rates condition on a 1 to 5 scale with written anchors for each number, adds photos and a GPS fix, and flags defects. A rating at or below 2 creates a work order for the maintenance district through a flow, with the photos attached and a Teams post to the district channel. The app works offline by district: the inspector caches their district's assets at the yard in the morning. A component library kept the three developers' screens identical.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://inspections · synthetic dataOpen full size ↗

A working replica of Field Inspections, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system. Composite example: a representative build from that engagement type, with agencies, programs and figures changed.

Stack

Power Apps canvas (tablet layout, responsive containers)
Dataverse asset registry
Barcode reader control
Offline cache per district
Component library
Power Automate
Microsoft Teams
SharePoint library for photos
Three-developer Agile team

What was hard, and what was done about it

Forty thousand assets do not fit in a gallery

Everything is a server-side, delegable query against Dataverse views, filtered by district first. The offline cache is the inspector's district only, refreshed at the yard, and the app shows how old the cache is.

Ratings drift unless the scale is on the screen

Each number on the 1 to 5 scale has a one-line written anchor and an example photo, visible while rating. Supervisors review a sample weekly; disagreement rate is a metric on the dashboard, not a feeling.

Three developers, one app

Shared component library, naming conventions, a screen-ownership map and a daily merge rhythm. Canvas apps do not merge like code, so the team worked on separate screens and integrated on a schedule.

The camera and the barcode control have opinions

Asset tags were weathered. The scanner control is a first attempt; search by tag number is always one tap away. Photos are resized before upload, named by asset and timestamp, and the thumbnail comes from local cache.

Training field crews is part of the build

Two-screen flows, big targets, high contrast for sunlight, and nothing that needs a keyboard in a truck. The pilot district's feedback changed the rating screen twice before rollout.

Security and governance

Inspectors write inspections; they do not edit the asset registry. Maintenance edits work orders; it does not touch inspections. Dataverse roles per team, and the work-order flow runs as a service account.

Outcome

Inspections entered the system the day they happened. Work orders for failing assets were created without anyone typing them. The agency rolled the app from the pilot district to all districts within the year.

Retiring InfoPath for a DHS component

A hundred-odd forms, a triage rule for each, and the repeating table everyone was afraid of.

The situation

A DHS component ran its daily business on InfoPath forms and SharePoint Designer workflows: requests, certifications, inspections, travel, training. InfoPath was end of life, the workflows broke on every SharePoint upgrade, and nobody had a list of what existed. The ask was a modernization program, not an app.

What was built

An inventory first: every form library crawled, each template opened, its rules, data connections, repeating sections, signatures and workflow steps counted into a complexity score. Then a triage rule applied to each form, agreed with the business owners before a line was built: simple capture went to Microsoft Lists with a customized form, anything with branching rules, repeating tables or approvals became a canvas app over SharePoint, and the handful with multiple related entities, role-based stages and audit needs became model-driven apps on Dataverse. SharePoint Designer workflows were rebuilt as Power Automate flows. A shared component library gave every rebuilt form the same header, people picker, repeating-row control and acknowledgment block. Historical form XML was migrated into list columns so the old submissions stayed searchable without InfoPath.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://forms · synthetic dataOpen full size ↗

A working replica of Forms Modernization, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system. Composite example: a representative build from that engagement type, with agencies, programs and figures changed.

Stack

InfoPath template analysis (XSN, rules, data connections)
Microsoft Lists customized forms
Power Apps canvas (tablet layouts)
Power Apps model-driven on Dataverse
Power Automate (SharePoint Designer workflow replacement)
Component library
PnP PowerShell for inventory and XML migration
Section 508
GCC tenant

What was hard, and what was done about it

Nobody knew how many forms there were

The crawl found more than twice the number the business estimated, including dozens with no submissions in two years. Those were retired with the owner's signature, not rebuilt. The inventory became the program's backlog and its status report.

Repeating tables

InfoPath's repeating table is the feature canvas apps do not have. The pattern that held up: a gallery bound to a collection with add, edit and remove rows, patched to a child list on submit with the parent ID, and a component so every form did it the same way. Nested repeating sections were the trigger to move a form to model-driven.

Rules do not port

InfoPath rules are declarative and tangled. Each form's rules were rewritten as a short decision table reviewed by the owner before build, which caught logic that had silently stopped working years earlier.

Digital signatures and audit

Signed InfoPath forms had legal weight. The replacement is an acknowledgment block capturing the signed-in identity, a timestamp and a snapshot of the submitted values in a locked field, with versioning on and a flow that makes the item read-only after sign-off. Legal review of the approach was a release gate.

Old submissions still matter

Thousands of historical XML submissions were parsed with PnP PowerShell into list columns, with the original XML kept as an attachment, so records requests could be answered without an InfoPath client.

Security and governance

Each rebuilt form inherited the permission model of the library it replaced, then tightened where a form had been readable by everyone by accident. Approvals run as a service account; owners approve from their own identity. Section 508 review on every form before release, and the whole program lived inside the GCC tenant's connector allowlist.

Outcome

InfoPath was gone from the component ahead of the end-of-support date. The triage rule and the component library were reused on the next engagement, which is the point of a program rather than a pile of apps.

Model-driven apps on Dataverse

For people running a process: a real data model, business process flows, security roles and auditing. Composite builds from federal and county work.

Grant lifecycle management on DataverseModel-driven app · Composite · Big Four federal advisory work Capital project change requests and approvalsModel-driven app · Composite · public-sector work at a global engineering firm

Grant lifecycle management on Dataverse

One model-driven app from application to closeout, with the business process on the screen.

The situation

A grant program ran on an aging Access database, a shared drive of award letters and a spreadsheet per program officer. Nobody could answer "what stage is this award in" without a phone call, and audit requests meant reconstructing history from email.

What was built

A model-driven app on Dataverse with a real data model: Applicant, Application, Award, Milestone, Disbursement, and Site Visit. A business process flow walks each award through Intake, Eligibility review, Award, Monitoring and Closeout, with required steps on each stage so a record cannot advance half-filled, and business rules for the field-level logic inside a stage. Power Automate generates award letters from Word templates and routes approvals; auditing is on for every table that matters. A Power BI report is embedded in the app for program leadership. The whole thing ships as a managed solution with environment variables through Dev, Test and Prod.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://grants · synthetic dataOpen full size ↗

A working replica of Grants Management, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system. Composite example: a representative build from that engagement type, with agencies, programs and figures changed.

Stack

Power Apps model-driven
Dataverse (tables, relationships, business rules, auditing)
Business process flow (5 stages)
Security roles, business units, field-level security
Power Automate with Word templates
Power BI embedded
Managed solutions, environment variables

What was hard, and what was done about it

The migration was the project

Years of Access and Excel data had duplicate applicants spelled three ways and awards keyed on nothing stable. A matching pass on tax ID, then normalized name plus address, produced a merge list that program staff reviewed before load. Every migrated row carries its source and original ID so an audit can trace it back.

Security roles are a design artifact, not a checkbox

Program officers edit their own region's records, grants management edits everything financial, leadership reads all. That is business units per region, roles with user-versus-business-unit depth per privilege, and field-level security on banking fields so even an editor cannot see an account number without the profile. Written down as a matrix first, then built.

A business process flow is not a workflow

The stage bar shows where an award is; it does not do anything. Approvals, letters and notifications live in flows triggered by stage changes, so the process can change without rebuilding the BPF.

Forms got slow before they got finished

The award form started with every related subgrid on one tab. Moving subgrids to their own tabs so they load on demand, and trimming columns on views, cut load time to something a program officer would tolerate.

Audit is a feature

Auditing was enabled table by table with a retention plan, and audit history is one click from the form (Related › Audit history), so an audit request is a screen instead of an archaeology project.

Security and governance

Business units per region, roles with explicit privilege depth, field-level security on financial fields, and Dataverse auditing on. Service accounts own the flows. Nothing is enforced only in a form script.

Outcome

One system of record for the program. Closeout paperwork that took weeks to assemble became a report. The first audit request after go-live was answered from audit history.

Capital project change requests and approvals

Amount-based routing, approvers who go on vacation, and a budget line that two people cannot both spend.

The situation

A county public works department approved capital project change orders by email chain. Thresholds were tribal knowledge, approvers were sometimes out, and a budget line could be committed twice before anyone noticed. Audit asked for the approval trail and got a PST file.

What was built

A model-driven app on Dataverse: Project, Budget Line, Change Request, Approval Step, Vendor, Routing Rule and Delegation. A business process flow takes a change request from Draft through project-manager review, program director and sponsor approval to Executed. Routing is amount-based: under one threshold the director signs off, above it the sponsor, above the next the board packet is generated. Approvals arrive as adaptive cards in Teams and email, with a delegation table so an approver's deputy can act while they are out. Every decision is a row with who, when and why. A Power BI report shows budget variance by project and the approval queue by age.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://change-requests · synthetic dataOpen full size ↗

A working replica of Capital Change Requests, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system. Composite example: a representative build from that engagement type, with agencies, programs and figures changed.

Stack

Power Apps model-driven
Dataverse with auditing
Business process flow (5 stages)
Power Automate approvals, adaptive cards
Microsoft Teams
Word templates for board packets
Power BI
Pipelines for Dev, Test, Prod

What was hard, and what was done about it

Thresholds change and approvers leave

Routing rules live in a configuration table, not in the flow, so finance changes a threshold without a deployment. Delegation is a table too: who covers whom, from when to when, and the approval card says who actually clicked.

Two people committing one budget line

Commitments against a budget line are written with optimistic concurrency. The second writer gets a clear conflict with the current remaining balance instead of a silent overspend. A pre-operation plugin recalculates the remaining balance and rejects the write on a row-version mismatch; nothing is trusted from a form script.

Approvals that survive a restart

Long-running approval flows were rebuilt so state lives in the Approval Step rows, not in the flow run. A flow can be fixed and redeployed mid-approval without losing where anyone was.

The board packet is a document, not a screen

Above the top threshold, a Word template merges the request, budget impact, approvals and attachments into a packet the clerk can file. The template is versioned alongside the solution.

ALM with a real audience

Managed solutions through Dev, Test and Prod with pipelines, environment variables for mailboxes and the Teams channel (thresholds stay in the configuration table so finance owns them), and a UAT sign-off recorded before each promotion. The county's IT shop could operate it without the vendor.

Security and governance

Project managers create and edit their own requests until submitted, then they are read-only to them. Approvers act only on steps assigned to them or delegated to them. Auditing captures every field change and every decision. Finance alone edits budget lines.

Outcome

The approval trail became a report. Double-commitments stopped. Median approval time dropped because deputies could act and nobody waited on a buried email.

Power BI

Semantic models with one definition per measure, row-level security the business can maintain, and reports leadership opens. The first is law-firm work; the second is a federal composite.

Billing operations reporting for firm leadershipPower BI · Law firm · 2025–2026 Program oversight dashboard for a federal grant programPower BI · Composite · Big Four federal advisory work

Billing operations reporting for firm leadership

Three sources that disagreed, one semantic model, and a monthly spreadsheet cycle that stopped.

The situation

Leadership's monthly billing view was assembled by hand: an extract from the billing system in SQL Server, a SharePoint list of matter attributes, and an Excel workbook of targets and adjustments, stitched together in a spreadsheet that took most of a week and never matched the prior month's numbers.

What was built

A Power BI semantic model over the three sources through the on-premises gateway: a star schema with a fee fact at invoice-line grain, conformed timekeeper, matter, client and date dimensions, and a monthly snapshot table for A/R aging so history does not rewrite itself. DAX measures for billed, collected, realization and aging with one owned definition each. Row-level security by department from a security table maintained by Finance, so a department head opens the same report and sees only their slice. Scheduled refresh overnight with incremental refresh on the fact. Published through a Power BI app with audiences for leadership, department heads and finance operations; the audiences are what the permission audit in the security case was later built to verify.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://billing-dashboard · synthetic dataOpen full size ↗

A working replica of Billing Operations, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system.

Stack

Power BI Desktop and Service
Power Query
Star schema, snapshot fact
DAX measures
Row-level security
On-premises data gateway
Incremental refresh
Power BI app audiences
Sensitivity labels

What was hard, and what was done about it

The three sources did not agree on who a timekeeper was

The billing system used a numeric ID, SharePoint used a display name, Excel used initials. A conformed timekeeper dimension with all three keys, built in Power Query and reviewed by Finance, is the only place that mapping lives now. A reconciliation page in the report shows rows that failed to match, so a mismatch is visible instead of silently dropping revenue from a department.

History must not move

A/R aging computed live from current balances changes last month's number every time cash is applied. A month-end snapshot table, written by the refresh on the first of the month, makes last month's aging permanent. Leadership stopped asking why the numbers changed.

One definition per measure

Realization meant three different things to three people. Each measure has one owned definition, written on the report's definitions page and versioned with the model. Arguments moved from the number to the definition, which is where they belong.

Row-level security that Finance can run

RLS rules are driven by a security table of user, department and scope, maintained by Finance in a list, not by editing the model. Adding a new department head is a row. Testing is done with 'view as' before each publish.

Refresh windows and a large fact

The fact table grew past what a full nightly refresh could finish before the morning. Incremental refresh on invoice date, with a rolling two-month window, brought refresh from hours to minutes and kept the gateway off the critical path.

Security and governance

Row-level security by department from a Finance-owned table, app audiences for the three reader groups, sensitivity labels on the report, and export to Excel restricted through the tenant export setting to the finance operations security group, with the report-level export setting to match. The permission audit tooling from the security case confirms who can actually see which audience.

Outcome

The monthly spreadsheet cycle ended. Leadership opens one report on the second business day of the month with numbers that match the prior month. Department heads got their own view without a second report.

Program oversight dashboard for a federal grant program

What leadership asked for after the site-visit app went live: one page, by region, accessible.

The situation

Once site visits were captured in an app and awards lived in Dataverse, program leadership wanted a single page: findings by region and severity, corrective actions by age, site-visit coverage, and disbursement against plan. The audience included regional directors who should see only their region, and the whole thing had to meet Section 508 in a GCC tenant.

What was built

A Power BI report in the GCC service over two sources: the site-visit lists and the corrective-action history through dataflows, and the remaining grants tables directly from Dataverse. A small star schema with award, grantee, region and date dimensions, a findings fact and a corrective-action fact with status history. Row-level security by region from a Dataverse table, matched to the same nested Entra groups the apps use. An overview page for leadership and a detail page with the overdue list and a definitions table. Embedded in the model-driven app for program officers and published as an app for leadership.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://program-dashboard · synthetic dataOpen full size ↗

A working replica of Program Oversight, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system. Composite example: a representative build from that engagement type, with agencies, programs and figures changed.

Stack

Power BI (GCC)
Dataflows
Dataverse connector
Row-level security by region
Nested Entra groups
Embedded in model-driven app
Section 508 accessibility checks
Paginated report for the monthly leadership packet

What was hard, and what was done about it

Dataverse refresh that would not finish

Refresh against Dataverse failed on the corrective-action history, because the Dataverse connector reads through the TDS endpoint, which caps a query's result size and run time. Moving that table into a dataflow that loads it in yearly slices on its own schedule, and reading the dataflow from the model, kept refresh inside its window.

Row-level security through nested groups

Regional directors sit in groups nested under a leadership group. RLS was tested with every nested path using 'view as'. DAX cannot read Entra groups, so the security table holds users; the group expansion was later automated with a Microsoft Graph transitive-members script that rewrites the region security table nightly, because manual checking did not scale past four regions.

Accessible is not optional

Every visual has alt text and a data table view, severity is encoded by shape and label as well as color, tab order is set, and the report was walked with a screen reader before release. The definitions table doubles as the accessible summary.

Leadership wanted one number

'How are we doing' needed a definition before it needed a visual. Coverage, open actions and median days to close were defined with the program office and written on the report, which ended the debate about which number was the number.

Security and governance

RLS by region from a Dataverse table, nested Entra groups for audiences, GCC service only, and export allowed only for the leadership security group through the tenant export setting, for the monthly packet.

Outcome

Regional directors stopped requesting custom extracts. The overdue-actions table became the agenda for the monthly regional call, and the monthly leadership packet is a paginated report instead of a deck built by hand.

Platform and governance

The layer under all of it: environments, identities, billing, tooling and the starter kit that let the next developer ship without a handover.

Platform foundation and starter kitPlatform · Law firm · 2026

Platform foundation and starter kit

The shared layer that makes the next app a clone, not a rediscovery.

The situation

The apps were there; the foundation wasn't. Development happened wherever, accounts were used for whatever, and the licensing question for premium connectors had been dodged for a year.

What was built

Environments with a job each: a development environment for all building, a metered test environment and a metered production environment linked to a pay-as-you-go billing plan, and, inherited from before the foundation work, the tenant default environment for the flows, which move beside their apps as each is promoted. A three-account model: a daily account, an admin account used by hand in the admin center and never from a script, and a service account that owns every flow and connection. An app registration for scripts that is deliberately capped at write scope. A starter template cut from the first app: four screens, admin gating, people picker, attachments with the patch, CSV export, and demo data so it renders before any connection exists. A deployment guide that takes a new developer from machine setup to first push in about an hour, starting with the corporate TLS error they will hit first. And a knowledge base listing every environment, connection, list, flow and ID, because that is the part that goes stale first.

Data flow. Boxes are components; arrows are the direction data moves.

Try it

replica://platform · synthetic dataOpen full size ↗

A working replica of Platform Console, rebuilt from scratch with made-up people and numbers. Click around; nothing here touches a real system.

Stack

Power Platform Admin Center
Pay-as-you-go billing (Azure subscription)
Power Platform CLI, pinned
Entra ID app registration and security groups
Intune managed web links
PnP PowerShell
Node build scripts
patch-package

What was hard, and what was done about it

Standing rules, written down

Develop in Dev only. Promote Dev, then Test, then Prod, and only on an explicit go. While an app is in testing, every link, settings row, flow fallback and icon points at Dev, because a production link in a test artifact is a premature promotion. Nothing destructive runs unattended on a corporate machine. Important writes are verified.

Consent bypass is per app ID

A rebuild in another environment is a new app ID and a new bypass. A consent re-prompt after a push usually means the connection surface changed. Both are documented as trigger conditions now, not surprises.

Pay-as-you-go is per environment, not per app

So metered environments are kept narrow. The per-app meter bills each unique unlicensed user per app per month; anyone with a Power Apps licence, per-app pass or trial never appears, which means an unlicensed tester is the only valid billing test. Linking the plan needs Owner or Contributor on the Azure subscription or resource group.

Tooling drifts

The CLI is pinned to a known-good version because a later release fails on push. The selected environment sticks to the auth profile, so re-select Dev after any metered-environment work. Corporate TLS inspection breaks Node's certificate checks; the clean fix is pointing Node at the firm root certificate.

Security and governance

Separation of identities by purpose, least scope on the registration, and no script ever running as the admin account. Tenant-level actions, consents, bypasses and group changes, are requested from IT with the exact scope and run by them.

Outcome

The next developer on the finance side built his first code app from the template in a month, without a handover meeting. Twenty-odd documents exist: runbooks, guides, a billing guide, and the knowledge base.

Have one of these in your estate?

Describe it in a paragraph. You will get a straight read on whether it is worth rebuilding, and what it would take.

darwin@northgateplatform.com