[Data model] Replace ambiguous delimiter-encoded processed_data rows with a versioned typed representation

[seonghobae]

Buyer / data-model gap

Protected develop@622e5e6c3d534f230c390f10e3832efadfc01825 persists transformed synchronous ETL output as one text value assembled by EtlService:

KEY:value,KEY:value,...

The implementation intentionally preserves commas and colons inside values, e.g. NAME:A:B,C,, and nested arrays/objects are embedded as compact JSON. Because the outer representation has no escaping, length-prefixing, schema, or canonical parser contract, the stored text is not uniquely reversible: a downstream consumer cannot distinguish field delimiters from legitimate value commas/colons without reconstructing Java-specific transformation knowledge.

docs/etl/bounded-atomic-batches.md already calls typed target schemas a separate milestone. This issue makes that buyer-visible gap explicit rather than treating processed_data.data as a durable interoperable schema.

Status: known_gap. Do not overlap current #198/#199 EtlService amount-integrity writer; implementation should start only after that source lane is stable/integrated.

RCA

• Immediate cause: transformed fields are concatenated with : and , delimiters while arbitrary string values may contain those same characters.
• Technical root cause: processed_data.data is a historical display-like serialization rather than a versioned typed storage/event contract.
• Control failure: current tests prove that comma/colon values are preserved, but they do not prove a unique round trip from persisted representation back to the original transformed field map.
• Acquisition/interoperability risk: data lineage, schema evolution, replay/export, downstream SQL/analytics, connector handoff, and independent clients must reverse-engineer implementation details; different records can have ambiguous textual interpretations.

Bounded architecture options

1. Preferred — versioned typed canonical representation. Define a schema-versioned JSON/JSONB or normalized relational representation for transformed records with deterministic field semantics, explicit version identity, size bounds, migration/compatibility rules, and a lossless round-trip test. Choose the physical form only after consumer inventory and PostgreSQL query/index requirements are known.
2. Alternative — explicit length-prefixed/escaped text codec. Feasible but inferior for external interoperability and schema evolution unless compatibility requirements force text storage.
3. Reject — continue undocumented delimiter parsing. Preserving delimiters without an unambiguous codec is not a data contract.
4. Reject — silently rewrite existing rows in place. Historical records require source/consumer inventory, migration rehearsal, rollback/forward-recovery, and integrity verification.

TDD / migration acceptance

• inventory every repository/external consumer of processed_data.data before physical migration;
• fail-first round-trip tests with commas, colons, Unicode, empty/null values, nested arrays/objects, and field-name normalization collisions;
• define one versioned machine-readable transformed-record schema and compatibility policy;
• representative PostgreSQL migration rehearsal for existing rows, explicitly classifying rows that cannot be unambiguously decoded from the legacy format rather than guessing;
• new writes are lossless and deterministic under the selected representation;
• old/new application compatibility and rollback/forward-recovery are explicit;
• no raw payload or PII is copied into logs merely for migration diagnostics;
• ERD/data model, API/event/connector contracts, data governance/retention, Operability, Traceability and CHANGELOG agree with the integrated physical state;
• descriptive multi-word snake_case for any new owned database objects;
• exact source, realistic database, non-vacuous coverage, security/SBOM/provenance and independent review gates pass before protected integration.

Relationships: #159 canonical documentation; #183/#184 Flyway schema authority; #188 recovery; #198/#199 amount integrity; #165 release/provenance.

[seonghobae]

Temporal-schema follow-through — 2026-08-10

The same protected legacy processed_data bootstrap has a second data-model asymmetry that the typed-representation migration should not silently carry forward:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

whereas the newer durable-job authority correctly uses TIMESTAMPTZ for created_at / updated_at. A timestamp-without-time-zone value can preserve a local wall-clock reading but does not by itself carry an unambiguous UTC instant. For acquisition-grade replay/audit/lineage across hosts and regions, a future versioned processed_data representation should make temporal semantics explicit rather than inheriting this historical ambiguity.

This does not authorize racing #155, which currently owns the clean-bootstrap SQL, or rewriting historical rows in place. Fold the temporal boundary into #204's consumer/migration inventory after that bootstrap lane stabilizes:

• identify every consumer that interprets processed_data.created_at and the actual database/session timezone assumptions in supported deployments;
• choose an explicit instant/wall-clock contract (normally an unambiguous instant for ingestion audit, with source-local event time represented separately when needed);
• add realistic PostgreSQL migration tests around DST/offset boundaries and mixed session timezones;
• classify existing TIMESTAMP rows whose original timezone cannot be proven instead of inventing an offset;
• update ERD/data-governance/lineage/recovery contracts together with the typed record representation.

Status remains known_gap; no protected behavior changes until a safe migration path is proven.