Add payer reports table

[neekolas]

Add database schema and SQL queries to store payer reports and their attestations

• Creates new payer_reports table to store report metadata including merkle roots, sequence IDs, and submission status in 00011_store-payer-reports.up.sql
• Creates new payer_report_attestations table to store node signatures with foreign key relationships in 00011_store-payer-reports.up.sql
• Adds SQL queries InsertOrIgnorePayerReport and InsertPayerReportAttestation in payerReports.sql for handling report and attestation insertions with duplicate prevention

📍Where to Start

Start with the schema definitions in 00011_store-payer-reports.up.sql to understand the data structure, then review the SQL queries in payerReports.sql that interact with these tables.

---

Macroscope summarized 39ab894.

Summary by CodeRabbit

• New Features
  • Introduced structured support for storing, retrieving, and managing payer reports and attestations with conflict handling.
  • Added detailed payer report data structures and enhanced report generation with randomized Merkle roots for improved integrity.
  • Added comprehensive validation logic for payer reports ensuring consistency and correctness.
  • Provided flexible querying of payer reports with filtering by status and creation time.
  • Added detailed documentation describing payer report validation and misbehavior scenarios.
• Chores
  • Improved SQL query formatting and database schema with indexing for better performance.
  • Enhanced data validation and error handling for payer report storage operations.
  • Updated utility functions for safer slice-to-array conversions and nullable SQL types.
  • Upgraded indirect dependencies in tooling for improved stability and features.

[coderabbitai[bot]]

[!WARNING]

Rate limit exceeded

@neekolas has exceeded the limit for the number of commits or files that can be reviewed per hour. Please wait 8 minutes and 55 seconds before requesting another review.

⌛ How to resolve this issue?

After the wait time has elapsed, a review can be triggered using the @coderabbitai review command as a PR comment. Alternatively, push new commits to this PR.

We recommend that you space out your commits to avoid hitting the rate limit.

🚦 How do rate limits work?

CodeRabbit enforces hourly rate limits for each developer per organization.

Our paid plans have higher rate limits than the trial, open-source and free plans. In all cases, we re-allow further reviews after a brief timeout.

Please see our FAQ for further information.

📥 Commits

Reviewing files that changed from the base of the PR and between 933b54313ac71cf353a337e14d6a8e87705ea4ce and 4a2a97c39b62945164b6f4e4fa85347fc65418f6.

⛔ Files ignored due to path filters (4)

