A service boundary from data access patterns

A service boundary is credible when one side can commit its own changes without asking the other side to join the transaction. Org charts, capability maps, and nouns in a workshop can suggest where to look. The database tells you whether the proposed split can survive contact with production.

I start with data access patterns because old systems record their actual contracts in reads, writes, locks, triggers, jobs, and recovery procedures. The hard part is separating incidental coupling from a business invariant. A screen that reads customer names beside invoices creates a presentation dependency. A posting routine that updates an invoice, a ledger entry, and a credit balance in one commit may encode an invariant that cannot tolerate partial success. Those two dependencies deserve different treatment.

The useful output is not a pretty domain diagram. It is an evidence pack: a table ownership map, a matrix of tables written in the same transaction, a map of reads that cross candidate domains, and a short list of invariants that explain the strongest clusters. With that evidence, a team can choose a boundary and state exactly what must change before the first independent deployment.

Start with transactions, not table names

Tables written in the same transaction are the strongest first signal because the application currently treats those changes as one unit of success or failure. If invoice, ledger_entry, and customer_balance repeatedly change under one transaction identifier, splitting them across services would replace a local commit with coordination, compensation, or a changed business rule.

Names alone mislead. A table called customer may hold account identity, credit status, shipping preferences, and a denormalized sales total. A table called order_status may act as a queue shared by fulfillment and billing. Prefixes often reflect the team that created a table, not the behavior that now depends on it. Even foreign keys only describe declared referential relationships. They say nothing about a nightly program that reads three tables, writes two more, and must be restartable after row 80,000.

Define a transaction as the database sees it: statements between begin and commit or rollback, including statements issued by triggers and stored procedures. Autocommit statements each form their own transaction. Batch programs need an extra identity for the job run and checkpoint because a loop that commits every 500 records carries a wider business operation than any individual database transaction.

Capture at least these fields for every observed access:

• transaction identifier and timestamp
• executable, job, route, or entry point
• table and operation type
• rows affected or a coarse cardinality band
• call chain or stored procedure name when available

Do not begin by assigning every table to a domain. First build facts without forcing them into the desired answer. Domain labels come after the co-write clusters appear. That ordering prevents the workshop vocabulary from contaminating the measurement.

Build a co-write matrix that exposes atomic work

A co-write matrix counts how often two tables receive writes in the same database transaction. It turns thousands of traces into a weighted graph: tables are nodes, and an edge joins two tables when at least one transaction writes both. Edge weight can record transaction count, affected row count, or the share of each table's writes involved in the pair.

Suppose normalized traces land in a table named data_access:

create table data_access (
  captured_at timestamp not null,
  transaction_id varchar(100) not null,
  entry_point varchar(200) not null,
  table_name varchar(200) not null,
  operation varchar(10) not null,
  rows_affected bigint
);

with writes as (
  select distinct transaction_id, table_name
  from data_access
  where operation in ('INSERT', 'UPDATE', 'DELETE')
), pairs as (
  select a.table_name as table_a,
         b.table_name as table_b,
         count(*) as shared_transactions
  from writes a
  join writes b
    on a.transaction_id = b.transaction_id
   and a.table_name < b.table_name
  group by a.table_name, b.table_name
)
select table_a, table_b, shared_transactions
from pairs
order by shared_transactions desc;

The output has the shape table_a | table_b | shared_transactions. The largest values deserve inspection, but raw counts are not enough. A housekeeping job can dominate volume while encoding no user facing invariant. A rare year end posting may carry the strongest atomic requirement in the system. Add the entry point to the grouping, then compare the same pair across online requests, scheduled jobs, imports, and operator tools.

Normalize the weight from both directions. If 98 percent of writes to customer_balance occur with ledger_entry, that edge matters even when those transactions form a small share of all ledger traffic. I use two conditional measures: transactions writing A that also write B, divided by all transactions writing A; and the inverse. An asymmetric result often reveals a satellite table that belongs with a larger aggregate.

Sampling must preserve transaction boundaries. Capturing every hundredth SQL statement destroys the evidence because the sample may retain one half of a co-write and discard the other. Sample complete transactions by transaction identifier, or capture every transaction for selected entry points. Mask values if needed, but retain table names, operation types, timing, and transaction membership.

Separate business invariants from implementation habits

A dense co-write cluster proposes a boundary; it does not prove one. The team must explain why each strong edge exists and what would break if the two writes committed separately. That explanation distinguishes a business invariant from code that happens to use one connection.

