#812 — Optimize Merchant List Endpoints

Repo: Twill-AI/facade State: closed | Status: done Assignee: meliascosta, sparsh-twillpayments

Created: 2025-12-08 · Updated: 2025-12-29

Description

Improve merchant list endpoints performance and functionality by adding database indexes, extending sortable fields, creating lightweight response DTOs, adding status count endpoints, implementing filtering support, and adding new commission/volume fields for both partner and master portals.

Changes Required

1. Database Schema (app/db/master_tables.py)

• Add indexes to DbMerchant table:

  total_payment_volume = Decimal(digits=(20, 2), null=True, default=None, index=True)
  risk_category = Varchar(length=50, null=True, default=None, index=True)
  business_mcc_sic = Varchar(length=10, null=True, default=None, index=True)

2. Add New Fields to DbMerchant (app/db/master_tables.py)

• Add commission tracking fields:

  total_commission = Decimal(digits=(20, 2), null=True, default=None, index=True)
  mtd_commission = Decimal(digits=(20, 2), null=True, default=None, index=True
  mtd_payment_volume = Decimal(digits=(20, 2), null=True, default=None,  index=True)

• Add last activity tracking fields:

  last_activity_type = Varchar(length=50, null=True, default=None)  # partner_message, merchant_activity, twill_message
  last_activity_timestamp = Timestamptz(null=True, default=None, index=True)
  last_activity_initiator_id = UUID(null=True, default=None)
  last_activity_initiator_name = Varchar(length=255, null=True, default=None)
  last_activity_initiator_role = Varchar(length=50, null=True, default=None)  # p

Notes

Add implementation notes, blockers, and context here

Related

Add wikilinks to related people, meetings, or other tickets