• dev/gen/protos is excluded by !**/gen/**
• dev/gen/sqlc is excluded by !**/gen/**
• pkg/proto/openapi/xmtpv4/payer_api/payer_api.swagger.json is excluded by !pkg/proto/**
• pkg/proto/xmtpv4/envelopes/payer_report.pb.go is excluded by !**/*.pb.go, !pkg/proto/**

📒 Files selected for processing (21)

• pkg/db/queries/models.go (1 hunks)
• pkg/db/queries/payerReports.sql.go (7 hunks)
• pkg/db/sqlc/payerReports.sql (1 hunks)
• pkg/migrations/00012_store-payer-reports.down.sql (1 hunks)
• pkg/migrations/00012_store-payer-reports.up.sql (1 hunks)
• pkg/payerreport/README.md (1 hunks)
• pkg/payerreport/id.go (0 hunks)
• pkg/payerreport/interface.go (2 hunks)
• pkg/payerreport/manager.go (7 hunks)
• pkg/payerreport/queryBuilders.go (1 hunks)
• pkg/payerreport/report.go (1 hunks)
• pkg/payerreport/report_test.go (1 hunks)
• pkg/payerreport/store.go (1 hunks)
• pkg/payerreport/store_test.go (1 hunks)
• pkg/payerreport/utils.go (1 hunks)
• pkg/payerreport/validation.go (1 hunks)
• pkg/testutils/random.go (1 hunks)
• pkg/utils/number.go (1 hunks)
• pkg/utils/slice.go (1 hunks)
• pkg/utils/sql.go (1 hunks)
• pkg/utils/time.go (1 hunks)

Walkthrough

This change introduces a comprehensive payer report storage and validation subsystem. It adds SQL migrations and queries for new payer_reports and payer_report_attestations tables, Go models and data access methods, and a new payerreport package implementing report data structures, storage, validation, and test coverage. The update also includes new utility functions for SQL and byte array handling, and documentation describing the validation process and misbehavior scenarios.

Changes

File(s)	Change Summary
pkg/migrations/00012_store-payer-reports.up.sql pkg/migrations/00012_store-payer-reports.down.sql	Adds SQL migrations to create and drop payer_reports and payer_report_attestations tables, with appropriate indexes and schema.
pkg/db/sqlc/payerReports.sql	Adds and reformats SQL queries for inserting, selecting, and updating payer reports and attestations.
pkg/db/queries/models.go	Adds Go struct models for PayerReport and PayerReportAttestation.
pkg/db/queries/payerReports.sql.go	Adds Go methods for new SQL queries: insert, fetch (single and multiple), and update payer reports and attestations.
pkg/payerreport/report.go	Introduces core data structures for payer reports, including status enums, report types, serialization, and ID computation.
pkg/payerreport/manager.go	Refactors report generation to return PayerReportWithInputs, updates Merkle root logic, and introduces helper types/functions.
pkg/payerreport/validation.go	Implements validation logic for payer report transitions and structure, with detailed error reporting.
pkg/payerreport/interface.go	Removes the old PayerReport struct, splits the manager interface, adds a new store interface, and updates method signatures.
pkg/payerreport/id.go	Renames ABI argument fields for consistency: nodesMerkleRoot → nodesHash, nodesLeafCount → nodesCount.
pkg/payerreport/store.go	Implements the Store type for report/attestation persistence, querying, and conversion, with validation and builder-style query filters.
pkg/payerreport/utils.go	Adds a utility function to extract the minute from a sequence ID via envelope lookup and parsing.
pkg/payerreport/README.md	Adds documentation describing the payer report validation process and misbehavior scenarios.
pkg/payerreport/report_test.go	Updates tests to use random 32-byte arrays for cryptographic fields.
pkg/payerreport/store_test.go	Adds comprehensive tests for storing, retrieving, and querying payer reports, including error and idempotency checks.
pkg/utils/slice.go	Updates SliceToArray32 to include length validation and error handling; adds slice conversion helpers.
pkg/utils/sql.go	Adds utility functions for nullable SQL types and slice conversions.
pkg/utils/time.go	Changes import style for the time package (no functional change).
pkg/utils/number.go	Adds safe integer conversion functions with overflow checks.
pkg/testutils/random.go	Adds a helper function to generate random 64-bit integers.

Sequence Diagram(s)

sequenceDiagram
    participant Client
    participant Store
    participant DB

    Client->>Store: StoreReport(report)
    Store->>DB: InsertOrIgnorePayerReport
    DB-->>Store: Success/Error
    Store-->>Client: ReportID/Error

    Client->>Store: FetchReport(reportID)
    Store->>DB: FetchPayerReport
    DB-->>Store: Report Row
    Store-->>Client: PayerReportWithStatus

    Client->>Store: StoreAttestation(attestation)
    Store->>DB: InsertOrIgnorePayerReportAttestation
    DB-->>Store: Success/Error
    Store-->>Client: Success/Error

    Client->>Store: FetchReports(query)
    Store->>DB: FetchPayerReports
    DB-->>Store: Report Rows
    Store-->>Client: []PayerReportWithStatus

sequenceDiagram
    participant Validator
    participant Store
    participant Logger

    Validator->>Store: FetchReport(prevID)
    Store-->>Validator: prevReport

    Validator->>Store: FetchReport(newID)
    Store-->>Validator: newReport

    Validator->>Validator: IsValidReport(prevReport, newReport)
    Validator->>Logger: Log validation result
    Validator-->>Client: Valid/Invalid, Error

Possibly related PRs

• xmtp/xmtpd#646: Introduces foundational SQL queries and Go methods for building payer reports and unsettled usage tracking, complementing the payer report schema and functionality expanded in this PR.

Suggested reviewers

• mkysel
• fbac

✨ Finishing Touches

• [ ] 📝 Generate Docstrings

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

🪧 Tips

Chat

There are 3 ways to chat with CodeRabbit:

• Review comments: Directly reply to a review comment made by CodeRabbit. Example:
  • I pushed a fix in commit <commit_id>, please review it.
  • Explain this complex logic.
  • Open a follow-up GitHub issue for this discussion.
• Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
  • @coderabbitai explain this code block.
  • @coderabbitai modularize this function.
• PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
  • @coderabbitai gather interesting stats about this repository and render them as a table. Additionally, render a pie chart showing the language distribution in the codebase.
  • @coderabbitai read src/utils.ts and explain its main purpose.
  • @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.
  • @coderabbitai help me debug CodeRabbit configuration file.

Support

Need help? Create a ticket on our support page for assistance with any issues or questions.

Note: Be mindful of the bot's finite context window. It's strongly recommended to break down tasks such as reading entire modules into smaller chunks. For a focused discussion, use review comments to chat about specific files and their changes, instead of using the PR comments.

CodeRabbit Commands (Invoked using PR comments)

• @coderabbitai pause to pause the reviews on a PR.
• @coderabbitai resume to resume the paused reviews.
• @coderabbitai review to trigger an incremental review. This is useful when automatic reviews are disabled for the repository.
• @coderabbitai full review to do a full review from scratch and review all the files again.
• @coderabbitai summary to regenerate the summary of the PR.
• @coderabbitai generate docstrings to generate docstrings for this PR.
• @coderabbitai generate sequence diagram to generate a sequence diagram of the changes in this PR.
• @coderabbitai resolve resolve all the CodeRabbit review comments.
• @coderabbitai configuration to show the current CodeRabbit configuration for the repository.
• @coderabbitai help to get help.

Other keywords and placeholders

• Add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.
• Add @coderabbitai summary to generate the high-level summary at a specific location in the PR description.
• Add @coderabbitai anywhere in the PR title to generate the title automatically.

CodeRabbit Configuration File (.coderabbit.yaml)

• You can programmatically configure CodeRabbit by adding a .coderabbit.yaml file to the root of your repository.
• Please see the configuration documentation for more information.
• If your editor has YAML language server enabled, you can add the path at the top of this file to enable auto-completion and validation: # yaml-language-server: $schema=https://coderabbit.ai/integrations/schema.v2.json

Documentation and Community

• Visit our Documentation for detailed information on how to use CodeRabbit.
• Join our Discord Community to get help, request features, and share feedback.
• Follow us on X/Twitter for updates and announcements.

[neekolas]

[!WARNING] This pull request is not mergeable via GitHub because a downstack PR is open. Once all requirements are satisfied, merge this PR as a stack on Graphite. Learn more

• #760 : 2 dependent PRs (#761 , #764 )
• #703 👈 (View in Graphite)
• #702
• #701
• #700
• main

---

How to use the Graphite Merge Queue

Add either label to this PR to merge it via the merge queue:

• Queue - adds this PR to the back of the merge queue
• Hotfix - for urgent hot fixes, skip the queue and merge this PR next

You must have a Graphite account in order to use the merge queue. Sign up using this link.

An organization admin has enabled the Graphite Merge Queue in this repository.

Please do not merge from GitHub as this will restart CI on PRs being processed by the merge queue.

This stack of pull requests is managed by Graphite. Learn more about stacking.

[macroscopeapp[bot]]

Add database tables and functionality to store and manage payer reports and their attestations

• Adds new database tables payer_reports and payer_report_attestations through migration scripts in 00012_store-payer-reports.up.sql
• Implements core data structures and interfaces in report.go and interface.go for managing payer reports
• Creates database store implementation in store.go with methods for storing, retrieving, and updating reports
• Adds SQL queries and generated code in payerReports.sql for database operations
• Updates protobuf definitions to include new fields for timestamps and node signatures

📍Where to Start

Start with the database schema in 00012_store-payer-reports.up.sql to understand the data model, then review the core interfaces in interface.go.

---

Macroscope summarized c666f67.

[graphite-app[bot]]

Merge activity

• Jun 17, 12:16 PM UTC: neekolas added this pull request to the Graphite merge queue.
• Jun 17, 12:17 PM UTC: CI is running for this pull request on a draft pull request (#894) due to your merge queue CI optimization settings.
• Jun 17, 12:23 PM UTC: Merged by the Graphite merge queue via draft PR: #894.