Creating Custom Indexes

Custom indexes allow you to define specific ways to query your ledger data, enabling efficient filtering and sorting based on fields like accountId, status, or even nested values within the metadata object.

By the end of this tutorial, you will be able to create a custom index tailored to your querying needs and use it to retrieve records efficiently.

Getting started

The easiest way to interact with the Twisp GraphQL API is to login to the Twisp Console and use the GraphiQL tool.

If you prefer to use your own GraphQL client, you can send authenticated requests to the Twisp API endpoint.

To seed your setup with some example accounts, sets, and tran codes, you can use the Example Setup.

Designing a Custom Index

Before creating an index, you need to decide how you want to query your data. Let's design an index on the Account table to quickly find accounts based on their accountId and a category field within their metadata.

Key components of an index definition:

• on: The record type (table) the index applies to (e.g., Account).
• name: A unique, human-readable name for the index (e.g., account_metadata_category).
• partition: How data is grouped. Queries must filter on the partition key(s). Good partitioning spreads data evenly. We'll partition by accountId.
  • alias: A name for the key (e.g., accountId).
  • value: A CEL expression referencing the document (the record being indexed) to get the partition value (e.g., document.accountId).
• sort (Range Key): Defines the order within a partition, enabling range queries (gte, lte) and sorting. We'll sort by metadata.category.
  • alias: A name for the key (e.g., category).
  • value: A CEL expression for the sort value (e.g., string(document.metadata.category) - casting to string ensures predictable sorting).
  • sort: ASC or DESC.
• constraints (Optional): CEL expressions that must all be true for a record to be included. We'll only index accounts that have a metadata.category.
  • Example: { hasCategory: "has(document.metadata.category)" }
• unique (Optional): If true, ensures the combination of partition and sort keys is unique for each indexed record. Defaults to false.

For our example:

• Name: account_metadata_category
• On: Account
• Partition: alias: "accountId", value: "document.account_id"
• Sort: alias: "category", value: "string(document.metadata.category)", sort: ASC
• Constraints: { hasCategory: "has(document.metadata.category)" }
• Unique: false

Create the Custom Index

Use the schema.createIndex mutation to create the index defined above.

Copymutation CreateAccountMetadataIndex {
  schema {
    createIndex(
      input: {
        name: "account_metadata_category"
        on: Account
        unique: false
        partition: [{ alias: "accountId", value: "document.account_id" }]
        sort: [{ alias: "category", value: "string(document.metadata.category)", sort: ASC }]
        constraints: { hasCategory: "has(document.metadata.category)" }
      }
    ) {
      name
      on
      unique
      partition {
        alias
        value
      }
      range {
        # 'range' is the field for sort keys in the response
        alias
        value
        sort
      }
      constraints
      historical
      search
    }
  }
}

Copy{
  "data": {
    "schema": {
      "createIndex": {
        "name": "account_metadata_category",
        "on": "Account",
        "unique": false,
        "partition": [
          {
            "alias": "accountId",
            "value": "document.account_id"
          }
        ],
        "range": [
          {
            "alias": "category",
            "value": "string(document.metadata.category)",
            "sort": "ASC"
          }
        ],
        "constraints": {
          "hasCategory": "has(document.metadata.category)"
        },
        "historical": false,
        "search": false
      }
    }
  }
}

This mutation creates the account_metadata_category index on the Account table. The response confirms the index structure, including its partition and range (sort) keys.

Query Using the Custom Index

To use your new index, specify index: { name: CUSTOM } in your query and provide the index name and filters in the where.custom argument.

You must provide an equality (eq) filter for all partition key aliases. You can optionally provide filters (eq, gte, lte, prefix, etc.) for sort key aliases.

