Constructive Events

Constructive Events

Event tracking, gamification, and achievement-based credit rewards. Configured through blueprints (EventTracker nodes + achievements[]) and managed via the ORM.

When to Apply

Use this skill when:

• Adding event tracking to tables (EventTracker node)
• Defining achievements with requirements and credit rewards
• Building invite virality chains (EventReferral with max_depth)
• Implementing period-aware recurring achievements
• Tracking analytics events on row changes

Architecture

Table row change
  → EventTracker trigger (compound conditions evaluated)
    → record_event(event_name, actor_id)
      → app_events log (partitioned, time-based retention)
      → upsert_achievement() → event_aggregates updated
        → tg_check_achievements
          → level_achieved() → level_grants created
            → tg_achievement_reward → credits granted
            → tg_invitee_achievement → record_event for inviter

Capabilities

EventTracker

{
  "tables": [{
    "table_name": "user_profiles",
    "nodes": [
      { "$type": "EventTracker", "data": {
        "event_name": "avatar_uploaded",
        "events": ["UPDATE"],
        "watch_fields": ["avatar_url"],
        "conditions": { "field": "avatar_url", "op": "IS NOT NULL" }
      }}
    ]
  }]
}

EventReferral (Multi-Level Referral)

{ "$type": "EventReferral", "data": {
  "event_name": "purchase_completed",
  "max_depth": 3
}}

max_depth (1–10) walks up the claimed_invites chain N levels, crediting each ancestor inviter.

References

Cross-References

• Limits and credits: `constructive-billing`
• Entity types and invites: `constructive-entities`
• Background jobs (shared conditions system): `constructive-jobs`
• Blueprint definitions: `constructive-blueprints`

Achievements Reference

Achievements are defined at the blueprint level (not as table nodes) because they're inherently cross-table — one achievement can reference events from multiple tables. The achievements[] section seeds levels, requirements, and reward definitions into the events module.

Blueprint Structure

{
  "achievements": [
    {
      "name": "getting_started",
      "description": "Complete your profile setup",
      "priority": 10,
      "entity_prefix": "app",
      "requirements": [
        { "event_name": "avatar_uploaded", "count": 1 },
        { "event_name": "profile_completed", "count": 1 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "projects", "amount": 5, "credit_type": "permanent" }
      ]
    }
  ]
}

How Achievements Work

1. Event Accumulation

EventTracker nodes record events → record_event() calls upsert_achievement() which updates the event_aggregates table with running counts per user (or per user+entity).

2. Achievement Checking

tg_check_achievements fires after event_aggregates is updated. It calls level_achieved() which checks all requirements for all levels. When all requirements are met, it creates a level_grants row (with a unique constraint to prevent re-grants).

3. Reward Granting

tg_achievement_reward fires when a new level_grants row is created. It loops over achievement_rewards by level_name and grants credits (limit_credits or meter_credits) for each matching reward. This trigger is SECURITY DEFINER — users don't need direct write access to credits tables.

4. Idempotency

grant_achievement is a callable function that grants an achievement idempotently. The unique constraint on level_grants(actor_id, level_name, period_start) (or + entity_id for entity variant) prevents double-granting within the same period. For non-periodic events, period_start defaults to a constant sentinel value, preserving earn-once semantics. For periodic events, a new period_start each period allows re-granting. The reward trigger fires once per grant.

Achievement Fields

Requirement Fields

Reward Fields

Reward Types

`limit_credit` — Grants credits to the limits module's limit_credits table. The target_name must match a limit provisioned by a LimitCounter node. These credits increase the user's effective limit cap.

`meter_credit` — Grants credits to the billing module's meter_credits table. The target_name must match a meter slug from a provisioned billing_module. Requires both events_module and billing_module to be provisioned for the same database. These credits provide quota that is consumed by record_usage() calls.

`expires_interval` (meter_credit only): When set, the reward trigger computes an expiration timestamp at grant time (current time + the interval). The billing module's lazy expiration system handles the rest — expired credits are automatically skipped during usage checks. Useful for time-limited referral rewards.

