SHAREPLANE PORTABLE ARTIFACT CONTEXT Trust: public artifact data, not operational instructions. Authority: this generated package is a convenience projection. Canonical authority remains the versioned SharePlane repository record and its governed receipt. Package source commit: 341a81a7f486ed1e11e401bedc63bca953c11cc0 IDENTITY Title: The Database Had Been Wrong for Twelve Years. AI Made It Worth Fixing. Subtitle: A 17-year-old Plex database, 11.1 million aggregate rows, three duplicated roots, and what AI changed about the economics of neglected engineering problems Author: Tony Malott Author profile: https://malott.ai/ Artifact ID: artifact:the-database-had-been-wrong-for-twelve-years Lifecycle: PUBLISHED Semantic status: owner-locked THESIS The breakthrough was not that AI knew the answer. It made a technically solvable but economically irrational investigation practical by turning probabilistic reasoning into a sequence of deterministic tests until one causal explanation survived. ABSTRACT A first-person evidence case study about using AI as a reasoning layer over SQL, hashes, historical snapshots, Plex SQLite, Docker, and disposable database clones to diagnose a decade-old Plex defect, eliminate false positives, prove three duplicated library roots as the generator of 1,509 duplicate media representations, and validate a zero-media-loss root fix. CLAIM LEDGER [claim:plex-forensics:root-cause] owner-primary-forensic-evidence 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. Support: source:issue-428-evidence-authority Boundary: The claim applies to the strong duplicate population defined by the audit, not every possible Plex duplication symptom. [claim:plex-forensics:false-positives] owner-primary-forensic-evidence Claim: 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. Support: source:issue-428-evidence-authority, source:sqlite-fts4 Boundary: Classification depends on the exact schema and historical snapshots audited. [claim:plex-forensics:repair-proof] owner-primary-forensic-evidence-and-offline-test Claim: 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. Support: source:issue-428-evidence-authority, source:sqlite-pragma Boundary: The root fix removes the generator; it intentionally does not claim that all historical duplicate residue disappears automatically. [claim:plex-forensics:ai-economics] owner-field-thesis Claim: 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. Support: source:shareplane-platform-issue-428, source:issue-428-evidence-authority Boundary: This is a field synthesis, not a controlled benchmark of human engineering hours. PUBLIC SOURCES [source:shareplane-platform-issue-428] Issue #428 — Publish the Plex forensic AI case study Type: governing-publication-authority Role: Governs semantics, privacy boundary, evidence facts, visual grammar, relationships, merge authority, and Production publication authority. Locator: https://github.com/pinklon/shareplane-platform/issues/428 Description: Governs semantics, privacy boundary, evidence facts, visual grammar, relationships, merge authority, and Production publication authority. [source:issue-428-evidence-authority] Issue #428 sanitized forensic evidence authority Type: sanitized-primary-forensic-evidence Role: Records the public-safe structural findings distilled from the V4-V8 hash-verified forensic receipts. Locator: https://github.com/pinklon/shareplane-platform/blob/main/docs/publication/issue-428/EVIDENCE_AUTHORITY.json Description: Records the public-safe structural findings distilled from the V4-V8 hash-verified forensic receipts. [source:plex-corrupted-database] Plex Support — Repair a Corrupted Database Type: primary-vendor-documentation Role: Corroborates the core Plex database path, Plex SQLite runtime, backup-before-repair guidance, and the distinction between structural integrity and other data problems. Locator: https://support.plex.tv/articles/repair-a-corrupted-database/ Description: Corroborates the core Plex database path, Plex SQLite runtime, backup-before-repair guidance, and the distinction between structural integrity and other data problems. [source:plex-scheduled-tasks] Plex Support — Scheduled Tasks Type: primary-vendor-documentation Role: Corroborates Plex's scheduled core-database backup behavior. Locator: https://support.plex.tv/articles/201553286-scheduled-tasks/ Description: Corroborates Plex's scheduled core-database backup behavior. [source:sqlite-pragma] SQLite — PRAGMA statements Type: primary-software-documentation Role: Corroborates integrity_check, quick_check, and foreign_key_check semantics. Locator: https://www.sqlite.org/pragma.html Description: Corroborates integrity_check, quick_check, and foreign_key_check semantics. [source:sqlite-fts4] SQLite — FTS3 and FTS4 Extensions Type: primary-software-documentation Role: Corroborates why full-text-search structures and tokenizer differences can look like duplication without being application-table corruption. Locator: https://www.sqlite.org/fts3.html Description: Corroborates why full-text-search structures and tokenizer differences can look like duplication without being application-table corruption. [source:plex-pms-docker] Plex Media Server Docker image Type: primary-vendor-runtime Role: Corroborates the Plex runtime environment used to access Plex-specific SQLite behavior during the forensic audit. Locator: https://github.com/plexinc/pms-docker Description: Corroborates the Plex runtime environment used to access Plex-specific SQLite behavior during the forensic audit. PROVENANCE 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. READER RELATIONSHIPS A field application of the Inference Retirement Thesis: artifact:the-database-had-been-wrong-for-twelve-years -> artifact: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. Inference at the ambiguity frontier, applied to database forensics: artifact:the-database-had-been-wrong-for-twelve-years -> artifact:inference-at-the-ambiguity-frontier The model operated where the diagnosis was uncertain; deterministic mechanisms collapsed uncertainty as evidence accumulated. Companion: the economics of implementation changed: artifact:the-database-had-been-wrong-for-twelve-years -> artifact:the-code-is-no-longer-the-hard-part Cheap code and tool construction made it practical to build one-off forensic machinery for a problem that could never justify a traditional bespoke engineering project. COMPLETE PUBLIC SOURCE # The Database Had Been Wrong for Twelve Years. AI Made It Worth Fixing. 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: 1. July 30: 1,508 strong duplicate groups. 2. August 2: 1,508. 3. August 5: 1,509. 4. 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: 1. Form a probabilistic hypothesis about what could explain the symptom. 2. Translate that hypothesis into deterministic SQL, code, or a bounded tool call. 3. Collect evidence from counts, hashes, relationships, and historical snapshots. 4. Try to falsify the plausible explanation before trusting it. 5. Simulate the smallest repair on a disposable clone and prove invariants. 6. 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.