Mail Ledger — Tamper-Proof Email Archive

Project Overview

A construction and consulting firm runs on its correspondence. Approvals, drawings, bill submissions and tender notices all arrive by email, and a single deleted thread can undo months of a contract position. Mail Ledger removes that risk by keeping a second copy that nobody — including the administrator — can alter.

The system reads the company's Google Workspace mailbox with a read-only credential and writes each message into a private database on the firm's own server. Staff sign in to a Gmail-shaped portal to read, search, download and reply. Deleting mail in Gmail has no effect on the ledger.

A second phase absorbed the firm's legacy Yahoo mailbox — 28 GB that could not be migrated into a 30 GB Workspace account. Message bodies went to the server, attachments to object storage, and the address remains fully usable: mail sent from the portal can still go out under it.

The Interface

Modelled closely on Gmail, deliberately. Staff open this beside their own mailbox all day; anything they have to learn is friction, and friction is what makes people keep a copy of the mail somewhere else.

Sign-in screen — **Signing in.** Staff accounts live in the firm's own database — no Google licence per employee. Failed attempts lock an account, and both the success and the failure are recorded.

Inbox message list — **The list.** Folders derived from the labels each message arrived with, so nothing is re-filed — a folder is a query, not a move. Both mailboxes appear together, tagged by origin.

Dashboard showing conversations awaiting a reply — **Dashboard.** Conversations where the last message came in and nothing has gone back, aged into bands. Automated senders — tender portals, GST notices, no-reply addresses — are excluded, which is the difference between a worklist and a wall of noise.

Reading a message — **Reading.** Sender HTML is rendered inside a sandboxed frame after every remote reference has been stripped, so tracking pixels never fire. Each message carries a permanent record number and a body checksum. Remote images load only when the reader asks, and are fetched by the server rather than the browser.

Search results with Gmail-style operators — **Search.** The full Gmail operator set — `from:`, `has:attachment`, `filename:`, `before:`, `larger:`, quoted phrases and negation — over a MySQL full-text index, across five years of mail.

Compose window — **Composing.** Replies go out through the Gmail API under a separate send-only credential. Recipient suggestions come from the archive itself, drafts survive closing the window, and a branded company footer is added automatically — with the address of whichever mailbox the message will leave by.

Administration — people and access — **Access.** Staff accounts, roles and per-mailbox permissions. Accounts are disabled, never deleted, so the audit trail always has a name to point at.

Activity log — **Audit.** Every sign-in, message opened, file downloaded and refusal — filterable, and with no edit or delete path anywhere in the codebase.

Screenshots are of the live system with correspondent names, addresses, subjects and IP addresses replaced by invented equivalents. Layout, counts and behaviour are untouched.

Key Features

Read-only mirror of Google Workspace mail, synced continuously

Legacy IMAP mailbox archived and kept in sync every 5 minutes

Gmail-style folders, categories, stars and conversation threading

Full-text search with the complete Gmail operator vocabulary

Reply, reply-all and forward — including from the legacy address

Reply-tracking dashboard: what is waiting, and for how long

Correspondent reports — volume, response rate and open threads

Roles, per-mailbox access control and a complete audit log

Attachment deduplication by content hash, with object-storage offload

Print and PDF output of any message or whole conversation

Per-user appearance settings, kept private to each account

Company email footer with statutory identifiers, centrally managed

Technical Implementation

PHP 8 and MariaDB on shared hosting, with no framework and no package manager — every dependency is written out, because the deployment target offers no Composer and no build step. Google is reached through two separate service accounts with domain-wide delegation: one authorised for gmail.readonly, the other for gmail.send. Neither can do the other's job, and that was verified by offering each the other's scope and watching Google refuse.

The legacy mailbox is read over IMAP in resumable slices, three concurrent workers at a time — a fourth connection earns an immediate rate-limit refusal from the provider. Attachments are content-addressed by SHA-256 so an identical drawing sent to eight people is stored once, then pushed to Cloudflare R2 with a Signature V4 implementation written by hand over curl.

Sender HTML never reaches a browser unmodified. A sealing pass rewrites every remote reference out of the markup before it is served, because the host's CDN discards Content-Security-Policy headers and substitutes its own — measured, not assumed. The message body then renders inside a sandboxed frame, and any image the reader chooses to see travels inside the page as bytes rather than as a link the frame's opaque origin would refuse.

Problems Worth Naming

28 GB that would not fit anywhere

The old mailbox was larger than the Workspace account meant to receive it. Splitting it solved the arithmetic — bodies and headers into MySQL, 19.5 GB of attachments into object storage with no egress fee — while the portal presents both halves as one mailbox.

A count that was slower than counting

The message list stopped counting at 5,000 and displayed "many", on the assumption that an exact count would be expensive. Measured, the exact count took 16 ms against the ceiling's 37 ms — the optimisation was slower than the thing it replaced, and told the reader less.

A 33-second query, and a 30-millisecond one

Reply-rate analysis written as a correlated subquery re-scanned the table once per message, each pass an unindexable LIKE over recipient lists. Reading both sides once and walking them together with a moving cursor produced identical answers in 30 ms.

An import that could never finish

Each import worker treated a pass as complete only when it stored and skipped nothing — a state a finished slice never reaches, because what remains in it is promotional mail that is skipped on every pass. Three workers cycled indefinitely, holding the connections the live sync waited for, so the archive looked busy while new mail quietly stopped arriving.

Images that were refused before they were requested

Message bodies render in a sandboxed frame, which the browser gives an opaque origin — and under an opaque origin img-src 'self' matches nothing, so same-origin image URLs were refused before any request was made. Embedding the bytes in the page fixed it without weakening the sandbox that keeps hostile mail away from the session.

Outcome

Five years of correspondence — back to July 2021, across 1,343 correspondents — is now searchable in one place and cannot be removed from it. Two mailboxes on entirely different platforms behave as one, and mail can be answered from either address without leaving the portal.

The dashboard surfaces what is genuinely waiting on a reply rather than everything unanswered, which turned a four-thousand-row backlog into a list somebody works through in a morning. Replies are answered within a day 92% of the time, measured across the last year of threads.

Project Details

Client

Mukherjee Associates Infra Pvt Ltd

Industry

Infrastructure & Civil Construction

Technology

PHP 8 MariaDB Gmail API IMAP Cloudflare R2 SigV4 Full-text search

Scale

12,659 messages · 18,281 files · 19.5 GB

Duration

2 weeks to production

Discuss Similar Project

Built To Resist

No delete route, no delete permission, no delete code

Tracking pixels stripped before the page is built

Record number and body checksum on every message

Audit log with no edit or delete path anywhere

Attachments outside the webroot, served after a permission check

More Projects

Admin Dashboard

HR Management System

Office Attendance System View all projects