#108 — Uses on Serialized JSON: Wrong Matches and Slow Queries

[Topmatrixmor2014]

Labels: Official Campaign | FWC26 GrantFox OSS Maybe Rewarded backend indexer performance bug

This is a backend issue for the GrantFox FWC26 campaign. Index event participants as columns and query them directly instead of scanning serialized JSON text.

Requirements and Context

backend/src/services/eventIndexer.js — queryEventsByType (and queryEventsByPublicKey, getEventStats):

let where = `payload::text ILIKE $1 AND event_type = $2`;
const params = [`%${publicKey}%`, eventType];

• payload::text ILIKE '%GABC…%' is a full-text substring scan over the entire contract_events table. The table already stores from_addr and to_addr columns, but they are only populated on insert and never used for queries.
• ILIKE '%<publicKey>%' can match unrelated text: the public key GAAAA… (55 chars) appears inside other fields (e.g. a memo containing the string, or a nested payload array), producing wrong results — a user can see events they were not part of, and events are missed if the key appears in a field the query wasn't meant to match.
• getEventStats has the same payload::text ILIKE scan, grouping on event_type — the most common dashboard query, and it's a full table scan on every call.

Objectives

1. Rewrite queries to use from_addr = $1 OR to_addr = $2 (exact match) with an index on those columns (add a knex migration if missing).
2. Keep payload matching only as an explicit opt-in (e.g. a search query param), and bound it.
3. Add tests: exact-participant filtering returns only the user's events; a public key string appearing in a memo does not match.

Suggested Execution

1. Fork and branch: git checkout -b fix/indexer-column-queries.
2. Patch eventIndexer.js query helpers; add a migration for the index.
3. Add tests; run npm run lint && npm test in backend/.

Acceptance Criteria

• Participant queries use indexed exact-match columns, not ILIKE on JSON.
• A memo containing a public key no longer produces false matches.
• ≥4 tests; npm run lint clean.

Guidelines

• Keep the in-memory fallback store in sync (it can keep the substring filter, documented as best-effort).

Timeframe: 48 hours

[github-actions]

🤖 Greptile AI Context

Greptile has indexed this repository and can provide AI-assisted code review
and context for this issue.

How to use Greptile on this issue:

• Comment @greptileai to ask about relevant code, files, or implementation approaches
• Reference specific files or error messages for targeted analysis
• Use @greptileai explain <file> to get an explanation of any file

Greptile is configured with project-specific rules in .greptile/config.json.

[sasasamaes]

I’d like to take ownership of this issue and deliver a clean, production-ready solution.

I’m a Full Stack Engineer with 6+ years of experience building and maintaining scalable applications using Node.js, TypeScript, React, and modern backend architectures. I’ve worked extensively with REST APIs, database design (PostgreSQL, MongoDB), and system integrations.

I’m also a Sensei and active community member at Dev.f (https://devf.la/), where I mentor developers and contribute to their technical growth.

My approach is to:

• Quickly understand the existing codebase and architecture
• Implement a clean and consistent solution following best practices
• Ensure proper validation, error handling, and maintainability
• Deliver well-structured, readable code with clear communication

I also have experience in Web3 (Solidity, Rust, Cairo), which strengthens my approach to system reliability and data integrity.

I prioritize fast execution, low back-and-forth, and high-quality delivery.

Ready to start immediately.

[grantfox-oss]

🦊 GrantFox — @sasasamaes 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 #889)
2. Your PR will be reviewed by the FinChippay maintainers

Good luck! Track your progress on GrantFox.