# SiegeStack — full text Every indexable page on siegestack.com, in full, concatenated so it can be read without crawling. Generated from the pages themselves by scripts/build-llms-full.mjs; do not edit by hand, the next build overwrites it. The short guide, and the terms under which this material should be cited, are at https://siegestack.com/llms.txt — READ THAT FIRST. In particular: every figure here comes from a single engagement and is not a benchmark, no client or employer is named anywhere or can be inferred, and where no baseline was measured no percentage is published. Private and noindex pages are deliberately absent. Generated 2026-08-27. --- # SiegeStack — custom integrations, dashboards and apps https://siegestack.com/ ## Business Systems Integration & Automation for Distribution Your systems should talk to each other. Tired of spreadsheets, manual exports, and "the data doesn't match" conversations? We build integrations that actually work. Let's Talk Why We're Different Free · no sign-up ### Barcode label maker Set a label size, drop in a spreadsheet, print the run. Your file is read in the browser and never uploaded. HEX CAP SCREW 1/4-20 x 1 ZINC 10045512BIN A-12-03 [Open the label maker](https://siegestack.com/label-tool) [What size is my label? →](https://siegestack.com/label-tool#sizes) ✓ Custom Solutions ✓ Fast Turnaround ✓ Ongoing Support ✓ Clear Pricing ### Proof, Not Promises Real work we've shipped — not hypothetical capabilities. [CASE STUDY CRM & ERP Integration Showcase See exactly how we connect Salesforce, HubSpot, and enterprise ERPs. Real architecture, real results. View the showcase →](https://siegestack.com/etl-showcase) [AI WORKFLOW How We Use Claude AI to Ship Real Work The iteration loop, the honest limitations, and a copy-paste template you can steal for your own workflow. Read the case study →](https://siegestack.com/working-with-claude-blog) [~27%](https://siegestack.com/working-with-claude-blog#perf-gain) performance gain [300+](https://siegestack.com/working-with-claude-blog#perf-gain) views audited [99%+](https://siegestack.com/working-with-claude-blog#perf-gain) match rate All three come from one write-up of how we work, not from separate client engagements. **Corrected 27 August 2026.** The **99%+** figure was labelled *automation match rate* and linked to the section on replacing spreadsheet macros. It comes from the database view audit, so it now carries that section’s own label and links there, beside the two figures it was measured with. The article separately reports a reconciliation rate for the macro-replacement work; the two are different measurements that happen to share a figure, and the link went to the wrong one. **Corrected 21 August 2026.** A fourth figure — a B → A+ security grade — was removed from this row. It came from a personal project, and standing it beside professional results without saying so overstated what it was. It is still in [the article](https://siegestack.com/working-with-claude-blog#security-grade), labelled. ### What We Build Solutions that solve real problems and deliver measurable results. Worked examples with real numbers are in the [case study](https://siegestack.com/working-with-claude-blog). Larger multi-site engagements are described under [operations modernization](https://siegestack.com/operations-modernization). Running Epicor? Start with [P21 & Kinetic reporting](https://siegestack.com/prophet-21) or [SQL Server performance](https://siegestack.com/sql-server-erp-performance). #### Stop Manual Data Entry Automate data flows between your tools. No more copy-pasting between spreadsheets and systems. [→ ETL & Data Pipelines](https://siegestack.com/services/etl-data-pipelines) #### See Your Numbers, Real-Time Custom dashboards that actually answer your questions. Know what's happening without digging through reports. [→ BI Dashboards](https://siegestack.com/services/bi-dashboards) #### Connect Everything Make your systems talk to each other. CRM to ERP, e-commerce to inventory, whatever you need. [→ ERP Integration](https://siegestack.com/services/erp-integration) #### Sleep Through the Night Built-in monitoring, smart retries, and automatic recovery. Problems get fixed before you know they happened. [→ Monitoring & Alerts](https://siegestack.com/etl-showcase) #### One Source of Truth Consolidate data from everywhere into a single warehouse. Stop hunting through 5 different systems to answer one question. [→ Data Warehousing](https://siegestack.com/operations-modernization) #### Reports That Run Themselves Daily summaries, weekly KPIs, monthly board reports—generated and delivered automatically. No more manual Excel work. [→ Automated Reporting](https://siegestack.com/services/automated-reporting) #### Make the Slow Things Fast Reports that time out and queries that crawl usually have a cause, not a mystery. We find which ones are actually slow, fix the reason, and measure the difference. [→ Performance Tuning](https://siegestack.com/services/performance-tuning) #### Understand What You Inherited Systems nobody documented, built by people who left. We map how it actually fits together so your team can change it without holding their breath. [→ System Mapping](https://siegestack.com/operations-modernization) #### Retire the Fragile Macros Spreadsheet macros that only run on one person's machine, in the right order, when they remember. Replaced with something scheduled, logged, and monitored. [→ Legacy Automation](https://siegestack.com/case-studies) #### Tools Your Team Will Actually Use Small, purpose-built tools that replace a manual process, with validation that catches mistakes before they cost you and documentation people will read. [→ Internal Tools](https://siegestack.com/case-studies) #### Knowledge That Outlives the People Who Have It The person who knows how the process works will eventually leave. We turn what is in their head into guides your team will actually open, organised the way they already think. [→ Training & Documentation](https://siegestack.com/operations-modernization) #### Find Out Who Can Actually Read Your Data Most exposures are not break-ins. They are a key in a page, a table with access control that was never switched on, or a policy that matches nothing while looking like it works. We check what an anonymous browser can actually retrieve, and close it properly. → Access Control Audit [→ Access Review](https://siegestack.com/services/security-access-audit) #### Lock Down What You Already Shipped Security headers that permit exactly what they were added to prevent are the norm, not the exception. We remove what forces the loophole open rather than documenting why it has to stay, and we show you the grade before and after. → Application Hardening [→ Security Headers](https://siegestack.com/services/security-access-audit) #### Forms That Don't Lose Submissions A form that sends an email and reports success only if the send worked will silently destroy enquiries the day a credential expires. We move the durable write in front of the notification, so a delivery failure is an alert, not a deletion. → Reliability Review [→ Silent Failure Audit](https://siegestack.com/delivery-config-audit) #### The Bugs Nothing Reports Redirect rules that never run. A sitemap the whole internet says is fine and no parser will accept. Assets cached for a year against markup that changed yesterday. These pass every check, return no error, and stay broken for months. We probe the live system instead of reading the config. [→ Delivery & Config Audit](https://siegestack.com/delivery-config-audit) #### Numbers You Can Actually Trust Before you act on a dashboard, it is worth knowing whether pages are tagged twice, whether one property is fed by several sites, and whether your staging traffic is counted as real. Frequently no code needs writing — the reports were just never true. → Measurement Audit [→ Analytics Audit](https://siegestack.com/delivery-config-audit) #### Prove Nothing Broke For a large mechanical change, "the diff looks equivalent" is not evidence. We measure the old and new versions side by side and compare what actually renders, so a refactor ships with proof instead of confidence. → Refactor Verification [→ How We Work](https://siegestack.com/about) Running a distribution or manufacturing operation? The heavier operational work — EDI, ERP carve-outs, opening and moving sites, quality systems, vendor-managed inventory, inventory accuracy and system selection — is described under [operations modernization](https://siegestack.com/operations-modernization). If you want the shorter version first — what usually turns out to be wrong in a distribution stack, and which of these five kinds of work fixes it — start with [technology for distributors](https://siegestack.com/industries/distribution). ### What That Looks Like Built One client engagement, described without the specifics. A company ran its entire operation on one large system that nobody had documented. Knowledge lived with whoever had been there longest, training was done by shoulder-surfing, and the same questions were answered over and over. We built them an internal knowledge library. Rather than inventing a structure, it mirrors the module layout of the system staff already work in every day, so finding a reference feels familiar from the first visit. Guides are slide decks instead of walls of text, every slide is individually linkable so a colleague can be sent to step nine rather than to a document, and the tooling sits alongside the documentation instead of in a separate place people forget about. It is explicitly a living document. Published guides went out while later modules were still being written, because a library that waits until it is complete never ships. 7 guides published 72 slides written 4 areas covered 5 more in progress The same engagement produced the in-browser tool described above, embedded directly in the guide that explains when to use it. Documentation people ignore and tools people cannot find are the same problem. Shipping them together is the fix. ### Software That Handles the Weird Stuff APIs go down. Data formats change. Networks timeout. We build systems that expect this and keep running anyway. #### No Emergency Calls Problems get caught, logged, and fixed before you even know they happened. You'll get a summary report, not a 2 AM page. #### Built for Growth Whether you're processing 100 records or 10 million, the architecture stays the same. No "we need to rebuild this" conversations later. #### Battle-Tested Integrations We've connected Salesforce, Shopify, Epicor, NetSuite, and dozens of others enough times to know where things break and how to prevent it. ### Here's How We Work Together No mystery. No disappearing acts. You'll know exactly what's happening at every step. 1 #### We Talk 30 minutes on a call. You tell us what's broken, what's frustrating, what you wish worked better. We ask questions and take notes. 2 #### We Propose You get a document that says exactly what we'll build, how long it takes, and what it costs. No surprises later. 3 #### We Build You get weekly updates with working software to test. Questions? Feedback? We're a Slack message away. ### You're Probably Wondering... Here's what most people ask before we start working together. #### "Is custom development expensive?" Honestly? It depends. But most of our projects pay for themselves within a few months through time saved and errors avoided. We'll give you a clear number upfront—no surprises. #### "How long until I see something working?" Weeks, not months. We get a working version in your hands fast so you can test it with real data. Then we iterate based on what you actually need. #### "What happens when something breaks?" We build things that handle their own problems. But when you need us? We're here. Maintenance and support included, plus documentation so you're never locked in. #### "We've been burned by developers before." We get it. That's why we do weekly updates, clear scope documents, and no work starts without a signed agreement on exactly what we're building. You'll never wonder where things stand. #### "Do we own the code you write?" 100%. Everything we build is yours. Full source code, documentation, deployment instructions—the works. You can hand it to another team tomorrow if you want. #### "Our IT team is nervous about security." Good—they should be. We follow security best practices (encrypted connections, credential management, audit logging). Happy to walk through our approach with your team before we start. ### Let's Figure This Out Together Grab 30 minutes on my calendar. Tell me what's driving you crazy. I'll tell you if we can help—and if we can't, I'll point you in the right direction. No pitch, no pressure. --- # Case studies https://siegestack.com/case-studies ## Case Studies Three problems, described without the specifics. Everything below is anonymised. No client or employer is named, no client data appears here, and the details that would identify an organisation — industry, size, location, systems by name, item numbers and dollar figures — have been removed rather than disguised. What is left is the part that transfers — the problem as it presented, what it actually turned out to be, and what was built. Where a number would matter, it is called out as something to measure rather than asserted. **Why there are no percentages on this page.** Consulting case studies tend to open with a figure — 82% faster, 40% fewer errors. Those numbers are only worth anything if someone measured the before state, and in most operational work nobody did, because the before state was nobody's job to instrument. Where a baseline was captured, it is named. Where it was not, saying so is more useful than a number nobody can reproduce. When a figure on this site turns out to be wrong, or to have been stood beside things it does not belong with, it is [written up in the corrections log](https://siegestack.com/corrections) rather than quietly edited away. Case 01 ### [A KPI console for people who were reading the operation from memory](https://siegestack.com/case-studies/kpi-console) Two audiences, two trust levels. Cost-bearing reports are refused to floor displays at the report-definition layer, not the render layer. [Read the full case study →](https://siegestack.com/case-studies/kpi-console) Case 02 ### [The report that worked on Today and timed out on Month](https://siegestack.com/case-studies/month-to-date-timeout) Fast on Today, dead on Month-to-date. The most consistently misdiagnosed complaint in ERP reporting. [Read the full case study →](https://siegestack.com/case-studies/month-to-date-timeout) Case 03 ### [One label service instead of a label program per label](https://siegestack.com/case-studies/label-service) A program per label became one configuration-driven service. New labels stopped being deployments. [Read the full case study →](https://siegestack.com/case-studies/label-service) ### The pattern across all three None of these started as the problem they turned out to be. “We need a dashboard” was really a question about which rows count as evidence. “The dashboard is slow” was one missing index on a vendor table. “We need another label program” was the absence of anywhere to define a label source. That is the common shape of operational software work: the request describes the symptom accurately and the cause not at all, and the useful first move is always to reproduce the symptom against the actual data before agreeing to build anything. ### Related - [All insights](https://siegestack.com/insights) — articles and case studies in one place. - [Operations modernization](https://siegestack.com/operations-modernization) — the larger engagement these sit inside: consolidate onto one system of record, then optimize on top of it. - [CRM & ERP integration](https://siegestack.com/etl-showcase) — the integration and pipeline work that feeds reporting like this. - [How the analysis actually gets done](https://siegestack.com/working-with-claude-blog) — the working method behind these engagements. - [Why your ERP report times out on month-to-date](https://siegestack.com/erp-report-slow-month-to-date) — the technical write-up of Case 02. - [Technology for distributors](https://siegestack.com/industries/distribution) — the shape of operation all three of these ran inside. #### Recognise any of these? If a report is slow on longer date ranges, or a number nobody trusts is driving a decision, the first conversation is short and costs nothing. [Get in touch](https://siegestack.com/#schedule) · [See the engagement model](https://siegestack.com/operations-modernization) --- # Why your ERP report is fast on Today and times out on month-to-date https://siegestack.com/erp-report-slow-month-to-date ## Why Your ERP Report Is Fast on Today and Times Out on Month‑to‑Date It is almost never the amount of data. It is which column the date filter lands on, and whether anything indexes it. This is the most common performance complaint in ERP reporting and the most consistently misdiagnosed. The report returns instantly for today. Widen it to week‑to‑date or month‑to‑date and it hangs, then times out. Because the pain scales with the date range, it reads like a data‑volume problem, and the usual response is to rewrite the query or ask for a bigger server. Both are usually wrong. **The short version.** A short window and a long window frequently read *different tables*. Today often reads open, in‑flight records. Week and month have to reach into posted history. If the history table has no index on the date column you are filtering, the database has no way to skip rows — it reads the entire table to find the ones in your range. The fix is often a single index. The care is all in applying it. ### Why the two date ranges are not the same query Transactional ERPs almost always split their data in two: a live table for records still moving, and a history table for records that have posted. Receipts, invoices, picks and shipments all tend to follow this pattern under different names. A well‑built report follows that split. Today reads the open table only, because everything from today is still open. Week and month union the open table with history, because most of that period has already posted. Those are two genuinely different execution plans against two very differently sized tables, so treating the slow one as “the same report, just more data” misses what is actually happening. The live table is small — it holds only what has not yet posted, so it is naturally self‑trimming. The history table holds years. That is the one where indexing decides whether your report returns in a second or not at all. ### The specific failure: an unindexed date column Here is the shape it usually takes. A report filters invoice history on a pick‑date column. That table had four indexes on it — the order number, a history id plus line, the lot, and a rowguid. Every one of those is a perfectly sensible index for a transactional system to ship with. Not one of them helps a query whose `WHERE` clause filters on pick date. With no index on the filtered column, SQL Server cannot seek to the rows in your date range. It scans, reading every row in the history table and discarding the ones outside the window. Today was fast because it never touched that table. Month was slow because touching that table meant reading all of it. Notice what is *not* wrong here. The query is not badly written. The server is not undersized. The data volume is normal for the business. The only defect is that the access path the report needs does not exist. ### How to confirm it in about ten minutes - **Establish the split.** Run the report at each period setting and record the runtime. If today is sub‑second and week is minutes, you are looking at a threshold, not a gradient. A genuine volume problem degrades smoothly; an access‑path problem falls off a cliff. - **Read the actual execution plan** for the slow version, not the estimated one. You are looking for a scan on a large table underneath your date filter. That single operator is usually the whole story. - **Measure logical reads** with `SET STATISTICS IO ON`. This is the honest number: it does not move with server load, caching or who else is on the box, so it is the one to record before and after. Elapsed time is what the user feels, but logical reads are what you actually changed. - **List what is currently indexed** on the table, and check whether the filtered column appears as the leading key of anything. Being present somewhere in an index is not the same as being seekable — a column buried as the third key of a composite index will not serve a range scan on its own. If those four point the same direction, the diagnosis is settled and you have a defensible before‑state to compare against. ### The part that needs more care than the diagnosis Finding it is the easy half. Applying it in a live ERP is where the risk sits, and this is the part most write‑ups skip. | Consideration | Why it matters before you run `CREATE INDEX` | | Whose schema is it | If the tables belong to your ERP vendor rather than to you, adding an index is a supportability decision before it is a technical one. Some vendors are relaxed about additive indexes; some treat any schema change as voiding support; some overwrite them on upgrade. Ask before you apply, and record the answer — the next upgrade may silently remove your index and the timeout will come back looking like a new bug. | | Which SQL Server edition | Online index operations are an Enterprise feature. On Standard Edition, index creation takes a schema‑modification lock and blocks users on that table for the whole build. On a big history table that is not a few seconds. This belongs in a maintenance window. | | Open transactions | An idle query window somewhere holding an open transaction will block the build, and the build will in turn block everyone else. The result looks, from the floor, like the database froze. Close the tabs, and check for open transactions before you start. | | Write cost | Every index is maintained on insert, update and delete. On a high‑churn transactional table, an index you added for one monthly report is paid for on every transaction all month. Usually still worth it. Occasionally not. | | The reversal plan | Capture the table size, row count and existing index list before you change anything, so the change can be evaluated honestly and dropped cleanly if it does not help. | ### Designing the index Lead with the column you filter on. For a date‑range report, that means the date column is the first key — that is what makes the range seekable. Columns you group or join by follow. Columns you only return can go in an `INCLUDE` list rather than the key, which keeps the index narrower while still avoiding a lookup back to the base table for every row. Resist the urge to add one index per report. Three overlapping indexes on the same table, each serving one query, cost more in write overhead and buffer pool than one well‑ordered index serving all three. If you already have an index whose leading key is right and it is missing a column or two, extending it is usually better than adding a sibling. ### When it genuinely is volume Sometimes the scan is unavoidable and the answer is architectural rather than an index: - The report aggregates most rows in the table anyway, so there is nothing to skip. A pre‑aggregated snapshot, refreshed nightly, beats recomputing it per page load. - The history table is genuinely enormous and every query hits it. Partitioning by date can help, though it is a much larger commitment than an index. - Several dashboards recompute the same expensive figures independently. Materialise them once on a schedule and have every dashboard read the result. The tell is the shape of the degradation. A missing index produces a cliff — fine, fine, fine, dead. A true volume problem produces a slope. Diagnose which one you have before choosing a fix, because the architectural remedies cost weeks and the index costs an afternoon and a maintenance window. ### The general lesson “The dashboard is slow” is a symptom report, and it is an accurate one — the user is not wrong. But it describes the experience, not the cause, and the instinct it triggers (optimise the query, buy hardware) points away from the actual defect most of the time. Ask a narrower question instead: *what exactly does the date filter filter on, and does anything index it?* In ERP reporting that question resolves a surprising share of performance complaints, usually to one line of DDL and a conversation with whoever owns the schema. ### Related reading - [All insights](https://siegestack.com/insights) — articles and case studies in one place. - [Case studies](https://siegestack.com/case-studies) — this diagnosis written up as a case, plus a KPI console and a label service. - [Operations modernization](https://siegestack.com/operations-modernization) — what happens after the reporting is trustworthy: slotting, order consolidation, purchasing and network replenishment. - [CRM & ERP integration](https://siegestack.com/etl-showcase) — the pipeline and integration work that feeds reporting like this. - [Automated reporting](https://siegestack.com/services/automated-reporting) — once the report is fast enough to trust, the next question is why anyone is still running it by hand. #### Got a report that dies on longer date ranges? Usually diagnosable from the execution plan and the index list in a single sitting. The first conversation costs nothing. [Get in touch](https://siegestack.com/#schedule) · [Read the case studies](https://siegestack.com/case-studies) --- # SQL Server performance for ERP reporting https://siegestack.com/sql-server-erp-performance ## SQL Server Performance for ERP Reporting Reports that crawl usually have a cause, not a mystery. The method below finds it, and the risk is almost never in the fix. A slow ERP report is reported as “the dashboard is slow,” which is an accurate description of the symptom and tells you nothing about the cause. The useful first move is never to optimise the query. It is to establish whether the database is reading the rows it needs or reading everything and discarding most of it — because those two situations look identical from the outside and have completely different fixes. **The single most useful reframe:** when a report is fast on a short date range and dies on a longer one, that is not evidence of a volume problem. It is evidence of an **access-path** problem that volume has made visible. The distinction matters because the volume interpretation leads to hardware, archiving and partitioning conversations, and the access-path interpretation frequently leads to one index. ### The two patterns behind most of it Pattern 01 #### The filter column nobody indexed The report filters history on a date column. The indexes that exist on that table cover the order number, a history identifier and line, a lot number, a row identifier — every access path except the one the filter actually needs. So the date range forces a scan of the whole history table. The symptom scales with the date range, which is exactly why it gets misdiagnosed as data volume. It is also why the short-range version of the same report is often instant: it frequently reads a different, far smaller table altogether. Pattern 02 #### Predicates that make an existing index unusable An index only helps if the optimiser can seek on it, and wrapping the indexed column in an expression prevents that. Date arithmetic applied around the column in a `WHERE` clause is the usual offender. Rewriting so the column sits bare on one side of the comparison and the arithmetic moves to the constant makes the same index usable again — with no schema change at all. The close relative: scalar functions in a `SELECT` list, which silently force row-by-row execution across the whole result. Inlining them is ugly and fast, and ugly-and-fast is the correct trade in a report nobody reads the source of. Both of these are worth trying first for a reason that has nothing to do with elegance: **they touch nothing the vendor owns.** ### The method | Step | What it establishes | | Reproduce the symptom against real data, at each period setting | That the complaint is real, specific and repeatable — and which setting is the boundary. A complaint you cannot reproduce is not yet a problem you can fix. | | Capture the baseline before touching anything | Runtime at each setting, logical reads, table sizes, and the current index state. **You cannot go back for this.** Every engagement that skipped it ended up unable to prove its own result. | | Read the plan, not the query | Whether the expensive operator is a scan where a seek was expected, and which predicate is responsible. This is the step that separates a diagnosis from a guess. | | Try the changes that touch nothing first | Predicate rewrites and function inlining need no permission, no window, and no rollback plan. | | Design the index, then negotiate it | Key columns in the right order, included columns to cover the query, and an honest estimate of the write cost it adds. Then take it to whoever owns the platform. | | Apply it in a window, and measure again | Same measurements, same conditions, compared against the baseline — and the build duration recorded so the next window can be planned rather than guessed. | **Measure logical reads, not seconds.** Runtime moves with server load, cache state and whoever else is on the system, so the same query can look twice as fast on a quiet afternoon and you will believe you fixed something. Logical reads count the pages actually touched and do not move with load. It is the honest number, and it is the one to put in front of anyone who has to approve the change. ### Why the fix needs more care than the diagnosis In ERP work the tables shipped with the product. That single fact drives everything about how a change gets applied: - **Adding an index to a vendor schema is a supportability decision before it is a technical one.** It is frequently approved. It should never be applied merely because it was obviously correct. - **Index creation takes a schema-modification lock.** On SQL Server Standard Edition the build blocks live users for its duration, which places it in a maintenance window rather than in the afternoon it was diagnosed. - **An idle query window holding an open transaction will block the build** and create a lock chain that looks, to everybody else, like the database has frozen. Close the tabs first. This sounds trivial until it is happening. - **Write it down where the next upgrade will look.** Anything added to a vendor object can be removed by an upgrade, and a report that silently gets slow again a year later is a genuinely miserable thing to diagnose twice. ### When it genuinely is volume Sometimes the access path is correct and the table really is too big for the question being asked. That case is recognisable rather than assumed: the plan already shows a seek, logical reads are proportionate to rows actually returned, and the report is slow in a way that scales smoothly rather than falling off a cliff at a particular date range. That is a different engagement — archiving strategy, pre-aggregation, or moving reporting off the transactional system. It is worth naming here because jumping to it first is the expensive mistake, and it is the one an outside firm has the most incentive to recommend. ### Worth measuring if you run this There are no percentages on this page. A figure is only worth quoting if somebody captured the before state, and in most operational work nobody did. These are the numbers that make the work assessable: - Report runtime at each period setting, before and after — captured before you apply anything. - Logical reads for the query, which is the measure that does not move with server load. - Index build duration, so the next maintenance window is planned rather than guessed. - Write-path cost added by the new index, measured on the transactions that maintain it. - How many reports in the estate share the same root cause — usually more than one. ### Frequently asked questions #### Is a slow report always a missing index? No, but an unindexed filter column is the most common single cause in ERP reporting and the most consistently misdiagnosed, because the symptom scales with the date range and therefore looks like data volume. Check the access path before assuming volume. #### Why measure logical reads instead of runtime? Runtime moves with server load, cache state and who else is on the system, so the same query can look twice as fast on a quiet afternoon. Logical reads count pages actually touched and do not move with load, which makes them the honest before-and-after measure. #### Can you tune without adding indexes? Often yes. Rewriting a predicate so the indexed column appears bare on one side restores index usability with no schema change at all, and inlining a scalar function removes row-by-row execution. Those are usually tried first precisely because they touch nothing the vendor owns. #### How long does a diagnosis take? Confirming whether a specific report is an access-path problem is usually about ten minutes against a restore or replica. Designing and safely applying the fix takes longer, and most of that time is scheduling rather than work. #### Will this void our ERP support? That is the platform owner's call, not ours, which is exactly why the conversation happens before anything is applied. Many vendors permit added indexes; some do not. Predicate rewrites and reporting-layer changes avoid the question entirely. ### Related - [Why your ERP report is fast on Today and times out on month-to-date](https://siegestack.com/erp-report-slow-month-to-date) — this method applied end to end to one real report, including the confirmation steps. - [Epicor P21 & Kinetic reporting and integration](https://siegestack.com/prophet-21) — the platform-specific side. Start there if the question is *which rows count* rather than *why is this slow*. - [Case studies](https://siegestack.com/case-studies) — including the report that worked on Today and timed out on Month. - [Performance tuning](https://siegestack.com/services/performance-tuning) — what an engagement built on this method actually involves, and what determines its cost. - [Technology for distributors](https://siegestack.com/industries/distribution) — where performance work sits among the other four things that usually need doing. - [All insights](https://siegestack.com/insights) — technical articles and anonymised breakdowns. #### Got a report that dies on longer date ranges? Bring the report and the period setting where it falls over. Confirming whether it is an access-path problem takes about ten minutes, and that conversation costs nothing. [Book a call](https://siegestack.com/#schedule) · [Read the worked example](https://siegestack.com/erp-report-slow-month-to-date) --- # Epicor Prophet 21 and Kinetic reporting and integration https://siegestack.com/prophet-21 ## Epicor P21 & Kinetic Reporting and Integration Reporting, integration and tooling for distributors already running the ERP. The hard part is rarely the query — it is knowing which rows count. Prophet 21 and Kinetic hold everything the business runs on and answer operational questions reluctantly. The data is there. Assembling it correctly is a different job from querying it, and most reporting that goes wrong on these platforms goes wrong quietly — a number that looks plausible, moves in the right direction, and is built on rows that should never have been counted. **Where the depth actually is.** The hands-on work behind this page is on **Prophet 21**. Kinetic shares the parts that transfer — SQL Server underneath, a vendor-owned schema, the same access-path failure modes in reporting — and differs at the application layer. That distinction is stated here rather than smoothed over, because a consultancy that claims equal depth in every product on its own keyword list is telling you something about its writing, not its experience. ### Four traps in P21 reporting that produce confidently wrong numbers These are not exotic. They are the ordinary shape of the schema, and each one has a default behaviour that flatters the metric in exactly the cases nobody is checking. Trap 01 #### Open and posted live in different tables The same reporting period can span unposted transactions and posted history, and those sit in separate tables with different columns. A daily view that reads only the open side is correct. Extend it to a week or a month without unioning history and the number is not wrong by a little — it silently omits everything that has been posted, which on a full month is most of it. This is the classic ERP reporting bug on this platform: it looks right on the view people check most often, and drifts on every longer window. It is also the reason a report can be fast on Today and unusable on month-to-date, which is [a separate problem with the same trigger](https://siegestack.com/erp-report-slow-month-to-date). Trap 02 #### Undated lines counted as successes On-time percentage compares a receipt or ship date against a due date, and due date itself is a fallback chain — promise, then required, then original. The decision that matters is what to do with a line that has *no* due date at all. Rolling those into the numerator treats missing data as a success. It flatters the metric precisely where the data is worst, which is where you most need the metric to be honest. Excluding them from the ratio and reporting the exclusion count separately is the version that survives being questioned by the person whose department it measures. Trap 03 #### Flags that only exist on one side Some completeness flags exist on open records and have no equivalent in posted history. Score the history rows zero and a full month reads worse than a single day, purely because most of the month has been posted. The fix is to exclude them from the ratio with an explicit marker rather than let them default to failure — and to say so on the report, because a ratio with a hidden denominator rule is a ratio nobody should act on. Trap 04 #### Identity is a fallback chain, and the fallbacks fail silently Transactions frequently carry initials or a short code rather than a person. Mapping that to a human runs through a chain — a description field, a network login, the local part of an email address — and every chain has rows that reach the end unmatched. Hiding them produces a leaderboard that is permanently and invisibly wrong. Displaying an explicit *unmapped* bucket turns the same rows into a data-quality finding somebody can close. An unattributed transaction is information; a silently dropped one is a bug you will never be told about. **The pattern across all four:** most of the work in an operational metric is deciding which rows are evidence and which are absence of evidence. A dashboard that gets that wrong is worse than no dashboard, because it is confidently wrong in the direction nobody checks. Worked through properly in [the KPI console case study](https://siegestack.com/case-studies). ### What changes when the schema belongs to the vendor Nearly every constraint that makes this work different from ordinary application development comes from one fact: the tables shipped with the ERP and you did not create them. | Constraint | What it means in practice | | Indexes are a supportability decision first | Adding one to a vendor table is a conversation with the platform owner before it is a technical exercise. It is frequently approved. It should never be applied because it was obviously correct. | | Index builds take a schema-modification lock | On SQL Server Standard Edition the build blocks live users for its duration. That places it in a maintenance window, not in the afternoon it was diagnosed. | | An idle query window can block the build | An open transaction on a forgotten tab creates a lock chain that looks, to everyone else, like the database has frozen. Close the tabs first. This sounds trivial until it happens to you. | | Upgrades can reintroduce what you changed | Anything applied to a vendor object needs to be written down somewhere the next upgrade will be read, or it will quietly disappear and the report will get slow again with no explanation. | | Customisation belongs outside the schema where possible | Reporting layers, configuration tables and services alongside the ERP survive upgrades. Changes inside vendor objects are a liability you carry forever. | ### Beyond reporting Integration #### Getting data in and out without a nightly file Connecting the ERP to a CRM, a storefront or a warehouse system is mostly a question of what happens on the bad day — the API that times out, the format that changed, the record that arrives twice. Retries, circuit breakers, idempotent writes and monitoring are the parts that decide whether an integration is an asset or a recurring incident. The patterns, with production-shaped code, are on the [integration showcase](https://siegestack.com/etl-showcase). The design rule that matters most: replace file drops with a service consumers can ask. A file is stale the moment it is sent, forks into several slightly different versions, and nobody can tell which is current. Bulk maintenance #### Loading tooling that refuses to half-finish Bulk maintenance in P21 — pricing, contracts, and anything else that goes out through an export and comes back through an import — usually means reshaping one export into several differently-formatted files by hand. It works, it takes a long time, and it fails in the worst possible way: halfway through a load, leaving a partial mess behind. Purpose-built tooling for this runs in the browser with no backend and nothing leaving the machine. It detects which file is which, stamps the batch identifier down every row, normalises date formats, drops the header the exporter adds, and writes out correctly named files. **The validation design is the part worth stealing.** Failures split into two tiers: conditions that guarantee a broken load are *errors* and stop you; conditions that are merely suspicious are *warnings* and let you proceed. An orphaned child record is an error. A mismatch between related lines is a warning. Collapsing those two into one category is what teaches people to ignore validation entirely. Mapping #### Understanding what you inherited Custom views accumulate over years, each joining tables that join other tables, and no map exists. Before any performance work is safe, the map has to exist — parse every view definition, extract the join relationships, cross-reference them against declared key constraints, and write the result somewhere queryable rather than somewhere pretty. The part that decides whether the map is trustworthy is the failure list. Any definition the parser cannot confidently interpret gets written down for a human decision instead of being skipped. **A parser that quietly drops what it does not understand produces a map that is confidently wrong, which is worse than no map at all.** ### Worth measuring if you run this No percentages are published on this page. Any figure worth quoting needs a before state somebody actually captured, and in most operational work nobody did, because instrumenting the before state was nobody's job. These are the measurements that make an engagement here assessable: - Report runtime at each period setting, and logical reads for the query — captured *before* anything is applied, because you cannot go back for it. - The share of rows landing in the unmapped bucket, tracked down over time as the mapping is cleaned. - Purchase price variance surfaced in the first full month, against what was being caught by hand before. - Elapsed time from an operational question being asked to it being answered, before and after. - Failed or partially completed bulk loads per month. ### Frequently asked questions #### Will you add indexes to our P21 database? Only with the platform owner's agreement, and in a maintenance window. The tables ship with the ERP, so adding an index is a supportability decision before it is a technical one. It also takes a schema-modification lock, which blocks live users for the duration of the build. #### Do you need production access? Read access to a restore or a replica is enough to diagnose almost everything. Nothing is applied to production without an agreed window, a captured baseline and a way back. #### Can you work on Kinetic as well as Prophet 21? Yes. The reporting and access-path work transfers directly because both sit on SQL Server and both are vendor-owned schemas. The application-layer specifics differ, and the deepest hands-on experience here is on Prophet 21 — that is stated plainly rather than smoothed over. #### Do we own what you build? Yes, entirely. Full source, documentation and deployment instructions. You can hand it to another team tomorrow. #### Do you upgrade or re-implement the ERP itself? No. This is reporting, integration and tooling around a system you already run. Full re-implementation is a different kind of engagement and there are firms that specialise in it. ### Related - [What a P21 upgrade does to your reporting](https://siegestack.com/prophet-21-upgrade-reporting) — the three different projects people call an upgrade, what each one breaks, and the baseline to capture before the window opens. - [SQL Server performance for ERP reporting](https://siegestack.com/sql-server-erp-performance) — the diagnostic method, independent of which ERP sits on top. Start there if the question is *why is this slow* rather than *which rows count*. - [Why your ERP report is fast on Today and times out on month-to-date](https://siegestack.com/erp-report-slow-month-to-date) — the single worked example, start to finish. - [Case studies](https://siegestack.com/case-studies) — the KPI console, the month-to-date timeout, and the label service, all anonymised. - [CRM & ERP integration showcase](https://siegestack.com/etl-showcase) — integration patterns with production-shaped code. - [Operations modernization](https://siegestack.com/operations-modernization) — the larger engagement: consolidate onto one system of record, then optimize. - [Technology for distributors](https://siegestack.com/industries/distribution) — the same work described by the operation it runs in rather than by the ERP it runs on. #### Running P21 or Kinetic and not trusting a number? Bring the report and the question it is supposed to answer. The first conversation is thirty minutes and costs nothing, and frequently ends with a diagnosis rather than a proposal. Book a call · [See the case studies](https://siegestack.com/case-studies) · [Send it in writing](https://siegestack.com/contact) ### Thirty minutes, on the report you don't trust Pick a time. Bring the report, the number that looks wrong, and what it is supposed to mean. If the answer is one of the four traps above, you will have it inside the call. If it is something else, you will at least leave knowing which layer it is in. --- # What a Prophet 21 upgrade does to your reporting https://siegestack.com/prophet-21-upgrade-reporting ## What a Prophet 21 upgrade does to your reporting The upgrade rarely breaks the ERP. It breaks what you built on top of it — and it does so quietly, because a report that reads the wrong rows still returns a number. Three different projects get called “the P21 upgrade”, and they are not the same size, do not carry the same risk, and do not break the same things. Before anyone estimates the reporting work, it is worth being precise about which one is actually happening — the answer changes whether the reporting layer needs a repair, a rebuild, or nothing at all. **Scope, stated up front.** This page is about the reporting and integration layer *around* Prophet 21, not about running the upgrade. The upgrade itself is the platform owner’s project, run with Epicor or a reseller, and it should stay that way. What follows is the part that usually has no owner: the custom reports, dashboards, indexes, extracts and integrations that were built against a schema somebody else is about to change. ### Three things called an upgrade One #### A version upgrade — same product, newer release The ordinary case. The schema is still the vendor’s and still recognisably itself, but tables gain columns, views get redefined, and objects that were never documented in the first place move without announcement. Nothing here is dramatic on its own. The damage is cumulative and it lands entirely on things that read the database directly. The reporting risk is proportional to how far behind you are, because every skipped release’s schema changes arrive in the same window. A shop two releases behind is doing a repair. A shop that has not upgraded since the desktop client was current is doing an archaeology project first. Two #### The desktop-to-web transition — same product, different client This one is largely historical now and is still the reason a lot of P21 reporting is in the state it is in. Epicor stopped adding features to the Prophet 21 desktop application at the Spring 2021 release, moved new development to the web application, and ended desktop fixes on 30 November 2022. Dates and versions are in the sources below; re-check them against Epicor before planning around them. What that did to reporting was not subtle: anything whose delivery mechanism was the desktop client — a report launched from a menu, a print job wired to a workstation, a tool that assumed a Windows session — needed a new home, whether or not the query underneath it was still correct. A good deal of it never got one. It got a person running it manually instead, and that person is now the documentation. If you are still on a desktop-era version in 2026, the reporting question is downstream of a support question, and the support question has the date on it. Three #### Replacing Prophet 21 with Epicor Kinetic — a different product Not an upgrade. Prophet 21 and Kinetic are separate products with separate code bases and separate roadmaps, built for different industries — Prophet 21 for wholesale distribution, Kinetic for discrete and mixed-mode manufacturing. Moving between them is a re-implementation. For the reporting layer that means the honest estimate is *none of it comes across*. Not the queries, not the indexes, not the extracts, not the mappings. The business logic survives — what a metric is supposed to mean, which rows should count, which exceptions are real — and that is worth writing down properly before the old system goes away, because it is the only asset that transfers. When a proposal calls this an upgrade, that is worth a direct question. The word is doing a lot of work. ### What actually breaks In every one of the three cases the ERP comes back up and the standard screens work. The failures are in the layer nobody in the upgrade project owns, and they share a characteristic that makes them expensive: **they do not raise errors.** | What it is | How it fails | How you find out | | Queries reading vendor tables directly | A column changes meaning, or a row that used to be excluded starts arriving. The query still runs. | Somebody notices the number moved and cannot explain why — typically a month later, at close. | | Custom indexes on vendor tables | Dropped or not carried forward. Nothing is obliged to preserve an object the vendor did not ship. | The long-range report starts timing out again. Same query, same plan shape as before it was ever fixed. | | Reports bound to the old client | The delivery mechanism disappears while the query stays valid. | Immediately, and this is the good case — a visible break gets fixed. | | Integrations bound to table shape | An added column or a changed nullability breaks a load, or worse, does not break it and writes something subtly wrong. | The load that fails is found the same day. The one that succeeds incorrectly is found by reconciliation, if there is one. | | Anything reading an undocumented view | Redefined upstream. The view still exists and still returns rows. | Usually never, directly. It surfaces as two reports disagreeing. | This is the same failure mode described in [the four traps in P21 reporting](https://siegestack.com/prophet-21), arriving by a different route. There, the wrong rows were counted because the data model was misread. Here, the model was read correctly and then changed underneath. The symptom is identical — a plausible number, moving in a believable direction, built on rows that should not be in it — and so is the reason it survives so long: nothing in the system objects. ### The inventory, and the baseline you cannot go back for Two artefacts are worth producing before the upgrade window opens, and both become impossible to produce properly afterwards. **The dependency inventory** is the list of everything outside the ERP that reads the ERP: reports, dashboards, scheduled extracts, integrations, spreadsheets with live connections, and the workstation scripts that were never called integrations because one person wrote them. For each one, what it reads and who notices when it is wrong. Most of this list does not exist anywhere when the question is first asked, and assembling it is frequently the most useful part of the exercise regardless of the upgrade — it is the first time anyone has written down what the business actually depends on. **The performance baseline** is runtime and logical reads for the reports that matter, captured on the current production system. Logical reads rather than wall-clock time, for the reasons set out in [the SQL Server method page](https://siegestack.com/sql-server-erp-performance): runtime moves with cache state and load, reads do not. This is the measurement that cannot be recovered. Once the window closes the old state is gone, and every later argument about whether the upgrade made reporting slower becomes a disagreement about how it used to feel. **Index definitions belong in source control, not only in the database.** An index applied to a vendor table during a tuning engagement is invisible to the upgrade and to whoever runs it. If the only record of it is the database it was applied to, the upgrade is the event that silently removes a fix somebody paid for — and the report that starts timing out afterwards looks like a new problem. Keep the definition, the date, and the reason it exists. ### Worth measuring if you run this No percentages are published on this page, for the same reason they are absent from the rest of this site: a figure is only worth quoting if somebody captured the before state, and in upgrade work almost nobody did. These are the measurements that make the reporting side of an upgrade assessable rather than anecdotal. - Report runtime *and* logical reads for the reports that matter, captured before the window and again after — the same reports, the same parameters, both times. - Count of items in the dependency inventory, and how many of them nobody could name an owner for. - Custom indexes present before the upgrade, versus present after. - Number of reports that returned a different figure after the upgrade for the same period, and how many of those differences were correct. - Elapsed time from the window closing to the last piece of reporting being confirmed working, rather than assumed working. ### What this is not This is not upgrade delivery, migration planning, or ERP selection. It does not include running the upgrade, sizing the environment, or negotiating with the vendor. Full re-implementation is a different kind of engagement and there are firms that specialise in it — that boundary is stated on [the P21 page](https://siegestack.com/prophet-21) as well, and it has not moved. What it is: the inventory, the baseline, and the repair of the reporting and integration layer either side of somebody else’s window. ### Frequently asked questions #### Do you run the upgrade itself? No. The upgrade is the platform owner’s project, run with Epicor or a reseller. This is the reporting and integration layer around it — the inventory of what depends on the schema, the baseline captured before the window, and the repairs afterwards. Those are separate jobs and it is better for you if they are separate people. #### Is moving from Prophet 21 to Kinetic an upgrade? No. They are separate products with separate code bases and separate roadmaps, aimed at different industries — Prophet 21 at wholesale distribution, Kinetic at discrete and mixed-mode manufacturing. Moving between them is a re-implementation, and none of your reporting comes across. Anyone describing it as an upgrade is either being loose with the word or has not looked at the schema. #### Will our custom indexes survive? Assume not, and plan to re-apply them. An index added to a vendor table is not part of the vendor’s shipped schema, so nothing in the upgrade is obliged to preserve it. The failure is quiet: the report still returns, it just reads the whole history table again. Keep the index definitions in source control with the reason each one exists, not only in the database. #### When should the baseline be captured? Before the upgrade window opens, on the current production system. Runtime and logical reads for the reports that matter cannot be recovered afterwards — once the window closes, the old state is gone and every performance argument becomes a memory of how it used to feel. #### We are several versions behind. Is that a reporting problem or a support problem? Both, and the support problem is the one with a date on it. Being unsupported does not make a report wrong today; it makes the eventual upgrade larger, because the schema changes accumulate and arrive together. The reporting work is the same either way — inventory what depends on the schema, baseline it, and expect the gap between your version and the current one to be roughly the size of the repair. ### Sources, and how dated these are The vendor-specific claims on this page are dated by nature and are stated here with their source so you can re-check rather than trust the page — the same convention used on the Claude articles on this site, and for the same reason: product timelines move, and a page quoted back as current two years later is worse than no page. - **Desktop feature freeze and end of fixes.** The Spring 2021 release (Prophet 21 2021.1) is described as the final desktop version to receive new features, with new development moving to the web application, and no further desktop fixes after 30 November 2022 — [Echopath, December 2020](https://echopath.com/p21-new-features-only-web-app/). That is a third-party summary of an Epicor announcement, not Epicor’s own page; treat the dates as a starting point and confirm them with your account team. - **Prophet 21 and Kinetic as separate products.** Separate code bases and roadmaps, aimed at distribution and at discrete/mixed-mode manufacturing respectively — [ERP Research product comparison](https://www.erpresearch.com/compare/epicor-kinetic-vs-epicor-prophet-21), and Epicor’s own product pages for each. - **Everything else here** — what breaks, what to inventory, what to measure — is method rather than vendor fact, and does not go stale on a release schedule. Vendor claims on this page last checked 18 August 2026. ### Related - [Epicor P21 and Kinetic reporting and integration](https://siegestack.com/prophet-21) — the main platform page, and the four data-model traps that make P21 reports quietly wrong. Start there if the question is *which rows count*. - [SQL Server performance for ERP reporting](https://siegestack.com/sql-server-erp-performance) — why logical reads are the honest baseline measure, and what applying an index to a vendor schema actually costs. - [Why your ERP report is fast on Today and times out on month-to-date](https://siegestack.com/erp-report-slow-month-to-date) — the worked example of the index that an upgrade is most likely to remove. - [Operations modernization](https://siegestack.com/operations-modernization) — including ERP carve-out and cutover, which is the re-implementation shape rather than the upgrade one. - [Case studies](https://siegestack.com/case-studies) — anonymised, with the measurement discipline this page argues for. #### Upgrade window on the calendar? The inventory and the baseline are worth doing before it opens, and they are cheap compared with reconstructing them afterwards. Thirty minutes to work out which of the three projects you are actually doing, and what it puts at risk. [Book a call](https://siegestack.com/prophet-21#schedule) · [Send it in writing](https://siegestack.com/contact) · [Back to the P21 page](https://siegestack.com/prophet-21) --- # Operations modernization https://siegestack.com/operations-modernization ## Operations Modernization Consolidate onto one system of record. Then optimize on top of it. In that order. Most distribution and manufacturing operations carrying two systems of record know they should consolidate. What they usually miss is that the consolidation and the optimization are the same project, and doing them in the wrong order wastes both. **The sequencing principle.** Do not optimize what is being retired, and do not strand the data the optimization depends on. Those two sentences determine whether this work compounds or gets rebuilt. ### Why Order Matters More Than Effort While a business runs on two systems, every network-level answer is assembled across two sources of truth by hand. Those answers are not merely imprecise — they are wrong in ways that are difficult to detect, because nothing about a reconciled spreadsheet announces which side of it was stale. So the analysis that would justify the investment cannot be trusted until the consolidation is done. And the consolidation, done without regard for the analysis, throws away exactly the history the analysis needs. | Dependency | Consequence if ignored | | Optimization models are built against the destination system, once | Logic built against the system being retired is discarded at cutover, then built again. | | Operational history is migration scope, not an archive afterthought | Pick, lot and bin history is the input to the velocity model. Left in a decommissioned database, a migrated site restarts its optimization clock at zero. | | Network analysis waits for a single system of record | Transfer-versus-buy computed across two systems will be confidently wrong, and the errors will not be obvious. | | Destination layout is designed, not copied | Migration is the one moment warehouse layout can be redesigned at near-zero marginal cost. Copying the legacy layout forfeits it, and changing it later means physically moving inventory you just put away. | That last one is the one people miss. If sites migrate before the slotting model exists, they get loaded into a bin structure mirroring whatever they had. Fixing it afterwards is a physical cost, not a data cost. ### The Three Tracks Track 1 #### Consolidate Migrate the remaining business onto one system of record, with operational history brought forward rather than stranded. Seven workstreams get planned and scoped from day one: master data, inventory, open transactions, financial, history, customizations and integrations, and reporting. The first four are what everyone plans for. **The last three are what run migrations late**, because they are typically discovered in month three rather than scoped in month one. **Guiding principles we hold to:** - **One system of record, with a hard date.** Indefinite parallel operation is the most expensive available outcome and the most common one. - **Migrate the business, not the database.** Where the destination does something differently and adequately, adopt it. Recreating legacy behaviour requires justification, not default acceptance. - **Balance before beauty.** Inventory quantity and value, receivables, payables and open backlog reconcile exactly before anything else is judged. - **Every extract is repeatable.** Scripted, versioned, re-runnable. No hand-edited spreadsheets in the load path — these get run many times before the run that counts. - **Retain the cross-reference permanently.** The old-key-to-new-key map is the only way to answer questions about pre-migration history for years afterwards. **History strategy is a business decision, not a technical one**, and it is the single largest lever on cost and timeline. We generally recommend migrating open items plus item-level usage and sales summary — preserving what the analytical work needs at a fraction of full-detail cost — while treating pick, lot and bin history as in-scope regardless, because that is the input to Track 2. **Electronic trading partner integration is almost always the critical path.** Map rebuilds and partner-side test cycles are measured in weeks and cannot be compressed by adding staff. We enumerate partners in week one and open those conversations during build, not after it. Track 2 #### Optimize Once the data is in one place, the same analytical capacity that accelerated the migration turns to the operational questions. **Slotting.** Assign every item to a bin whose travel cost matches its pick frequency, subject to cube, weight, zone and handling constraints. Re-evaluated on a cadence rather than set once a decade. Conventional ABC classification ranks by dollar volume, which is the wrong primary axis for slotting — a high-value item picked twice a month should not occupy prime real estate. The model uses three inputs instead: **velocity** derived from pick-line frequency weighted toward recent periods, **handling class** from cube, weight and pack configuration, and **affinity** — items that repeatedly appear on the same order, slotted near each other regardless of individual velocity. That third one is where pattern detection genuinely beats judgement, because nobody holds pairwise co-occurrence across tens of thousands of items in their head. **Multi-order consolidation.** Three orders shipping today each containing the same item from the same lot means someone travels to that pallet three times and breaks it down three times. The pull should happen once, split downstream. We measure the duplicate-touch baseline first — that number is the size of the prize, and most operations do not know it — then build wave grouping that maximises shared touches while respecting ship date, carrier cutoff and staging capacity. Slotting and consolidation compound: consolidation reduces the number of trips, slotting reduces the cost of each trip. Done in isolation, either is diluted by the other. **Purchasing.** Where analytical capacity converts to cash fastest, because the levers are inventory dollars, freight dollars and unit price — all recorded in detail and rarely analysed in aggregate. In recommended sequence: lead-time reality check against promised dates with variance rather than averages; order point tuning where policy and actual usage have drifted apart; purchase order consolidation across sites to reach price breaks and freight thresholds; container fill analysis; forecast variance by programme; a forward expedite-risk list; and a vendor scorecard that is a standing report rather than an argument. **Network replenishment.** Most multi-site operations maintain sourcing item by item and never analyse it as a network. Items that should be centrally stocked and transferred are almost certainly being bought at several sites, and items needing local stock are almost certainly being transferred at a cost nobody has priced. Neither condition announces itself. This one waits for Track 1 to complete, for the reason in the table above. Track 3 #### Enable The failure mode of analytical work is that it lives in one analyst's spreadsheet and leaves when they do. The model gets built once and parameterised per site. Each location has its own velocity profile, zone topology and constraint set, so recommendations differ — but the logic, the queries and the review workflow are shared. That is the difference between seven separate projects and one project run seven times. Alongside it: documented playbooks held somewhere the whole company can reach, and site leads brought into the analysis months before their site is affected. People who have followed the work adopt the result faster than people handed a finished workbook on go-live day. ### How the Engagement Runs **Prove, package, propagate.** Run the full analysis at one proving-ground site. Convert what worked into a documented playbook. Apply it at the next site with that site's data. Each subsequent site should take materially less effort than the one before — and if it does not, that is the signal the playbook is not yet real. **Baselines before changes.** Travel proxy per pick ticket, share of picks from prime locations, forward-bin replenishment frequency, and how much prime space slow movers currently occupy. Without these, any claimed improvement is an opinion. Capturing them is the first two weeks. #### The access model Nothing here is a black box making operational decisions. Access escalates in three stages, and each one has to earn the next: | Stage | What it means | | Read-only | Analysis against a snapshot. Nothing is written anywhere. | | Human-executed recommendations | Output arrives as a reviewable workbook — current state, proposed state, expected effect, and the sequence required to get there without deadlocking. A supervisor approves or rejects. | | Staged writes | Approved changes staged as instructions, still executed through the host system's own transaction path, still with a human approving the batch. | ### What You Actually Receive - A costed migration plan with the six gating business decisions surfaced and answered, not deferred. - Scripted, versioned, re-runnable extracts and a reconciliation pack per data domain. - An inventory of every customisation, integration, scheduled job and report, each with an explicit retire / replace / rebuild decision and a named owner. - A slotting model parameterised per site, delivered as a reviewable move plan with an execution sequence. - A duplicate-touch baseline and a wave grouping plan. - A purchasing analysis pack covering lead-time variance, order point drift, consolidation candidates and vendor performance. - Documented playbooks, so site two costs less than site one. ### Work That Stands on Its Own The three tracks describe a full programme. Most of what follows also gets bought singly, usually because something has a date on it — an acquisition closing, a lease ending, an audit booked, or the one person who understood a system handing in their notice. #### EDI operations Trading partner onboarding and mapping across X12 and EDIFACT — 830, 850, 856, 810, 862, DELFOR — through partner testing and certification into production. The build is rarely the hard part. What fails is everything around it: **suspense records nobody is watching**, so a rejected document is discovered by the customer rather than by you; per-partner configuration that exists only in one person's memory; and no triage procedure, so every exception escalates to whoever set it up. We deliver monitoring that alerts, a triage runbook, each partner's configuration written down, and a handover — because the honest test of an EDI operation is what happens the week after the person who built it leaves. Related to the note in Track 1: partner-side test cycles are measured in weeks and cannot be compressed by adding people. Enumerate partners in week one. #### ERP carve-out and cutover The inverse of Track 1. An acquired facility arrives running on the seller's system, usually under a transition services agreement with an expiry date, and has to be stood up on yours before the clock runs out. Item and supplier masters, pricing, open orders, in-flight inventory and the history decision — the same seven workstreams as a consolidation, on someone else's timetable and with a counterparty whose interest in the extract ends the day they hand it over. Get the data requirements into the transition agreement while there is still leverage to ask. The cross-reference map matters more here than anywhere, because the questions that arrive afterwards are about records that were created in a system you no longer have. #### Site standup, relocation and consolidation A warehouse move is a systems project wearing a forklift. Opening a location, closing one into another, or moving one across a country all reduce to the same problem: knowing how the site actually works before anything changes, agreeing what the target looks like, then sequencing a cutover that orders keep flowing through. The documented as-is is the deliverable people skip and then need. It is what makes the target state an argument you can have on paper rather than a discovery you make on the first morning in the new building. Done across several sites and more than one country, including operations where the floor and the system had drifted years apart. #### Quality and compliance systems Selecting and implementing a quality management system, and getting material certifications out of shared drives and email into something searchable, current and attributable. The migration is the risk, not the platform. A certificate is evidence because of its provenance, so a document estate that arrives with its revision history flattened has lost the thing that made it worth keeping. Scope that before choosing the destination, not after. #### Vendor- and customer-managed inventory Programmes where you hold or replenish stock on a customer's site: kanban and just-in-time scanning, license plates, bin and label services, replenishment signals, and the packing and picking measures that show whether the thing is actually working. These fail at the seam. The customer's floor and your ERP disagree, and the disagreement is invisible until a line goes down. Build the reconciliation before the volume. #### Inventory accuracy and demand planning Cycle counting and physical inventory that produce a number that survives challenge, plus forecast loading and demand planning on top of it. A count is a measurement, not a fix. Recurring variance in the same places is a process or a data problem that counting merely reveals — and re-counting an operation that has not addressed the cause is an expensive way to rediscover it each quarter. #### System selection and rationalization Vendor-neutral evaluation: what the operation actually needs, what each candidate genuinely does under its demo, what it costs to run rather than to buy, and which of the tools you already pay for it makes redundant. No reseller relationship, no referral fee, nothing to sell you on the other side of the recommendation. A defensible answer is sometimes that you already own something that does the job and nobody was ever trained on it. ### Who This Is For Operations running more than one system of record, or one system nobody has analysed in aggregate. Multi-site distribution where transfers happen but nobody has priced them. Warehouses where slotting was set once and has not been revisited. Purchasing where order points are maintained by hand and lead times are assumptions rather than measurements. This is a different job from reporting, and the two work best together. A dashboard tells you what happened and where to look — [we build those too](https://siegestack.com/#solutions), and most engagements start there because you cannot change what you cannot yet see. This work is the step after: it changes where things sit, how they are picked, and what gets bought. See it, then change it, then watch the same dashboard confirm the change landed. If you are not sure which of the two you need yet, [technology for distributors](https://siegestack.com/industries/distribution) lays out the five kinds of work side by side and what each one is for. #### Start with the baseline The first conversation is about what you can measure today, because that determines what any of this can be held to later. [Get in touch](https://siegestack.com/#schedule) · [See the integration work](https://siegestack.com/etl-showcase) · [How the analysis actually gets done](https://siegestack.com/working-with-claude-blog) --- # CRM and ERP integration showcase https://siegestack.com/etl-showcase ## CRM & ERP Integration Showcase Custom ETL pipelines, API integrations, and data engineering — see exactly how we connect your systems ### Bulletproof Reliability Every system we build anticipates failures before they happen. Your data stays safe, your integrations stay connected, and your business keeps running. ### Zero Downtime Graceful error recovery means problems get logged and fixed automatically. No 3 AM phone calls, no lost transactions, no angry customers. ### Scales With You Built to handle millions of records from day one. As your business grows, your systems grow with you - no expensive rewrites needed. ### Systems We Work With Every Day We've built integrations with these platforms dozens of times. We know where the gotchas are, what the APIs don't tell you, and how to make them work reliably. #### CRM Platforms Sync your customer data, automate workflows, and keep your sales team in the loop. Salesforce ✓ HubSpot ✓ Zoho CRM ✓ Pipedrive ✓ Microsoft Dynamics ✓ #### ERP Systems Deep integration with distribution and manufacturing ERPs. We speak your system's language. Epicor P21 ✓ Epicor Kinetic ✓ Infor ✓ Dynamics NAV ✓ Macola ✓ #### E-commerce & Retail Unify your sales channels, sync inventory, and automate order fulfillment. Shopify ✓ WooCommerce ✓ BigCommerce ✓ Amazon ✓ Square ✓ Don't see your system? If it has an API, we can connect it. **Corrected 27 August 2026.** This line said “We’ve integrated with 50+ platforms.” Nobody counted them. The figure is removed rather than replaced with a smaller one, because the honest number is not known either — the systems named above are the ones this site is willing to stand behind. Want to see how we actually build these integrations? Below are real code examples - the same patterns we use in production. Share this with your technical team if you'd like them to review our approach. ETL Pipeline SQL Queries API Integration Web Services Monitoring #### Salesforce CRM Sync 3 error patterns handled #### Shopify Order Processing 3 error patterns handled #### Data Warehouse Load 3 error patterns handled extraction.js Production Ready async function extractData(source) { try { const connection = await db.connect({ host: source.host, timeout: 30000, retries: 3 }); const data = await connection.query( 'SELECT * FROM orders WHERE updated_at > ?', [lastSync] ); return { success: true, data, count: data.length }; } catch (error) { if (error.code === 'ETIMEDOUT') { await notifySlack('DB timeout - switching to backup'); return await extractFromBackup(source); } logError('extraction', error); throw new RetryableError(error); } } #### Error Handling Strategies Connection timeout Auto-retry with exponential backoff Schema mismatch Dynamic field mapping with validation Data type conflicts Type coercion with fallback defaults #### Complex Analytics Query Revenue analysis with error-safe aggregations WITH daily_revenue AS ( SELECT DATE(order_date) as day, SUM(COALESCE(amount, 0)) as revenue, COUNT(DISTINCT customer_id) as customers, -- Handle NULL values safely COUNT(CASE WHEN status = 'failed' THEN 1 END) as errors FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '90 days' AND amount IS NOT NULL -- Filter invalid data GROUP BY DATE(order_date) ) SELECT day, revenue, customers, -- Prevent division by zero CASE WHEN customers > 0 THEN revenue / customers ELSE 0 END as avg_per_customer, -- Safe percentage calculation ROUND( 100.0 * errors / NULLIF(customers + errors, 0), 2 ) as error_rate_pct FROM daily_revenue ORDER BY day DESC; #### Data Quality Check Validation query with anomaly detection SELECT 'Null Customer IDs' as issue, COUNT(*) as count, CURRENT_TIMESTAMP as checked_at FROM orders WHERE customer_id IS NULL UNION ALL SELECT 'Negative Amounts' as issue, COUNT(*) as count, CURRENT_TIMESTAMP FROM orders WHERE amount CURRENT_TIMESTAMP -- Alert if any issues found HAVING SUM(count) > 0; #### Incremental Sync Query Safe delta extraction with watermarking -- Get last successful sync timestamp WITH last_sync AS ( SELECT COALESCE( MAX(sync_timestamp), TIMESTAMP '2024-01-01' -- Safe default ) as watermark FROM etl_metadata WHERE pipeline = 'orders' AND status = 'success' ) SELECT o.order_id, o.customer_id, o.amount, o.updated_at, -- Include metadata for tracking CURRENT_TIMESTAMP as extracted_at FROM orders o CROSS JOIN last_sync ls WHERE o.updated_at > ls.watermark AND o.updated_at #### Stripe Payments 3 error patterns handled #### Shopify Webhooks 3 error patterns handled #### Inventory Aggregation 3 error patterns handled rest-client.js Production Ready class APIClient { constructor(baseURL, options = {}) { this.baseURL = baseURL; this.timeout = options.timeout || 30000; this.retries = options.retries || 3; this.backoffMs = options.backoffMs || 1000; } async request(endpoint, options = {}) { let lastError; for (let attempt = 1; attempt controller.abort(), this.timeout ); const response = await fetch(`${this.baseURL}${endpoint}`, { ...options, signal: controller.signal, headers: { 'Content-Type': 'application/json', ...options.headers } }); clearTimeout(timeoutId); if (!response.ok) { throw new APIError(response.status, await response.text()); } return await response.json(); } catch (error) { lastError = error; if (error.name === 'AbortError') { console.warn(`Request timeout, attempt ${attempt}/${this.retries}`); } if (attempt setTimeout(resolve, ms)); } } #### Error Handling Strategies Network timeouts Exponential backoff with configurable retries Rate limit exceeded (429) Automatic retry with Retry-After header respect Server errors (5xx) Circuit breaker pattern with fallback #### E-commerce Checkout 3 error patterns handled #### Multi-Tenant SaaS 3 error patterns handled #### Real-time Order Tracking 3 error patterns handled server.js Production Ready const express = require('express'); const helmet = require('helmet'); const cors = require('cors'); function createServer(config = {}) { const app = express(); // Security middleware app.use(helmet()); app.use(cors(config.corsOptions)); app.use(express.json({ limit: '10mb' })); // Request ID for tracing app.use((req, res, next) => { req.id = req.headers['x-request-id'] || crypto.randomUUID(); res.setHeader('x-request-id', req.id); next(); }); // Request logging app.use((req, res, next) => { const start = Date.now(); res.on('finish', () => { logger.info({ requestId: req.id, method: req.method, path: req.path, status: res.statusCode, duration: Date.now() - start }); }); next(); }); // Global error handler app.use((err, req, res, next) => { const status = err.status || 500; const isOperational = err.isOperational || false; logger.error({ requestId: req.id, error: err.message, stack: isOperational ? undefined : err.stack, status }); res.status(status).json({ error: isOperational ? err.message : 'Internal server error', requestId: req.id }); }); // Graceful shutdown const server = app.listen(config.port || 3000); process.on('SIGTERM', async () => { logger.info('SIGTERM received, shutting down gracefully'); server.close(() => { logger.info('Server closed'); process.exit(0); }); }); return { app, server }; } #### Error Handling Strategies Unhandled exceptions Global error handler with structured logging Process termination Graceful shutdown with connection draining Request tracing failures Correlation IDs with distributed tracing #### Production Monitoring That Actually Helps Every integration we build includes real-time monitoring. You see what's happening, we get alerted before problems become emergencies. Integration Health Dashboard Example — not live data Every number and event below is **made up to show the layout**. Nothing here is measured, nothing is connected to a running system, and no client's figures appear on this page. It is here to show what you get to look at, not what anyone achieved. API Uptime 99.97% Avg Response Time 142ms Retry Rate 2.3% Records Processed 1.2M Example events 2 hrs ago RESOLVED Salesforce API rate limit approached - auto-throttled 4 hrs ago INFO Scheduled sync completed: 45,231 records in 3m 42s Yesterday WARNING Shopify webhook delay detected (avg 2.1s) - monitoring 2 days ago RESOLVED P21 connection timeout - failover to replica successful **Corrected 21 August 2026.** Until today this panel carried a pulsing “Live” indicator and week-on-week deltas — “+0.02% vs last week” and the rest — with nothing anywhere saying the figures were invented. It read as telemetry from a running system. The indicator and the deltas are gone and the panel is now marked for what it is. Stating this rather than quietly editing it is the same rule that keeps unmeasured percentages off the rest of this site. retry-handler.js Production Ready // Smart retry logic with exponential backoff and circuit breaker class ResilientApiClient { constructor(config) { this.baseUrl = config.baseUrl; this.maxRetries = config.maxRetries || 3; this.baseDelay = config.baseDelay || 1000; this.maxDelay = config.maxDelay || 30000; this.timeout = config.timeout || 10000; // Circuit breaker state this.failures = 0; this.circuitOpen = false; this.circuitResetTime = null; this.failureThreshold = config.failureThreshold || 5; this.circuitResetTimeout = config.circuitResetTimeout || 60000; } async request(endpoint, options = {}) { // Check circuit breaker if (this.circuitOpen) { if (Date.now() = 400 && error.status = this.failureThreshold) { this.openCircuit(); } // Calculate delay with exponential backoff + jitter if (attempt controller.abort(), this.timeout); try { const response = await fetch(`${this.baseUrl}${endpoint}`, { ...options, signal: controller.signal, headers: { 'Content-Type': 'application/json', ...options.headers } }); if (!response.ok) { const error = new Error(`API error: ${response.status}`); error.status = response.status; error.retryAfter = response.headers.get('Retry-After'); throw error; } return await response.json(); } finally { clearTimeout(timeoutId); } } calculateDelay(attempt, error) { // Use Retry-After header if provided (rate limiting) if (error.retryAfter) { return parseInt(error.retryAfter) * 1000; } // Exponential backoff: 1s, 2s, 4s, 8s... with jitter const exponentialDelay = this.baseDelay * Math.pow(2, attempt); const jitter = Math.random() * 1000; // 0-1s random jitter return Math.min(exponentialDelay + jitter, this.maxDelay); } openCircuit() { this.circuitOpen = true; this.circuitResetTime = Date.now() + this.circuitResetTimeout; this.alertOps({ type: 'circuit_breaker_open', message: 'Too many failures - circuit breaker activated', resetTime: new Date(this.circuitResetTime).toISOString() }); } async logRetry(details) { // Log to your monitoring system console.log(`[RETRY] ${JSON.stringify(details)}`); await metrics.increment('api.retry', { endpoint: details.endpoint }); } async alertOps(alert) { // Send to Slack, PagerDuty, etc - but batched, not spammy await alertService.send({ ...alert, timestamp: new Date().toISOString(), service: this.baseUrl }); } sleep(ms) { return new Promise(resolve => setTimeout(resolve, ms)); } } // Usage example const salesforce = new ResilientApiClient({ baseUrl: 'https://yourinstance.salesforce.com/services/data/v57.0', maxRetries: 3, timeout: 15000, // 15s timeout failureThreshold: 5, // Open circuit after 5 consecutive failures circuitResetTimeout: 60000 // Try again after 1 minute }); ##### Scenarios This Handles Slow API responses (timeout) Configurable timeout + abort controller kills hung requests Rate limiting (429 errors) Respects Retry-After header, exponential backoff with jitter Cascading failures Circuit breaker prevents hammering a dead API Alert fatigue Batched alerts, severity levels - you get summaries, not spam #### Like What You See? This is how we build every system. Your project gets the same attention to detail, the same bulletproof error handling, the same production-ready code. [Schedule a Call](https://siegestack.com/#schedule) [Learn More About Us](https://siegestack.com/) The two service pages behind this showcase describe what an engagement looks like rather than what the code looks like: [ERP integration](https://siegestack.com/services/erp-integration) for making two systems agree, and [ETL & data pipelines](https://siegestack.com/services/etl-data-pipelines) for moving the data between them on a schedule you can audit. Feel free to share this page with your technical team for review --- # How to use Claude AI effectively https://siegestack.com/working-with-claude-blog Claude AI Workflow · Real Case Study ## How We Use AI in Production Engineering **Most people are using Claude wrong.** They treat it like a chatbot. I use it like a production system — and it ships hours of real work every week. By **Scott Allen Willis** · April 10, 2026 · Updated August 27, 2026 · ~24 min read · [View as slide deck →](https://siegestack.com/working-with-claude) How to Use Claude AIClaude AI WorkflowClaude Best PracticesClaude vs ChatGPTClaude PromptingAI Iteration LoopSystem Design This guide is for people who already opened a Claude tab, got an answer that *almost* worked, and walked away thinking AI is overhyped. It's not overhyped. You're using it like a search engine when you should be using it like a junior engineer who never sleeps. Below is the exact workflow I use to ship production code, audit databases nobody documented, and harden the things I have already shipped. The same loop, every time, across every domain. **The short version, if you are deciding rather than doing.** The value is not in the prompt. It is in the loop around it: **define what done means, draft, test against the real system, give feedback precise enough to converge, then ship or repeat.** Almost everyone stops after the draft, which is why almost everyone concludes the tool does not work. Two consequences worth a manager's attention. **Verification has to be a step somebody owns**, not a habit you hope for — the failure mode is output that is plausible, well-structured and wrong, and it is discovered in production or not at all. And **route work by difficulty, not by defaulting to the biggest model**; it is the same delegation judgement you already apply to people, and it is where most of the cost savings live. Everything below is evidence for those two claims, including the times they cost me. If you want the argument rather than the receipts, you have already got it — the rest is a conversation. **Updated August 2026.** This article now includes the security and correctness work that came after the original version: taking a content security policy from B to A+, closing a data exposure I had shipped myself, three production bugs that report nothing, and how to prove a mechanical refactor changed nothing. Corrections are marked where they appear rather than quietly edited, because an article about verification that silently rewrites its own history is worth nothing. Six are marked so far: a heading that credited a performance gain to a larger population than it was measured over, two invented statistics — how often the model is wrong, and how many people skip the testing step — the claim that Claude has no memory between sessions, the claim that multi-modal work belongs to ChatGPT, and the outcome of the litigation — kept in the corrections record even though the section it belonged to has been removed. What changed on the product side, and how I checked it, is its own section — with sources. **What is Claude AI?** Claude is a large language model built by [Anthropic](https://www.anthropic.com/) for reasoning, coding, long-form writing, and tool use. The most effective way to use Claude is not "better prompting" — it's wrapping the model in a structured workflow with real artifacts, real tests, and tight feedback loops. Prompting is Stage 1. System design is Stage 2. This guide is about Stage 2. ### What Most People Get Wrong About Claude Three failure modes account for almost every "Claude isn't that useful" complaint I see online: - **They one-shot it.** They paste a vague request, take the first answer, and use it. No iteration, no verification, no second pass. Then they're surprised when the output is generic. - **They feed it summaries instead of artifacts.** They describe their problem in English when they should paste the actual error, the actual query, the actual log line. Summaries leak the exact signal Claude needs. - **They treat the chat as the system.** They run 40-message threads until compaction breaks them. The chat is a tool. The system is your workflow around the chat — your scoped tasks, your context files, your verification loop. If you fix only those three things, your Claude experience improves more than switching models ever will. ### My Actual Claude Workflow (Step-by-Step) People keep asking me for my "prompt template." There isn't one. The thing that matters is the loop, not the wording. Here it is, in full: **Define the outcome → Claude drafts → Test against reality → Specific feedback → Ship or repeat.** The critical insight: most people stop at step 2. They get a draft and try to use it. The real value is in steps 3 and 4 — testing against your actual environment and giving feedback precise enough that the next iteration converges instead of wandering. That's it. That's the engine. Below is what each step actually means in production. #### 1. Define the outcome (before you type a single word) This is the single most important step and the one people skip. You must know exactly what "done" looks like in production reality. Not "a good query." Not "a legal-looking brief." **Done** means: copy-paste into the live environment with zero errors, or filed with zero procedural defects, or a function that logs real probes and blocks nothing legitimate. Write the definition in plain English first: *"After this loop, I will have a single script that replaces the legacy ones, runs in under two seconds on a 10k-row dataset, and requires zero user training."* A model is a pattern-matcher, and a good one. If the target pattern is fuzzy, the output is fuzzy. Clarity here is the entire game. #### 2. Claude drafts Feed the crystal-clear outcome plus all the real data — never summaries. Paste the actual query text, the actual docket entries, the actual log lines, the actual analytics exports. State your hard constraints up front. One-shot draft. No hand-holding yet. Let it go full creative. You'll fix it in the next steps. #### 3. Test against reality (this is the step that gets skipped) **Correction, 21 August 2026.** This heading said “this is where 95% of AI users fail.” Nobody measured that, and an article about testing claims against reality does not get to publish one it invented. The figure is removed; the point it was decorating stands. You become the merciless QA department. Run the code in a real dev environment. File the draft in test mode. Deploy the function and hit it with real traffic. Check the indexing tools for the new content. Document every failure with surgical precision. Do not say "it's wrong." Say: *"Line 47 throws an invalid handle on the temp buffer because the query uses an alias that only exists in the older code path."* #### 4. Specific feedback (the convergence engine) This is the art. Your feedback must be so precise that the next draft cannot possibly make the same mistake. - **Bad:** "Make it better." - **Good:** *"The script fails on records where the invoice number contains a hyphen because you assumed all invoice numbers are numeric. Add a string check and handle that case explicitly."* - **Even better:** *"Reproduce the exact error I just saw, then fix it, then show me the before/after diff so I can verify."* You are training the model on your exact domain reality in real time. That training only works if your descriptions are reproducible. #### 5. Ship or repeat Two choices only. **Ship** = it passes your production test with zero caveats. **Repeat** = go straight back to step 3 with the new failure data. No "maybe one more prompt." The loop is sacred. You keep repeating until the output is production-ready every single time. That's why the audit work delivered measurable gains, and why the exposures and silent faults described below were found at all. ### Why This Loop Crushes Every Other AI Workflow - **It forces you to stay the domain expert.** You define done. You test reality. The model can't hide behind vagueness because you removed it. - **It turns hallucinations into fuel.** Every failure becomes training data for the next iteration. - **It compounds across domains.** The more loops you run on one kind of work, the faster the next kind converges, because precise feedback becomes a habit rather than an effort. - **It's model-agnostic.** Swap Claude for Grok, Gemini, GPT — the loop still works because the human is the constant. ### Pro Tips to Make Your Loop Tighter - **Keep a "war room" context file.** Paste it at the start of every new session: definition of done, previous failures, system constraints. Externalized memory beats hoping the model remembers. *This is a Project — see what moved.* - **Build a personal pattern library.** After every successful ship, save the final output plus the exact feedback that got it there. That library is more valuable than any prompt template you'll find online. *This is an Agent Skill — see what moved.* - **Include one worked example of a finished output.** Not a description of the format — an actual instance of the thing. This is the single highest-yield addition I have made to the loop since writing the original version of this article, and it was missing from my own template for two years. An example pins length, tone, structure and level of detail simultaneously, in a way that no amount of adjectives does. If you don't have a real one to hand, write a fake one; a fabricated example of the right shape works nearly as well as a real one. - **Say that "I don't know" is an acceptable answer.** One sentence in the brief. A model with no explicit permission to report a gap will bridge it, because producing something is the default behaviour and the bridge is usually plausible. A model that has been told an admitted gap is a valid deliverable will hand you the gap — and a gap is actionable in a way that a confident wrong answer is not. - **Time-box every loop to 15–20 minutes.** If it isn't converging, the outcome wasn't defined clearly enough. Go back to step 1. Don't keep prompting. That's the entire methodology. It's boring, unsexy, and brutally effective — exactly like real engineering. Everything else in this article is what happens when you run this loop, hard, against real problems. ### Professional: A 27% Performance Gain Across 70 Rewritten Database Views **Correction, 27 August 2026.** This heading said “Across Hundreds of Database Views.” Hundreds were audited; the ~27% is aggregated over the **70 that were actually rewritten**, which is a different and smaller population. The audit figure and the gain figure sat beside each other in the summary below and the heading merged them. I work on line-of-business systems built on relational databases. Custom views accumulate over years. Reports get slow. The interesting question is never "is this view slow" — it's "is the join order wrong, is there a non-sargable predicate, is the index even being used." Working with Claude on the audit, I batched the changes into discrete change sets and treated each one as a unit of work with a baseline, a hypothesis, and a rollback. Two patterns came up over and over: - Date math wrapped around an indexed column in a WHERE clause kills sargability. Rewriting it so the column appears bare on one side makes the index usable again. - Scalar functions in SELECT lists silently force row-by-row execution. Inlining them is ugly but fast. 300+ views audited ~27% aggregate gain 70 change sets 99%+ match rate How the gain was measured: **elapsed time**, against a baseline captured with a **SQL Server trace**, aggregated over the **70 rewritten views** rather than over everything audited, on **10 March 2026**. The 99%+ match rate above is the same audit, measured differently: each rewritten view’s result set compared against the original’s, with a match defined as exact equality on every field. The reconciliation rate further down this article is a different measurement on different work. None of those patterns are clever. The leverage was running the loop fast enough that we could touch dozens of change sets in the time a single audit normally takes. Claude wasn't doing the optimization — I was, with Claude as the rubber duck that could also write the rewrite. If you want the reverse-direction story — pulling data out of complex systems for real reporting — that's exactly what we build at the [SiegeStack ETL Showcase](https://siegestack.com/etl-showcase). ### Professional: Retiring Nine Spreadsheet Macros The other professional win was an architecture change disguised as a cleanup. The process ran on nine spreadsheet macros. Each one had to be opened by a person, on a particular machine, in a particular order. They broke in the way macros always break: someone had the file open, or a column moved, or the workbook was on a laptop that was at home that day. When it failed there was no log, only a person noticing that something downstream looked wrong. The replacement has three parts, and the separation between them is the whole point. **PowerShell does the work.** One script instead of nine macros, watching for input, querying the database directly, and reconciling records. It runs on the server rather than on somebody's desktop, so it does not care whose machine is on or who has a file open. **SQL Server Agent runs the schedule.** This is the choice people skip past, and it is the one that mattered. The obvious option was the operating system's task scheduler. Agent was better for reasons that have nothing to do with elegance: it already lived next to the data, it keeps job history without anyone building logging, it has retry and failure notification as configuration rather than code, and it is somewhere the people who look after the database already look. A scheduled task on a random server is invisible until it has been failing for three weeks. **A web service delivers the result.** Previously the output was a file that got sent to people. Files go stale the instant they are sent, they fork into six slightly different versions, and nobody can tell which is current. Exposing the result over a service inverts that — consumers ask for it when they need it and get the current answer, and other systems can consume the same endpoint instead of someone re-keying numbers out of an attachment. Reconciliation runs at 99%+. The remaining percent is genuinely ambiguous and is surfaced for a human rather than guessed at, which is the same errors-versus-warnings distinction as the tool above. Anything a machine cannot decide honestly should be handed to someone who can, not resolved quietly on its behalf. How that rate was measured: the new script’s output compared against the legacy output the macros produced, row by row, with a match defined as exact equality on every field, on 4 May 2026. It is a different measurement from the match rate in the view-audit summary above, on different work — the two share a figure and nothing else. Claude co-authored all three parts. What Claude did not do was pick the architecture. The decision to put orchestration in Agent rather than a scheduled task, and to deliver over a service rather than as a file, came from knowing which failures actually hurt — and those decisions are most of the value. The code was the easy part. ### Professional: Mapping a Schema Nobody Had Documented Before the performance work above could start, there was a more basic problem: nothing described how the pieces fit together. Hundreds of view definitions had accumulated over years, each one joining tables that joined other tables, and no map existed. You cannot safely change what you cannot see. So the first project was building the map. Claude wrote a parser that read every view definition, extracted the join relationships, and cross-referenced them against the declared key constraints. Output went to flat files rather than a dashboard, deliberately — a queryable artifact beats a pretty one when you are about to make seventy changes against it. The part that mattered was the parse log. Any definition the parser could not confidently interpret got written to a failure list instead of being silently skipped. That list was short, but everything on it was a genuine edge case worth a human decision. A parser that quietly drops what it does not understand produces a map that is confidently wrong, which is worse than no map at all. Claude wrote the parser. I decided what counted as a join in the ambiguous cases, and those decisions are the only reason the output was trustworthy. ### Professional: A Tool and Its Manual, Built Together A recurring manual task involved taking an export out of one system, reshaping it by hand into several differently-formatted files, and loading it back in. It worked, it took a long time, and it broke in quiet ways — a mistyped identifier or a date in the wrong format would fail halfway through a load and leave a partial mess behind. The replacement runs entirely in the browser. No backend, no upload, nothing leaves the machine. Drop the export files in, and the tool detects which is which, stamps the batch identifier down every row, normalizes the date formats, drops the header the exporter adds, and writes out the correctly-named files. The validation design is the part worth stealing. Failures are split into two tiers: conditions that guarantee a broken load are **errors** and stop you, while conditions that are merely suspicious are **warnings** and let you proceed. Collapsing those into one category is what makes people ignore validation entirely. Then the manual — a two-part slide deck, with every slide individually linkable so a colleague can be sent to step nine rather than to the document. Illustrating it produced the most instructive failure of the whole project. The obvious approach was to have the browser screenshot itself. That does not work: the captures could not be retrieved to disk. Second attempt, having the page capture its own DOM, also failed. What eventually worked was dropping out of the browser entirely — an OS-level screen capture driven from a shell script that brings the right window forward, switches to the right tab, grabs the pixels, and crops them. Three approaches, two dead ends, and the working one was the least elegant. That is the loop doing its job. The first idea was reasonable, it was wrong, and finding that out took minutes instead of an afternoon. ### Personal: Taking a Security Policy from B to A+ The same site scored a B. Not because anything was wrong with the server configuration — the headers were all present — but because the content security policy carried `unsafe-inline`, and a policy with `unsafe-inline` in it is not a policy. It is a header that looks like one. It permits precisely the class of injection the policy exists to stop. Removing it is not a configuration change. It means removing everything that made it necessary, and on a site that has grown organically that is a lot: 258 inline event handlers → 0 118 inline scripts → 0 49 style blocks → 0 1,316 inline style attributes → 0 The end state has no `unsafe-inline` and no `unsafe-eval` in any directive, and the grade went from B / 75 to A+ / 125. The interesting part is the exception. Two hundred and three structured-data blocks stayed inline and that is correct: browsers never execute them, and the script directive does not govern them at all. A sweep that "fixed" those would have broken every piece of structured data on the site in exchange for nothing. **Knowing which rule does not apply is worth as much as knowing which one does**, and it is the kind of judgement a model will not make for you unless you already know the answer. ### Personal: Closing a Data Exposure I Had Shipped Myself This one is uncomfortable to write up, which is the reason to write it up. A members table held real personal data — names, email addresses, phone numbers, cities. The signup page talked to the database directly from the browser, using a key printed in the page source, scoped by an email address that the database had no way to verify. Row-level security had never been enabled. From an unauthenticated browser on the other side of the internet, that table returned success and every row in it. The fix has two halves and both were necessary. On the code side, every operation moved server-side into a function that takes the caller's identity from a **verified ID token** — signature checked against the identity provider's public keys — and ignores whatever email the request body claims. The browser key and the client-side database SDK came off the page entirely. On the database side, row-level security was enabled and forced, and the public grants revoked. Verified from an outside browser: the table now refuses. Thirty-six assertions cover forged, expired, unsigned, wrong-audience and tampered tokens. Two things came out of it that transfer to anything with a database behind it: - **Check for existing policies before enabling row-level security.** That table already had four access policies on it, written at some point and completely inert because the feature was switched off. Enabling it would have *activated* them. Revoking the grants is what actually closed the hole, because privilege denial is evaluated before policies are. - **A policy that matches a token claim does nothing if your identity provider is not the database's own.** The claim is null, so the policy matches no rows, and it looks exactly like working access control in the dashboard. This is the most convincing kind of broken security: it reads correctly, it deploys cleanly, and it protects nothing. ### The Bugs That Report Nothing Three separate production faults on the same project, none of which produced an error, a log line, or a failing check. All three read correctly in every file involved. I am grouping them because the lesson is identical. **A 404 that lasted six months.** The platform supports two redirect configuration files and one silently takes precedence over the other. A rule in the losing file never runs, and nothing anywhere reports this — both files are individually valid and both read as if they work. One endpoint returned 404 for six months. Another for two days. **Two hyphens.** A `--` inside an XML comment is illegal and makes the entire document not well-formed. Search Console reported "couldn't fetch" and zero pages indexed, while the URL returned 200, the correct content type, the correct length, and fetched perfectly when requested as the crawler. **Nothing in the HTTP response reveals it. Only parsing does.** I spent real time looking at headers for a problem that was four bytes of comment syntax. **A year-old stylesheet.** Static assets served immutable for a year with no filename fingerprinting, so every reference carries a version token. Ship markup that depends on a new CSS class without bumping that token and returning visitors get unstyled pages for up to a year — while it looks perfect to you, because your first visit fetched both fresh. The same trap bites during debugging: edit a stylesheet, retest without bumping, and a correct fix appears to have failed because the browser served you the cached copy. **The common thread:** every one of these passes review and produces no error anywhere. Configuration that reads correctly is not evidence that it works. The only thing that finds this class of bug is probing the live URL and parsing what actually comes back. ### Never Put a Credential on the Critical Path of a Public Form Two public forms on that site sent an email and returned success only if the send succeeded. When the mail credential died, every submission returned a 500 and **was destroyed** — the handler logged only the domain of the sender's address, never the address itself, so there was nothing to recover from afterwards. Two real people who wrote in over two days were lost that way. Not a theoretical data-loss bug; a specific one, with specific people on the other end who think they were ignored. Both forms now post to a platform form handler that records the submission before any mail is attempted. **Read it, write it down, then try to send it.** Anything that can fail independently of the user's intent belongs after the durable write, never before it. The same project produced a second lesson about mail that is worth having in advance: the SMTP handshake time was measured at 28.7s, 2.3s, 21.5s, 22.4s and 1.5s. The timeout was set to 7 seconds, which meant roughly half of all sends aborted — and it failed *only in production*, while identical code worked locally every time. I initially wrote the slow handshakes off as an artifact of my home connection. That assumption cost considerably more time than the bug did. ### Proving a Refactor Changed Nothing Removing 1,316 inline styles is a mechanical change across dozens of files, and "I read the diff and it looks equivalent" is not a claim anyone should accept, including from themselves. So the verification was measurement rather than reasoning. Check out the pre-change tree into a temporary directory, serve it and the working tree on two local ports, and for every rendered element on every page read about twenty computed properties, concatenate them, and hash the result. Compare the hashes. Compare the geometry too — bounding rectangles and document height — because identical computed styles will not reveal a font-metric shift. It caught two real bugs that looked fine in the diff and produced no console error: - Three pages had a `