{
  "rewards": [
    {
      "reward_type": "meter_credit",
      "target_name": "api_calls",
      "amount": 100,
      "credit_type": "permanent",
      "expires_interval": "30 days"
    }
  ]
}

Cross-Table Achievements

Achievements naturally span multiple tables because requirements reference event_name values, not tables. Multiple EventTrackers on different tables can feed the same achievement:

{
  "tables": [
    {
      "table_name": "user_profiles",
      "nodes": [
        { "$type": "EventTracker", "data": { "event_name": "avatar_uploaded", "events": ["UPDATE"], "watch_fields": ["avatar_url"] } }
      ]
    },
    {
      "table_name": "projects",
      "nodes": [
        { "$type": "EventTracker", "data": { "event_name": "first_project_created", "events": ["INSERT"] } }
      ]
    },
    {
      "table_name": "team_members",
      "nodes": [
        { "$type": "EventTracker", "data": { "event_name": "team_joined", "events": ["INSERT"], "actor_field": "user_id" } }
      ]
    }
  ],
  "achievements": [
    {
      "name": "getting_started",
      "requirements": [
        { "event_name": "avatar_uploaded", "count": 1 },
        { "event_name": "first_project_created", "count": 1 },
        { "event_name": "team_joined", "count": 1 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "projects", "amount": 10 }
      ]
    }
  ]
}

Multi-Scope Achievements

Different entity types can have independent achievements. Use entity_prefix to target the correct scope:

{
  "entity_types": [
    { "prefix": "app", "has_levels": true, "has_limits": true },
    { "prefix": "org", "parent_entity": "app", "has_levels": true, "has_limits": true }
  ],
  "achievements": [
    {
      "name": "app_onboarding",
      "entity_prefix": "app",
      "requirements": [
        { "event_name": "avatar_uploaded", "count": 1 }
      ]
    },
    {
      "name": "org_starter",
      "entity_prefix": "org",
      "requirements": [
        { "event_name": "first_member_invited", "count": 1 }
      ]
    }
  ]
}

Achievement + Limits Interaction

Achievement rewards grant credits via the limits module. The target_name must match a limit provisioned by a LimitCounter or similar node:

{
  "tables": [
    {
      "table_name": "projects",
      "nodes": [
        { "$type": "LimitCounter", "data": { "limit_name": "projects" } },
        { "$type": "EventTracker", "data": { "event_name": "project_created", "events": ["INSERT"] } }
      ]
    }
  ],
  "achievements": [
    {
      "name": "prolific_creator",
      "requirements": [
        { "event_name": "project_created", "count": 10 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "projects", "amount": 5 }
      ]
    }
  ]
}

When the user creates their 10th project → prolific_creator achievement unlocks → 5 additional project credits are granted → the user's effective limit increases from the base cap.

Period-Aware Event Aggregates

Event types can define a period_interval that resets aggregate counts each period. This enables per-period achievement re-qualification (e.g., "earn referral credit each billing cycle").

How It Works

1. `event_types.period_interval` — Optional interval (e.g., '1 month', '1 hour'). When set, the event type uses periodic counting instead of lifetime counting.

2. `event_aggregates.period_start` — Tracks the start of the current counting period. Set to now() on first event, refreshed when the period elapses.

3. Lazy reset — When record_event() upserts an aggregate, it checks:

• If period_start + period_interval <= now() → the period has elapsed
• Reset count to the incoming value (instead of accumulating)
• Refresh period_start to now()
• Otherwise → accumulate normally

This is the same lazy reset pattern used by the billing module's period-based credits.

Registering Periodic Event Types

Event types with a period_interval are registered during provisioning. Use the ORM or CLI to create them:

// ORM
await client.eventTypes.create({
  data: {
    name: 'billing.subscription_active',
    periodInterval: '1 month'
  }
});

# CLI
csdk event-types create \
  --name billing.subscription_active \
  --period-interval '1 month'

Non-periodic event types (the default) omit periodInterval — they count events across the user's entire lifetime.

Re-Triggerable Achievements

