You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ponymail.apache.org by rb...@apache.org on 2026/06/22 18:41:26 UTC
(incubator-ponymail-foal) branch rbowen-database-schema created (now 608cf3c)
This is an automated email from the ASF dual-hosted git repository.
rbowen pushed a change to branch rbowen-database-schema
in repository https://gitbox.apache.org/repos/asf/incubator-ponymail-foal.git
at 608cf3c Document complete database schema in architecture.md
This branch includes the following new commits:
new 608cf3c Document complete database schema in architecture.md
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
(incubator-ponymail-foal) 01/01: Document complete database schema in architecture.md
Posted by rb...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
rbowen pushed a commit to branch rbowen-database-schema
in repository https://gitbox.apache.org/repos/asf/incubator-ponymail-foal.git
commit 608cf3c3acb863f2ca4d1f3dfd63f77f4abb6f97
Author: Rich Bowen <rb...@rcbowen.com>
AuthorDate: Mon Jun 22 14:40:45 2026 -0400
Document complete database schema in architecture.md
Expanded from a brief 4-index summary to full field-level documentation
covering all 7 indices (mbox, source, attachment, account, session,
notification, auditlog) with types and descriptions sourced from
tools/mappings.yaml.
---
docs/architecture.md | 132 ++++++++++++++++++++++++++++++++++++++++++---------
1 file changed, 110 insertions(+), 22 deletions(-)
diff --git a/docs/architecture.md b/docs/architecture.md
index 5cc3f1a..277a55f 100644
--- a/docs/architecture.md
+++ b/docs/architecture.md
@@ -86,47 +86,135 @@ concatenated into `webui/js/ponymail.js` via `build.sh` during development.
The sole data store. Contains these indices (prefixed by `db_prefix`,
default `ponymail`):
-| Index | Content |
+| Index | Purpose |
|-------|---------|
| `ponymail-mbox` | Parsed email metadata (from, subject, date, body, list-id, threading info) |
| `ponymail-source` | Raw RFC 2822 email source (the original message as received) |
+| `ponymail-attachment` | Binary attachment data |
| `ponymail-account` | User session/preference data |
+| `ponymail-session` | Active login sessions (cookie ↔ account mapping) |
+| `ponymail-notification` | Per-user notifications for watched threads |
| `ponymail-auditlog` | Admin action audit trail |
+The definitive field mappings are in `tools/mappings.yaml` (used by
+`setup.py` to create indices). Below is a summary of the key fields.
+
---
## Data Model
### Email Document (`mbox` index)
-Each email is stored with:
-
-- `mid` — Internal permalink ID (DKIM-based, collision-resistant)
-- `dbid` — SHA3-256 of the raw message source
-- `message-id` — Original Message-ID header
-- `from`, `to`, `cc`, `subject`, `date`, `epoch`
-- `body` — Parsed plain-text body
-- `list` / `list_raw` — List-ID header
-- `in-reply-to`, `references` — Threading headers
-- `private` — Whether the email is on a private list
-- `attachments` — Array of attachment metadata (filename, hash, size)
-- `gravatar` — MD5 of sender address for avatar lookup
+| Field | Type | Description |
+|-------|------|-------------|
+| `mid` | keyword | Internal permalink ID (DKIM-based). Also the document `_id` |
+| `dbid` | keyword | SHA3-256 of the raw source. Also the `_id` in the source index |
+| `message-id` | keyword | Original Message-ID header |
+| `from` | text | Sender display (searchable) |
+| `from_raw` | keyword | Sender address (exact match) |
+| `to` | text | Recipient(s) |
+| `cc` | text | CC recipients |
+| `subject` | text | Subject line (`fielddata: true` for aggregations/word cloud) |
+| `body` | text | Parsed plain-text body |
+| `body_short` | text | First ~200 characters of body |
+| `date` | date | UTC datetime (`yyyy/MM/dd HH:mm:ss`) |
+| `epoch` | long | Unix timestamp parsed from Date: header |
+| `list` | text | List-ID header (searchable) |
+| `list_raw` | keyword | List-ID header (exact match, format: `<list.domain>`) |
+| `forum` | keyword | List address as `list@domain` |
+| `in-reply-to` | keyword | In-Reply-To header (for threading) |
+| `references` | text | References header chain |
+| `private` | boolean | Whether this email is on a private list |
+| `deleted` | boolean | Soft-delete flag (hidden from UI) |
+| `attachments` | nested | Array: `{filename, content_type, hash, size}` |
+| `gravatar` | text | MD5 of sender address for avatar lookup |
+| `permalinks` | keyword[] | All IDs this email is accessible under |
+| `size` | long | Size of raw source in bytes |
+| `html_source_only` | boolean | Body stored as HTML (html2text unavailable at archive time) |
+| `_notes` | text | Internal annotations |
+| `_archived_at` | long | Epoch when the email was archived |
+
+**Threading fields** (only present when `archiver.threadinfo` is enabled):
+
+| Field | Type | Description |
+|-------|------|-------------|
+| `top` | boolean | Is this email the root of a thread? |
+| `thread` | keyword | Permalink ID of the thread root |
+| `previous` | keyword | Permalink ID of the parent message |
### Source Document (`source` index)
-- `id` — Same `dbid` as the corresponding mbox entry
-- `source` — The complete raw RFC 2822 message
+| Field | Type | Description |
+|-------|------|-------------|
+| `_id` | — | Same as `dbid` in the mbox entry |
+| `source` | binary | The complete raw RFC 2822 message |
+| `message-id` | keyword | Original Message-ID (for cross-reference) |
+| `deleted` | boolean | Soft-delete flag |
+
+### Attachment Document (`attachment` index)
+
+| Field | Type | Description |
+|-------|------|-------------|
+| `_id` | — | The attachment `hash` from the mbox entry |
+| `source` | binary | Raw attachment binary data |
+
+### Account Document (`account` index)
+
+| Field | Type | Description |
+|-------|------|-------------|
+| `cid` | keyword | Account ID |
+| `credentials.email` | keyword | OAuth-provided email |
+| `credentials.name` | keyword | OAuth-provided display name |
+| `credentials.uid` | keyword | OAuth-provided user ID |
+| `internal.admin` | boolean | Has admin privileges |
+| `internal.oauth_provider` | keyword | Which OAuth provider authenticated this user |
+| `internal.oauth_data` | object | Provider-specific data (dynamic) |
+| `request_id` | keyword | Session correlation ID |
+
+### Session Document (`session` index)
+
+| Field | Type | Description |
+|-------|------|-------------|
+| `cookie` | keyword | Session cookie value |
+| `cid` | keyword | Linked account ID |
+| `updated` | long | Last activity epoch |
+
+### Notification Document (`notification` index)
+
+| Field | Type | Description |
+|-------|------|-------------|
+| `recipient` | keyword | Email address of the user to notify |
+| `mid` | text | Message permalink ID |
+| `message-id` | keyword | Original Message-ID |
+| `from` | text | Sender of the triggering message |
+| `subject` | keyword | Subject line |
+| `list` | text | List-ID |
+| `date` | date | Message date |
+| `epoch` | long | Message epoch |
+| `private` | boolean | Whether the message is private |
+| `seen` | long | Epoch when the notification was marked read (0 = unseen) |
+| `type` | keyword | Notification type |
+
+### Audit Log Document (`auditlog` index)
+
+| Field | Type | Description |
+|-------|------|-------------|
+| `date` | date | When the action was performed |
+| `author` | keyword | Admin who performed the action |
+| `remote` | keyword | Remote IP address |
+| `action` | keyword | Action type (edit, delete, hide, unhide) |
+| `target` | keyword | Document ID affected |
+| `lid` | keyword | List-ID context |
+| `log` | text | Description of what was changed |
-### Threading
+---
+
+### Threading Model
Threading is reconstructed at query time from `in-reply-to` and
`references` headers. The `thread.json` endpoint walks up/down the
-chain to build a tree structure.
-
-Optional threading metadata (enabled via `archiver.threadinfo` config):
-- `top` — Boolean: is this email the root of a thread?
-- `thread` — ID of the thread root
-- `previous` — ID of the parent message
+chain to build a tree structure. When `archiver.threadinfo` is enabled,
+pre-computed `top`, `thread`, and `previous` fields accelerate lookups.
---