[T2.4b] Shared SQL helper v_weighted_spend (config-source decision required)

[realproject7]

Epic: #1229 · Spec: §6.2, §8 Phase 2 (T2.4b) · Type: CODE · Estimate: 0.3 day · Depends on: T0.2 + T2.1 + T2.2

Scope

Shared SQL primitive that encapsulates per-wallet base computation. Consumed by scripts/airdrop-finalize.ts (T2.6), /api/airdrop/projection (T2.5d), and /api/airdrop/leaderboard (T2.5f).

Output shape

Columns: address, buy_volume, qualified_refs, has_fc_bonus, multiplier, weighted_spend, community_total.

community_total is either a window value broadcast to every row OR a single-row companion (implementer's choice).

Eligibility filter BUILT IN

```sql
FROM pl_activations a
JOIN (buy aggregates) ...
WHERE a.activated_at IS NOT NULL
AND a.is_blacklisted = FALSE
```

Guarantees every consumer sees the same eligible-wallet set; no caller-side filter drift.

qualified_refs formula (RE1 round-23 — explicit)

qualified_refs(wallet W) =
  COUNT(*)
  FROM pl_referrals r
  WHERE r.referrer_address = W
    AND r.referred_address IS IN (helper cohort)
        -- i.e., the referred wallet:
        --   - exists in pl_activations
        --   - activated_at IS NOT NULL
        --   - is_blacklisted = FALSE
    AND (buy_volume of referred address) >= MIN_REFERRAL_THRESHOLD

A referee only counts if it (a) is in the eligible cohort AND (b) has crossed the spend threshold. A referee that activated but didn't spend, or spent but is blacklisted, does NOT count.

Implementation pattern: subquery/CTE joining pl_referrals against the same eligibility-filtered base CTE used for buy_volume. Same source-of-truth filter — no separate referee eligibility check possible.

Multiplier formula (for reference)

multiplier(W)      = 1 + min(qualified_refs(W) + has_fc_bonus(W), 10) * REFERRAL_MULTIPLIER_PER_REF
weighted_spend(W)  = buy_volume(W) * multiplier(W)
community_total    = SUM(weighted_spend) across all eligible W

Does NOT compute share_at_tier(T)

Callers apply share = released_pool × (weighted_spend / community_total) themselves using milestone-tier released_pool resolved at call time.

⚠️ CONFIG-SOURCE DECISION REQUIRED (pick ONE)

The helper needs CAMPAIGN_START, CAMPAIGN_END, and MIN_REFERRAL_THRESHOLD — values that live in TS AIRDROP_CONFIG and differ for TEST/PROD modes. A plain Postgres view cannot read NEXT_PUBLIC_AIRDROP_MODE.

• (A) Shared SQL fragment in lib/airdrop/sql.ts ✅ RECOMMENDED
  • TS function weightedSpendQuery(config: AirdropConfig): { sql, params } returns parameterized query
  • Each caller passes active AIRDROP_CONFIG
  • Test/prod correctness automatic; no DB-side coupling to runtime mode
• (B) Postgres view + pl_airdrop_config settings table
  • Single-row table holds campaign_start, campaign_end, min_referral_threshold
  • View JOINs against it; operator updates row at deploy
  • Adds 1 table + 1 migration; couples DB to runtime mode
• (C) Hardcoded views per environment ❌ REJECTED (drift risk)

Document chosen option (A or B) in PR description.

Acceptance

• Option (A) or (B) chosen with rationale in PR
• Returns correct shape for test wallet with: 100 PLOT spent, 2 qualified referrals, FC bonus → multiplier = 1.6, weighted_spend = 160
• Eligibility filter excludes blacklisted wallets
• Eligibility filter excludes non-activated wallets
• qualified_refs excludes non-activated referees (unit test: A refers B (activated) → counted; A refers C (never activated) → NOT counted)
• qualified_refs excludes blacklisted referees (A refers D (activated + blacklisted) → NOT counted)
• qualified_refs excludes under-threshold referees (A refers E (activated, spent 49 PLOT < 50 threshold) → NOT counted; F (activated, spent 50) → counted)
• Unit test compares output for TEST_CONFIG vs PROD_CONFIG → different campaign windows produce different buy_volume

Dependencies

T0.2 (schema), T2.1 (config), T2.2 (cleanup so buy points are raw PLOT)

[realproject7]

Completed by PR #1278.