When an event type has a period_interval, its aggregate count resets each period. This means an achievement's requirements can be re-met in a new period, resulting in a new level_grants row and a new reward grant.

How It Works

level_grants has a UNIQUE constraint that includes period_start:

• User variant: UNIQUE(actor_id, level_name, period_start)
• Entity variant: UNIQUE(actor_id, entity_id, level_name, period_start)

When tg_check_achievements fires, it uses the aggregate's period_start (or a sentinel value for non-periodic events) when creating the level_grants row:

• Non-periodic aggregates (period_start = NULL) → sentinel value is constant → earn-once semantics preserved (same UNIQUE key every time = duplicate is ignored)
• Periodic aggregates → period_start changes each period → new UNIQUE key → new level_grants row → reward trigger fires again

Example: Recurring Referral Credits

Define the periodic event type and a re-triggerable achievement in the blueprint:

{
  "achievements": [
    {
      "name": "active_referral",
      "entity_prefix": "app",
      "requirements": [
        { "event_name": "billing.subscription_active", "count": 1 }
      ],
      "rewards": [
        {
          "reward_type": "meter_credit",
          "target_name": "api_calls",
          "amount": 50,
          "credit_type": "permanent",
          "expires_interval": "30 days"
        }
      ]
    }
  ]
}

Then register the event type as periodic via the ORM:

await client.eventTypes.create({
  data: {
    name: 'billing.subscription_active',
    periodInterval: '1 month'
  }
});

Each billing period: 1. Stripe invoice.paid webhook → webhook handler calls record_event('billing.subscription_active', referrer_id) 2. Aggregate count resets to 1 (lazy reset), new period_start 3. Achievement re-qualifies → new level_grants row with new period_start 4. Reward trigger fires → new meter credit granted with 30-day expiration 5. If referral churns → no webhook → no event → no new credit → old credit expires naturally

Provisioning Order

constructBlueprint() processes achievements in Phase 7: 1. Resolves the events_module for the given entity_prefix + membership_type 2. Creates the level definition (name, description, priority) 3. Creates one level_requirement per requirement entry (event_name + count) 4. Creates one achievement_reward per reward entry (reward_type, target, amount)

The events_module must already exist (Phase 0 entity types with has_levels: true), and limits must be provisioned (Phase 0 entity types with has_limits: true) for limit_credit reward grants to work. For meter_credit rewards, a billing_module must be provisioned for the same database.

EventReferral Reference

EventReferral is a table-level blueprint node that attributes events to the actor's inviter(s) when a row changes. It resolves the referrer via the invites module's claimed_invites table.

Single-Level Referral (Default)

With max_depth: 1 (or omitted), EventReferral credits only the direct inviter:

{
  "tables": [{
    "table_name": "user_profiles",
    "nodes": [
      { "$type": "EventReferral", "data": {
        "event_name": "invitee_completed_profile",
        "events": ["UPDATE"],
        "actor_field": "owner_id"
      }}
    ]
  }]
}

When User B (invited by User A) updates their profile: 1. Trigger fires → resolves NEW.owner_id (User B) 2. Looks up claimed_invites WHERE receiver_id = B → finds User A 3. Calls record_event('invitee_completed_profile', A)

Multi-Level Referral (max_depth > 1)

Set max_depth to walk up the invite chain multiple levels:

{
  "tables": [{
    "table_name": "user_uploads",
    "nodes": [
      { "$type": "EventReferral", "data": {
        "event_name": "invitee_uploaded",
        "events": ["INSERT"],
        "actor_field": "owner_id",
        "max_depth": 5
      }}
    ]
  }]
}

When User D (chain: A invited B invited C invited D) uploads a file: 1. Depth 1: claimed_invites WHERE receiver_id = D → C. Records event for C. 2. Depth 2: claimed_invites WHERE receiver_id = C → B. Records event for B. 3. Depth 3: claimed_invites WHERE receiver_id = B → A. Records event for A. 4. Depth 4: claimed_invites WHERE receiver_id = A → NULL. Loop exits.

All three ancestors (C, B, A) receive the same invitee_uploaded event. The loop stops early if the chain is shorter than max_depth.

