Firestore indexes

Firestore Indexes for Execution Logs

This document describes the Firestore indexes needed for the execution_logs collection.

Required Indexes

1. Query by User and Timestamp

Collection: execution_logs
Fields:

• userId (Ascending)
• executedAt (Descending)

Use Case: Query user’s recent order executions

Create via Console:

Collection: execution_logs
Fields:
  - userId: Ascending
  - executedAt: Descending
Query Scope: Collection

Create via Firebase CLI:

firebase firestore:indexes:create \
  --collection-group=execution_logs \
  --field-name=userId \
  --order-by=userId:ascending \
  --field-name=executedAt \
  --order-by=executedAt:descending

---

2. Query by Market and Timestamp

Collection: execution_logs
Fields:

• marketId (Ascending)
• executedAt (Descending)

Use Case: Query all executions for a specific market

Create via Console:

Collection: execution_logs
Fields:
  - marketId: Ascending
  - executedAt: Descending
Query Scope: Collection

---

3. Query by Order Type and Success Status

Collection: execution_logs
Fields:

• orderType (Ascending)
• success (Ascending)
• executedAt (Descending)

Use Case: Filter failed executions by type for debugging

Create via Console:

Collection: execution_logs
Fields:
  - orderType: Ascending
  - success: Ascending
  - executedAt: Descending
Query Scope: Collection

---

Auto-Generated Index Configuration

Add to firestore.indexes.json:

{
  "indexes": [
    {
      "collectionGroup": "execution_logs",
      "queryScope": "COLLECTION",
      "fields": [
        {
          "fieldPath": "userId",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "executedAt",
          "order": "DESCENDING"
        }
      ]
    },
    {
      "collectionGroup": "execution_logs",
      "queryScope": "COLLECTION",
      "fields": [
        {
          "fieldPath": "marketId",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "executedAt",
          "order": "DESCENDING"
        }
      ]
    },
    {
      "collectionGroup": "execution_logs",
      "queryScope": "COLLECTION",
      "fields": [
        {
          "fieldPath": "orderType",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "success",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "executedAt",
          "order": "DESCENDING"
        }
      ]
    }
  ],
  "fieldOverrides": []
}

---

Deployment

Deploy indexes with:

firebase deploy --only firestore:indexes

Or deploy everything (rules + indexes):

firebase deploy --only firestore

---

Query Examples

Get user’s recent executions

const executions = await db.collection('execution_logs')
  .where('userId', '==', '0x...')
  .orderBy('executedAt', 'desc')
  .limit(20)
  .get();

Get failed executions for debugging

const failures = await db.collection('execution_logs')
  .where('success', '==', false)
  .orderBy('executedAt', 'desc')
  .limit(50)
  .get();

Get all limit order executions

const limitOrders = await db.collection('execution_logs')
  .where('orderType', '==', 'limit')
  .orderBy('executedAt', 'desc')
  .get();

---

Monitoring Queries

Count executions by type (last 24 hours)

const oneDayAgo = Date.now() - 86400000;
const stats = await db.collection('execution_logs')
  .where('executedAt', '>', oneDayAgo)
  .get();

const counts = {
  limit: 0,
  take_profit: 0,
  stop_loss: 0,
  trailing_stop_loss: 0,
  tsl_update: 0,
};

stats.forEach(doc => {
  const type = doc.data().orderType;
  counts[type] = (counts[type] || 0) + 1;
});

console.log('Executions (24h):', counts);

Success rate by order type

const allLogs = await db.collection('execution_logs')
  .orderBy('executedAt', 'desc')
  .limit(1000)
  .get();

const stats: Record<string, { total: number; successful: number }> = {};

allLogs.forEach(doc => {
  const { orderType, success } = doc.data();
  if (!stats[orderType]) stats[orderType] = { total: 0, successful: 0 };
  stats[orderType].total++;
  if (success) stats[orderType].successful++;
});

Object.entries(stats).forEach(([type, data]) => {
  const rate = (data.successful / data.total * 100).toFixed(1);
  console.log(`${type}: ${rate}% success (${data.successful}/${data.total})`);
});