storing messages

[tttp]

If possible and for privacy reasons, I would split the messages in two:

1. the long term storage: it is whatever is needed for analytics (how many emails per period/campaign) and to train the model (the embedding vector)

2. the short term storage: in principle, most of the personal information of the citizen (including the message) should be kept only until the politician has replied)

We might need to adjust a bit, for instance to allow the politician to delay the reply after the final vote, but conceptually, I'd like to consider them as two different storage with different purposes

long term storage

• messageid (UUID)
• channel (which provider if REST API, which email server if stalwart)
• timestamp
• senderid: hash (email sender)
• campaign
• bge-m3 vector
• date replied
• id reply sent

notes:

1. one citizen can write to many representative as part of the same campaign
2. one citizen can write many times to the same representative

We probably should store them, so far we are using dupe_rank (duplicate ranks) for the later anything more than 0 means the citizen is a bit spammy ;)

#short term storage

at least name + email+ timestamp of the sender, whatever is needed to reply

[1v4n4]

Do we store replies?

Should we allow images/attachments? Or have the possibility to develop that?

[debiB]

Current State Analysis

• Long Term Storage : You effectively have this already in the messages table. It stores metadata, embeddings, sender hash, and campaign info, but excludes the message body and sender PII.
• Short Term Storage : This is currently missing.
  • For API messages , the content and PII are currently dropped after processing.
  • For Email (Stalwart) , the system relies on the email server to hold the content (instructing it to file emails into specific folders).

Proposed Plan

We will implement a distinct Short Term Storage mechanism while keeping the existing messages table as the Long Term Storage .

1. Long Term Storage ( messages table)

• Status : Mostly complete.
• Action : Ensure it has all fields you requested.
  • messageid (UUID): Currently id (Serial) or external_id . We can add a UUID column if strictly needed for internal referencing.
  • channel , timestamp , sender_hash , campaign , bge-m3 vector : Already exists.
  • date replied : Already exists ( reply_sent_at ).
  • id reply sent : Already exists ( reply_template_id ), but we might want to track the specific sent message ID if different from the template.

2. Short Term Storage (New Table: message_inbox )

• Purpose : Holds PII and content only until a reply is sent.
• Proposed Schema :
  • id : Primary Key
  • message_id : Foreign Key to Long Term Storage ( messages.id )
  • sender_name : Text
  • sender_email : Text
  • subject : Text
  • body : Text
  • attachments : JSONB (links to files)
  • created_at : Timestamp 3. The Flow

1. Ingestion : When a message arrives (API or Email), we create records in both tables.
2. Reply : When a politician replies via the app, we:
   • Send the reply (via SMTP/API).
   • Update messages (Long Term) with date replied .
   • DELETE the record from message_inbox (Short Term).

Questions

1. For messages coming from the Website/API, should we inject them into the Email Server (Stalwart) so it becomes the single place for all message content?
2. What happens if a politician never replies to a message?

• Option A: Auto-delete the content after X days (e.g., 30 days) to ensure privacy.
• Option B: Keep it indefinitely until they manually reply or delete it.

3. Is support for attachments (images/PDFs) a requirement for the first release (MVP) , or can this be added later?
4. Do we need to encrypt the content at the application level before storing it, or is standard database/server security sufficient?

Proposed Architecture

• Long-Term (Supabase): Keeps the "Metadata" (Who sent it, when, which campaign, sentiment score). No message body, No names/emails. Used for analytics.
• Short-Term (Stalwart): Keeps the "Content" (The actual text, sender name, email address).
• The Flow: When a politician opens the dashboard, we fetch the metadata from Supabase and the content from Stalwart on-the-fly. When they hit "Reply", the system sends the email and immediately instructs Stalwart to delete the sensitive content.

[tttp]

Hi,

For privacy reasons, I'd keep the storage of messages in stalwart, no need of message_inbox)

For messages coming from the Website/API, should we inject them into the Email Server (Stalwart) so it becomes the single place for all message content?

Good question, we don't really have that problem right now so we can skip it, but ideally:

• if the message received is "generic", we can reply automatically and no need to save the initial message
• if the message needs additional answers, forward it to the politician AND/OR save it in stalwart

let's create a separate issue to be handled in the next version

What happens if a politician never replies to a message?

We have a problem, we should alert the politicians at least

• Option B: Keep it indefinitely until they manually reply or delete it.

We can't really delete user messages if not handled. we'll figure out ways to - at least - provide a generic answers, eg "the vote happened, this is the result"

Is support for attachments (images/PDFs) a requirement for the first release (MVP) , or can this be added later?
not needed

Do we need to encrypt the content at the application level before storing it, or is standard database/server security sufficient?

ideally: all PII is stored in stalwart, only metadata is stored in the database, so standard database security is enough