Skip to content

Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.

5.9 KB
Raw Blame
-- Pump dashboard alerts → multi-rule, server-side model (Task 04).
--
-- Supersedes the single-row-per-user `user_alert_configs` model. Each user can
-- now create many independent rules, each with its own kind, target, threshold,
-- delivery channels and cooldown. Rules are evaluated by the pumpfun-monitor
-- cron against real persisted data sources, so they fire across devices even
-- with no dashboard tab open.
--
-- NOTE: this file is applied by scripts/apply-migrations.mjs as a single
-- multi-statement body via the websocket Pool (pool.query). Statements are kept
-- simple and split-safe (no DO/$$ blocks) so the file also survives any naive
-- split-on-';' fallback.

create table if not exists pump_alert_rules (
    id               uuid        primary key default gen_random_uuid(),
    user_id          uuid        not null references users(id) on delete cascade,
    -- kind drives which event stream the evaluator matches against.
    --   graduation   → pump.fun → AMM migration (global, or scoped to a mint/agent)
    --   price_above  → token USD market cap rises above `threshold`
    --   price_below  → token USD market cap falls below `threshold`
    --   whale_buy    → a buy of >= `threshold` SOL on the target mint
    --   new_mint     → a tracked agent launches a new coin (requires target_agent)
    kind             text        not null check (kind in ('graduation','price_above','price_below','whale_buy','new_mint')),
    -- Targeting. At most one of these is set; both null = a global rule
    -- (only valid for `graduation`).
    target_mint      text,
    target_agent     uuid        references agent_identities(id) on delete cascade,
    -- threshold semantics depend on kind:
    --   price_above / price_below → USD market cap
    --   whale_buy                 → minimum buy size in SOL
    --   graduation / new_mint     → unused (null)
    threshold        double precision,
    -- Delivery channels. Each is independent; at least one must be set.
    deliver_in_app   boolean     not null default true,
    webhook_url      text,
    -- Per-rule HMAC secret, shared with the receiver so it can verify the
    -- `webhook-signature` header (Standard Webhooks format). Generated by the
    -- API when a webhook_url is set.
    webhook_secret   text,
    telegram_chat    text,
    cooldown_seconds integer     not null default 300 check (cooldown_seconds between 5 and 86400),
    enabled          boolean     not null default true,
    -- Optional user-facing label; defaults are derived from kind/target in the UI.
    label            text,
    created_at       timestamptz not null default now(),
    updated_at       timestamptz not null default now()
);

create index if not exists pump_alert_rules_user on pump_alert_rules(user_id, created_at desc);

create index if not exists pump_alert_rules_kind_enabled on pump_alert_rules(kind) where enabled;

create index if not exists pump_alert_rules_agent on pump_alert_rules(target_agent) where target_agent is not null;

create index if not exists pump_alert_rules_mint on pump_alert_rules(target_mint) where target_mint is not null;

-- ── Cooldown + dedupe tracker ───────────────────────────────────────────────
-- One row per rule. `last_fired_at` enforces cooldown; `last_event_id` dedupes
-- event-driven rules (graduation/new_mint/whale_buy) so the same on-chain event
-- is never delivered twice; `last_state` arms price rules so they fire on a
-- threshold *crossing* rather than on every tick the condition holds.
create table if not exists pump_alert_rule_fires (
    rule_id        uuid        primary key references pump_alert_rules(id) on delete cascade,
    last_fired_at  timestamptz,
    last_event_id  text,
    last_state     jsonb       not null default '{}'::jsonb,
    updated_at     timestamptz not null default now()
);

-- ── Per-rule delivery log ───────────────────────────────────────────────────
-- Records the outcome of each channel for each delivery so the dashboard can
-- surface "webhook failed" / "telegram failed" states instead of silently
-- dropping them. Bounded by a periodic prune in the cron.
create table if not exists pump_alert_deliveries (
    id          bigserial   primary key,
    rule_id     uuid        not null references pump_alert_rules(id) on delete cascade,
    user_id     uuid        not null references users(id) on delete cascade,
    channel     text        not null check (channel in ('in_app','webhook','telegram')),
    ok          boolean     not null,
    detail      text,
    event_id    text,
    created_at  timestamptz not null default now()
);

create index if not exists pump_alert_deliveries_rule on pump_alert_deliveries(rule_id, created_at desc);

create index if not exists pump_alert_deliveries_user_fail on pump_alert_deliveries(user_id, created_at desc) where ok = false;

-- ── Backfill the legacy single-config graduation rule ───────────────────────
-- The old `user_alert_configs` only ever fired graduation alerts server-side
-- (whale/fees/launch were tab-local). Preserve that one real, cross-session
-- behaviour by materialising an equivalent global graduation rule per user.
-- Idempotent: skipped when the user already has a global graduation rule.
insert into pump_alert_rules (user_id, kind, deliver_in_app, webhook_url, cooldown_seconds, enabled, label)
select c.user_id, 'graduation', true, c.webhook_url, greatest(5, least(86400, c.cooldown_seconds)), true, 'Graduations (migrated)'
from user_alert_configs c
where c.graduation = true
  and not exists (
    select 1 from pump_alert_rules r
    where r.user_id = c.user_id
      and r.kind = 'graduation'
      and r.target_mint is null
      and r.target_agent is null
  );