Two teams, one database, and the schema change that needed a meeting

Renaming a column from display_name to name took three weeks. The column belonged to the catalogue team, was read by the fulfilment team in four places they had not documented, and appeared in a report written by a third team that no longer existed. Nobody could establish who had to agree.

The symptom

$ mysql -Nse "SELECT table_name FROM information_schema.tables
  WHERE table_schema='shop'" | wc -l
88

$ grep -rho "from ['"]?([a-z_]*)" --include='*.php' 
    services/*/src | sort -u | wc -l
61

# 88 tables. 61 of them read by more than one service.
# and the ownership record:
$ ls docs/ | grep -i owner
# (nothing)

Sixty-one tables read by more than one service and no record of who owns any of them is the state that makes a column rename a three-week negotiation. The information exists — it is in the code — and reconstructing it takes a day and is out of date the following week.

Why it happens

A shared database starts as one application and one team. The second team arrives, needs three columns, and reads them directly because that is the smallest thing that works — and there is no moment at which anybody decides the schema is now an interface.

The fix

Declaring ownership, in a file

# schema-ownership.yml
orders:
  owner: fulfilment
  writers: [fulfilment]
  readers: [fulfilment, reporting]

products:
  owner: catalogue
  writers: [catalogue]
  readers: [catalogue, fulfilment, reporting]
  interface: catalogue_products_v1     # a view

prices:
  owner: catalogue
  writers: [catalogue, pricing]        # ← two writers
  readers: [catalogue, fulfilment, pricing]
  note: |
    Two writers is a known problem. pricing writes
    promotional prices; catalogue writes base prices.
    Tracked in ENG-4102.

The file is a convention until something enforces it, and the useful artefact it produces immediately is the list of tables with more than one writer — which is exactly the list of places where a boundary is missing. There were six, and three turned out to be genuine mistakes rather than deliberate arrangements.

// a migration touching a table this service does not own
// fails the build, and names the owner
$ownership = Yaml::parseFile(base_path('../schema-ownership.yml'));
$service   = config('app.service_name');

foreach ($this->pendingMigrationFiles() as $file) {
    foreach ($this->tablesTouchedBy($file) as $table) {
        $owner = $ownership[$table]['owner'] ?? 'nobody';

        $this->assertSame($service, $owner, sprintf(
            '%s touches %s, owned by %s',
            basename($file), $table, $owner
        ));
    }
}

Naming the owner in the failure message is what turns a build failure into a conversation with a specific person rather than a search. The table extraction is a regular expression over the migration source, which is crude and catches the cases that matter — a raw statement in a closure is the gap and it is rare enough to accept.

A view as the published interface

CREATE VIEW catalogue_products_v1 AS
SELECT id,
       sku,
       display_name AS name,        -- the rename, absorbed
       price_cents,
       status
FROM products;

GRANT SELECT ON shop.catalogue_products_v1 TO 'fulfilment'@'%';
REVOKE ALL ON shop.products FROM 'fulfilment'@'%';

The view is what makes the underlying rename a local change: the consuming team sees name before and after, and the catalogue team can rename the column whenever they like. That is the entire point and it costs one view definition per consumed table.

Versioning the view name gives the same deprecation path as an HTTP API, with the same requirement to measure usage before removing v1. A view is also a query rather than stored data, so it costs nothing at rest and can hide an expensive join — which is a hazard worth naming, since a consumer cannot see what a view costs.

The grant, which is what makes it real

-- one database user per service, rather than one for the
-- application
CREATE USER 'fulfilment'@'%' IDENTIFIED BY '...';

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders
  TO 'fulfilment'@'%';
GRANT SELECT ON shop.catalogue_products_v1 TO 'fulfilment'@'%';

-- and the migration user, which is separate and is NOT
-- what the application connects as
GRANT ALTER, CREATE, DROP, INDEX ON shop.* TO 'migrator'@'%';

Without the grant the ownership file is advisory and the view is a suggestion, because nothing stops a service reading the table directly. The failure mode of getting a grant wrong is loud — a query fails with a permission error — which makes this much safer to introduce than it sounds.

Separating the migration user from the application user is the part that gets skipped and is the one that prevents an application bug from dropping a table. It also means a migration runs with credentials that are not in the application’s environment, which is a small operational complication and a real containment.

The six tables with two writers

  prices        deliberate: base and promotional prices, no
                column overlap. documented, left alone.
  stock_levels  deliberate, and racy: two services
                incrementing one column. → an event, one writer.
  customers     accidental. fulfilment wrote last_ordered_at
                in 2020. → a fulfilment-owned table.
  order_notes   accidental, and nobody knew. the writer was
                a 2019 cron job that had been failing.
  audit_log     everybody writes, and it is append-only,
                so there is no conflict.
  settings      everybody writes. a genuine mess, and the
                largest remaining item.

Two of the six were accidental and one of those had been broken for three years, which is the usual outcome of finally writing down who owns what. The stock levels case was a real correctness bug that had been producing occasional wrong counts and had never been attributed.

Verifying it worked

# the rename that started this, executed without a meeting
$ cd services/catalogue
$ php artisan make:migration rename_display_name_to_name
$ php artisan migrate

$ mysql -u fulfilment -p -e 
    'SELECT name FROM shop.catalogue_products_v1 LIMIT 1'
name
Desk lamp

$ mysql -u fulfilment -p -e 'SELECT * FROM shop.products LIMIT 1'
ERROR 1142 (42000): SELECT command denied to user
'fulfilment'@'%' for table 'products'

$ vendor/bin/phpunit --filter MigrationsOnlyTouchOwnedTables
Tests: 1 passed

The rename executing without a meeting is the acceptance test for the whole exercise, and the permission error is the assertion that the boundary is enforced rather than documented. Both are worth running deliberately rather than assuming.

What this costs

A layer of indirection and a grant to maintain, which means a new consumer needs a view, a grant and an entry in the ownership file before it can read anything. That friction is the point and it will be experienced as bureaucracy the first time somebody needs one column urgently.

The view also hides its cost from the consumer. A view over a three-table join looks like a table to whoever queries it, and a consumer writing a query with a WHERE clause on a column that is computed in the view gets a full scan with no indication why. Documenting what each view actually is, next to its definition, is the mitigation and it is documentation that goes stale.

The honest limitation is that none of this makes the two services independent — they still share a database, a connection pool and a failure domain. What it buys is that a schema change is a local decision rather than a negotiation, which is a smaller claim than “we have boundaries” and is the one that is true.