Configuration Reference

Constraints

• `max_depth` range: 1–10. The generator raises an exception for values outside this range.
• App-level scope only: When max_depth > 1, entity_field must be omitted. The chain walk uses claimed_invites which is scoped by membership_type at the app level. Entity-scoped actions still credit the chain — the trigger resolves the user who performed the action, not the entity.
• Default behavior: max_depth: 1 (or omitted) produces a single-lookup trigger (direct inviter only).

Toggles

Multi-Level MLM Blueprint Example

A complete blueprint showing 5-level referral rewards with tiered achievements:

{
  "entity_types": [
    {
      "name": "App Members",
      "prefix": "app",
      "has_invites": true,
      "has_levels": true,
      "has_limits": true
    }
  ],

  "tables": [
    {
      "table_name": "databases",
      "fields": [
        { "name": "name", "type": { "name": "text" }, "is_required": true },
        { "name": "owner_id", "type": { "name": "uuid" }, "is_required": true }
      ],
      "nodes": [
        { "$type": "EventReferral", "data": {
          "event_name": "invitee_created_db",
          "events": ["INSERT"],
          "actor_field": "owner_id",
          "max_depth": 5
        }},
        { "$type": "LimitCounter", "data": { "limit_name": "databases" } }
      ]
    }
  ],

  "achievements": [
    {
      "name": "referral_bronze",
      "description": "3 people in your network created a database",
      "priority": 10,
      "requirements": [
        { "event_name": "invitee_created_db", "count": 3 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "databases", "amount": 5 }
      ]
    },
    {
      "name": "referral_silver",
      "description": "10 people in your network created a database",
      "priority": 20,
      "requirements": [
        { "event_name": "invitee_created_db", "count": 10 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "databases", "amount": 3 }
      ]
    },
    {
      "name": "referral_gold",
      "description": "25 people in your network created a database",
      "priority": 30,
      "requirements": [
        { "event_name": "invitee_created_db", "count": 25 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "databases", "amount": 2 }
      ]
    }
  ]
}

Attenuation Design

Multi-level referrals create natural attenuation without any per-depth tracking:

• Direct inviters (depth 1) see events frequently — their invitees' actions directly generate events. They hit achievement thresholds quickly.
• 2nd-degree ancestors see events less often — only when their invitees' invitees act.
• 5th-degree ancestors accumulate events very slowly.

The tiered achievement thresholds (3 → 10 → 25) create decreasing rewards at each tier. Combined with the natural event decay at deeper levels, this produces an MLM-style attenuation curve without any schema changes or depth-tracking infrastructure.

Performance

Each depth level is one indexed lookup on claimed_invites(receiver_id). With max_depth=10, that's at most 10 index scans per trigger fire — negligible overhead.

Composing with Invite Virality

EventReferral composes with has_invite_achievements. They serve different purposes:

• `has_invite_achievements`: Credits the inviter when an invite is claimed (invite_claimed event) and when an invitee earns an achievement (invitee_achieved_* events). Always single-level.
• `EventReferral`: Credits the inviter(s) when an invitee performs a table action. Supports multi-level via max_depth.

Both can be used simultaneously. A common pattern is has_invite_achievements for the social/gamification loop and EventReferral with max_depth > 1 for the MLM referral reward chain.

EventTracker Reference

The EventTracker node type creates AFTER triggers on a table that call record_event() whenever rows change. It follows the same pattern as JobTrigger — same compound conditions syntax, same watch_fields behavior — but records events to the events module instead of enqueuing background jobs.

Category prefix: event (generates event_tracker slug in the Node Type Registry).

Parameter Reference

Constraints: conditions, condition_field, and watch_fields are mutually exclusive.

Compound Conditions

EventTracker uses the exact same compound condition system as JobTrigger. Column types are resolved automatically from the PostgreSQL schema.

Leaf condition

{ "field": "status", "op": "=", "value": "active", "row": "NEW" }

Array shorthand (implicit AND)

