Skip to content
KEDBYTE
Site navigation
How Data Works
Chapter
32

Corruption, Reconciliation and Repair

Part E · Storage and Recovery|3,555 words|about 15 min read|Volume E

32.0 What this chapter gives you#

  1. A wrong answer can come from damaged bytes, an invalid relationship, a faulty query, a mistaken business entry or a misunderstanding of the report. These are different problems with different evidence.
  2. This chapter teaches a disciplined path from suspected disagreement to verified diagnosis and bounded repair. It explains checksums, structural checks, reconciliation and evidence preservation without treating a repair command as a substitute for understanding.
  3. The exercises use synthetic records and disposable byte strings. They do not modify a live database, bypass engine safeguards or claim to recover a user’s damaged files.

32.1 Detecting disagreement#

32.1.1 PLAIN — in simple words#

  1. Disagreement is a reason to investigate, not a diagnosis by itself. A report total can be wrong even when every stored byte is intact.
  2. Begin by stating the expected relationship and the observed difference. Then identify which layer could explain it: query, input, schema, transaction, storage or outside-world measurement.
  3. Preserve uncertainty. The earlier expected-stock-10/observed-stock-9 example remains unexplained unless new evidence actually establishes a cause.

32.1.2 PLAIN — a picture in your head#

  1. Two calculators show different totals. One may contain a different list, one may use a different tax rule, or one may be broken. The disagreement alone does not identify which.
  2. Comparing the inputs and rules is often more informative than replacing the calculator immediately.
  3. Where the comparison breaks: a database may have several simultaneous faults. Finding one query error does not prove that every underlying record is healthy.

32.1.3 PLAIN — a worked example#

  1. The canonical agreed lines total 28,650 paise. A report joining lines to product tags produces 51,300 paise because some lines appear once per tag.
  2. The difference is 22,650 paise, exactly the repeated notebook contribution in that fixture. The stored agreed prices need not be corrupted for the report to be wrong.
  3. A repair aimed at reducing stored order totals to compensate for the report would damage correct source data. The query’s grain and join must be fixed instead.
  4. This demonstrates a verified logical cause in the synthetic fixture; it does not resolve any unrelated real inventory discrepancy.

32.1.4 PLAIN — what is really happening inside#

  1. Reproduce the smallest relevant observation using a stable data boundary. A changing live dataset can make two correctly run queries disagree because they observed different states.
  2. Keep the exact query, parameters, schema version and result. A screenshot of one number without its population or exclusions is weak evidence.
  3. Classify findings separately: suspected, reproduced, explained, repaired and revalidated. These are stages, not interchangeable labels for “done.”

32.1.5 TECHNICAL — the engineer’s version#

  1. Distinguish physical corruption, structural inconsistency, violated business invariants and incorrect derived queries. A diagnostic plan should select tests that discriminate among these classes.
  2. A stable snapshot or isolated copy can support reproducible comparisons, subject to the acquisition procedure. Query plans and joins must be reviewed at their actual grain. [S58] [S66]
  3. Do not modify authoritative rows merely to make a dashboard match a preferred number. Identify the source of authority and preserve the original evidence before proposing a correction.

32.1.6 WORDS — remember these#

  1. Disagreement: two observations or expectations do not match — a finding that requires diagnosis rather than automatically proving storage damage. Physical corruption: stored representation is damaged or invalid — a failure involving bytes or engine structures rather than merely an undesirable business value. Logical inconsistency: records or calculations violate their intended meaning — a mismatch that can exist even when all files are structurally readable.

32.2 Checksums and their limits#

32.2.1 PLAIN — in simple words#

  1. A checksum or digest summarizes bytes so a later comparison can detect many changes. It answers a question about byte identity under the chosen algorithm.
  2. It does not explain whether the original bytes were correct, whether a transaction was authorized or how to reconstruct missing information.
  3. If someone can replace both a file and its stored checksum, a matching pair does not authenticate the file’s origin. Integrity comparison and trusted provenance are separate requirements.

