The messages that arrive through the contact form are stored in Neon, and I read them in /admin/, a private panel: only the addresses in ADMIN_EMAILS get in, with a one-time code. Until recently it was a list with one status per lead: good for reading, not for following up.
Now the panel tracks every lead (DEC-031, in Spanish): statuses, history, notes, filters, export and replies in the lead’s language. Here’s what I took care of in each part.
Six statuses, with the same identifiers as before
A lead moves through New, Contacted, Proposal, Won, Lost and Discarded (spam or not a fit). The panel itself is in Spanish (Nuevo, Contactado, Propuesta, Ganado, Perdido, Descartado). In the database and the API they’re English identifiers:
export const LEAD_STATUSES = ['new', 'contacted', 'proposal', 'won', 'lost', 'spam'] as constFive of them already existed; I only added proposal. Renaming them to match the panel would have broken the published version while the migration runs, because that version would keep writing the old names. Adding a value breaks nothing.
That’s why migration 0007 is additive only: a new enum value (ADD VALUE 'proposal' BEFORE 'won'), two tables and an index. The previous version works against the migrated database, so I migrate first and publish after.
On screen, every status has a color, an icon and a label, so color is never the only signal.
The history is written in the same statement as the change
I wanted to know who moved a lead, from which status to which, and when. The access log is written separately and can fail without leaving a trace. That’s why the history has its own table, lead_status_changes.
The challenge is atomicity. The API talks to Neon over HTTP (I explained why), and that driver has no interactive transactions. The way out was not to need them: the change and its history go in a single statement with CTEs. The first one reads the row with for update, the second changes the status and returns the previous one, and the third inserts the history from what the second returned:
const recorded = db.$with('recorded').as(
db.insert(leadStatusChanges).select(
db
.select({
id: sql`gen_random_uuid()`.as('id'),
leadId: updated.id,
at: sql`${at.toISOString()}::timestamptz`.as('at'),
actor: sql`${actor}::text`.as('actor'),
fromStatus: updated.previous,
toStatus: updated.status,
})
.from(updated)
.where(ne(updated.previous, updated.status)),
),
)The final where means that picking the same status again writes no history, and the row lock keeps the previous status exact if two changes arrive at once. A test adds a check to the history table that no row can pass, and requires the lead’s status to stay as it was: if one part fails, nothing is saved.
Notes that never leave the panel
Every lead has internal notes in lead_notes, with author and date, of up to 1,000 characters. The limit lives in the contract (the field’s maxlength) and also in a check in the database.
Notes don’t go into the list of leads, the CSV, the e-mail notification or the access log. The log records that I added a note and its id, but not what it says. A test saves a note and requires its text not to appear in the list’s response.
Saving one is also a single statement, an insert … select that takes the id from the lead itself: if the lead doesn’t exist, there’s no note and the API answers 404.
Filters in the URL and a search that ignores accents
I filter by status, service, language, date range and text, and sort by newest or oldest. The filters live in the URL (/admin/?status=won&from=2026-11-01), so a view can be bookmarked and the Back button undoes the last filter. If a bookmark carries a value the panel no longer knows, that filter is dropped and the rest are kept. Dates are whole days in the browser’s time zone, which travels with every request.
The search looks in the name, e-mail, phone and message, ignoring accents and case. I ruled out unaccent and pg_trgm: they don’t come the same in PGlite, where the tests run, or in the local Postgres. Instead, the same table of letters in SQL and in JavaScript:
const FOLD_FROM = 'ÁÀÄÂÃÉÈËÊÍÌÏÎÓÒÖÔÕÚÙÜÛÑÇáàäâãéèëêíìïîóòöôõúùüûñç'
const FOLD_TO = 'AAAAAEEEEIIIIOOOOOUUUUNCaaaaaeeeeiiiiooooouuuunc'That way “gomez” finds “Gómez”.
Pagination uses a cursor (date and id), not OFFSET: the cost of a page doesn’t grow with the page number. Every page brings the total of the filtered view, counted in parallel. At the top, a summary shows the leads per status and those of the last 7 and 30 days, in a single query with count(*) filter (where …).
A CSV for Excel that runs no formulas
Exporting the filtered view has three traps:
- Accents: without a BOM, Excel reads “Pérez” as “Pérez”. The file is UTF-8 with a BOM.
- Separator: Excel splits columns by the system’s list separator, which in es-CO is
;because the comma is the decimal mark. Google Sheets and LibreOffice detect it on their own. - Formula injection: a spreadsheet runs a cell that starts with
=,+,-or@as a formula, and anyone can type that into a public form.
export function neutralizeFormula(value: string): string {
return /^[=+\-@\t\r]/.test(value) ? `'${value}` : value
}The apostrophe keeps it as text. As a bonus, the phone goes out as +57…, the rule turns it into '+57…, and Excel doesn’t show it in scientific notation.
An export holds 5,000 rows at most; with more, the API asks you to narrow the filters. The file is built on the server, not in the browser, because the export must be logged. And since the personal data of every lead in the view leaves in it, it fails closed: if it can’t be written to the access log, there’s no file.
} catch (err) {
log.error('export audit failed', err)
c.header('Retry-After', '60')That log stores who exported, how many rows and with which filters, but not the search text, which could be someone’s name or e-mail.
Replying in the lead’s language
Every lead stores the language of the page it wrote from. Its record has “Reply by e-mail” and, if the lead left a phone number, “Reply on WhatsApp”: links with the message already written in that language, which I can change before sending.
The texts live in admin-replies.json and its pair in en/, and they’re validated at build time. Only {firstName}, {name} and {service} fit between braces; an unknown or unclosed brace breaks the build. A test fails if they contain numbers or currencies, because the first reply must not promise prices or dates the site doesn’t state.
And it weighs less
The signed-in panel loads separately from the login, so the first load of /admin/ went from 23.8 to 19.1 KB gzipped.
Check it yourself
- The decision and what I ruled out:
docs/DECISIONES.md(DEC-031, in Spanish). - Statuses, filters, history and summary:
repository.ts; the notes, inactivity.ts. - The CSV and its tests:
csv.tsandcsv.test.ts. - The panel’s routes:
admin-leads.ts, with their tests inadmin-crm.test.ts. - The contract:
packages/contracts/src/crm.ts. - The migration and how to publish it:
0007_lead_crm.sqlanddocs/DESPLIEGUE.md(in Spanish). - How to edit the replies:
docs/CONTENIDO.md(in Spanish), section «Panel: respuestas a los contactos».
The panel is private, but how this site is built shows where it fits with the rest. And if you’d like something like it for your business, write to me.