First-party tracking in SQL to measure the product without leaking health data
Why Markmedi has its own analytics layer, with a closed list of events, typed props and daily aggregates, and why no third-party tag runs inside the app.
When you build a SaaS on your own, you need to know what's happening in the product. Which modules clinics use, where the free trial stalls, how long it takes from sign-up to the first scheduled appointment, which campaign brought in the people who subscribed. The default answer is to drop a Google Analytics or PostHog tag into the app and call it done.
In Markmedi that isn't an option inside the logged-in area. A medical record screen has the patient's name, diagnosis and clinical photos. A third-party analytics tool collects the URL, the page title, the clicked text and, with session replay, the entire screen. A single URL with a patient's name or a misconfigured replay would be enough to send health data out. So I built my own analytics layer, in SQL, with a simple rule: it only accepts what was declared beforehand, and what is declared cannot identify a patient.
Where the data lives
Events go to a separate database connection, analytics. It can point to a different database, which keeps the metrics tables apart from the main database and lets them follow a different retention policy. In development the connection can be the same one.
Writes don't happen in the request path. The event is validated and pushed onto a buffer in Redis, and a command scheduled every minute flushes the buffer in batches of a thousand rows. If Redis or the analytics database fail, the failure is reported and the user notices nothing. The rule behind this code is that analytics never takes down a request.
![]()
The raw tables
There are five tables, with no foreign keys, designed for fast inserts:
analytics_visitors: the anonymous visitor, with first and last visit and the user, once they log in.analytics_touches: each acquisition touchpoint, with UTMs, click id type (gclid, fbclid and the like), referrer host and channel.analytics_sessions: sessions with active time, page count, device, OS and browser buckets, display mode (browser or PWA), screen width bucket, and country and region from the edge headers.analytics_pageviews: the route name, never the URL, the module, active time and scroll depth. The path is only recorded on the public website.analytics_events: the event, its source, the clinic, the doctor, the subject and the props.
Recording the route name instead of the URL was one of the most useful decisions. A patient's URL contains their id; the route is patients.show. To measure module usage, the route is all I need.
A closed list of events
Every event exists in a PHP enum, with about 95 cases: the website (home, pricing, diagnostic, demo), conversions (sign-up, demo requested, subscription started), the free trial funnel, the booking widget funnel, the appointment lifecycle, app usage (PWA installed, global search), consultations and AI, medical records and clinical forms, documents, management (CRM, campaigns, automations, finance, inventory) and the patient portal.
Each case declares the props it accepts, and each prop has a restricted type: a bounded integer, a boolean, an enum or a slug matching ^[a-z0-9_.:-]{1,64}$. Free text doesn't exist. A prop outside the declaration is dropped and reported.
case RecordSigned = 'record_signed';
public function props(): array
{
return match ($this) {
self::RecordSigned => [
'template' => PropRule::slug(),
'sections' => PropRule::int(0, 50),
'with_ai' => PropRule::bool(),
],
// ...
};
}A test walks the enum and fails if any prop name contains a forbidden segment: name, email, phone, cpf, rg, patient, notes, birth, among others. It isn't a mathematical guarantee, but it turns the most likely mistake (someone hastily adding patient_name) into a red CI.
An event's subject is restricted too. The only entity that can be a subject is the appointment. Patients, medical records and images are never subjects, so no event points to them, not even by id.
Server and browser events
On the server, events tied to a clinic are recorded with DB::afterCommit. If the business transaction rolls back, the event doesn't exist. The actor is only attributed when the person is on that clinic's staff, and demo clinics are left out so they don't pollute the metrics. Impersonated sessions (when I log in as a user to provide support) are flagged and excluded from every aggregate.
In the browser, the front end sends batches with navigator.sendBeacon to a collector at /e. That route has its own design: it skips CSRF as well as the clinic and plan middlewares, and uses a read-only session, which reads the logged-in user but never writes to the session. The body is capped at 64 KB and 50 items, there is per-IP rate limiting, and the response is always 204, accepted or not, so it can't serve as an oracle for anyone. User, clinic and impersonation come from the server session, never from the payload, so the browser can't attribute an event to another clinic.
Daily aggregates
Day-to-day queries don't read the raw tables. An overnight job rolls up the previous day into three tables:
analytics_daily_traffic: date, context, landing route, channel, source, medium, campaign, referrer and device, with sessions, visitors, pages and funnel conversions (sign-ups, demos, diagnostics, subscriptions, appointments).analytics_daily_usage: date, clinic, user, role, module, route, display mode and device, with views and active seconds.analytics_daily_features: date, clinic, doctor and feature, with count and duration.
The rollup deletes and rewrites the whole day inside a transaction. That makes it idempotent: I can rerun it after fixing a bug without duplicating anything. The SQL is portable between MySQL and SQLite, which lets me test the rollup in the regular suite.
Retention differs for each level. Raw data is kept for 24 months and deleted in batches by a scheduled command. Aggregates are kept forever, because they no longer carry anything that identifies a session.
The dashboards are Filament pages, for super admins only: acquisition, appointments, campaigns, clinics, single-clinic detail, free trial, clinical forms and platform usage.
What about off-the-shelf tools?
They're still there, just in the right place. GTM, GA4, PostHog and the Meta pixel load only on the marketing website and on the public booking widget. The server decides, based on the route context, and a test walks the routes to make sure no tag appears in the logged-in area, the patient portal or the links sent to patients. When entering the app, a change in the Inertia version forces a full page load, so that no website script survives the navigation.
![]()
In the booking widget, patient data fields have the ph-no-capture class, which excludes them from PostHog replay, and there's a test for that as well.
For paid campaigns, there is server-side conversion sending, which feeds the Meta Conversions API and GA4. It accepts exactly four events (diagnostic submitted, demo scheduled, sign-up completed and trial started) and sends the minimum those platforms require for attribution: event id, canonical path and their own cookies. No user, clinic or prop data leaves through there.
What is never recorded
The list of what stays out is as important as the list of what goes in:
- Patient name, email, phone number or CPF (Brazil's individual taxpayer ID), and the patient id.
- Medical record or clinical image ids.
- Clinical content, ICD codes (CID in Brazil) and any typed text.
- Raw IP and user agent (only the device, OS and browser buckets).
- App URLs (only the route name) and link tokens.
- Clinical form values: a completed form is counted by its key, never by its answers.
- User and clinic on events from the patient portal and patient links.
![]()
Wrapping up
The cost of running my own analytics is real: tables, rollups, dashboards and tests that a tag would handle in five minutes. In exchange, I know exactly what leaves the application, and I can explain it to a clinic, or to an LGPD (Brazil's data protection law) auditor, by pointing to an enum and a test. The buffer and the scheduled commands run on the same infrastructure described in Queues with Laravel Horizon, and the remaining privacy rules are in Multitenancy and security. The full project is at Markmedi.