32.2.2 PLAIN — a picture in your head#

  1. A parcel’s inventory code helps detect that its contents changed after packing. It does not prove that the packer selected the right goods.
  2. A dishonest person who repacks the parcel and rewrites the code can defeat a comparison against that same untrusted label.
  3. Where the comparison breaks: cryptographic digests have formal security properties and collision limits. An everyday inventory code is only an analogy, not an implementation of those properties.

32.2.3 PLAIN — a worked example#

  1. The lab hashes a fixed synthetic byte string, changes one byte in a separate copy and hashes it again. The observed digests differ for that fixture.
  2. It also verifies that hashing the unchanged bytes reproduces the original digest. These checks establish deterministic behaviour and the detected alteration in the example.
  3. They do not prove that no two different byte strings can ever share the digest or that the original string represented a true sale.
  4. A trusted manifest must bind the expected digest to the intended source and version through an appropriate authority or custody process.

32.2.4 PLAIN — what is really happening inside#

  1. Checksums are computed over a defined byte range. Omitted files, metadata or fields are outside that range’s protection.
  2. Database page checksums can detect some storage damage when pages are checked, but a logically valid wrong UPDATE can produce a perfectly checksum-valid page.
  3. Verification timing matters. A successful check yesterday does not prove that a file is unchanged today unless the relevant bytes were checked again or protected by another justified mechanism.

32.2.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL data checksums provide page-level corruption detection within their documented scope. They are not a general repair facility or a proof of business correctness. [S137]
  2. Cryptographic digest comparison, such as SHA-256 through Python’s hashlib, can identify exact artifact bytes when paired with a trusted expected digest. It does not supply authentication by itself. [S99] [S100]
  3. Define the manifest’s coverage and protect its provenance. Hashing a ZIP after delivery identifies that ZIP’s bytes; it does not imply every internal claim or test result is true.

32.2.6 WORDS — remember these#

  1. Checksum coverage: the bytes included in an integrity calculation — the exact range or file set a checksum can help verify. Trusted expected digest: an integrity reference with justified provenance — a digest bound to an intended artifact through a trusted source or process. Authentication: establish a source or actor under a security protocol — a property not supplied merely by an unkeyed matching checksum.

32.3 Structural checks#

32.3.1 PLAIN — in simple words#

  1. A structural check asks whether the database’s internal representation satisfies the checker’s defined rules. It can detect problems invisible to an ordinary SELECT.
  2. Different checks cover different things. A general integrity check may not test every foreign-key relationship or every business invariant.
  3. A successful result means the selected checks found no covered problem. It does not mean that every possible fault has been ruled out.

32.3.2 PLAIN — a picture in your head#

  1. A building inspection can verify that shelves are stable without verifying that every form is filed under the right customer. A separate filing audit checks those relationships.
  2. Both checks matter, and neither should be described as the other.
  3. Where the comparison breaks: database checkers have precise implementation scopes, may require permissions and can consume substantial resources. Running every checker on a busy production system is not automatically safe.

32.3.3 PLAIN — a worked example#

  1. In an isolated SQLite demonstration, a child row can exist without its intended parent if foreign-key enforcement was bypassed during insertion.
  2. A general PRAGMA integrity_check and a separate PRAGMA foreign_key_check answer different questions. The earlier labs demonstrate that a structurally accepted database can still contain the orphan relationship.
  3. Enabling foreign keys afterward does not retroactively create the missing parent or prove all old rows valid. Inspect existing relationships explicitly.
  4. Repairing the orphan requires a business decision based on evidence; inventing a parent record just to silence the checker is not necessarily correct.

32.3.4 PLAIN — what is really happening inside#

  1. Checkers traverse selected engine structures and compare them against invariants encoded in the checker.
  2. They can miss application-level rules because those rules may not exist in the schema. A capacity total exceeding ten is invisible to a checker that only knows each row’s positive quantity constraint.
  3. Checker errors should be retained exactly, together with version and options. Summarizing them as “database broken” loses information needed for a bounded diagnosis.

32.3.5 TECHNICAL — the engineer’s version#

  1. SQLite documents integrity_check and foreign_key_check separately. Use both when their respective coverage is needed, while respecting the workload and acquisition boundary. [S75] [S59]
  2. PostgreSQL amcheck provides defined table/index consistency checks. Its options, lock implications and limitations must be reviewed before use; it is not a universal repair command. [S146]
  3. A checker’s successful exit should be recorded with the exact object scope. Testing one index or one database does not certify every other store in the service.

