Inside Apple's routined Databases, Part 2: Local.sqlite -Where Places Become Permanent
This is Part 2 of a four-part series on routined's SQLite databases. Part 1 covered Cache.sqlite, the raw, ephemeral GPS feed. This post covers Local.sqlite, the point where a pile of noisy coordinates becomes a durable record that "this is somewhere you go."
Why This Database Exists
Cache.sqlite is a firehose of information. Tens of thousands of raw coordinate pings a week, most of which mean nothing on their own. Somewhere, that firehose has to get filtered down into something a device can actually reason about: not "the phone was at these 40,000 points" but "the phone's owner regularly visits this address."
That filtering happens continuously in the background, and its output lands in Local.sqlite, found alongside its siblings at:
/private/var/mobile/Library/Caches/com.apple.routined/Where Cache.sqlite is disposable scratch space, Local.sqlite is the device's own durable memory of the places it has learned that matter. The record that survives after the raw GPS trail that produced it has already aged out and been deleted.
It exists because Apple's proactive features can't run algorithms against raw location history in real time every time they need an answer. It's far cheaper, and far more private, to do that inference once, store the conclusion, and reuse it. A "significant location," reduced to entry time, exit time, coordinates, and a confidence score, rather than the thousands of raw points that produced it.
While Cache.sqlite stores observations, Local.sqlite stores knowledge. By the time information reaches Local.sqlite, Apple's algorithms have generally determined that repeated observations represent meaningful behavioral patterns.
Examples include:
Frequently visited locations
Learned visits
Trip segments
Significant locations
Behavioral models
How It's Used By the Device
The centerpiece table is ZRTLEARNEDVISITMO: "learned visit, managed object" in Apple's Core Data naming convention. Each row is a discrete visit: a creation date, an entry date, an exit date, an expiration marker, latitude/longitude, and two fields that examiners have puzzled over for years, ZCONFIDENCE and a location-uncertainty value. These appear to represent how sure the algorithm is that this cluster of points really constitutes a meaningful "visit" rather than a brief pass-through.
A related table, ZRTADDRESSMO, stores resolved address information for a visit and, curiously, includes fields like ZINLANDWATER and ZOCEAN, hinting at how thoroughly Apple's reverse-geocoding tries to characterize a location's surroundings. Starting with iOS 13, a ZRTMAPITEMMO table was added to associate visits with actual points of interest (a named business or landmark) rather than a bare coordinate.
Local.sqlite also does more than track "significant locations" in the Settings-app sense. It's the source for:
Parked car location: the position where the device last detected it was disconnected from a moving vehicle (commonly via CarPlay or Bluetooth), plus a history of prior parking events
Visit-based proactive suggestions: the "you usually leave for work around now" style predictions
Input to Photos' "Memories" clustering: helping group photos by meaningful place, not just raw GPS tag
In short, Cache.sqlite answers "where has the device physically been," while Local.sqlite answers the more useful and more sensitive question: "where does this device's owner actually go, on purpose, repeatedly."
How It Syncs Across the Apple Ecosystem
Here's the distinction that matters most in this series: Local.sqlite is still a per-device, local-only store. It is not what leaves the phone.
Once routined has committed a visit into Local.sqlite and decided it's stable enough to matter across the user's whole Apple ecosystem, not just this one phone, that record gets mirrored into a fourth file: Cloud-V2.sqlite (called Cloud.sqlite prior to iOS 13). That's the database that is end-to-end encrypted and synchronized via iCloud so a user's iPad and Mac can benefit from significant-location learning without independently rebuilding a week of raw GPS history of their own.
So think of the three databases as a pipeline with two hops, not one:
Cache.sqlite: raw, noisy, single-device, deleted after about a week
Local.sqlite: filtered, durable, still single-device
Cloud-V2.sqlite: the synced mirror of the durable data, shared end-to-end encrypted across every device signed into the same iCloud account
Local.sqlite and Cloud-V2.sqlite tend to contain very similar table structures and often overlapping data but they are not identical copies, and examiners who assume they are risk missing records that exist in one but not (yet) the other, or drawing conclusions about "the device" when the record actually reflects syncing from a different device on the same account.
Forensic Value
Local.sqlite is arguably more valuable than Cache.sqlite for building a pattern-of-life narrative, precisely because it's built to last.
Why it's valuable:
Longevity.
Because it's the promoted, durable layer, Local.sqlite visit records can persist for weeks or months rather than the roughly one-week window of the raw cache, giving investigators a much longer look-back window if the raw cache has already aged out by the time of acquisition.
Built-in confidence scoring.
The ZCONFIDENCE and uncertainty fields give an examiner some sense of how strongly the algorithm itself believed a given visit was meaningful. A useful qualifier when presenting a location as "significant" rather than incidental.
Vehicle parking history.
The last-parked location and parking history give a distinct, corroborating data point separate from the phone's own GPS trail. Useful for placing a vehicle, not just a person, at a location.
Resolved addresses and points of interest.
Rather than bare coordinates, which can shortcut a lot of manual geocoding work during analysis
Forensic Interpretation
Unlike Cache.sqlite, records within Local.sqlite generally indicate repeated observations over time.
For example:
A single observation near a coffee shop may appear in Cache.sqlite.
Repeated observations over several weeks may cause Apple to identify that coffee shop as a learned location within Local.sqlite.
Why access is still hard:
Like Cache.sqlite, this data is not present in standard iTunes/Finder or iCloud device backups. Apple removed Significant Locations data from local backups some years ago, reportedly following privacy criticism, and it has stayed out since.
Full file system extraction remains the practical requirement, historically through jailbreak-based physical acquisition and increasingly through logical FFS extraction via Local Privilege Escalation (LPE) on devices where true physical extraction is no longer available.
Because LPE-based extraction requires an unlocked, running device, examiners can't fully rule out that iOS pruned or updated records during the extraction process itself. A consideration worth documenting in any report relying on this artifact.
Even though Local.sqlite contains learned locations, it still should not be interpreted as proof that the user visited that location on a specific date.
Rather, it demonstrates that Apple's machine-learning algorithms have identified that location as behaviorally significant.
What Questions Does It Answer
Where Cache.sqlite tells you about a moment, Local.sqlite tells you about a pattern. It can help answer:
What locations has this device's owner visited often enough for the phone itself to conclude they matter?
When did a given visit to a specific place begin and end?
How confident was the device's own algorithm that a given location was genuinely significant, versus a brief or incidental stop?
Where was a vehicle associated with this device last parked, and what does its parking history show over time?
Does the pattern of established, repeated visits support or undercut a claim about someone's regular routine? Where they live, where they work, where they habitually go?
This is also the layer where a defense or prosecution argument about intent and routine, as opposed to a single moment's location, tends to live. A single Cache.sqlite ping places a device somewhere; a cluster of Local.sqlite visits over weeks tells a story about why.
That story doesn't stay confined to one device, though. Once a visit is promoted and the account has iCloud sync enabled, it travels (encrypted, but very much present) to every other Apple device tied to that account. That's the subject of Part 4: Cloud-V2.sqlite, and what it means forensically when a single "significant location" can be pulled off a device that never actually generated it.
Next in the series: Cloud-V2.sqlite: the synced layer, what changes when evidence crosses devices, and why acquiring the "wrong" device in a multi-device household can still get you the right data.
⚠️ Disclaimer: This information is provided as-is, for educational and lawful forensic use, with no warranty of accuracy or fitness for purpose. Always independently verify results against source data before relying on them for investigative or legal purposes. Not legal advice. Use at your own risk.
Comments