Admin Analytics Dashboard (Indexer + Endpoint)

[codebestia]

Background

This is two phases, as scoped by the request: (1) indexer handlers that keep analytics data current in Postgres, and (2) admin-facing endpoints that read it. MerchantAnalytics and TokenAnalytics already exist in the schema (per-token live counters) but nothing writes to them yet applyInvoicePayment updates Invoice/Transaction only. That's a gap this issue closes, not a new problem it introduces.

Events split into three buckets for dashboard purposes:

Bucket	Events	Feeds
Volume-moving	InvoicePaidEvent, SubscriptionChargedEvent, TicketPurchasedEvent, TicketResoldEvent (royalty only — no platform fee on resale)	MerchantAnalytics, TokenAnalytics, PlatformDailyStats
Growth-counting	MerchantRegisteredEvent, InvoiceCreatedEvent, SubscriptionPlanCreatedEvent, SubscribedEvent, EventCreatedEvent	PlatformDailyStats "new X today" counters (and are also directly queryable via count() at request time for point-in-time totals — no new schema needed for that)
Refund adjustments	InvoiceRefundedEvent, InvoicePartiallyRefundedEvent	Tracked as separate refund totals, not netted against volume — verify against the contract before assuming refunds reduce total_volume on-chain; don't guess

Proposed Steps

Phase 1 — Indexer

1. Retrofit applyInvoicePayment (invoice.services.ts) to also, inside the same $transaction: upsert MerchantAnalytics (by [merchantId, token], incrementing totalVolume/totalFees/transactionCount) and TokenAnalytics (by token, same increments, plus uniqueMerchants — only bump this the first time a given merchant appears for that token), and increment today's PlatformDailyStats row (totalVolume, totalFees, transactionCount).
2. New handler for SubscriptionChargedEvent → same shape of writes as above, keyed off subscription_id/plan_id (resolve merchant via the Subscription's merchantId, not the event ). This handler is also exactly what "subscription payments" endpoint depends on — implementing it here satisfies both.
3. New handler for TicketPurchasedEvent and TicketResoldEvent → same shape of writes (out of scope if event ticketing isn't implemented yet elsewhere in the backend; if Event/Ticket models don't exist yet, log-and-skip with a clear comment rather than blocking this issue on building ticketing from scratch).
4. New handlers for the growth-counting events → increment the relevant PlatformDailyStats "new X" counter for today's row.
5. PlatformDailyStats rows are upserted by UTC calendar day (date truncated to midnight UTC) — upsert on each event, not a scheduled batch job, consistent with how MerchantAnalytics/TokenAnalytics already work.

Phase 2 — Endpoints (mounted at src/routes/admin/analytics.routes.ts, protected by authenticateAdmin)

6. GET /admin/analytics/summary — protocol-wide current totals: sum of TokenAnalytics across tokens, plus live count()s (Merchant, Invoice grouped by status, Subscription grouped by status).
7. GET /admin/analytics/timeseries?from=&to= — reads PlatformDailyStats rows in the given date range, ordered by date. No granularity param in this issue — daily only; weekly/monthly rollups are a follow-up if needed.
8. GET /admin/analytics/tokens — top tokens by volume, reading straight from TokenAnalytics ordered by totalVolume desc (mirrors the contract's get_top_tokens_by_volume, served from Postgres instead of a live contract call — much cheaper for a dashboard that gets refreshed often).

Schema Changes

PlatformDailyStats (new model)

id                String   (uuid, PK)
date              DateTime (unique, truncated to UTC midnight)
totalVolume       BigInt   (default 0)
totalFees         BigInt   (default 0)
transactionCount  BigInt   (default 0)
newInvoices       Int      (default 0)
newMerchants      Int      (default 0)
newSubscriptions  Int      (default 0)
newTickets        Int      (default 0)
updatedAt         DateTime (@updatedAt)

Per-token daily breakdown (e.g. "USDC volume trend over time" as opposed to protocol-wide) is explicitly out of scope here — TokenAnalytics already gives current per-token totals; a PlatformDailyTokenStats table is a reasonable future enhancement, not part of this issue.

Acceptance Criteria

• PlatformDailyStats model added; prisma migrate dev runs cleanly
• InvoicePaidEvent handling now also updates MerchantAnalytics, TokenAnalytics, and today's PlatformDailyStats row, in the same transaction as the existing Invoice/Transaction writes
• SubscriptionChargedEvent has a working handler that produces the same category of writes
• GET /admin/analytics/summary returns protocol totals matching what's actually in TokenAnalytics/MerchantAnalytics/live counts
• GET /admin/analytics/timeseries returns PlatformDailyStats rows for the requested range, empty array (not an error) for a range with no activity
• GET /admin/analytics/tokens is ordered by volume descending
• All three endpoints require authenticateAdmin (no requireSuperAdmin needed — read-only)
• Status/governance events are explicitly not handled by this issue's indexer work

[Peculiarr7]

Hi! I’m a full-stack developer with a strong blockchain background, and I’m confident I can resolve this issue quickly and effectively. I’ll deliver a clean, reliable solution with immediate attention to detail, ensuring it’s completed as soon as possible. Please assign it to me—I’m ready to get started right away.

[G-ELM]

I’d like to take this issue on. I’ve gone through the scope and the main implementation path looks well-defined: I can handle the Prisma migration/model, retrofit the existing invoice payment flow, add the subscription/ticket/growth event handlers, and implement the three admin analytics endpoints behind authenticateAdmin.

I’ll keep the analytics writes transactional with the existing invoice/transaction updates, use UTC-day upserts for PlatformDailyStats, and make uniqueMerchants increment only when a merchant is first seen for a token. For refunds, I’ll keep them as separate adjustments rather than assuming they should reduce volume, and I’ll verify the existing contract semantics before implementing that behavior.

I’ll also keep the boundaries of the issue intact: no unnecessary schema for per-token daily stats, no weekly/monthly aggregation, and no status/governance event handling. If ticketing models/events aren't implemented yet, I’ll follow the proposed log-and-skip approach rather than expanding the scope.

I can also add/adjust tests around the transactional analytics updates and endpoint behavior, including empty timeseries ranges and volume-descending token results.

Happy to take of this if you’re okay assigning it to me.

[grantfox-oss]

🦊 GrantFox — @G-ELM has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #41)
2. Your PR will be reviewed by the ShadeProtocol maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Third Campaign campaign!

@G-ELM's PR #51 was approved and merged by @codebestia.

🏆 @G-ELM: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (599 total points). Track your full progress on GrantFox.

👏 Great work, @G-ELM! Keep contributing to ShadeProtocol.