32.3.6 WORDS — remember these#

  1. Structural integrity check: inspect engine representation rules — a diagnostic procedure for covered page, tree or table/index invariants. Referential check: verify relationships between records — a test that child references resolve according to the schema’s key rules. Checker scope: the objects and properties actually examined — the boundary limiting what a successful diagnostic result establishes.

32.4 Business reconciliation#

32.4.1 PLAIN — in simple words#

  1. Business reconciliation compares records that should agree under a defined rule. It can reveal missing, duplicated or misclassified work even when storage is structurally healthy.
  2. Start with grain and inclusion rules. Comparing all-time accepted orders with today’s delivered items creates disagreement even when both lists are correct for their own purpose.
  3. A matching total is useful but not sufficient. Two opposite errors can cancel numerically while the wrong customers or products remain affected.

32.4.2 PLAIN — a picture in your head#

  1. Mira compares each reservation slip with its stock allocation, not just the total number of slips in two piles.
  2. Ten slips in each pile can still refer to different reservations.
  3. Where the comparison breaks: real reconciliation may involve delayed arrivals and approved exceptions. An unmatched record is not automatically fraud, corruption or a completed failure until timing and scope are examined.

32.4.3 PLAIN — a worked example#

  1. The canonical dataset has four agreed lines, six units and a total of 28,650 paise. Verify each complete (order_id,line_no) key and its product, quantity and agreed price.
  2. Suppose one copied line is increased by 100 paise and another decreased by 100 paise. The combined total can still match while both line values are wrong.
  3. A record-level comparison catches the mismatch. A total-only comparison does not.
  4. For reservations, compare each accepted request to its reservation and stock effect. Keep rejected requests and pending delivery intents in their own categories rather than forcing every count to be equal.

32.4.4 PLAIN — what is really happening inside#

  1. Reconciliation often joins independent representations at explicit identity boundaries. Missing matches, duplicate keys and incompatible values should be categorized separately.
  2. Define the allowed timing lag and late-arrival policy. A recently committed order may legitimately await a downstream report refresh under the system’s contract.
  3. Keep the source of truth explicit for each field. A derived report cannot automatically overrule the original agreement merely because the report was generated later.

32.4.5 TECHNICAL — the engineer’s version#

  1. A reconciliation specification includes population, grain, keys, units, time boundary, expected equations, allowed exceptions and authoritative sources.
  2. Use anti-joins or complete-key comparisons to identify missing and extra records before comparing aggregate totals. Chapter 13’s join-grain rules apply directly. [S58]
  3. Reconciliation findings should be reproducible from captured inputs or a defined snapshot. A live query whose population changes during investigation needs an explicit observation contract. [S66]

32.4.6 WORDS — remember these#

  1. Business reconciliation: compare representations that should agree — an identity- and meaning-aware check of records, totals and state transitions. Authoritative field source: the record allowed to decide a disputed value — a defined authority for a particular fact, not necessarily one universal source for every field. Offsetting errors: wrong values cancel in an aggregate — discrepancies that preserve a total while corrupting individual records.

32.5 Evidence-preserving repair#

32.5.1 PLAIN — in simple words#

  1. Repair changes evidence. Before changing a damaged or disputed system, preserve enough of the original state to explain what was found and why the proposed correction is justified.
  2. A repair should be bounded: identify the records, preconditions, exact changes, expected postconditions and a safe stopping rule.
  3. When the evidence does not establish the correct value, the repair must not invent one. Escalate the unresolved decision or restore from an appropriate verified source.

32.5.2 PLAIN — a picture in your head#

  1. An archivist photographs a damaged page before repairing it and records which words were restored from a verified duplicate. They do not silently fill illegible words with a plausible story.
  2. The repair record distinguishes observed text from reconstructed text and unresolved gaps.
  3. Where the comparison breaks: copying a live database incorrectly can itself create an inconsistent artifact. Evidence acquisition needs a supported procedure that includes relevant recovery files and metadata.