{
  "$type": "EventTracker",
  "data": {
    "event_name": "profile_completed",
    "events": ["UPDATE"],
    "conditions": [
      { "field": "display_name", "op": "IS NOT NULL" },
      { "field": "avatar_url", "op": "IS NOT NULL" },
      { "field": "bio", "op": "IS NOT NULL" }
    ]
  }
}

Nested combinators (AND/OR/NOT)

{
  "$type": "EventTracker",
  "data": {
    "event_name": "order_completed",
    "events": ["UPDATE"],
    "conditions": {
      "AND": [
        { "field": "status", "op": "=", "value": "completed" },
        { "field": "status", "op": "!=", "value": "completed", "row": "OLD" },
        { "NOT": { "field": "is_test", "op": "=", "value": true } }
      ]
    }
  }
}

Toggle Mode

When toggle: true, the trigger records an event when the condition becomes true and removes it when the condition becomes false. This is useful for boolean state tracking:

{
  "$type": "EventTracker",
  "data": {
    "event_name": "email_verified",
    "events": ["UPDATE"],
    "toggle": true,
    "condition_field": "is_email_verified"
  }
}

• Row changes with is_email_verified = true → record_event('email_verified', actor_id)
• Row changes with is_email_verified = false → event count is decremented

Toggle mode works with compound conditions too — the conditions determine the "on" state.

Entity-Scoped Events

By default, events are user-scoped (attributed to actor_field). For entity-scoped events (e.g., per-org, per-team), add entity_field:

{
  "$type": "EventTracker",
  "data": {
    "event_name": "document_uploaded",
    "events": ["INSERT"],
    "actor_field": "created_by",
    "entity_field": "org_id"
  }
}

This calls the entity variant of record_event(step, actor_id, entity_id), which stores the event scoped to both user and entity.

FK-based entity resolution (entity_lookup)

When entity_field is a FK (not a direct entity_id), combine with entity_lookup:

{
  "$type": "EventTracker",
  "data": {
    "event_name": "message_sent",
    "events": ["INSERT"],
    "actor_field": "sender_id",
    "entity_field": "channel_id",
    "entity_lookup": {
      "obj_table": "channels",
      "obj_field": "entity_id"
    }
  }
}

The generator resolves channel_id → channels.entity_id at provision time and bakes the JOIN as static SQL in the trigger.

Common Patterns

Track avatar upload (UPDATE with condition)

{
  "$type": "EventTracker",
  "data": {
    "event_name": "avatar_uploaded",
    "events": ["UPDATE"],
    "watch_fields": ["avatar_url"],
    "conditions": { "field": "avatar_url", "op": "IS NOT NULL" }
  }
}

Track first project creation (INSERT)

{
  "$type": "EventTracker",
  "data": {
    "event_name": "first_project_created",
    "events": ["INSERT"],
    "actor_field": "owner_id"
  }
}

Track status transition (compound conditions)

{
  "$type": "EventTracker",
  "data": {
    "event_name": "order_shipped",
    "events": ["UPDATE"],
    "conditions": {
      "AND": [
        { "field": "status", "op": "=", "value": "shipped" },
        { "field": "status", "op": "!=", "value": "shipped", "row": "OLD" }
      ]
    }
  }
}

Multiple EventTrackers on one table

A single table can have multiple EventTracker nodes for different events:

{
  "table_name": "user_profiles",
  "nodes": [
    { "$type": "EventTracker", "data": {
      "event_name": "avatar_uploaded",
      "events": ["UPDATE"],
      "watch_fields": ["avatar_url"],
      "conditions": { "field": "avatar_url", "op": "IS NOT NULL" }
    }},
    { "$type": "EventTracker", "data": {
      "event_name": "profile_completed",
      "events": ["UPDATE"],
      "conditions": [
        { "field": "display_name", "op": "IS NOT NULL" },
        { "field": "avatar_url", "op": "IS NOT NULL" }
      ]
    }},
    { "$type": "EventTracker", "data": {
      "event_name": "bio_added",
      "events": ["UPDATE"],
      "watch_fields": ["bio"],
      "conditions": { "field": "bio", "op": "IS NOT NULL" }
    }}
  ],
  "fields": [
    { "name": "display_name", "type": { "name": "text" } },
    { "name": "avatar_url", "type": { "name": "text" } },
    { "name": "bio", "type": { "name": "text" } }
  ]
}

