Auto-increment per User

Dev Support
1

Hey, there :wave:

My question regards auto-incrementation in a specific case, that goes beyond normal usage.

I am building an app where users aggregate their banking transactions.

My schema connects a table User (one) with a table Transaction (many).

For each user, the related transactions should increment like follow T-001, T-002, … T-N, etc.

This implies that for 2 different users, the first transaction of each will be indexed T-001.

And the second transaction of each will be indexed T-002.

And that’s totally fine. Increment indexes don’t have to be unique (and they should not be).

This is why I can’t apply the auto-increment feature in Transaction table.

So I need to find another approach.

My first approach consisted of storing an index value in the User table. This index value would be auto-incremented with a custom function that would be triggered before creating a transaction.

The problem is that transactions are created in parallel, and so custom functions will be executed in parallel.

Because of that, some functions will access the same index value at the same time.

As a consequence, some transactions within the same user will be affected to the same index value.

Hence, this way doesn’t work.

Another solution would consist of locking the index value during User queries and updates, to ensure table calls are executed sequentially, thus garanteeing that 2 calls never access the same index value at the same time.

But 8base doesn’t allow such a granular control on database writing and quering — which makes totally sense!

A final solution would be to create an external API that would return on demand an auto-incremented index for each given user.id. But it’s quite over-engineered.

So I need your help.

Any idea about how to create and manage indexes that are auto-incremented for each transaction per user ?

Hope I made the request clear.

Thanks :v:

Andréas

2

If you don’t need the transaction IDs immediately, maybe try the following.

Add a boolean field to your Transactions table called Indexed that has a boolean value that defaults to false.

Make a scheduled task that runs 1-once every X minutes (5-minutes maybe).

This query will return a list of all un-indexed transactions, grouped by the owning user’s ID, and sorted by the createdAt timestamp:

query {
  transactionsList(
    filter: {
      indexed: {
        equals: false
      }
    },
    groupBy: {
      query: {
        createdBy: {
          id: {
            as: "UserID"
          }
        }
        _group: {
          as: "UserTransactions"
        }
      }
    }
  ) {
    groups {
      UserID: ID
			UserTransactions: TransactionGroup (sort: {
        createdAt: ASC
      }) {
        items {
          id
          indexed
          amount
          createdAt
        }
      }
    }
  }
}

The createdAt timestamp is the only/most-reliable way to know handle the transaction order for your index. So basing the index ordering off it is I feel your best bet.

Create a task function called something like batchIndexUsersTransactions. This function would expect the JSON response of a single group returned from the query above. For example:

{
          "UserID": "ckcp42rwd00rz07mf8d8812qo",
          "UserTransactions": {
            "items": [
              {
                "id": "ckn0jhibf00lj07lceow99unh",
                "indexed": false,
                "amount": 100,
                "createdAt": "2021-04-02T16:46:45.244Z"
              },
              {
                "id": "ckn0jhbdx01av08kwb7hm67zk",
                "indexed": false,
                "amount": 100,
                "createdAt": "2021-04-02T16:46:36.262Z"
              },
              {
                "id": "ckn0jhcx601b308kwgwbthafi",
                "indexed": false,
                "amount": 100,
                "createdAt": "2021-04-02T16:46:38.251Z"
              },
              {
                "id": "ckn0jhft200yc08l58ewv5hrn",
                "indexed": false,
                "amount": 100,
                "createdAt": "2021-04-02T16:46:41.990Z"
              }
            ]
          }
        }

That function would query the last indexed transaction belonging to the user using a query like:

query($userId: ID!) {
  transactionsList(
    last: 1,
    filter: {
      indexed: {
        equals: true
      },
      createdBy: {
        id: {
          equals: $userId
        }
      }
    }
  ) {
    items {
      index
    }
  }
}

If the return value is empty, you know that the user has had no transactions and this first batch would start at T-0000 and be incrementally numbered based on the createdAt order.

If the return value is an indexed transaction, you take the last index value, (i.e. T-0098) and then do incrementally numbered index values based on the createdAt order.

mutation {
  t1: transactionUpdate(data: {
    id: "someID1"
    indexed: true
    index: "T-00091"
  }) { updatedAt }
	
  t2: transactionUpdate(data: {
    id: "someID1"
    indexed: true
    index: "T-00092"
  }) { updatedAt }
	
  t3: transactionUpdate(data: {
    id: "someID1"
    indexed: true
    index: "T-00093"
  }) { updatedAt }  
	# ect...
}
3

Hey, @sebastian.scholl :wave:

Very clear and actionable advice! I sincerely appreciate your help. :pray:

I will get back to you once the implementation is done.

Thank you,
Andréas