Mail Raw Cleanup

Draft proposal generated by AI and may not have been reviewed for accuracy.

Summary

BinktermPHP currently stores both normalized UTF-8 message text in message_text and, in many cases, a second copy of the body in raw_message_bytes.

This proposal outlines three possible strategies:

1. Leave the current dual-storage model in place.
2. Drop `raw_message_bytes` completely and standardize on `message_text`.
3. Make `raw_message_bytes` the primary stored body and derive display text from it on demand.

The goal of this proposal is not to pick an implementation immediately, but to clarify the tradeoffs in storage cost, rendering fidelity, code complexity, and operational risk.

Background

Today the mail pipeline uses:

• `message_text` as the main body field for display, search, quoting, export, robot processing, and most application logic
• `raw_message_bytes` as an optional secondary body field intended to preserve exact original bytes for art fidelity
• `message_charset` as the declared or inferred encoding for the body

In practice:

• imported FTN messages may benefit from `raw_message_bytes`
• locally-authored messages currently duplicate data by storing a UTF-8 body in both columns
• most of the application still uses `message_text`
• only a limited set of rendering and export paths benefit from exact raw bytes

That means the current model pays a storage penalty everywhere, but realizes the fidelity benefit only in a few places.

Hypothetical Storage Estimates

Assume:

• 20,000 total messages
• average message sizes ranging from 500 bytes to 2 KB
• figures below are payload-only approximations and do not include row headers, TOAST metadata, indexes, or PostgreSQL page overhead

Single Copy Only

If only one body representation is stored:

| Average body size | 20,000 messages | |---|---:| | 500 bytes | 10,000,000 bytes (~9.5 MB) | | 1 KB | 20,480,000 bytes (~19.5 MB) | | 2 KB | 40,960,000 bytes (~39.1 MB) |

Dual Copy Model

If both message_text and raw_message_bytes contain a full copy of the body:

| Average body size | 20,000 messages | Approximate doubled payload | |---|---:|---:| | 500 bytes | ~9.5 MB | ~19.1 MB | | 1 KB | ~19.5 MB | ~39.1 MB | | 2 KB | ~39.1 MB | ~78.1 MB |

Extra Storage Attributable To Dual Storage

The extra storage consumed by a second full-copy body column is roughly:

| Average body size | Extra storage for 20,000 messages | |---|---:| | 500 bytes | ~9.5 MB | | 1 KB | ~19.5 MB | | 2 KB | ~39.1 MB |

These estimates are intentionally simple. Real PostgreSQL usage may be somewhat lower due to TOAST compression, or somewhat higher due to row/page overhead and fragmentation.

Strategy 1: Leave It As-Is

Description

Keep both columns:

• `message_text` remains the normalized application body
• `raw_message_bytes` remains the exact-byte preservation field

No major schema or runtime behavior changes are required.

Advantages

• lowest implementation risk
• preserves best possible fidelity for byte-sensitive formats
• does not require broad changes to search, quoting, robots, drafts, export, or API behavior
• avoids migration and compatibility work

Disadvantages

• highest steady-state storage cost
• local messages can still be redundantly stored twice
• most code continues to depend on `message_text`, so the raw-byte investment is underused
• does not solve the concern that the current model is paying for duplication everywhere

Caveats

• this is operationally safe, but it is not an optimization
• it is easiest to justify only if exact-byte rendering is considered a strategic feature worth the storage overhead

Best Case For Choosing This

Leave it alone if:

• storage pressure is acceptable
• exact-byte fidelity for future rendering is important
• engineering time is better spent elsewhere

Strategy 2: Drop raw_message_bytes Completely

Description

Remove raw_message_bytes from netmail and echomail, and make all code rely exclusively on message_text.

Under this model:

• `message_text` is the only body source
• art rendering uses normalized text
• `message_charset` may still exist for metadata and outbound encoding decisions

Advantages

• simplest data model
• removes duplicated body storage immediately
• reduces row size, TOAST usage, and long-term storage growth
• most current code already works primarily from `message_text`

Disadvantages

• exact original body bytes are lost permanently
• PETSCII fidelity becomes unrealistic
• Amiga ANSI fidelity may also degrade in cases where the original 8-bit byte stream mattered
• any code that currently prefers `message_bytes_b64` must be rewritten or downgraded

Caveats

This likely is not the best long-term answer if byte-accurate rendering remains a project goal.

Dropping the raw column would require:

• removing backend payload generation for `message_bytes_b64`
• removing frontend code paths that consume base64 raw bytes
• changing any art or ad-save logic that currently prefers raw bytes
• accepting that some rendering paths become approximate rather than exact

Storage Effect

Using the 20,000-message estimate above, the savings from dropping the second body copy are roughly:

| Average body size | Approximate savings | |---|---:| | 500 bytes | ~9.5 MB | | 1 KB | ~19.5 MB | | 2 KB | ~39.1 MB |

