Duramata data platform setup

data-platform-setup.md

Data Platform Setup

Architecture

Lifecycle analytics use Cloudflare Pipelines writing raw NDJSON events into R2. Workers emit semantic events such as sprite_created; RARRA metrics are SQL queries over those events, not categories computed at write time. If the activation definition changes, update the SQL and re-query historical data.

The API worker is the producer:

1. Writes raw events with EVENTS_PIPELINE.send(records).
2. Keeps Loops and Slack credentials out of the request path.

The automation worker is the consumer:

1. Receives R2 object-create notifications for Pipeline output objects.
2. Reads NDJSON product events from EVENTS_BUCKET.
3. Uses D1 to derive first-sprite facts and contact email from existing tables.
4. Updates Loops and posts Slack notifications when appropriate.

The automation queue is an internal storage notification detail:

API Worker -> EVENTS_PIPELINE -> R2 events bucket -> R2 notification queue -> automation worker

To add a new event, define its schema, emit it at the write site with EVENTS_PIPELINE.send(), include base fields (event_name, user_id, environment, occurred_at), and add a query or automation consumer only when needed.

Event Schema

Every event includes:

Field	Description
event_name	Semantic event name, for example sprite_created.
user_id	User id when the event belongs to a user.
environment	production, staging, preview, or local.
occurred_at	ISO 8601 timestamp.

Current MVP event:

{
  "event_name": "sprite_created",
  "user_id": "user_...",
  "sprite_id": "sprite_...",
  "environment": "staging",
  "occurred_at": "2026-06-25T12:00:00.000Z"
}

Future raw events should follow the same shape and add only event-specific fields needed for analysis.

Querying RARRA Metrics

Cloudflare Pipelines writes NDJSON files under:

r2://<events bucket>/events/<date>/<hour>/

Buckets:

Environment	Bucket
Preview	duramata-events-preview
Staging	duramata-events-staging
Production	duramata-events-prod

Use R2 SQL with the environment bucket and R2 API credentials. Later, MotherDuck can connect to the same R2 data through the S3-compatible API, and Hex can query MotherDuck.

Example D7 retention cohort:

SELECT week, round(100.0 * retained / acquired, 1) AS d7_retention_pct
FROM (
  SELECT date_trunc('week', a.occurred_at) AS week,
    count(DISTINCT a.user_id) AS acquired,
    count(DISTINCT r.user_id) AS retained
  FROM events a
  LEFT JOIN events r
    ON a.user_id = r.user_id
    AND r.event_name = 'reply_received'
    AND r.days_since_signup <= 7
  WHERE a.event_name = 'waitlist_enrolled'
  GROUP BY week
)
ORDER BY week DESC;

Example weekly activations:

SELECT date_trunc('week', occurred_at) AS week,
  count(DISTINCT user_id) AS activations
FROM events
WHERE event_name = 'reply_received'
  AND is_activation = true
GROUP BY week
ORDER BY week DESC;

Loops Setup

1. Open Loops dashboard, then Settings, then API.
2. Set LOOPS_API_KEY in the deploy environment. The API worker already uses this key for transactional email; the automation worker uses the same secret.
3. Create contact properties:
   • hasCreatedSprite as Boolean.
   • firstSpriteCreatedAt as Date.
4. Create an event-triggered workflow with event name spriteCreated.
5. Send the onboarding or welcome email from that workflow.

The automation worker calls:

Endpoint	Purpose
PUT https://app.loops.so/api/v1/contacts/update	Set contact properties.
POST https://app.loops.so/api/v1/events/send	Trigger spriteCreated.

Slack Setup

1. Create a Slack app at https://api.slack.com/apps.
2. Enable Incoming Webhooks.
3. Add the app to the workspace and choose a channel such as #product-activity.
4. Set the webhook URL as SLACK_WEBHOOK_URL in the deploy environment.

Slack is optional. When SLACK_WEBHOOK_URL is unset, sprite creation and Loops automation continue normally.

Environment Variables

Variable	Required by	Required	Source
EVENTS_PIPELINE	API worker	Yes	Provisioned by Alchemy.
EVENTS_BUCKET	Automation worker	Yes	Provisioned by Alchemy.
EVENTS_OBJECTS_QUEUE	Automation worker	Yes	Provisioned by Alchemy.
LOOPS_API_KEY	API and automation workers	Yes	Loops dashboard, Settings, API.
SLACK_WEBHOOK_URL	Automation worker	No	Slack app Incoming Webhooks.
CLOUDFLARE_R2_ACCESS_KEY_ID	Alchemy deploy and Pipeline	Yes	Cloudflare dashboard, R2 API tokens.
CLOUDFLARE_R2_SECRET_ACCESS_KEY	Alchemy deploy and Pipeline	Yes	Cloudflare dashboard, R2 API tokens.

Verification

1. Run bun run typecheck.
2. Create a sprite in staging and confirm an NDJSON object with event_name = 'sprite_created' appears in the events bucket within the Pipeline batch window.
3. For a user's first sprite, confirm Loops shows hasCreatedSprite = true and a spriteCreated event in the contact event history.
4. When SLACK_WEBHOOK_URL is set, confirm a message is posted to the selected channel.
5. Create a second sprite for the same user and confirm the raw event is still written, but Loops and Slack are not fired again.