Database Statistics

The Database Statistics feature provides system administrators with a high-level overview of database record counts and import activity. This data helps monitor system health, track growth, and verify data import operations.

Accessing Database Statistics

Navigate to Admin > Database Tools from the admin navigation. The statistics are displayed at the top of the page in summary cards.

Overview

The Database Statistics displays four key metrics:

Understanding Each Metric

Total Customers

What it counts: All rows in the customers table

Includes:

• Active customers
• Blocked customers
• Customers with identity verification flags
• All statuses and states

Does NOT include:

• Hard-deleted records (if any)
• Soft-deleted records ARE included in count

Format: Number with thousand separators (e.g., "12,450")

Total Rides

What it counts: All rows in the rides table

Includes:

• Active rides
• Completed rides
• Cancelled rides
• Paused rides
• All ride statuses

Does NOT include:

• Hard-deleted records (if any)

Format: Number with thousand separators (e.g., "156,789")

Total Vehicles

What it counts: All rows in the vehicles table

Includes:

• Available vehicles
• In-use vehicles
• Maintenance vehicles
• Offline vehicles
• All statuses

Format: Number with thousand separators (e.g., "342")

Last Import

What it shows: Timestamp of the most recent data import operation

Import types tracked:

• customers_csv_import - Customer CSV imports
• rides_csv_import - Rides CSV imports
• csv_import - General CSV imports
• manual_sync - Stripe API sync operations

Format: Localized date and time string (e.g., "12/25/2025, 2:30:45 PM")

If no imports: Displays "Never"

How Statistics Are Calculated

Real-Time Queries

Each statistic is calculated in real-time via database queries:

1. Customer Count: SELECT COUNT(*) FROM customers
2. Rides Count: SELECT COUNT(*) FROM rides
3. Vehicles Count: SELECT COUNT(*) FROM vehicles
4. Last Import: SELECT processed_at FROM stripe_webhook_logs WHERE event_type IN (...) ORDER BY processed_at DESC LIMIT 1

Query Performance

• Uses PostgreSQL's optimized COUNT(*) operation
• head: true option returns only metadata (no row data)
• Indexed columns support fast lookups
• Typical response time: 200-500ms

The Database Tools Page

Page Layout

The Database Tools page is divided into sections:

1. Statistics Cards (top) - The four metrics described above
2. Import Tools Grid - Buttons for customer, rides, and Stripe imports
3. Help Section - Instructions and file format requirements

Statistics Card Display

Each card shows:

• Icon: Visual indicator with colored background
• Value: Large numeric or date value
• Label: Description of the metric

Refresh Behavior

Statistics:

• Load on page visit
• Manual refresh required for updates
• No auto-refresh (to reduce server load)

Data Interpretation

Monitoring Growth

Track metrics over time to monitor:

• Customer acquisition rates
• Ride volume trends
• Fleet expansion

Verifying Imports

After running an import:

Identifying Issues

Access Controls

Current State

The Database Statistics API (/api/admin/database/stats) is accessible without explicit role checking.

Recommended Access

Integration with Import Tools

Pre-Import Verification

Before running any import:

1. Note current counts from statistics cards
2. Calculate expected post-import counts
3. Proceed with import

Post-Import Verification

After import completes:

1. Refresh the Database Tools page
2. Verify counts increased by expected amount
3. Check "Last Import" timestamp updated
4. Review import job details for errors

Technical Details

API Endpoint

URL: GET /api/admin/database/stats

Response Format:

{
  "totalCustomers": 12450,
  "totalRides": 156789,
  "totalVehicles": 342,
  "lastImport": "2025-12-25T14:23:15.000Z"
}

Caching

• No caching implemented: Fresh queries on each request
• Cache-Control: no-cache, no-store, must-revalidate headers set
• force-dynamic mode prevents Next.js static caching

Error Handling

If database queries fail:

• Returns HTTP 500 status
• Error message included in response
• Statistics cards show "0" or "Never" as fallbacks

Database Tables

customers Table

Primary table for customer records with 40+ fields including:

• Personal information
• Contact details
• Identity verification status
• Wallet balance
• Ride history statistics

rides Table

Primary table for ride records with 50+ fields including:

• Ride timestamps
• Distance and duration
• Pricing information
• Customer and vehicle references
• Zone information

vehicles Table

Primary table for vehicle records with 45+ fields including:

• Vehicle identification
• IoT device linking
• Battery status
• Location data
• Status and availability

stripe_webhook_logs Table

Audit log for Stripe events and imports:

• event_id: Unique event identifier
• event_type: Type of event (import type for stats)
• processed_at: Timestamp of processing
• status: Processing status

Best Practices

Regular Monitoring

1. Daily check: Glance at counts during daily operations
2. Pre/post-import: Always verify counts around import operations
3. Anomaly investigation: Investigate unexpected count changes

Documentation

Keep records of:

• Expected counts after each import
• Timestamp of major imports
• Any count discrepancies and resolutions

Import Hygiene

1. Test imports first: Use small test files before large imports
2. Record baseline: Note counts before each import
3. Verify success: Always check post-import counts

Troubleshooting

Statistics Not Loading

Possible Causes:

• Database connection issues
• API endpoint unreachable
• Network timeout

Solutions:

• Refresh the page
• Check browser console for errors
• Verify database connectivity

Counts Don't Match Expected

Possible Causes:

• Import had errors (partial import)
• Duplicate records skipped
• Filters applied during import

Solutions:

• Check import job error details
• Review import logs
• Verify source data integrity

Last Import Shows "Never"

Possible Cause: No import events logged in stripe_webhook_logs

Stale Data After Import

Cause: Page needs refresh after import completes

Solution: Manually refresh the page after import operations complete

Limitations

Current Limitations

1. No historical data: Only current counts, no trends
2. No breakdown by status: Total counts only
3. No subaccount filtering: Shows all records across all subaccounts
4. No export functionality: Cannot export statistics

Not Included

• Soft-delete filtering (deleted_at records are counted)
• Status-based counts (e.g., active vs. completed rides)
• Time-series data
• Per-subaccount breakdowns

What's Next?

Now that you understand Database Statistics, explore these related topics:

• Analytics Dashboard - View operational metrics and charts
• Identity Analytics - Customer verification metrics
• Stripe Integration - Payment and import configuration