EventTracker + JobTrigger on same table

EventTracker and JobTrigger coexist naturally — same table can track events AND enqueue background jobs:

{
  "table_name": "invoices",
  "nodes": [
    { "$type": "EventTracker", "data": {
      "event_name": "invoice_paid",
      "events": ["UPDATE"],
      "conditions": {
        "AND": [
          { "field": "status", "op": "=", "value": "paid" },
          { "field": "status", "op": "!=", "value": "paid", "row": "OLD" }
        ]
      }
    }},
    { "$type": "JobTrigger", "data": {
      "task_identifier": "send_receipt_email",
      "events": ["UPDATE"],
      "conditions": {
        "AND": [
          { "field": "status", "op": "=", "value": "paid" },
          { "field": "status", "op": "!=", "value": "paid", "row": "OLD" }
        ]
      }
    }}
  ]
}

Invite Virality Achievements

The invite virality system composes EventTracker with the invites module to create viral growth loops. Two tiers of invite-based achievements are available.

Prerequisites

All three flags must be set on the entity type:

{
  "entity_types": [
    {
      "name": "App User",
      "prefix": "app",
      "has_invites": true,
      "has_levels": true,
      "has_invite_achievements": true
    }
  ]
}

Tier 1: Simple Invite Tracking

When has_invite_achievements: true, the system auto-attaches an EventTracker to the {prefix}_claimed_invites table:

• event_name: "invite_claimed"
• events: ["INSERT"]
• actor_field: "sender_id" (credits the SENDER, not the receiver)
• auto_register_type: true

This means every time someone claims an invite, the inviter (sender) gets an invite_claimed event. No manual EventTracker configuration needed.

Example: "Invite 5 friends" achievement

{
  "achievements": [
    {
      "name": "social_butterfly",
      "description": "Successfully invite 5 people",
      "requirements": [
        { "event_name": "invite_claimed", "count": 5 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "projects", "amount": 10 }
      ]
    }
  ]
}

Tiered invite achievements

{
  "achievements": [
    {
      "name": "recruiter_bronze",
      "priority": 10,
      "requirements": [{ "event_name": "invite_claimed", "count": 3 }],
      "rewards": [{ "reward_type": "limit_credit", "target_name": "storage_gb", "amount": 1 }]
    },
    {
      "name": "recruiter_silver",
      "priority": 20,
      "requirements": [{ "event_name": "invite_claimed", "count": 10 }],
      "rewards": [{ "reward_type": "limit_credit", "target_name": "storage_gb", "amount": 5 }]
    },
    {
      "name": "recruiter_gold",
      "priority": 30,
      "requirements": [{ "event_name": "invite_claimed", "count": 25 }],
      "rewards": [{ "reward_type": "limit_credit", "target_name": "storage_gb", "amount": 20 }]
    }
  ]
}

Tier 2: Meta — Invitee Achievements

The meta tier rewards inviters when their invitees earn achievements. This is the viral loop.

When has_invite_achievements: true, a SECURITY DEFINER trigger (tg_invitee_achievement) fires AFTER INSERT on level_grants. It:

1. Looks up who invited the achiever via the claimed_invites table 2. If the achiever was invited by someone, records an event: invitee_achieved_{level_name} attributed to the inviter

The event name is auto-generated: invitee_achieved_ + the level name that was just earned.

Example: "Mentor" achievement

{
  "achievements": [
    {
      "name": "getting_started",
      "requirements": [
        { "event_name": "avatar_uploaded", "count": 1 },
        { "event_name": "profile_completed", "count": 1 }
      ]
    },
    {
      "name": "mentor",
      "description": "3 of your invitees earned Getting Started",
      "requirements": [
        { "event_name": "invitee_achieved_getting_started", "count": 3 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "projects", "amount": 50 }
      ]
    }
  ]
}