Ask for the failure sentence. For an invoice and its ledger entry, it might be: "Finance must never recognize an invoice without the matching debit and credit entries." For an order and an audit row, it might be: "Operators need a record of who changed the order." The first may require atomic state or a carefully redesigned posting model. The second can often move to a durable event or database outbox without making the audit table part of order ownership.

Classify each co-write edge into one of four reasons:

1. A business invariant requires all changes to succeed together.
2. Referential cleanup or cascading logic keeps stored data consistent.
3. A derived value or index is maintained synchronously for faster reads.
4. The code reused a transaction because the tables were nearby.

Only the first reason is strong evidence that the tables belong behind one consistency boundary. The second may disappear when one service owns deletion and publishes a fact. The third is usually a projection problem. The fourth is migration debt.

Triggers deserve special attention because application traces can hide them. A routine may appear to update shipment, while a trigger adjusts inventory, inserts stock_movement, and writes an integration queue. Read trigger definitions and stored procedure bodies, then attribute their writes to the initiating transaction and entry point. Otherwise the proposed seam will fail during the first production case that activates hidden database behavior.

Locks and error handling provide corroborating evidence. Code that retries a deadlock across two tables, maps a constraint violation to a business message, or rolls back both changes after a validation failure probably relies on their shared fate. Document the exact constraint or recovery rule. "These tables are coupled" is too vague to drive a migration design.

Treat cross-domain reads as a different kind of debt

A read across candidate domains does not automatically invalidate the boundary. Reads can use APIs, replicated projections, caches, snapshots, or analytical stores without coordinating commits. The design question is how fresh and complete the data must be when the reader makes a decision.

Build a read matrix after candidate write owners emerge. Rows represent entry points, columns represent proposed domains, and each cell records tables read, frequency, cardinality, and whether the same entry point writes anywhere. Pay closest attention to decision reads: a read whose result determines a following write. A report that joins billing and customer data can tolerate a delayed projection. A credit check that reads exposure before approving a new order may need current data or a reservation protocol.

The dangerous pattern is read from domain B, calculate in application code, then write domain A while assuming B stayed unchanged. A local monolith can hide that race inside a database transaction by locking B's rows. After a service split, a synchronous API call returns a value but does not extend the caller's transaction across the network. The value can change before A commits.

Record a freshness class for every crossing read:

• exact at the decision point
• bounded delay with a stated maximum
• latest known value with reconciliation
• historical snapshot
• display only

This classification turns a vague "orders need customer data" dependency into a contract. Display only customer names can live in an order projection. A credit limit check may require the customer domain to own a reservation, so the order service asks it to reserve capacity instead of fetching a number and making the decision itself.

Wide reporting joins should not dictate transactional boundaries. Move them to a reporting projection fed by owned changes, or keep a read replica during the transition. Forcing operational services to call each other row by row so an old report still works creates a slow network join and spreads availability failures. Reports need an explicit data product, not accidental access to every operational schema.

Views and stored queries can conceal the crossing. Expand each view to its base tables when building the matrix, but retain the view name as the consumer's contract. A dozen programs may read open_account_summary without knowing that it joins receivables, customer status, and dispute data. Replacing the view once may be easier than changing every program, yet its refresh rule still needs an owner. Measure whether callers filter, aggregate, or fetch whole result sets because that determines whether a projection, query endpoint, or bulk export is the sensible replacement.

Look at negative reads as well. Code often asks whether a row does not exist: no unpaid invoice, no active hold, no prior request with this reference. Replication delay makes absence especially dangerous because a stale projection looks exactly like permission to proceed. Put those checks beside other decision reads and name the authority that can answer them. If the check protects uniqueness or a spending limit, move the decision to that authority instead of copying the table and hoping replication wins the race.

Read failure behavior belongs in the contract. When the remote owner is unavailable, the caller must fail closed, use a bounded stale value, queue work, or continue with an explicit risk. The correct choice depends on the business rule. A generic retry policy hides the decision until an outage, when operators have the least room to reason about it.

A boundary fails when the invariant crosses it

The practical test for a proposed split is simple: can each side accept or reject its own commands using data it owns, while preserving the stated business rules? If a command on side A needs side B to participate in the same commit, the seam is not ready.

Consider an order routine that performs these actions in one transaction:

1. Read the customer's available credit with a row lock.
2. Insert the order and order lines.
3. Increase the customer's committed exposure.
4. Insert an audit record and commit.