32.5.3 PLAIN — a worked example#

  1. In a disposable copy, identify one imported row whose source record and import trace prove that quantity 2 was mistakenly stored as 3. Preserve the source evidence, original row and acquisition identity.
  2. Propose a conditional correction targeting the complete key and expected old value. Check that exactly the intended row changes and that related totals reconcile afterward.
  3. If the old value no longer matches, stop and review rather than broadening the UPDATE until it affects something. The precondition protected the repair from silently overwriting intervening work.
  4. This is a teaching repair pattern, not authorization to change the canonical agreed orders or any live customer record.

32.5.4 PLAIN — what is really happening inside#

  1. Evidence preservation includes original files, logs, schema, version, configuration and observations relevant to the finding. A checksum records artifact identity but does not independently validate the diagnosis.
  2. Some storage-level repairs can discard data to regain structural readability. Such a result must be reported as recovery with known loss, not a complete restoration.
  3. Perform proposed changes on an isolated copy first where possible. Validate them before a separately authorized production intervention with a tested recovery path.

32.5.5 TECHNICAL — the engineer’s version#

  1. A repair manifest should contain input hashes, object scope, expected preimages, patch or statements, transaction boundary, postcondition checks, rollback/recovery plan and decision authority. Do not invent reviewer identities or approvals.
  2. SQLite’s corruption guidance warns about unsafe file operations, journal handling and interference with its locking assumptions. Preserve associated recovery material rather than deleting unfamiliar sidecar files. [S139]
  3. Prefer restoring verified data or rebuilding derived structures from authoritative sources when that is the justified remedy. Never present a generic low-level edit as universally safe for an unknown damaged database.

32.5.6 WORDS — remember these#

  1. Preimage: the exact state expected before a repair — captured values or bytes used to verify that a proposed change targets the reviewed input. Repair manifest: the record of a bounded correction — evidence, scope, changes, checks and authority supporting an intervention. Evidence-preserving repair: fix a problem without concealing its original state — a procedure retaining provenance and disclosing reconstructed or lost information.

32.6 Incident follow-through#

32.6.1 PLAIN — in simple words#

  1. A repair is not complete merely because the first error message disappeared. Check whether the cause remains, whether downstream copies are consistent and whether the same fault can recur.
  2. Separate immediate containment, restored service, verified data correction and long-term prevention. They can finish at different times.
  3. A useful incident record states facts, uncertainty and actions without assigning unsupported motives or blame.

32.6.2 PLAIN — a picture in your head#

  1. Replacing a wet ledger page helps today, but a leaking roof can ruin the replacement tomorrow. The repair and the cause need separate attention.
  2. Staff also need to know which reports used the damaged page before it was replaced.
  3. Where the comparison breaks: software causes can be interacting conditions rather than one broken part. An incident may have several contributing factors and no single simple root cause.

32.6.3 PLAIN — a worked example#

  1. A synthetic report is corrected from the tag-multiplied 51,300 paise to the properly scoped 28,650 paise. The source order lines remain unchanged.
  2. Follow-through identifies other reports using the same faulty join, adds a regression fixture with multiple tags, and records which exported results may need correction.
  3. The incident closes only after the agreed scope is checked and remaining unreviewed consumers are disclosed. Fixing one dashboard does not prove every downstream spreadsheet was replaced.
  4. The original unresolved physical stock discrepancy remains a separate finding, not absorbed into this explained reporting error.

32.6.4 PLAIN — what is really happening inside#

  1. Data flows create downstream effects: caches, reports, exports, messages and decisions. Lineage helps identify which outputs depended on the disputed input or query.
  2. A regression test should reproduce the actual failure condition, not merely repeat the happy path that already passed before the incident.
  3. Monitor the corrected invariant over an appropriate period and preserve the old and new definitions. Silent metric-definition changes can make a problem appear solved without changing the underlying behaviour.

