File Intake & Client Organization
Step 2 of the nightly reporting pipeline — how each new file gets from fund accounting into the right client folder in SharePoint, renamed and logged, without anyone pulling files by hand.
The new intake
Fund accounting delivers the night's files over SFTP into Azure Blob storage — a delivery path we set up as part of this work.
From there each file flows automatically into three SharePoint libraries: a visible RAW data copy, then a renamed copy sorted into a folder per client in the Working library. The arrival is written to a Run Log, so the team works in SharePoint with a dashboard.
Inside Step 2 — how intake works
Step 2 is the single intake step in the seven-step pipeline. Under the hood it runs as a short sequence, all automatic:
1A blob trigger fires. Power Automate's "When a blob is added or modified (properties only)" trigger fires when a new file lands in the Blob container. It connects with a dedicated service account (Microsoft Entra ID Integrated — token-based, no access key) and watches the container root only (see the auth note below).
2Copy to the RAW data library. The file is copied as-is into a client-visible RAW data library — no parsing, no renaming, original name and format kept. The team can see exactly what arrived, in one place, instead of logging into the SFTP.
3Parse the filename. The dot-delimited name is split into Report Type, Reporting Date, and Fund / Ticker Code. Real names are more complex than a simple sample and there can be several files per fund per day, so the parser picks the newest by the created timestamp.
4Look up the client. The parsed Fund / Ticker Code is matched against the Clients list to resolve which client the file belongs to and which Working folder it lands in.
5Rename & copy into Working / {client}. The copy going into Working / {client} is renamed to Trailmark's standard convention, with Report Type, Reporting Date, and Fund Code kept as metadata columns. The RAW data original keeps its Fundguard name, untouched.
6Write the first Run Log row. Intake appends a row to the Run Log SharePoint list recording what arrived for this client and run — the first status of the night, which later steps update as the run progresses and the dashboard shows.
One-time cutover backfill
The managed blob trigger watches only the container's root folder (matching the flat-container decision) and
ignores any blobs that already existed before it was switched on. At cutover we run a one-time backfill for whatever is already sitting in the container, or the trigger will never see it.
Where things will be stored
Storage is three separate SharePoint document libraries — not folders inside one library, and not a library per year:
Fund accountingSource system
→
SFTPNew — we build this
→
Azure BlobRaw files land here
→
RAW data libraryFlat — originals untouched
→
Working libraryRenamed & sorted, folder per client
→
Archive libraryYear / date folders
The RAW data library stays flat and keeps every original under its own Fundguard name. The Working library holds one folder per client, where the renamed copy lands. Once a night's cycle is done, its files are archived into the single Archive library under year and date folders (e.g. Archive / 2026 / 2026-07-14). The SFTP endpoint itself is only turned on for the nightly delivery window — see “Running the SFTP endpoint only when needed” below for what switches it on and off.
RAW data (library — flat, originals untouched)
gryphonprod1.…usvc…created_2026_07_14_21_19_25.xlsx ← original name kept
chmp.abor.chmp___champion_nightly_reports….pdf
Working (library — one folder per client)
├── USVC/ USVC Nightly Reports 7.14.2026.xlsx ← renamed to your convention
├── Champion/ Champion Nightly Reports 7.14.2026.pdf
└── … one folder per Active row in Clients
Archive (library — year / date folders)
└── 2026/
└── 2026-07-14/ … the night's files, moved here automatically once done
Library and folder names and the renamed-file convention shown here are illustrative — please confirm they match your current layout.
Running the SFTP endpoint only when needed
SFTP on Azure Blob storage carries a per-hour charge for every hour the endpoint is enabled — a passive cost that applies even when nothing is being transferred (it is not a per-file or per-GB fee).
The cost lever
Left on 24×7 the SFTP endpoint runs roughly
~$0.30/hour ≈ ~$216/month (exact rate on the Azure Blob Storage pricing page). Because fund accounting only delivers once a night, we
enable SFTP just for the nightly delivery window and disable it the rest of the day — so we pay for a few hours, not the whole month.
How we automate the window
An
Azure Automation Account runbook (PowerShell,
Set-AzStorageAccount -EnableSftp) runs on two schedules — enable before the window, disable after — signing in with a
Managed Identity scoped to just this storage account (no stored keys). Cost is effectively
$0/month: two roughly 1-minute jobs a night sit well inside Azure Automation's 500 free job-minutes per month.
Note: this windowing assumes the current once-a-night cadence. If reports ever need to run closer to real time, the SFTP window can't be as tight — a point to weigh against the Cadence open item. Storage, SFTP hours, and licensing are totalled in the separate cost document.
The Clients list
One simple SharePoint list decides which folder a file belongs in. It is separate from the notification and recipient lists — this one answers "which folder," not "who gets emailed." Onboarding a new client is a new row, not a developer task.
Clients — fields schema
| Field | Type | Purpose |
|---|
| Client Key | Text (unique) | The shared key the notification lists also reference — same identity, different concern |
| Client Name | Text | Full display name for logs and manual review |
| Fund / Ticker Codes | Text (multi) | Codes as they appear in the filename — used to match incoming files |
| Working Folder | Text | The folder under Working this client's files are copied into |
| Active | Yes / No | Whether this client is processed — an inactive row's files route to review instead |
The Clients list maps each client to its Working folder. That mapping is the whole point of Step 2's "look up the client" move.
Sample rows two real clients
| Client Key | Client Name | Fund / Ticker Codes | Working Folder | Active |
|---|
| USVC | USVC | usvc | Working / USVC | Yes |
| CHMP | Champion | chmp | Working / Champion | Yes |
The Fund / Ticker Codes are the filename prefixes the parser matches on (e.g. usvc.abor.…, chmp.abor.…). One incoming file → matched to its client → the renamed copy is dropped into that Working folder. Onboarding a new client is just a new row here.
What the new intake handles
The flow covers every piece of file handling end to end:
| Behavior | Note |
|---|
| Detect arrival & parse filename | Blob trigger ("When a blob is added or modified (properties only)") + filename parse, fired the moment a file lands over the new SFTP delivery |
| Strip invalid filename characters | Handled as part of the automated rename |
| Rename to standard convention | Happens at intake: the RAW data original keeps its Fundguard name; the Working copy is renamed to the standard convention, plus metadata columns |
| Sort into a per-client folder | Copy into Working / {client}, resolved via the Clients list |
| Avoid overwrite via (1)/(2) suffix | Explicit check-if-exists + rename logic built into the flow — Power Automate's Copy action has no automatic (1)/(2) renaming |
| Per-run status summary | Run Log rows + central logging, giving per-run status in SharePoint |
Open items to confirm
Unmatched files
What should happen to a file whose Fund / Ticker Code matches no Active row in Clients? Recommended: route it to a
Needs Review folder in the RAW data library and log it, rather than blocking the run or guessing a destination.
Clients list ownership
Recommended: Operations owns the Clients list directly — adding a client is a new row, not a developer task. Still to confirm: who specifically holds edit rights.
What stays the same
How the intake authenticates
The Blob and SharePoint connections run under a dedicated
service account via Microsoft Entra ID Integrated (token-based, no access key) — or a service principal. So there's no stored storage-account key, but there is a stored delegated connection tied to the service account. Passwordless Managed Identity is used where it's available: the Azure Function (email) and the Automation Account (SFTP window), both Azure resources.
Report, don't drop silently
A file that matches no Client row is logged and left for manual triage, never silently dropped or misfiled.
Central logging
Beyond the business-facing Run Log, every copy and routing decision is logged centrally (Application Insights), the same principle as every other step in the pipeline. Infrastructure and running costs for this step are covered in a separate document.