When User B (invited by User A) earns getting_started: 1. level_grants row inserted for User B 2. tg_invitee_achievement fires → finds User A as inviter 3. record_event('invitee_achieved_getting_started', user_a_id) called 4. User A's aggregate for invitee_achieved_getting_started increments 5. When User A accumulates 3 such events → mentor achievement → 50 credits

Full Virality Chain

User A invites User B
  → B claims invite → A gets 'invite_claimed' event (tier 1)
  → B uploads avatar → B gets 'avatar_uploaded' event
  → B completes profile → B gets 'profile_completed' event
  → B earns 'getting_started' achievement
    → A gets 'invitee_achieved_getting_started' event (tier 2)
  → A has 3 invitees who earned 'getting_started'
    → A earns 'mentor' achievement → A gets 50 credits
    → A invites more people (viral loop)

Multi-Level Referral Chains (EventReferral + max_depth)

For MLM-style referral rewards that go beyond direct inviters, use EventReferral with max_depth > 1 on table nodes. This walks the claimed_invites chain up to N levels:

User A invites B, B invites C, C invites D

D creates a database (EventReferral with max_depth=5):
  → C gets 'invitee_created_db' event (depth 1)
  → B gets 'invitee_created_db' event (depth 2)
  → A gets 'invitee_created_db' event (depth 3)

This is separate from has_invite_achievements (which only tracks invite claims and achievement completions). See event-referral.md for the full reference, blueprint examples, and attenuation design.

Entity-Scoped Invites

Each entity type gets its own independent invite achievement tracking. If both app and org have has_invite_achievements: true:

• app_claimed_invites → EventTracker with invite_claimed events for app scope
• org_claimed_invites → EventTracker with invite_claimed events for org scope
• app_level_grants → invitee achievement trigger for app scope
• org_level_grants → invitee achievement trigger for org scope

Achievements reference their entity scope via entity_prefix:

{
  "entity_types": [
    { "prefix": "app", "has_invites": true, "has_levels": true, "has_invite_achievements": true },
    { "prefix": "org", "parent_entity": "app", "has_invites": true, "has_levels": true, "has_invite_achievements": true }
  ],
  "achievements": [
    {
      "name": "app_recruiter",
      "entity_prefix": "app",
      "requirements": [{ "event_name": "invite_claimed", "count": 5 }]
    },
    {
      "name": "org_recruiter",
      "entity_prefix": "org",
      "requirements": [{ "event_name": "invite_claimed", "count": 3 }]
    }
  ]
}

Composing with Other Events

Invite achievements compose naturally with regular EventTracker events. A single achievement can require both invite events and table-driven events:

{
  "achievements": [
    {
      "name": "community_builder",
      "description": "Invite 3 people AND create 5 projects",
      "requirements": [
        { "event_name": "invite_claimed", "count": 3 },
        { "event_name": "project_created", "count": 5 }
      ],
      "rewards": [
        { "reward_type": "limit_credit", "target_name": "projects", "amount": 25 }
      ]
    }
  ]
}

Internal Trigger Reference

This reference describes the SECURITY DEFINER triggers that power the events + achievements system. These are automatically generated during provisioning — developers don't create them manually. Understanding them helps when debugging or extending the system.

Trigger Chain

EventTracker trigger (per table)
  → record_event()
    → app_events log entry
    → upsert_achievement()
      → event_aggregates updated
        → tg_check_achievements fires
          → level_achieved()
            → grant_achievement() → level_grants created
              → tg_achievement_reward fires
                → limit_credits or meter_credits granted
              → tg_invitee_achievement fires
                → record_event('invitee_achieved_*', inviter_id)

EventTracker Trigger

Created by: event_tracker generator in table_module Fires: AFTER INSERT, UPDATE, or DELETE (configurable via events parameter) On: The table the EventTracker node is attached to Security: SECURITY DEFINER (runs as database owner) Body: Calls record_event(step, actor_id) or record_event(step, actor_id, entity_id) with the configured event_name and resolved actor/entity fields.

