The Shape of the Constraint
Confluxys-Confluxis
The Shape of the Constraint
Sage 100 Advanced stores its data behind a ProvideX ODBC driver. The driver implements a crippled SQL dialect. No subqueries. No ORDER BY. No OR operator. One join per query, wrapped in non-standard curly braces. Dates require escape syntax. It's read-only.
Calvin Graham wrote a clean reference guide for working within these constraints. Pull narrow result sets with tight WHERE clauses. Do sorting, aggregation, and string manipulation in PowerShell. Join tables in your script, not your query. It's well-executed. Practical. The right document for someone who needs data out of Sage by Friday.
It's also a manual for living inside someone else's limitation.
The Misrecognition
Here's what happens: you encounter a driver that can't do subqueries, so you write procedural code to compensate. You encounter a dialect that can't sort, so you sort in your script. Each workaround feels like problem-solving. Each one is.
But something else is happening underneath. You're treating the driver's limitations as the shape of the problem.
They're not. They're an artifact of the interface. ProvideX can't do subqueries because ProvideX was designed in the 1980s for a different purpose. That constraint tells you nothing about the problem you're actually trying to solve — which is: how do I ask questions about this data?
The constraint is not the territory. But most people optimize within it as though it were. They build increasingly clever scripts to compensate for a SQL dialect that will never improve. The workarounds compound. The scripts become their own maintenance burden. And the fundamental assumption — that you must query through this driver — never gets questioned.
The Substrate Change
The alternative: mirror the tables into SQLite.
Pull the data once through the crippled driver. Dump it into a database that speaks real SQL. Now you have subqueries, multi-table joins, window functions, aggregations, ORDER BY, OR — everything ProvideX won't give you. The questions you can ask are no longer bounded by the interface you happened to inherit.
This isn't an optimization. It's a substrate change. You're not making ProvideX queries faster. You're refusing to think in ProvideX.
The mirror costs something. You need to build the sync. You need to decide what tables to pull, how often, what staleness you'll tolerate. But once it exists, you can ask questions you didn't plan for. Graham's approach requires a new script for every new question. The mirror approach requires a new query — in a language that actually supports the thought.
This is the same move as the CAD file argument. When the encoding constrains what you can think, the answer isn't cleverer workarounds within the encoding. The answer is changing the substrate.
The Second Misrecognition
But most people who make the substrate change stop here. They build the mirror, set up a cron job to refresh it nightly, and treat the sync as a cost — the price you pay for escaping ProvideX.
Scheduled sync. Full table dumps. One direction, one clock. Sage is the trunk. SQLite is a branch. The mirror is always subordinate, always decaying, always needing to be refreshed from the authoritative source.
This is arborescent architecture. A tree branching from a single root. And it works — until the table count grows and your sync window blows out, until you need fresher data than your schedule allows, until you realize you're spending as much time managing the mirror as you saved by escaping the driver.
Here's the thing: treating the sync seam as an inherent cost is the same mistake as treating the driver's limitations as the shape of the problem. You've escaped one constraint and immediately naturalized the next one. The shape of the obvious solution — scheduled, hierarchical, decaying — gets mistaken for the shape of the problem.
The same misrecognition, one level up.
The Seam Is the Architecture
What if the sync isn't a cost?
A purchase order gets created in Sage. That event propagates the relevant rows to SQLite. Inventory receives a shipment. Those tables update laterally. A cost change hits AP. The affected records flow to where they're needed.
This is rhizomatic sync. No single root. No scheduled wholesale refresh. The mirror isn't a snapshot that decays — it's a living surface with multiple entry points. Data flows between substrates based on events, not schedules.
The difference isn't technical. It's conceptual.
Arborescent sync asks: how do I keep the copy fresh? The mirror is a maintenance problem. Sage is the authority. Everything flows down from it.
Rhizomatic sync asks: what connections does this system need? The mirror isn't subordinate — it's a peer. Sage holds what Sage is good at holding. SQLite holds what SQLite is good at answering. The sync architecture isn't overhead. It's the system architecture.
This is the boundary again. The drawing and the shop floor. The specification and reality. You don't make one subordinate to the other. You work the seam between them.
Three Levels
The same mistake, three times:
-
The driver. ProvideX can't do subqueries. You write procedural code to compensate. You're optimizing inside a constraint you didn't choose and don't need.
-
The sync. You escape the driver, build a mirror, and treat the sync as a cost. Scheduled, hierarchical, decaying. You're optimizing inside the shape of the obvious solution.
-
The architecture. You notice the sync seam isn't a cost but a design surface. The connections between substrates are the system. The seam becomes the most interesting part.
Each level requires recognizing that the current constraint isn't the territory. Each time, the instinct is to optimize within the boundary rather than question whether the boundary is real.
The pidgin series was about how constraints shape language. This is about how constraints shape architecture — and how the escape from a constraint can reproduce the same shape at a higher level, until you notice.
Previously: The Distinction