Metabase Queries for Returns & Exchanges

This directory contains SQL queries for Metabase dashboards related to return and exchange analytics.

Directory Structure

metabase-queries/
├── README.md                           # This file
├── return-metrics.sql                  # Return metrics queries (Task 8.1)
├── exchange-metrics.sql                # Exchange metrics queries (Task 8.2)
├── fraud-detection.sql                 # Fraud detection queries (Task 8.6)
├── payment-reconciliation.sql          # Payment gateway queries (Task 8.7)
└── dashboard-definitions.md            # Dashboard layouts (Task 8.3)

Setup Instructions

1. Metabase Connection

Local Development:

Host: localhost
Port: 3002
Metabase URL: http://localhost:3002

Database Connection:
  Host: postgres_dev (Docker service name)
  Port: 5432
  Database: flowpos_dev
  Username: metabase_readonly (recommended)
  Password: [see setup below]

Staging:

Metabase URL: [Cloud Run URL for staging]
Database: flowpos_staging
Cloud SQL Instance: barto-dev:us-central1:metabase-db-staging

Production:

Metabase URL: [Cloud Run URL for production]
Database: flowpos_production
Cloud SQL Instance: barto-prod:us-central1:metabase-db-production

2. Create Read-Only User (Recommended)

-- Create read-only user for Metabase
CREATE USER metabase_readonly WITH PASSWORD 'secure_password_here';

-- Grant connect permission
GRANT CONNECT ON DATABASE flowpos_production TO metabase_readonly;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO metabase_readonly;

-- Grant select on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO metabase_readonly;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO metabase_readonly;

3. Apply Analytics Indexes

IMPORTANT: The required indexes are automatically created by the migration:

# Apply the analytics indexes migration
pnpm run migration:local:push  # For local dev
# or
pnpm run migration:push        # For staging/production

The migration 2026-02-28t23:54:59.921z-add-analytics-indexes.mjs creates the following indexes:

• idx_sale_business_created_at - Sale queries by business and date
• idx_sale_business_location_created - Location-specific analytics
• idx_sale_customer_created - Customer return history
• idx_sale_salesperson_created - Employee performance metrics
• idx_sale_detail_items_product_id - Product-level analysis (GIN index)
• idx_return_reason_business_active - Return reason lookups
• idx_return_policy_business_active - Policy validation
• idx_payment_detail_methods - Refund analysis (GIN index)
• idx_sale_business_status_created - Status-based queries
• idx_sale_business_type_created - Transaction type filtering
• idx_sale_approved_by_created - Manager approval tracking
• idx_sale_serial_number - Fraud detection by serial number

Performance Impact:

• Queries will be 10-100x faster with these indexes
• Index creation takes ~1-5 minutes depending on data volume
• Indexes add ~10-20% to database size

4. Import Queries

1. Open Metabase
2. Navigate to "New" → "Question" → "Native query"
3. Copy SQL from query files
4. Add parameters (see Parameter Guide below)
5. Save as "Question" with descriptive name
6. Tag appropriately

5. Create Collections

Organize queries in Metabase collections:

• Returns & Exchanges - Main collection
  • Return Metrics
  • Exchange Metrics
  • Operational Reports
• Fraud Detection - Security collection
  • High-Risk Customers
  • Employee Outliers
  • Serial Number Fraud
• Financial Reconciliation - Finance collection
  • Refund Status
  • Gateway Reconciliation

Parameter Guide

All queries use Metabase parameters for filtering:

Standard Parameters

{{business_id}}     - UUID, required, filters by business
{{start_date}}      - Date, optional, default: 30 days ago
{{end_date}}        - Date, optional, default: today
{{location_id}}     - UUID, optional, filters by location
{{status}}          - Text, optional, filters by status

Parameter Configuration in Metabase

1. Click "Variables" in query editor
2. Add parameter:
   • business_id: Field Filter → Sale → Business ID
   • start_date: Field Filter → Sale → Created At (From)
   • end_date: Field Filter → Sale → Created At (To)
   • location_id: Field Filter → Sale → Location ID

Query Files

return-metrics.sql

Contains queries for return analytics:

• Returns by day/week/month
• Return rate calculation
• Top return reasons
• Return value by product/category
• Damaged vs resellable breakdown

exchange-metrics.sql

Contains queries for exchange analytics:

• Exchanges by day/week/month
• Net exchange margin impact
• Most exchanged products
• Exchange settlement analysis

fraud-detection.sql

Contains queries for fraud detection:

• High-risk customers (>30% return rate)
• Employee outliers (>2σ from mean)
• Serial number fraud attempts
• Cross-location return patterns

payment-reconciliation.sql

Contains queries for payment gateway reconciliation:

• Refunds by status
• Failed refunds requiring attention
• Gateway transaction lookup
• Refund retry history

Performance Notes

• All queries are optimized for <2s execution
• Indexes required (see below)
• Use date range filters to limit data
• Consider materialized views for heavy queries

Required Indexes

-- For return queries
CREATE INDEX IF NOT EXISTS idx_sale_transaction_type_status 
ON sale(business_id, transaction_type, status, created_at);

-- For fraud detection
CREATE INDEX IF NOT EXISTS idx_sale_customer_returns 
ON sale(business_id, customer_id, transaction_type) 
WHERE transaction_type = 'return';

-- For employee analysis
CREATE INDEX IF NOT EXISTS idx_sale_employee_returns 
ON sale(business_id, created_by, transaction_type) 
WHERE transaction_type = 'return';

-- For payment reconciliation
CREATE INDEX IF NOT EXISTS idx_sale_payment_status 
ON sale(business_id, status, created_at) 
WHERE transaction_type IN ('return', 'exchange');

Dashboard Templates

See dashboard-definitions.md for complete dashboard layouts and configurations.

Troubleshooting

Query Timeout

• Reduce date range
• Check indexes are created
• Consider materialized views

Incorrect Results

• Verify business_id parameter is set
• Check date range filters
• Ensure status filters are correct

Performance Issues

• Run ANALYZE on tables
• Check query execution plan
• Consider query optimization

Support

For questions or issues:

• Check query comments for details
• Review execution plans
• Contact: [support-email]

---

Feature: 014-exchange-return
Phase: 8 (Reporting & Analytics)
Created: 2026-02-28
Status: In Progress