An org chart may put customer management and order management in separate departments. A direct service extraction would make steps two and three a distributed transaction. Calling the customer service first does not solve the problem: the order insert can fail after exposure increases. Calling it last reverses the orphan. Retrying blindly risks double counting.

There are three honest options. Keep credit exposure and order acceptance inside one boundary. Move the decision into a credit reservation owned by the customer side, with an idempotent reservation identifier and explicit confirm or release operations. Or change the business rule so temporary disagreement is allowed, then reconcile and place offending orders on hold. Each option changes ownership or semantics. A message broker alone changes neither.

The reservation approach needs states and time behavior, not a hopeful event name. reserve must return the same result for the same identifier. confirm must tolerate repeats. Expiry must account for a late confirmation. Operators need to see reservations that never reach a terminal state. If the organization cannot state those rules, it has not removed the distributed transaction; it has renamed the uncertainty.

This is where I argue against "split by business capability" as a complete method. The advice is popular because capabilities produce diagrams that executives and engineers can discuss together. It is useful for generating candidates. It is wrong as the final test because a capability map does not reveal commit scope, trigger behavior, locked decision reads, or restart semantics.

Batch jobs reveal the boundaries online traces miss

Online traffic rarely covers the full contract of a legacy system. Month end, settlement, imports, backfills, and operator corrections often cross tables that ordinary requests never touch. A boundary chosen from HTTP traces alone can look clean until the first scheduled run.

Inventory every executable that connects to the database, including scripts launched by schedulers, stored jobs, desktop clients, spreadsheet macros, and support utilities. Tie database sessions back to executable names or credentials where possible. Shared credentials make this harder, so combine session metadata, scheduler definitions, source search, and database audit records.

Batch commit cadence matters. A program that reads all unposted invoices, creates ledger rows, marks source rows, and commits every 500 items has at least three scopes:

• the database transaction for each chunk
• the checkpoint used to resume the run
• the business requirement for the entire posting period

Splitting services can preserve chunk commits while breaking restart behavior. Imagine the new ledger service accepts 430 entries before the caller crashes. The old program restarts from its last checkpoint and sends those entries again. Without a stable source identifier and idempotent acceptance, the target posts duplicates. With idempotency but no reconciliation, the source may still show 70 items as pending even though the ledger accepted them.

Walk one real restart path for every important batch. Record where it checkpoints, which writes happen before the checkpoint, how it recognizes prior work, what operators inspect, and how it repairs a partial run. Then assign ownership of that recovery process. Service diagrams tend to omit operator procedures, yet those procedures often hold the only working definition of consistency.

Rare jobs should receive weight by consequence as well as frequency. I mark an edge as operationally significant when a failure blocks close, payroll, shipment, regulatory reporting, or another named business event. This is judgment, not a fake mathematical score. The point is to stop high volume login updates from drowning out a low volume posting invariant.

Choose table ownership before choosing APIs

Every mutable table needs one proposed owner before API design begins. Shared write access lets both services preserve the old shortcuts, so the boundary exists only in deployment diagrams. Ownership means one service decides valid state transitions, performs writes, and handles repair.

Create a table register with these columns: table, proposed owner, writing entry points, co-write cluster, crossing decision reads, triggers, batch jobs, and unresolved invariant. Put an owner against derived tables too. "Shared" is a temporary migration state with an exit condition, not a domain.

Then search the source tree for every write path. Static search catches statements and ORM mappings that traces missed. Dynamic traces catch generated SQL and rarely obvious procedure calls that search missed. Neither source is sufficient alone. Compare them and explain discrepancies, especially dormant utilities that still have production credentials.

API commands should express decisions owned by the callee. reserveCredit(orderId, amount) is stronger than getAvailableCredit(customerId) followed by a caller side calculation. postInvoice(invoiceId, lines) is stronger than exposing CRUD operations for ledger tables. The command lets the owner protect its invariant as its storage changes.

Reads need ownership too, even when data is copied. A projection can contain customer name and status inside the order service, but the customer service remains the authority. Store the source identifier and version or event position so reconciliation can detect missed or reordered updates. Decide what the reader does when the projection is stale: continue, warn, reject, or fetch synchronously. Do not leave that choice to whichever engineer handles the first incident.

Database permissions can enforce the boundary before physical extraction. Give the future owner write rights and turn other writers into callers in controlled stages. Audit denied attempts during testing. A schema split without permission changes is cosmetic because any old job can still reach across it.

Score candidate seams by the work they require

