How SyteRay Classifies Every SyteLine Stored Procedure's Side Effects
Open the programmability folder of a production SyteLine database and you will find the real ERP: thousands of stored procedures accumulated over decades of implementation, customization, and patching. On SyteRay's reference deployment there are 8,636 of them. They encode pricing logic, posting rules, MRP behavior, and site-specific customizations, and in most shops no single person can say what all of them do. That opacity is a tax on every change: developers move slowly because any procedure might have hidden side effects, and it is a hard blocker for AI, because a system that executes procedures without knowing their effects is a hazard. SyteRay's answer is to crawl the entire catalog and classify every procedure's side effects as data. This post explains what that analysis produces and what it unlocks.
The Problem: Effects Are Invisible Until They Bite
A stored procedure's name tells you almost nothing about its blast radius. A procedure that looks like a report might update a staging table. A validation routine might call a posting procedure three levels down. In SyteLine environments the call graphs run deep: procedures call procedures that call procedures, and the effective side effects of the top-level call are the union of everything underneath.
This is why experienced SyteLine developers are so conservative. The senior engineer who has been at the site fifteen years carries the call graph in their head; everyone else reads code for hours before touching anything. That knowledge should be infrastructure, not tribal memory.
The Crawl: Cataloging the Whole Programmable Surface
At install time, and on demand afterward, SyteRay crawls the database's stored procedures and parses each one's body. For every procedure it extracts the tables read, the tables written (inserts, updates, deletes), the procedures called, and the parameters it takes. On the reference deployment this catalog covers all 8,636 procedures.
Direct effects are not enough, so SyteRay also resolves the call graph into an execution dependency map and computes transitive effects: if procedure A calls B and B writes a table, then A effectively writes that table too. On the reference deployment, 799 procedures with no direct data modification of their own were escalated to write-classified because something they call writes data. Those are exactly the procedures that fool a human reviewer skimming the code.
The classification is also temp-table-aware, which matters enormously in SyteLine: thousands of procedures write only to temporary tables, which is a very different risk profile from writing persistent data. The reference deployment's breakdown makes the point: of 8,636 procedures, 1,951 are read-only, 2,437 write persistent data, 3,343 write only temp tables, 819 use dynamic SQL, and a small remainder perform DDL or resist static classification. A stratified manual audit of 100 procedures across these classes found zero misclassifications.
- Every procedure is parsed for reads, writes, calls, and parameters.
- The call graph is resolved so effects propagate transitively through nested calls; 799 procedures were escalated this way on the reference deployment.
- Classification distinguishes persistent writes from temp-table-only writes and flags dynamic SQL separately.
- A 100-procedure stratified manual audit of the reference catalog passed with zero misclassifications.
Safety: Effect Classification as an Execution Gate
The first consumer of this catalog is SyteRay itself. When a user asks SyteRay to run a procedure, the effect classification decides how that request is treated. A procedure whose full transitive effect set is read-only can run on the normal answering path. A procedure with write effects, direct or inherited, is gated behind the same governance as record editing: the user sees what the procedure will affect and must explicitly confirm before it executes.
This transitive gating matters because the dangerous cases are exactly the non-obvious ones. A wrapper procedure that looks harmless but posts transactions two calls down is caught by the inherited write classification, not by hoping the name was descriptive.
Leverage: What Developers Get from the Catalog
The same data that keeps execution safe turns out to be the tool SyteLine developers have wanted for years. Impact analysis becomes a query: which procedures write to this table, directly or transitively? Answering that used to mean full-text searching procedure bodies and manually chasing call chains; now it is a question you ask in chat and get back with evidence.
Retirement candidates fall out of the same graph: procedures that no other procedure calls are the starting list for a dead-code review, which matters when you are planning an upgrade and every customization has a carrying cost. And onboarding changes character: a developer new to the site can ask what a procedure does and receive its actual read set, write set, and call graph rather than a folklore summary.
Why This Belongs in the Database Layer, Not in Documentation
Sites have tried to solve this with documentation before, and the documentation is always stale within a quarter. The difference here is that the catalog is derived from the code itself and refreshed automatically, so it cannot drift from reality the way a wiki does. It is the difference between a map someone drew once and a satellite view.
Effect classification is also what makes the rest of an ERP AI system honest. Evidence-first answering tells you where a number came from; effect classification tells you what an action will do before you take it. Together they make the system's behavior legible end to end, which is the bar an AI layer over a production ERP has to clear.
Key Takeaways
- 1SyteRay parses every stored procedure in your SyteLine database, 8,636 on the reference deployment, extracting reads, writes, calls, and parameters.
- 2Effects propagate through the call graph, so a procedure inherits the write effects of everything it transitively calls.
- 3The classification gates execution: read-only procedures run freely, write-effect procedures require explicit confirmation.
- 4The same catalog powers impact analysis, dead-code detection, and developer onboarding as simple queries.
Curious what your own procedure catalog looks like classified? A SyteRay evaluation includes the full crawl of your database. Contact ajay@netray.co.
More Insights
From Question to Governed SQL: How SyteRay's Evidence-First Answers Work
Generic AI chatbots fail on ERP data because they guess at schemas and invent numbers. SyteRay takes a different contract: every answer over your SyteLine database ships with the SQL that produced it and the tables it read. Here is the pipeline that makes that possible.
SyteRayWhy We Built Semantic Views for SyteLine: One Source of Truth for Open Orders
When two SyteLine reports disagree about how many orders are open, the root cause is almost never a bug. It is two different definitions. SyteRay's semantic layer encodes each business definition exactly once, as reviewable SQL views, and everything reads from them.
SyteRayGoverned Record Editing in SyteLine: Preview, Confirm, Audit, Undo
Letting an AI read your ERP is easy to accept. Letting it write is where trust is decided. SyteRay's editing flow makes every change previewable before it happens, explicit to confirm, fully audited after, and reversible. Here is the design.