Separating Source Data from Execution Snapshots in a Collection Architecture

✨ GPT-5.5’s Summary

A record of moving reference data that kept shifting between JSON, CSV, and DuckDB into PostgreSQL, while separating source evidence, observations, reference data, execution snapshots, and delivery artifacts.

Reference Data Kept Shifting Between JSON, CSV, and DuckDB

As I set up the data collection, review, execution, and delivery flow over several days, all kinds of issues kept popping up. I have never properly learned or worked with databases, so I changed the data storage method often, and each change was never just a simple replacement.

At first, I collected data in JSON and CSV, then attached DuckDB so I could inspect it. But the worker outputs became entangled with the coordinator’s repeated database rebuilds, and I kept losing track of what was source data and what was reference data.

I also drafted a plan to build the database with MySQL. But the problem was not MySQL itself. The problem was a structure where multiple workers or scripts directly updated the reference data. With that approach, concurrent updates, conflicts, ownership, and rollback problems were likely to keep repeating.

I reviewed other approaches after that, but the core problem remained the same. When the boundaries between collected data, reference data, execution data, and delivery artifacts are unclear, the same problem eventually comes back.

Mutation Ownership Mattered More Than the Database Product

Moving to PostgreSQL would not solve the problem by itself. Before choosing a database, I had to decide who was allowed to change the reference data.

Workers leave source evidence and observations in an append-only form. They do not modify the reference data directly. The coordinator reviews that evidence and those observations, then updates the reference data and its values. Keeping responsibility for changing the reference data in one place made it possible to trace what had to be rolled back when a conflict occurred.

I Split the Roles Inside PostgreSQL

So today, while moving to a PostgreSQL database, I also organized the entire flow I had been setting up over the past several days. Instead of treating everything as one database lump, I split the roles.

• Source evidence
  • Store evidence and original values collected from public sources
  • Keep processed values and states in a separate history instead of overwriting the source
  • JSON and CSV files may be evidence mirrors, but they are not reference data
• Observations
  • Workers submit only the observations from their assigned collection channels to an append-only ledger
  • Discovery and enrichment are separated into different roles
  • Workers do not modify reference data directly
• Reference data
  • The coordinator reviews the evidence and observations
  • Only reviewed values are incorporated into reference data according to the defined structure
  • Views are used as surfaces for calculation, review, and export
• Execution snapshots
  • A collection view is not used directly as the basis for actual execution
  • Targets that pass deduplication and quality gates are fixed into a snapshot at a specific point in time
  • Execution results, exclusions, and follow-up states are managed separately from the reference data
• Delivery artifacts
  • Files handed off as CSV, Google Sheets, Excel, or vox.ai uploads are generated from the reference data or execution snapshots
  • These are artifacts for display or delivery
  • The artifacts are not edited again as if they were reference data

I Could Trace the Flow from Evidence to Execution and Delivery

This completed a flow where I could trace where collected evidence accumulated, what workers left behind, which values became reference values, which snapshot was used for actual execution, and what became a delivery artifact.

Of course, the structure is still lacking in many ways, so there will be plenty more to work on. I will keep testing, getting feedback, and revising it, and all kinds of major changes will probably happen. That is how the flow, the harness structure, and my own intuition will all keep developing.

I am sharing this in case my flow setup helps someone else.