For more than a decade, something inside my Plex environment had been wrong. Movies would occasionally duplicate. Episodes would appear in strange ways. Paths sometimes behaved as though Plex had two different ideas about where the same media lived. Nothing was broken badly enough to justify tearing the whole system apart, but it was never quite right either.
I knew about the problem years ago. At one point I opened the database and tried to repair it myself. I knew enough SQL to be dangerous, which in retrospect was exactly the problem. I started changing things before I fully understood Plex's data model, realized I was probably making the database worse, and stopped.
That was probably the smartest decision I made during the first attempt.
The defect stayed there for another twelve or thirteen years.
By the time I came back to it, the Plex environment itself was approaching seventeen years old. The core SQLite database was 1,562,214,400 bytes, about 1.56 GB, with 82 tables, 164 indexes, 8 triggers, nearly 200,000 metadata records, more than 270,000 media records, more than a million media-stream records, 2.8 million tagging records, and 11,128,193 aggregate rows across the 80 non-internal tables counted in the forensic inventory.
That last number needs a caveat because database statistics are catnip for people who enjoy making numbers sound larger than they mean. The 11.1 million aggregate rows include Plex search indexes and other replicated structures. They are not 11.1 million independent movies, episodes, or human events.
This was no longer a little database I was going to poke at on a Saturday afternoon.
What changed was not Plex.
What changed was the economics of understanding it.
This Was Never Really a Chatbot Problem
The obvious bad approach would have been to somehow dump a 1.56 GB SQLite database into an LLM and ask it to find the problem. That would have been ridiculous.
The database already had deterministic machinery capable of answering questions about itself. SQL can count. SQLite can validate its own structures. Hashes can prove that evidence has not changed. Historical backups can tell us whether an anomaly is old, new, stable, or still being generated. Python and shell scripts can turn repeated analysis into reproducible tests.
The LLM did not need to become the database engine. It needed to operate above those tools as the reasoning layer.
That distinction became the architecture of the investigation.
We preserved the original extraction, made forensic copies, generated SHA-256 hashes, and worked against disposable clones. No repair was allowed to touch the evidence source. Every meaningful hypothesis had to become a deterministic query or test before we trusted it.
The model could speculate. The tools had to prove it.
That separation mattered because the first several things we found looked absolutely terrible and were mostly wrong.
The Database Looked Much Worse Than It Was
The first forensic passes surfaced:
- 2,116 apparent orphan media records
- 67,139 apparently duplicated tagging relationships
- 2,191 repeated physical-path groups
- 124 duplicated metadata GUID groups
- several tables that appeared to contain exactly the same data
If I had approached the database the way I had years ago, I probably would have started deleting things.
That would have been a disaster.
Take the 67,139 duplicate tagging relationships. The first query grouped records using only a media ID and tag ID. That made tens of thousands of legitimate Plex records look duplicated. We widened the comparison to include the actual semantic state, including position, text, timing information, thumbnail state, and additional metadata. Once we did that, the supposed 67,139 duplicates collapsed to exactly zero exact semantic duplicate groups.
The database was not wrong. Our first question was too crude.
The 2,116 orphan media records looked even worse. A media_item was pointing at metadata that no longer existed, which sounds like classic database damage until we traced the records far enough. Every one belonged to old Plex Sync and transcode-cache structures. The exact same 2,116 relationships were present in every historical snapshot we examined.
They were ancient residue from Plex behavior that no longer mattered, not 2,116 broken movies or television episodes.
We found what appeared to be duplicated tables too. Those turned out to be Plex full-text-search structures built over the same underlying content with different tokenization behavior. The rows looked duplicated because, at that level, they were supposed to.
Even repeated physical file paths could be legitimate. A television file named something like S01E01-E06 may represent six logical episodes while pointing at one physical file. Delete five of those "duplicates" and you have just repaired a healthy database into a broken one.
That became one of the strongest lessons from the investigation: finding anomalies is easy. Understanding which anomalies are actually defects is the work.
Eventually the Noise Started Collapsing
After several rounds of this, the database stopped looking generally sick. SQLite itself was healthy. Five historical snapshots passed structural integrity checks. The schema was stable. Plex's migration history was internally consistent. There was no meaningful SQLite freelist bloat. Foreign-key validation did not reveal some cascading wreckage.
The broad corruption hypothesis was dying.
Most of the scary numbers had disappeared under better analysis, but one population refused to go away.
There were 1,509 physical media paths represented by two separate Plex media records even though both records resolved to the same underlying metadata identity and physical file.
In 1,228 of those groups, the twins agreed on the file hash, size, and duration in addition to the same metadata parent and library. Hundreds were even closer to exact twins once non-identity fields were compared.
Those were not fuzzy similarities. They were redundant representations of the same media.
The distribution was also interesting. Of the 1,509 strong duplicates, 163 were in the Movies library, 1,276 were in English television, and 70 were in Japanese television.
Then the historical snapshots gave us the clue that mattered most:
- July 30: 1,508 strong duplicate groups.
- August 2: 1,508.
- August 5: 1,509.
- August 8 and August 10: still 1,509.
The system had generated another one.
That immediately changed the problem. I could no longer blame the entire thing on database surgery I had attempted years ago. Something in the current Plex configuration was still capable of producing duplicate state.
Cleaning the historical duplicates without finding that generator would accomplish almost nothing.
We Finally Found the Generator
We traced the duplicate media records backward through Plex's relational model until we reached the configured library locations.
That was where the whole thing finally became obvious.
Plex had the same physical roots registered twice inside three libraries.
| Library | Physical root | Internal location identities | Strong duplicate groups |
|---|---|---|---|
| Movies | /media/Movies | 7 and 19 | 163 |
| English TV | /media/TV/English | 17 and 18 | 1,276 |
| Japanese TV | /media/TV/Japanese | 26 and 25 | 70 |
That was suspicious enough, but the real proof came when we mapped the 1,509 strong duplicate media groups onto those locations.
Every one of the 163 movie duplicates split across the duplicated Movies locations. Every one of the 1,276 English television duplicates split across the duplicated English TV locations. Every one of the 70 Japanese television duplicates split across the duplicated Japanese TV locations.
The mapping was exact.
Plex had effectively been told to scan the same physical folder twice.
The database was doing what the configuration allowed it to do.
A newly created episode gave us an almost laboratory-clean demonstration. Plex created two media records for the same underlying file within seconds of one another. Same physical path. Same directory record. Same hash. Same size. Same duration. Same metadata identity. Different duplicated section-location identity.
That was the smoking gun.
For more than a decade, I had been looking at symptoms. Now we had the generator.
The Obvious Repair Was Still the Wrong Repair
At this point it would have been tempting to delete the duplicate-side media records and move on.
We tested that assumption too.
The three shadow locations contained 1,973 media records. Only 23 were completely interchangeable under a strict semantic comparison. The other 1,950 had accumulated divergent state or could not yet be proven redundant.
In more than 1,200 cases, one side had acquired different media_part state, often newer Plex analysis metadata such as credit markers. Some records existed only on one side. Others had related metadata elsewhere but no exact twin.
Years of Plex activity had accumulated useful state on top of the duplicated topology.
If we had simply deleted the "bad side," we would have thrown away information Plex had subsequently learned.
That discovery mattered almost as much as finding the root cause.
Cause and residue were two different problems.
The first task was not to remove every historical duplicate. The first task was to stop Plex from creating more of them.
So We Repaired the Cause First
We built another disposable database clone and applied the smallest transformation that could remove the generator without deleting media state.
Every media_item associated with a duplicated location was remapped to the canonical location for that exact same filesystem root. Then, and only then, the three redundant section_locations records were removed.
No media records were deleted. No media parts were deleted. No metadata was deleted. No physical files were touched.
Then we validated the repaired clone.
Before the change, the database contained 12 section locations and three duplicate root groups.
After the change, it contained nine section locations and zero duplicate root groups.
The repair proof was deliberately boring:
| Invariant | Before | After |
|---|---|---|
| Section locations | 12 | 9 |
| Duplicate root groups | 3 | 0 |
media_items | 271,949 | 271,949 |
media_parts | 272,011 | 272,011 |
metadata_items | 197,517 | 197,517 |
| Foreign-key violations | 0 | 0 |
PRAGMA quick_check | ok | ok |
PRAGMA integrity_check | ok | ok |
Media rows lost: zero.
That was the first point where I was willing to call what we had a repair rather than an experiment.
Then I loaded the repaired offline database and tested it.
The stuff that had been wrong for years started behaving correctly.
That was a hell of a moment.
What AI Actually Contributed
It would be easy to turn this into one of those stories where "AI fixed my database."
That is not what happened.
The model did not know the answer in advance. It did not possess some secret Plex repair recipe. It produced hypotheses that sometimes died under better evidence. What mattered was that the investigation could keep moving cheaply and rigorously.
The operating loop was simple:
- Form a probabilistic hypothesis about what could explain the symptom.
- Translate that hypothesis into deterministic SQL, code, or a bounded tool call.
- Collect evidence from counts, hashes, relationships, and historical snapshots.
- Try to falsify the plausible explanation before trusting it.
- Simulate the smallest repair on a disposable clone and prove invariants.
- Let a human verify whether the real system now behaves correctly.
That loop repeated until the remaining anomaly could be traced through relationships, the proposed repair could be executed against a disposable clone, and deterministic validation could prove that critical invariants still held.
The model was the probabilistic reasoning layer around deterministic tools.
That is a very different model of AI from using one as a chatbot.
The tools themselves were ordinary: SQLite, shell, Python, Docker, hashes, filesystem copies. Nothing magical there. A competent engineer could have used every one of them without AI.
The leverage came from continuously deciding what question to ask next, what tool should answer it, whether a result actually meant what it appeared to mean, and which plausible explanation needed to be attacked before we trusted it.
That is where the cost of the investigation collapsed.
The Execution Environment Became Part of the Investigation
There was another small episode that illustrates this nicely.
Our first generic SQLite tooling failed with:
unknown tokenizer: collating
That looked like another database problem for about five minutes.
It was not.
The Plex database used Plex-specific SQLite behavior for its search infrastructure, and the generic SQLite runtime could not interpret it. So the problem changed from "the database is broken" to "we are using the wrong execution environment."
We looked for Plex's own SQLite runtime. It was not installed on the Mac because this database had come from a Linux-style Plex deployment. Docker was available, though, and Plex publishes an official container containing the server runtime.
The investigation moved into Plex's own SQLite environment inside that container while still operating only against disposable forensic copies.
That detail captures the larger pattern perfectly. The useful AI was not the thing pretending to know every answer. It was the thing capable of recognizing when the current tool was wrong and selecting a better one.
Why This Problem Survived for Twelve Years
Nothing about this defect was fundamentally impossible.
A sufficiently experienced Plex engineer with strong SQLite skills could have found it years ago.
The problem was economics.
To solve it properly, somebody had to understand enough about SQLite internals, Plex's relational model, full-text-search structures, library scanning behavior, historical schema state, filesystem topology, dependency analysis, and safe repair practices to avoid making the situation worse.
For a personal media server that was mostly working, that level of effort never made sense. There was no rational reason to assemble a senior database investigation around an annoying duplication problem.
So I lived with it.
That is not unusual.
There is an enormous category of technology problems that are technically solvable but economically irrational to investigate. A spreadsheet has been wrong for six years. An automation works except for one bizarre edge case. An old database contains inconsistent history nobody fully understands. A home-lab service has accumulated strange state through fifteen upgrades. A small business application has one infuriating workflow nobody wants to touch because the original developer disappeared eight years ago.
These problems do not survive because humanity lacks the knowledge to solve them.
They survive because assembling enough knowledge around one small problem costs more than the problem is worth.
AI changes that boundary.
The Application Never Needed an AI Feature
Nothing about this required putting AI inside Plex.
There was no chatbot added to the interface. No vector database. No embeddings. No "Ask Your Media Server" button.
The useful architecture sat completely outside the application:
probabilistic reasoning → deterministic tools → evidence → validation
That is increasingly how I think about serious AI systems.
Use the model where uncertainty, exploration, hypothesis formation, and tool selection are valuable. Once something can be made deterministic, make it deterministic.
SQL should count rows. Hashes should prove identity. Tests should enforce invariants. Code should repeat known-good procedures.
The model should not impersonate those capabilities when better tools already exist.
The value of the model is often in figuring out which deterministic thing needs to happen next.
What We Deliberately Did Not Analyze
A seventeen-year-old Plex database obviously contains information about a media collection and potentially about how that system has been used over time.
None of that matters to this story.
There is no reason to expose:
- library inventories
- viewing history
- user or account identities
- content preferences
- IP addresses or device identities
- the raw database or private forensic report bundles
The useful evidence is structural.
A seventeen-year-old application database. 1.56 GB of SQLite. Eighty-two tables and 164 indexes. More than eleven million aggregate rows across the counted tables and search structures. A defect that survived for more than a decade. Tens of thousands of apparent anomalies that mostly disappeared when tested correctly. 1,509 genuine duplicate media representations. Three duplicated physical library roots. A repair that reduced those duplicate roots from three to zero while losing exactly zero media records.
That is enough.
The Part I Keep Coming Back To
The database had been wrong for more than a decade.
The breakthrough was not that AI somehow knew the answer. It was that AI made it practical to keep asking increasingly precise questions until the database could no longer hide the answer.
That changes what is worth investigating.
We have spent the last few years talking about AI primarily in terms of generating content, automating tasks, or replacing pieces of human labor. I think that frame misses one of the more consequential possibilities.
AI can make neglected complexity economically accessible.
Not by eliminating expertise. Not by pretending rigor no longer matters. Quite the opposite.
It can make rigorous investigation available to problems that could never justify assembling all of the necessary expertise before.
There is an extraordinary amount of broken, strange, aging, undocumented technology in the world. A lot of it is not waiting for a revolutionary new application.
It is waiting for somebody who can afford to understand it.
That may be one of the most important things AI just changed.
---
Evidence and external corroboration
The forensic counts and repair results in this essay come from the hash-verified V4–V8 audit receipts produced against five Plex database snapshots and disposable APFS clones. The public evidence authority intentionally records only structural facts needed for the case study.
Plex documents com.plexapp.plugins.library.db as the core Plex Media Server database and recommends making a copy before database work. Current Plex guidance also documents the Plex-specific SQLite runtime and notes that an integrity_check returning ok establishes structural validity but does not rule out incorrect data. Plex also documents scheduled core-database backups.
SQLite documents PRAGMA integrity_check as a low-level consistency check and separately notes that foreign-key errors require PRAGMA foreign_key_check.
External documentation corroborates mechanics. It does not establish the forensic root cause; that conclusion comes from the owner-controlled audit evidence and exact cross-snapshot relationship analysis.
Check the work, not just the conclusion.
Public research, authority, lineage, and author testimony are labeled separately. Sources can corroborate, challenge, or bound the argument; they do not replace Tony Malott's judgment.
Take the complete artifact with you.
The deterministic package contains a self-contained offline article, the exact public-route snapshot, canonical public metadata, receipt, source text when available, plain-text context, claim ledger, source records, and a member-hash manifest.
Sources, authority, and lineage
Each record states the role it plays. Research support and governance provenance are not treated as interchangeable.
Issue #428 — Publish the Plex forensic AI case study
Governs semantics, privacy boundary, evidence facts, visual grammar, relationships, merge authority, and Production publication authority.
Governs semantics, privacy boundary, evidence facts, visual grammar, relationships, merge authority, and Production publication authority.
Open sourceIssue #428 sanitized forensic evidence authority
Records the public-safe structural findings distilled from the V4-V8 hash-verified forensic receipts.
Records the public-safe structural findings distilled from the V4-V8 hash-verified forensic receipts.
Open sourcePlex Support — Repair a Corrupted Database
Corroborates the core Plex database path, Plex SQLite runtime, backup-before-repair guidance, and the distinction between structural integrity and other data problems.
Corroborates the core Plex database path, Plex SQLite runtime, backup-before-repair guidance, and the distinction between structural integrity and other data problems.
Open sourcePlex Support — Scheduled Tasks
Corroborates Plex's scheduled core-database backup behavior.
Corroborates Plex's scheduled core-database backup behavior.
Open sourceSQLite — PRAGMA statements
Corroborates integrity_check, quick_check, and foreign_key_check semantics.
Corroborates integrity_check, quick_check, and foreign_key_check semantics.
Open sourceSQLite — FTS3 and FTS4 Extensions
Corroborates why full-text-search structures and tokenizer differences can look like duplication without being application-table corruption.
Corroborates why full-text-search structures and tokenizer differences can look like duplication without being application-table corruption.
Open sourcePlex Media Server Docker image
Corroborates the Plex runtime environment used to access Plex-specific SQLite behavior during the forensic audit.
Corroborates the Plex runtime environment used to access Plex-specific SQLite behavior during the forensic audit.
Open sourceWhat is asserted—and how it is bounded
Research, author analysis, and personal testimony remain distinct. Supporting links and caveats stay attached to each claim.
The 1,509 strong duplicate media groups mapped exactly to three duplicated Plex library-root identities, establishing duplicate section locations as the generating condition for this defect population.
Boundary The claim applies to the strong duplicate population defined by the audit, not every possible Plex duplication symptom.
Initial anomaly counts materially overstated actual defects: 67,139 coarse duplicate tagging pairs became zero exact semantic duplicate groups after a stronger identity test, and 2,116 apparent orphan media records were classified as stable legacy Sync/transcode residue.
Boundary Classification depends on the exact schema and historical snapshots audited.
A disposable-clone root normalization reduced section locations from 12 to 9 and duplicate root groups from 3 to 0 while preserving all 271,949 media_items, 272,011 media_parts, and 197,517 metadata_items, with integrity_check remaining ok and zero foreign-key violations.
Boundary The root fix removes the generator; it intentionally does not claim that all historical duplicate residue disappears automatically.
AI materially changed the economics of this neglected engineering problem by making iterative expert-style investigation, tooling, falsification, and validation practical without placing the model in control of the database.
Boundary This is a field synthesis, not a controlled benchmark of human engineering hours.
Public boundary. The public Work contains only structural engineering evidence. It excludes viewing history, library inventories, user identities, account data, content preferences, IP addresses, device identities, raw database files, and private forensic receipts.
Continue the thinking
Each connection explains why the next work belongs here. The graph records the edge; this layer makes it useful to a reader.
Applications
Inference at the Ambiguity Frontier
The model operated where the diagnosis was uncertain; deterministic mechanisms collapsed uncertainty as evidence accumulated.
Inference belongs at the ambiguity frontier. Determinism belongs everywhere behind it.
The Best AI System Should Need Less AI Tomorrow
The investigation repeatedly used probabilistic reasoning to discover the next question, then converted stable knowledge into deterministic SQL, scripts, integrity gates, and a bounded repair transaction.
Use intelligence to conquer ambiguity, capture what works, and retire inference when uncertainty disappears.