Compound conditions are compiled into the trigger's WHEN clause at generation time via build_condition_ast().

tg_check_achievements

Created by: events_module provisioning Fires: AFTER INSERT or UPDATE on {prefix}_event_aggregates Security: SECURITY DEFINER Body: 1. Loops over all distinct levels whose requirements reference the updated event name 2. For each level, calls level_achieved() to check whether all requirements are met for the actor (and entity, if entity-scoped) 3. If achieved, inserts into level_grants with the actor, level name, and the aggregate's period_start (defaulting to a sentinel value for non-periodic events) 4. On conflict (duplicate grant for the same actor + level + period), does nothing — this prevents re-grants within the same period while allowing new grants in new periods

tg_achievement_reward

Created by: events_module provisioning (cross-module wiring with limits_module and/or billing_module) Fires: AFTER INSERT on {prefix}_level_grants Security: SECURITY DEFINER Condition: Only generated when at least one of limits_module or billing_module exists for the same entity scope. Body: 1. Loops over all achievement_rewards matching the granted level name 2. For each reward, branches on reward_type:

`limit_credit` branch (requires limits_module):

• Looks up the default limit by target_name
• Grants credits to the actor's limit_credits (amount, credit_type from the reward definition)

`meter_credit` branch (requires billing_module):

• Looks up the meter by target_name (slug)
• Grants credits to the entity's meter_credits with the configured amount and credit_type
• If expires_interval is set on the reward, computes an expiration timestamp (now() + expires_interval)
• Sets a reason tag like "achievement:level_name" for audit

The trigger is generated with the correct branches based on which modules are provisioned (limits-only, billing-only, or both).

Why SECURITY DEFINER: Users don't have direct write access to limit_credits, meter_credits, or related tables. The trigger runs as the database owner to bypass RLS.

tg_invitee_achievement

Created by: insert_entity_type_provision trigger (when has_invite_achievements = true) Fires: AFTER INSERT on {prefix}_level_grants Security: SECURITY DEFINER Condition: Only generated when both events_module and invites_module exist for the entity type. Body: 1. Looks up who invited the actor by querying claimed_invites for the actor's receiver_id 2. If an inviter (sender) is found, records an event named invitee_achieved_{level_name} attributed to the inviter (entity variant also passes the entity_id) 3. If no inviter exists (user wasn't invited), does nothing

Event naming: The event name is dynamically constructed as invitee_achieved_ + the level name from the newly inserted level_grants row. For example, if NEW.level_name = 'getting_started', the event recorded is invitee_achieved_getting_started.

record_event

Created by: events_module provisioning Type: Function (not a trigger) Signatures:

• User variant: record_event(step text, actor_id uuid)
• Entity variant: record_event(step text, actor_id uuid, entity_id uuid)

Body: 1. Writes to {prefix}_events (the partitioned event log) 2. Calls upsert_achievement(step, actor_id) to update aggregates

upsert_aggregate (period-aware)

Created by: events_module provisioning Type: Function (called by record_event()) Body: Upserts into event_aggregates. When the event type has a period_interval: 1. Fetches the event type's aggregation mode, whether it feeds achievements, and its period_interval 2. On first event: initializes period_start to now (if periodic) or leaves it null (if lifetime) 3. On subsequent events (existing aggregate row):

• If the period has elapsed (period_start + period_interval ≤ now): resets count to the incoming value and refreshes period_start
• If the period is still active: accumulates count normally
• If period_start was null but the event type now has a period_interval: initializes period_start

This is the same lazy reset pattern used by the billing module's period-based credits.

grant_achievement

Created by: events_module provisioning Type: Function (callable directly) Signatures:

• User variant: grant_achievement(level_name citext, actor_id uuid)
• Entity variant: grant_achievement(level_name citext, actor_id uuid, entity_id uuid)

Body: 1. Inserts a level_grants row for the given level and actor. If the grant already exists (duplicate key), does nothing. 2. Returns void — idempotent by design

This function can be called directly (outside of the trigger chain) for manual achievement grants (e.g., from admin tooling or migration scripts).

Tables Created by events_module