data-archaeology

Data archaeology

Working out what the data actually means.

Why this exists

In a system of any age the schema is an unreliable narrator. Columns are nullable that are never null, and non-nullable columns hold empty strings meaning "unknown." Enums contain values the code stopped writing four years ago. A column named status holds three different vocabularies depending on when the row was created. Foreign keys are declared in some places and merely implied in others.

None of this is in the documentation, because there isn't any. It is discoverable from two sources: the queries the code actually issues, which reveal intended meaning, and the data itself, which reveals what happened.

The gap between them is where migrations fail and reports go quietly wrong.

When this applies

• Undocumented schema or unclear domain model
• Before a migration, a reporting change, or a schema change
• Column names that don't match their contents
• You need to know what the data looks like, not what the schema permits
• Before data-pipeline-work or db-change-management

When it doesn't

• Well-documented schema with a maintained model
• You need control flow rather than data — that's trace-the-flow
• You need current row-level values for testing — that's test-data-strategy

Prerequisites

• Locate the workspace: FDE_WORKSPACE, else the charter's Location, else .fde/, else ../<repo>-fde/. Do not invent a second workspace.
• .fde/02-system-map.md — where data lives
• Read access to a database, ideally. Where you have none, this skill still works from migrations and code, at reduced confidence — say so.
• Understand the organization's data policy before running aggregates. Shape queries live in core test-data-strategy step 2 — run those, do not paste a second dialect-specific copy.

Procedure

1. Reconstruct the declared schema

# Migrations, in order, are the schema's history
find . \( -path "*migration*" -o -path "*/db/*" \) -name "*.sql" | sort | tail -40
# ORM / schema declarations — bind EXT from the language mix, do not assume one framework
grep -rn "CREATE TABLE\|Schema(\|DbSet\|entity-name" --include="*.$EXT" --include="*.sql" --include="*.xml" . | head -30