One source of truth
Property data lived in a property-management system, a large Airtable base and spreadsheets, and none of them agreed. I designed a relational core database the whole company could build on, loaded it with every property, unit, owner and vendor, and made sure each app could only see what it needed.
The problem
Every tool we added kept its own copy of the same records. A building could have a different ID in three systems, owners were linked to properties by hand, and nobody could say which copy was right.
New tools were being blocked by the same question every time: where does this data come from, and can we trust it?
Constraints
No budget for a vendor build
An outside studio bid on the work. I wrote the requirements they bid against, then brought the build in house.
The old systems stay in use
The property-management system remained the place staff worked, so the new layer had to sync with it, not replace it.
Records change owners
When a property changes ownership, the old system creates a brand-new record, so its ID can't be trusted as a permanent key.
Least access
Apps and contractors should never hold a key that can read everything.
Key decisions
Where a record is born decides which way it flows.
Records created in the property-management system sync one way into the core. Records born in our own tools flow back out. Two-way sync is only used where both sides have a modified timestamp and a change notice.
Give every building a stable key of our own.
Because ownership changes create new records upstream, the core assigns its own durable ID and keeps the upstream IDs as references.
Deny by default, and one scoped role per app.
Security starts closed. Each app gets its own database role that can read and write only its tables.
Re-runs must change nothing.
Every load is idempotent: running it twice produces zero changes. That made it safe to re-sync any time without fear of duplicates.
How it works
Outcome
- Loaded and reconciled every property, unit, owner and vendor, with each owner-property link checked against the source.
- Found accounting sub-accounts the API had been hiding, which fixed how vendors were linked to their accounts.
- Created 34 custom fields in the property system through its API from a versioned specification, so field changes are reviewable.
- The receipt app was built directly on top of it within weeks.
What I'd do differently
- Write the data rules down on day one, before the first table. I wrote them partway through, and earlier would have saved rework.
- Name an owner for each field per department sooner, so fixes go to a person, not a queue.