32.6.5 TECHNICAL — the engineer’s version#

  1. An incident report should include detection, impact scope, evidence, timeline, containment, correction, validation, remaining uncertainty and prevention actions with owners. Owners are actual designated people or roles, not invented identities.
  2. Track which claims were verified: byte integrity, structural validity, record-level reconciliation, downstream regeneration and operational recovery. Do not collapse them into one unqualified PASS.
  3. The final book package’s own validation follows the same principle: checksums identify files, tests exercise examples and layout checks inspect exports. None alone proves that every sentence is perfect or independently reviewed.

32.6.6 WORDS — remember these#

  1. Containment: limit further harm while investigating — an action that can precede full diagnosis or repair. Regression fixture: data and steps reproducing a past failure — a test case intended to prevent recurrence of the specific defect. Downstream impact: outputs and decisions depending on disputed data — the propagation scope that must be reviewed after a correction.

32.97 Practice and worked answers#

  1. Classify the inflated total. Joining order lines to multiple tags yields 51,300 instead of 28,650 paise. Answer: the fixture demonstrates query-grain multiplication, not necessarily damaged stored prices.
  2. Limit a digest. A file and its digest are both replaced by an attacker. Answer: their agreement does not authenticate the original source; trusted provenance is missing.
  3. Separate checks. SQLite integrity checking reports no covered structural issue, but a foreign-key check finds an orphan. Answer: the checks have different scopes and can legitimately produce those results.
  4. Find offsetting errors. Two values differ by +100 and −100. Answer: the total can match while record-level comparison fails.
  5. Protect a correction. The reviewed old quantity is 3, but the live row now contains 4. Answer: stop the conditional repair and re-review; do not overwrite the intervening change blindly.
  6. Handle an unknown value. No evidence establishes whether stock should be 9 or 10. Answer: retain the uncertainty and obtain a justified observation or decision rather than inventing a repair.
  7. Follow a query fix. One dashboard is corrected. Answer: inspect other consumers, exports and regression coverage before claiming complete downstream correction.
  8. Interpret test success. A checksum check, integrity check and query test all pass. Answer: each supports its bounded claim; together they still do not prove every business fact or unseen system copy is correct.

32.98 Common wrong ideas#

  1. Wrong: every wrong total means corrupted storage. Right: query grain, timing and meaning can produce disagreement with intact bytes.
  2. Wrong: a checksum proves the original data was true. Right: it helps compare bytes against a reference.
  3. Wrong: a checksum can reconstruct a missing page. Right: repair needs suitable redundant information or an authoritative source.
  4. Wrong: one integrity checker covers all constraints. Right: structural, referential and business checks differ.
  5. Wrong: equal totals prove equal records. Right: offsetting errors and mismatched identities can hide underneath.
  6. Wrong: a plausible replacement is a justified repair. Right: evidence or explicit authority must support the correction.
  7. Wrong: deleting recovery sidecars is harmless cleanup. Right: they may contain information required for consistency or diagnosis.
  8. Wrong: removing the symptom closes the incident. Right: cause, downstream impact and recurrence need follow-through.

32.99 Chapter summary in 20 lines#

  1. Disagreement is an observation, not an automatic diagnosis of corruption.
  2. Separate physical damage, structural inconsistency and wrong business meaning.
  3. Capture exact queries, parameters and observation boundaries.
  4. The tag-join fixture inflates a correct 28,650-paise source total to 51,300.
  5. Repair the faulty derivation rather than damaging correct source records.
  6. Checksums compare covered bytes against an expected reference.
  7. Matching digests do not by themselves authenticate origin or truth.
  8. Page checksums detect some damage but do not reconstruct lost content.
  9. Structural and foreign-key checks answer different questions.
  10. Business reconciliation requires explicit grain, scope, keys and units.
  11. Matching totals can conceal offsetting record-level errors.
  12. Preserve original evidence before proposing a repair.
  13. Bind each correction to reviewed preimages and a complete identity.
  14. Stop when repair preconditions no longer match.
  15. Do not invent missing values to make a checker pass.
  16. Preserve recovery files and use supported acquisition procedures.
  17. Validate proposed repairs in isolation before separately authorized intervention.
  18. Review downstream reports, caches, exports and decisions after correction.
  19. Add regression fixtures reproducing the actual failure condition.
  20. Close only the verified scope and keep unresolved findings visible.

Return to contents