Build log #1: the event log that never lies (schema tour)

Vaporware doesn't publish its schema. So here's ours.

This is a tour of the data model under GrowthCrew — the append-only event log, the identity graph, the touches, and the attributions computed on top. It's build log #1 because the schema is the foundation the honesty rests on. If the data underneath can be quietly edited, no amount of pretty reporting on top can be trusted.

The one rule: the event log is append-only

Everything starts with an immutable events table. Something happens in the world — a link is clicked, a promo code is redeemed, a signup completes, a payment clears — and we write one row. We never update it. We never delete it. We only append.

{
  "event_id": "evt_01H9...",
  "type": "promo_code_redeemed",
  "occurred_at": "2026-06-02T14:03:11Z",
  "recorded_at": "2026-06-02T14:03:11Z",
  "identity_id": "anon_8f2c...",
  "payload": {
    "code": "ADITYA20",
    "campaign": "yt_creator_june",
    "amount_cents": 4900,
    "currency": "usd"
  }
}

Append-only means the log is a record of what happened, not a mutable summary of what we currently believe happened. Once evt_01H9... is written, it says the same thing forever. That property is the entire reason the rest of the system can be honest — you can't launder a claim into a fact if the underlying events can't be rewritten.

Identities: anonymous, then known, then merged

Most touches happen before you know who the person is. They click a link as an anonymous browser. Later they sign up with an email. Those are the same human, and the schema has to figure that out without losing the earlier, anonymous history.

So identities starts anonymous and gets merged into a known identity when we get proof they're the same person — a matching session, a login, a code redemption tied to an account.

{
  "identity_id": "known_4d1a...",
  "status": "known",
  "email": "user@example.com",
  "merged_from": ["anon_8f2c...", "anon_bb90..."],
  "merged_at": "2026-06-02T14:05:40Z"
}

Crucially, the merge is also just an append-only event. We don't destroy anon_8f2c... and rewrite its clicks to point at the known identity. We record that a merge happened and resolve the identity graph at read time. The anonymous history stays intact — which matters, because the pre-signup touches are usually the ones that actually created the demand.

Touches: every interaction, carrying its confidence

A touch is a marketing interaction we can tie to an identity: a tracked link click, a code redemption, a self-report. Every touch carries its confidence tier right on the row — exact, strong, or inferred.

{
  "touch_id": "tch_77b2...",
  "identity_id": "anon_8f2c...",
  "channel": "influencer",
  "campaign": "yt_creator_june",
  "kind": "promo_code_redeemed",
  "confidence": "exact",
  "occurred_at": "2026-06-02T14:03:11Z"
}

The confidence tier lives with the data, not in a report. A promo-code redemption is born exact and stays exact. A "how did you hear about us?" answer is born inferred and never quietly gets promoted to make a campaign look better. Because touches are derived from the immutable event log, they carry the same don't-lie property upward.

Attributions: computed, versioned, and replayable

Here's the payoff. attributions is not a table we hand-edit — it's computed from touches, under a named model. And because the events are immutable, we can compute it as many different ways as we want, and recompute it whenever we improve the model.

-- attribution is a function of the immutable log, not a stored opinion
SELECT identity_id, campaign, credit
FROM attribute(
  touches => touches_for(identity_id),
  model   => 'triangulated'   -- or 'last_touch' | 'first_touch' | 'linear'
);

We support last_touch, first_touch, linear, and triangulated. The triangulated model is the one we actually trust — it blends tracked touches with lift/spike analysis and self-reports instead of handing all credit to whichever click happened to be last.

The important part: attribution is recomputable. Ship a better triangulation model next month and you don't lose history — you replay it against the same immutable event log and get corrected numbers for campaigns that already ran. Last-click told you one story in June; the better model can tell you the true story about June in July, because June's raw events never changed.

Contrast that with a system that stores a single "attributed_conversions" number and overwrites it. Change your logic there and history is gone. You can never ask "what would last-touch have said versus triangulated?" because you only kept one answer.

And the claimed numbers live separately

One more thing the schema does on purpose: platform-claimed conversions are stored in spend_daily, right next to the verified ones — never blended into them.

{
  "date": "2026-06-02",
  "channel": "meta",
  "spend_cents": 12000,
  "claimed_conversions": 47,
  "verified_conversions": 19
}

Claimed and verified are two columns, not one reconciled guess. The gap between them is a first-class number, because the gap is the product.

Why this is build log #1

The schema is the honesty. Immutable events mean numbers can't be quietly rewritten. Confidence tiers on every touch mean nothing gets promoted to look better. Recomputable attribution means we can get more honest over time without erasing the past. Claimed-separate-from-verified means we never launder a platform's claim into a fact.

[FACT-CHECK: whether we want to open-source the full schema / migrations, and where they'll live]

You can't verify a marketing tool's honesty from a dashboard. You can start to verify it from a schema. So here's ours — build log #2 will walk through the triangulation model that runs on top of it.