Copyquery QueryUsingCustomIndex {
  accounts(
    index: { name: CUSTOM }
    where: {
      custom: {
        index: "account_metadata_category" # Name of the custom index
        partition: [
          {
            alias: "accountId" # Partition key alias
            value: { eq: "a1b2c3d4-e5f6-7890-1234-567890abcdef" }
          }
        ]
        sort: [
          {
            alias: "category" # Sort key alias
            value: { eq: "Premium" } # Filter condition on sort key
          }
        ]
      }
    }
    first: 10
  ) {
    nodes {
      accountId
      name
      metadata
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

Copy{
  "data": {
    "accounts": {
      "nodes": [
        {
          "accountId": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
          "name": "Premium Customer Account",
          "metadata": {
            "category": "Premium",
            "region": "US-West"
          }
        }
        // ... other matching accounts up to 10
      ],
      "pageInfo": {
        "hasNextPage": false,
        "endCursor": "..."
      }
    }
  }
}

This query efficiently retrieves Account records using the account_metadata_category index, filtering first by the accountId partition and then by the category sort key.

Create a Vector Index

Custom indexes find records by exact values. A vector index finds records by meaning: a query for "coffee shop in Denver" can match an account named "Mile High Espresso" even though the two share no words. Twisp embeds text computed from each record with an embedding model, and returns the records whose embeddings are most similar to the query.

Use the schema.createVectorIndex mutation. The text expression chooses the text to embed, attributes adds values that queries can match exactly, and constraints limits the index to accounts that have a metadata.category.

Copymutation CreateAccountSemanticIndex {
  schema {
    createVectorIndex(
      input: {
        name: "account_semantic"
        on: Account
        text: "document.name + ' ' + document.description"
        attributes: [{ alias: "category", value: "string(document.metadata.category)" }]
        constraints: { hasCategory: "has(document.metadata.category)" }
      }
    ) {
      name
      on
      vector
      vectorConfig {
        provider
        model
        dimensions
        distance
        text
        attributes {
          alias
          value
        }
      }
    }
  }
}

Copy{
  "data": {
    "schema": {
      "createVectorIndex": {
        "name": "account_semantic",
        "on": "Account",
        "vector": true,
        "vectorConfig": {
          "provider": "bedrock",
          "model": "amazon.titan-embed-text-v2:0",
          "dimensions": 1024,
          "distance": "COSINE",
          "text": "document.name + ' ' + document.description",
          "attributes": [
            {
              "alias": "category",
              "value": "string(document.metadata.category)"
            }
          ]
        }
      }
    }
  }
}

The index uses the default model, amazon.titan-embed-text-v2:0, at 1024 dimensions. Twisp embeds the existing accounts in the background, and the index status becomes ACTIVE when they are all indexed.

Query Using the Vector Index

Specify index: { name: VECTOR } and give the query text in where.vector. Ask for edges to get each result's match.score, the similarity from -1 to 1 where higher is more similar.

Copyquery FindSimilarAccounts {
  accounts(
    index: { name: VECTOR }
    where: {
      vector: {
        index: "account_semantic"
        text: "coffee shop in Denver"
        attributes: [{ alias: "category", value: { eq: "merchant" } }]
      }
    }
    first: 5
  ) {
    edges {
      match {
        score
      }
      node {
        accountId
        name
        description
      }
    }
  }
}

Copy{
  "data": {
    "accounts": {
      "edges": [
        {
          "match": {
            "score": 0.71
          },
          "node": {
            "accountId": "3ea9bf2d-4f37-4a41-9f1c-0bc5e6a9d1a2",
            "name": "Mile High Espresso",
            "description": "Cafe on Larimer Street, Denver CO"
          }
        }
        // ... up to 5 results, most similar first
      ]
    }
  }
}

Vector indexes are eventually consistent: a new or changed account can take a few seconds to appear in results. For the full list of options and limits, see Vector Indexes.

Conclusion

In this tutorial, you learned how to design and create a custom index using the schema.createIndex mutation. You saw how to define partition keys, sort keys, and constraints using CEL expressions. Finally, you learned how to leverage your custom index in queries for efficient data retrieval based on specific fields, including nested metadata values, and how to use a vector index to find records by similarity.

Custom indexes are a powerful tool for optimizing query performance in Twisp. Consider creating them for your common query patterns.