Best Case For Choosing This

Drop the raw column if:

• exact-byte rendering is no longer a requirement
• storage reduction is more important than future PETSCII or Amiga ANSI fidelity
• the project wants a simpler and more conventional mail storage model

Strategy 3: Make raw_message_bytes Primary

Description

Store the original body in raw_message_bytes as the canonical body representation and derive normalized text from it as needed.

In the strongest version of this model:

• `raw_message_bytes` becomes the source of truth
• `message_charset` becomes mandatory or strongly enforced
• `message_text` is either dropped or treated as a cache/derived field
• display and processing paths perform on-the-fly `iconv` conversion from raw bytes to UTF-8

Advantages

• preserves the highest fidelity for all message formats
• treats the raw body as the canonical source, which is conceptually clean for archival and protocol correctness
• avoids ambiguity about which body is authoritative

Disadvantages

• highest application complexity
• many read paths would need on-demand decoding
• every search, excerpt, robot, quote, export, and list path must either: - decode on the fly, or - rely on a separate cached/derived UTF-8 field
• malformed or mislabeled charsets become a runtime problem rather than an ingest-time problem
• performance risk increases because body decoding moves from write-time to read-time

Caveats

This strategy is much larger than a storage cleanup.

It would effectively redesign message storage semantics and touch:

• message detail APIs
• list APIs and excerpt generation
• search logic
• quoting and reply preparation
• QWK/export paths
• robot processors
• ad-library and art-rendering logic

If message_text is fully removed, the project would need a reliable answer for:

• text search across mixed charsets
• excerpt generation without repeated decoding cost
• charset error handling when input bytes do not match declared metadata
• consistent conversion rules across all consumers

Search and indexing are also a major caveat.

The current application behavior assumes that message bodies are searchable as text. PostgreSQL text-search approaches such as:

• `ILIKE`
• trigram indexes
• full-text search via `to_tsvector`

are designed for text columns, not bytea.

If raw_message_bytes became the only body column, the project would likely lose effective indexed body search unless it also introduced a separate decoded text representation for search purposes.

That means one of the following would still be required:

• keep `message_text` as a searchable UTF-8 field
• add a separate derived `search_text` field
• perform charset-aware decoding in an indexed expression, which would be significantly more complex

Decoding raw bytes on the fly inside search queries would be operationally unattractive and would likely undermine index usefulness.

Storage Effect

If message_text is dropped and only raw_message_bytes is retained, storage savings are similar to Strategy 2:

| Average body size | Approximate savings versus dual storage | |---|---:| | 500 bytes | ~9.5 MB | | 1 KB | ~19.5 MB | | 2 KB | ~39.1 MB |

However, if message_text is retained as a cache for search or convenience, then the storage savings largely disappear and the system inherits more complexity without solving the duplication problem.

Best Case For Choosing This

Make raw bytes primary only if:

• byte-perfect storage is the highest priority
• the project is willing to redesign message reads around charset-aware decoding
• there is a strong archival or format-preservation goal that outweighs implementation cost

Comparative Summary

| Strategy | Storage | Fidelity | Code Complexity | Risk | |---|---|---|---|---| | Leave as-is | Worst | Best | Low | Low | | Drop raw bytes | Best | Worst for byte-sensitive formats | Medium | Medium | | Make raw bytes primary | Best if message_text removed | Best | High | High |

Practical Recommendation

Based on the current codebase, the most pragmatic approach is not one of the three extremes.

A more balanced operational strategy would be:

1. Keep `message_text` as the primary application field.
2. Keep `raw_message_bytes` only where it adds real value.
3. Stop storing `raw_message_bytes` for locally-authored UTF-8 messages.
4. Optionally limit raw-byte storage to imported messages, or to messages whose charset or art format implies byte-sensitive fidelity needs.

That hybrid approach is outside the strict scope of the three strategies listed above, but it would likely capture most of the storage savings without giving up the exact-byte preservation that motivated the feature in the first place.

Open Questions

1. Is PETSCII or other byte-sensitive rendering still a core product requirement?
2. Should local messages ever preserve a raw-byte body if they are authored directly in UTF-8?
3. Is `message_text` primarily a convenience cache, or is it the actual canonical body for the application?
4. Would a selective retention policy be acceptable even if a few rare rendering cases still depend on raw bytes?

Conclusion

The current dual-storage model is easy to reason about but wastes space.

Dropping raw_message_bytes entirely would reduce storage and simplify the schema, but likely at the cost of exact-byte rendering fidelity.

Making raw_message_bytes primary would preserve fidelity, but at the cost of significantly more complexity and runtime conversion work.

If the objective is to reduce waste without destabilizing the message system, the likely best direction is to keep message_text as the canonical application body and narrow raw_message_bytes to the cases where it is genuinely needed.