A useful seam score estimates migration cost and operational risk; it does not pretend to discover architecture through arithmetic. I compare candidates with the same set of observable questions and keep the raw evidence beside each rating.

For each proposed boundary, record:

• number and business importance of transactions that write both sides
• decision reads that require current remote state
• batch and recovery flows that cross the line
• tables with multiple active writers
• reports and exports that need a replacement read path

Use a small ordinal rating such as absent, manageable, substantial, and blocking. Avoid combining everything into one decimal number. Two candidates with the same total can carry very different risks: one may require many simple projections, while another has a single blocking financial invariant. The latter controls the decision.

The best first seam usually has cohesive writes and boring reads. One cluster owns its updates, while outside consumers mostly display or report its data. That shape supports an outbox, projections, and a small command surface. The worst seam has few obvious tables but many current decision reads and shared writers. Small schema size does not mean low coupling.

Time windows change the result. Analyze representative business periods, including scheduled runs and uncommon operator actions. Compare normal days with close or settlement periods. A matrix built from a quiet afternoon will understate coupling. Source analysis should supplement the window by listing entry points absent from captured traffic.

Keep uncertainty visible. Mark tables with incomplete traces, dynamic names, external writers, or unknown procedures. An unresolved edge is not zero. I would delay a boundary decision around an unknown posting routine before trusting a clean looking graph built from partial evidence.

Prove the seam with shadow behavior

A boundary earns trust when the proposed owners can reproduce current outcomes on recorded workloads without sharing writes. Before routing production commands to new services, run the candidate architecture in shadow and compare its state transitions with the original.

The harness should replay commands or captured traffic with stable identifiers, observe database effects, and compare business outputs rather than physical row order. Normalize timestamps, generated identifiers, and other nondeterministic fields. Compare totals, statuses, emitted facts, error classes, and restart outcomes. A mismatch needs a classified explanation, not a broad pass percentage.

Include cases that exercise the boundary:

1. successful commands with writes confined to one owner
2. rejected commands after a remote reservation or validation
3. retries after timeouts and duplicate delivery
4. batch restart from every checkpoint position
5. stale or missing projection data

Recorded traffic gives realism, but it rarely contains every failure. Add controlled faults between the steps that used to share a commit. Stop the consumer after it writes but before it acknowledges. Delay a projection update. Repeat a command identifier. Expire a reservation as confirmation arrives. These tests show whether the new design has explicit recovery behavior.

CodeHero uses a parity harness against recorded production traffic when it rewrites legacy systems, which fits this job because architecture changes are credible only when behavior stays accountable to the original. The evidence should remain readable by the customer's engineers: input identity, old result, new result, normalized differences, and the rule used to accept or reject them.

Do not demand byte for byte equality when the migration intentionally changes architecture. Demand equality for promised behavior and documented acceptance for intended differences. If posting order changes but balances, references, and recovery rules stay correct, physical sequence may not matter. If an error becomes success after a timeout, it matters even when final table counts match.

The first extraction should remove a transaction boundary

Choose the first service only after you can name its owned tables, commands, published facts, crossing reads, and recovery rules. A team should be able to point at every old cross-boundary write and say whether it was removed, converted into an owned command, or accepted as a temporary migration constraint with a dated exit condition.

I use one release gate: no production path may write tables on both sides of the proposed boundary. Temporary dual writes inside a migration adapter still count as cross-boundary writes. They need idempotency, comparison, and a removal plan, but they do not prove independent ownership.

The extraction sequence follows the evidence. First enforce one writer per table. Then replace display and reporting reads with projections or approved transitional access. Move decision reads into owner commands or explicit reservation protocols. Finally change deployment and storage boundaries. Reversing that order creates network calls while the old data coupling remains intact.

Keep the co-write and read matrices after extraction. They become regression checks. A new shared writer, a command that starts reading remote current state, or a batch job that bypasses the API should trigger review. Architecture diagrams age quietly; access evidence shows the breach.

Some systems contain an invariant that should remain local. Accept that result. A larger service with a coherent transaction is cheaper and safer than two services held together by synchronous calls, distributed locks, and operator repair. The goal is independent change where the business rules allow it, not the maximum number of deployables.

When the evidence supports a split, the seam stops being an opinion about nouns. It becomes a falsifiable claim: these tables change together, these reads can tolerate this contract, these commands preserve the invariant, and these failure tests demonstrate independent recovery. That is enough to move from a workshop boundary to a production one.