```yaml
---
slug: transactions-part-1
published: 2006-04-13
description: Gaia's original "transaction manager" explained
excerpt: ~
changelog:
  - on: 2020-02-23
    note: This was rescued from the archives of the old [felocity.org](https://web.archive.org/web/20050507121849/http://www.felocity.org/journal). As a result, many links in this piece were left pointing to archive.org destinations.
---
```

Everything is on track for SXSWi this year, and excited isn't even a good word for how I feel right now. It's 4 am [Ed. Now it is noon the following day…] and I'm downright giddy. Last year I blew my first chance to go because I didn't move fast enough. This year, I jumped on travel planning much quicker and managed to grab a room at the Hilton Austin. I'm in on the 10th and out on the 14th, and if I didn't have some larger projects, I'd be staying much longer. To those going, I'll see you there (just look for the kid who looks like an excited lost puppy). However, if you notice the title, this isn't about SXSWi entirely, but instead is focused much more on some meaty PHP things: transactions. For the new bank code at Gaia, there needed to be a way to move items and gold about the site through a standardized (and [5 nines" reliability) system. Add to that full logging in the event of failure, and the ability to "transact" just about any two things on the site, and you get the foundation of the transaction system. This is part one of a multi-part article which will touch on the surface and theories used in the transactions framework. (Part 2 will focus more or architecture.)

* **Part 1: Theory**
* [Part 2, Transaction Framework](/thunked/transactions-part-2)

When the project first began, transactions were an odd concept. Not like database transactions, but a sequence of events that were atomically linked and reversible. Examples included purchasing from the Gaia Store (give the system gold, the system gives an item), making a trade (place an item into an "escrow" neutral zone), auctioning off an item (again, an escrow style neutral zone), purchasing non-pixels such as a Trading Pass or a Guild Purchase where the result is a database row but no items or gold, and many other things that went beyond the basic "remove gold, add item" mentality. This meant it was time for some research into complex data analysis models. Enter Martin Fowler, whose books I had read back in college, but like most college students made a point of forgetting. Thankfully, John had Analysis Patterns which was all we needed to get underway. I'm going to digress for a moment to say Fowler is a very intelligent man, but I remember why I couldn't remember what he wrote- Fowler has a hard time communicating why a pattern works the way it does in a way that connected with me. If you're someone who needs to know the "why", you might have to suck it up like I had to. After going cover to cover, it appeared there was a very basic system underneath even the most complex banking systems. To abridge the text, we started referring to it as the Conceptual Account Thingy (or CAT for short).

## Accounts, Entries, and All That Jazz
Keeping with our simplified version of things, an Account was described as nothing more than a container. This container could hold things, and those things belong to some entity. As an added bonus, [Gaia's "DAO"](http://72squared.com/index.php/module/Articles/action/Display/id/37) information was also stored with the Account in order to provide a means to get out and save data when needed. Accounts were then abstracted and an Account class was created for the "things" on Gaia that were owned by "someone". The initial list included:

* Gold (owned by user)
* Inventory (owned by user)
* Trades (owned by the trade row, with two users participating)

The third one is very interesting because a Trade became what was called a "Summary Account" in that it contained two Accounts inside of it, a left half and a right half. The left half was owned by the trade row and by the left user, while the right half was owned by the trade row and the right user. Because of this, the only entity that owns the entire trade is the trade row itself. When retrieving Accounts, they are always fetched by their owner which is often a primary key. This ensures speed of performance during the Account loading process. With a means to load and save the data, the next step is to manipulate it.

According to Fowler, the Entry class is nothing more than an entry that will be logged to an Account. Think of recording a check in the checkbook's ledger. This process is formally called Posting an entry, but for practical purposes, entries are applied to an Account, and then the changed Account is saved back to the database. To soften the load on PHP, the entries became lightweight containers of metadata which provided little more than a pretty interface for method calls. A simplified version of an Entry looked like: (note, "Container" is nothing more than an abstracted hash table in order to provide useful namespacing functionality. In this case, the `datatypecontainer` enforces typecasting on items going in and coming out of it.)

```php
<?PHP
/**
* Required Include Files
*/
require_once(DIR_CLASSES . 'container/datatypecontainer.php');

class TransactionEntry
{
    function TransactionEntry()
    {
        $this->Container = new DataTypeContainer();
    }
    function importData($data) {}

    function exportData($preserveId = true) {}

    function setEntryId($v) {}
    function getEntryId() {}

    function setTransactionId($v) {}
    function getTransactionId() {}

    function setEntityId($v) {}
    function getEntityId() {}

    function setAccountType($v) {}
    function getAccountType() {}

    function setObjectId($v) {}
    function getObjectId() {}

    function setAttribute($v) {}
    function getAttribute() {}

    function setAsAdd() {}
    function setAsSubtract() {}
    function setPolarity($v) {}
    function getPolarity() {}

    function setTimestamp($v) {}
    function getTimestamp() {}

    function setStatus($v) {}
    function getStatus() {}

    function cloneInverse()
    {
        $obj = new TransactionEntry();
        $data = $this-&gt;exportData(false);
        $data["polarity"] = $data["polarity"] * -1;
        $obj-&gt;importData($data);
        return $obj;
    }
}

?>
```

I left the `cloneInverse` statement because it was of particular note. From above, it was said that everything done to an Account needs to be able to be undone. The secret to this is being able to "reverse" all of the entries that have been applied to a transaction, and then reapply them. The net result of this will always be the Account's original state. In extensions of this Entry object, decoration aliases are used such as `setUserId()` in order to simplify the application programmers' work. With entries, and things to apply those entries to, the next step is to set up the rules.

## Posting Rules
When the transaction manager hands an entry to an Account, it is the Account's responsibility to take that entry and apply it. For different Accounts, applying an entry means different things. Inventory Accounts (owned by a user) are by far the hardest with their need to add/remove binary strings. An Account can always look at the class of an Entry object if that Account supports multiple types of entries. At present, since the inventories on Gaia (housing, main, game inventory, etc) are separated, there is no need to develop sophisticated entry handling. Fowler gets into extreme detail about Posting Rules and covers more than is needed by this system. However, the basics of how to apply a rule and what happens when applying an entry to an Account need to still be established. It was agreed upon that should a posting rule fail, it will fail all future objects in the posting rule change, and triggering a rollback of the transaction by taking the `cloneInverse()` of each Entry and reapplying them.

![image](https://user-images.githubusercontent.com/1795/146686016-cfbac26c-f5a8-4af8-99c9-f263450b608b.png)

Every `Account` then has a public `postEntry()` method, which takes an `Entry` object as an argument. Using the generic accessor functions from the base `TransactionEntry` object, the `Account` can apply the Object ID, Attribute, and Polarity to their entry. Each call to `postEntry()` returns `TRUE` on rule posting success. All that remains is to tackle the fact that the web (and the Accounts) are constantly in use. Collisions can occur when a second transaction manager is started while the first one is still in process. Since the first transaction hasn't saved, the second process has dirty stale old data.

Fowler presents two methods of avoiding collision, both which are worth touching on briefly. Mileage varies with either one.

## Posting Rules With "Catchup"
Of the two methods Fowler discussed, journaling is by far the most widely employed. To draw a real-life analogy, it functions much like a checkbook register. At any given point, a quick glance through the register can tell someone what the "balance" is, including all transactions which haven't cleared. Much like processing checks out of order, an Account can be measured by taking its present state, and then applying all deduction Entry objects which have not cleared yet. This gives the transaction manager the net low for the account, making it possible for the system to check for insufficient funds, missing items, and incomplete database rows. In a set of linear steps in PHP/MySQL, you might get something like the following:

1. Process 1 writes what it wants to do (status uncleared) to the DB
2. Process 1 gets an insert ID as a result of this
3. Process 1 loads the Account
4. Process 1 loads all deducting transactions up to its insert ID on top of the Account
5. Process 2 can start, and at any point after #<span/>1, its Net Account will include all of Process 1's unresolved deductions.

While this is a very reliable system, the need to constantly bring an Account to current isn't a very cheap process query wise. Also, every system needs to implement this transaction system so that a blind write to someone's inventory isn't destroyed in the process. Since the Vending system, Shopping system, Item Granting System (used in games) all would not start equipped with this transaction manager, it was not meant to be. If you're starting from scratch, this is certainly the way to go. If not, well, you can always improvise.

## Improvising with innoDB Locking
The problem was, as stated above, the asynchronous nature of Account objects. Lacking the ability to journal the transactions, the other solution to an Asynchronous problem is to break it. By redefining the Accounts to be as small a data set as possible and introducing a simple retry. The most likely collision on the site is the Gold Account, which would require a user to earn gold on the site within the same timeframe as say, completing a Marketplace Transaction. Marketplace can see the lock, wait a split second, and try again. The resulting failing collision rate is so low that it's considered acceptable to have a user retry in the event of a complete lockout.

I've made mention a few times to locking, and this is done with two tools: innoDB and the MySQL `SELECT...FOR UPDATE` syntax. We've made liberal use of the innoDB storage engine for it's transaction support, and according to MySQL, using the `SELECT...FOR UPDATE` syntax places a good lock on the innoDB row, letting the process finish what it was doing. If during the select phase some database row it needs is locked, it reseeds at a small random interval and performs its work on a retry. When complete, the transaction is committed and the changes recorded.

For spanning multiple databases with a single transaction, John developed a DB spanning object for the DAO he developed. It chains commits to fire off in sequence, effectively minimizing the failing window gap. Half commits are still a problem should for example, all the servers magically lose power and they are mid-commit. Unfortunately, this is a failing of the improvisation that can only be solved with a true multi-phase commit. In production testing, it's failure rate is wholly dependent on the hardware's reliability. While this has a low failing rate, it is substantial enough to warrant exploring extensions to the DAO's DB-spanning system.

## Conclusions

The system is currently in testing with a very good success rate. There are a few tweaks needed to optimize the system of Account and Entry objects in order to reduce weight when including them for processing. I'd also like to better explore the DAO's integration into the Account object- the current method alters the conceptual DAO behavior in order to solve a problem where more than one Account object may be tied to the same database row. More on that next time though.

This was rescued from the archives of the old [felocity.org](https://web.archive.org/web/20050507121849/http://www.felocity.org/journal). As a result, many links in this piece were left pointing to archive.org destinations.

Document

Gaia's original "transaction manager" explained

Link

code

<?PHP
/**
* Required Include Files
*/
require_once(DIR_CLASSES . 'container/datatypecontainer.php');

class TransactionEntry
{
    function TransactionEntry()
    {
        $this->Container = new DataTypeContainer();
    }
    function importData($data) {}

    function exportData($preserveId = true) {}

    function setEntryId($v) {}
    function getEntryId() {}

    function setTransactionId($v) {}
    function getTransactionId() {}

    function setEntityId($v) {}
    function getEntityId() {}

    function setAccountType($v) {}
    function getAccountType() {}

    function setObjectId($v) {}
    function getObjectId() {}

    function setAttribute($v) {}
    function getAttribute() {}

    function setAsAdd() {}
    function setAsSubtract() {}
    function setPolarity($v) {}
    function getPolarity() {}

    function setTimestamp($v) {}
    function getTimestamp() {}

    function setStatus($v) {}
    function getStatus() {}

    function cloneInverse()
    {
        $obj = new TransactionEntry();
        $data = $this-&gt;exportData(false);
        $data["polarity"] = $data["polarity"] * -1;
        $obj-&gt;importData($data);
        return $obj;
    }
}

?>


Fence

Transacting Items on Gaia Online

```yaml
---
slug: transactions-part-2
published: 2006-10-02
description: More on Gaia Online's "Bank Transaction Manager"
changelog:
  - on: 2020-02-23
    note: This was rescued from the archives of the old [felocity.org](https://web.archive.org/web/20050507121849/http://www.felocity.org/journal). As a result, many links in this piece were left pointing to archive.org destinations.
---
```

It's been a while since I've touched on the topic, mostly because I've been busy with work for Gaia. However, this incubation period has let the transaction system mature, and it continues to hold out well with over 29 million completed transactions in the last 7 months. Since its inception, it's been adjusted to support item granting, and is currently being extended to support transactions in the Gaia Marketplace / Auction House. In the first article, I covered the surface of the transaction manager, and some of Fowler's theories which were behind it. In order to avoid recoding large parts of the site, a lock-first mentality was chosen for how to handle keeping an account up to date. This time, we're going to focus on all the players / classes of the transaction manager and how they work together.

* [Part 1: Theory](/thunked/transactions-part-1)
* **Part 2, Transaction Framework**

As in most programming languages, PHP provides support for both public and private methods within a class. In development, one of the primary objectives was to minimize the external facing interface for the end-developer, while still keeping the backend components easy for a developer to maintain. Each component was given specific tasks, responsibilities, and a list of classes that it would couple with in order to provide functionality.

## Bank Transaction Manager
The first component is the first of three end-developer facing classes. The Bank Transaction Manager is responsible for creating Transaction Entries, adding Transaction Entries to its internal stack, and then processing the entry stack while reporting on the success or failure of the process. When a Bank Transaction Manager is initialized, it creates an Account Manager object for tracking the accounts which it needs to load. As entries are created with the `createEntry()` method, they are fed to the end developer and then added back in to the system via `addEntry()`.

A Transaction Entry is nothing more than a shell object, providing a clean interface for setting an object's ID, it's attribute, quantity, and other associated properties. When `addentry()` is called, the Bank Transaction Manager uses these properties in order to decide what Account to load (via the Account Manager). This pairing is then set aside until execution.

During the execution phase, the Bank Transaction Manager tells the Account Manager to initialize all relevant accounts. It then loops through every entry/account pair and passes the entry into the account. In processing terms, these are called posting rules. Every posting rule has a boolean success, and the sum of all functions returning an ultimate success or failure for `execute()`.

If any errors are encountered, the Bank Transaction Error (a simple static class) is written to with information about the failure.

## Account Manager
In a horizontally scaled environment, a user's inventory or a row of trading information might reside on completely separate machines. To encapsulate the loading and saving of these various things is the reason for the Account Manager object. Requested accounts are queued up, and then loaded via a single call to `loadAccounts()`. Beyond simply loading accounts, the account manager should be able to

- handle information if the account has changed
- prevent data overlap by avoiding querying for duplicate data
- provide an interface for the instantiation of new Account objects

During the execution of the Bank Transaction Manager, the Account Manager is called (specifically when `BankTransactionManager::addEntry()` is used). Accounts are queued for loading as entries are added, and are then loaded at once. There are two ways to load the account data, one which uses blocking, and one which does not. Even at 4.5 million registered members, the blocking method has not been an issue, and so that is the method we'll be talking about. In an InnoDB database, a row can be locked during the select phase as part of a transaction by using the following syntax: `SELECT 'columns' FROM 'table' WHERE 'criteria' FOR UPDATE;`

The `FOR UPDATE` places a lock on the row, leaving your PHP script as the only script that has access until the lock expires. This feature is only available on InnoDB however, as the MyISAM engine supports neither row locking nor transactions.

When asked to save, the `AccountManager` calls the `isChanged()` method on every account in order to verify if it has been altered. Accounts which have been altered are then saved back to the database and the locks released.

## Transaction Entry
There's not much to say about the `TransactionEntry` object. As a shell, all it contains is a data payload. It's information consists of: "how many", "of what", "with what serial", and "adding or subtracting". To help simplify the development end, the Entry object is extended to support Game items, Regular Items, Gold, and Gift Credits. During execution, these small instruction sets are passed to the Account object.

## Account
Defined as the authoritative source regarding all data coming in and going out, these objects are modeled in spirit after the VO J2EE pattern. They exist to hold an account's data, and are decorated with methods to support the changing of that data. When the Account Manager calls `loadAccounts()`, every queued account object is asked for its database information and required fields. The sum of this data is aggregated, and passed into the `importData($fieldName, $data)` function. When the Bank Transaction Manager executes, it then takes every Transaction Entry object, and passes it directly into `postEntry($t)`. It is at this point that the heavy lifting is actually done.

Every account maintains a ruleset for how its data is to be altered, known as "posting rules". These rules are very strict, and breaking them should cause the entire transaction to fail for the user and return an error. The most common example of failure is if the posting rule contains an item type that the account does not support, such as posting gold information to an inventory account. With a single public interface, the logic of altering the account information is also internalized, protecting it from the outside world. Upon altering the account, `postEntry()` will return either `TRUE` or `FALSE`, which will be evaluated by the Bank Transaction Manager object.

Adding new accounts or extending existing accounts to add functionality is very easy. The methods exposed publicly are small, specifically `getDaoName()` and `getDaoFields()` for Gaia's database support, `importData()` for loading, `exportAccount()` for saving, `isChanged()` for the Account Manager's save required check, and `postEntry()` for altering the account's data.

## Code in Practice (End Developer Side)

```php
$btm = new BankTransactionManager($transaction);

// create entry (Account, Entry Type)
$e = $btm->createEntry("Gold.Gold");
$e->setUserId($user_id);
$e->addGold($gold_amount);
$btm->addEntry($e);

$e = $btm->createEntry("ServerGold.Gold");
$e->setUserId(ENTITY_SERVER);
$e->subtractGold($gold_amount);
$btm->addEntry($e);

try {
    $btm->execute();
}
catch (Exception $e) {
    throw $e;
}
```

More on Gaia Online's "Bank Transaction Manager"

$btm = new BankTransactionManager($transaction);

// create entry (Account, Entry Type)
$e = $btm->createEntry("Gold.Gold");
$e->setUserId($user_id);
$e->addGold($gold_amount);
$btm->addEntry($e);

$e = $btm->createEntry("ServerGold.Gold");
$e->setUserId(ENTITY_SERVER);
$e->subtractGold($gold_amount);
$btm->addEntry($e);

try {
    $btm->execute();
}
catch (Exception $e) {
    throw $e;
}


Transacting Items on Gaia Online, Part 2

```yaml
---
slug: time-based-queues-serverless-docmq
published: 2022-08-31T12:39:00-07:00
description: Nerding out on time-ordered priority queues
changelog: ~
---
```

Building a [serverless-friendly queue system](https://taskless.io) continues to be an adventure. Recently, we had to say goodbye to our Redis queues and replace them with something more durable. The journey there involved a lot about time storage, visibility windows, and how the most popular queue solutions solve these problem. So, if you were curious what makes Taskless reliable, the short answer is a database optimized for time-based queries. For a longer answer, we need to go on a journey.

## First, it's Time for Events

The first rule of the JAMStack is that everything is an event. Because servers aren't standing by and waiting for your every request, events are the only way for a serverless provider to know your code needs to run. In practical terms, when you make a request to a Next.js app or AWS lambda, that small chunk of code is invoked in response to the URL request, executed, sends the reply, and then shuts down. This is well and good for operations like "forward this blog post to a friend" or "leave a comment about how you definitely wouldn't forward this blog post to a friend," given both examples begin with an obvious interaction. But what about an operation like "send this to my inbox tomorrow morning"?

It turns out, time ruins everything.

Time sucks, and if you don't believe me, go [read Zack Holman's excellent piece on the matter](https://zachholman.com/talk/utc-is-enough-for-everyone-right) and return once you've come to a similar conclusion. Now that we agree the concept of time was a mistake, let's talk about sending something to your inbox "tomorrow". To avoid headaches, we're going to make some poor assumptions about what tomorrow means. In Luxon, we can express this arbitrary definition of "tomorrow" as `DateTime.now().zone("America/Los_Angeles").plus({days: 1}).startOf("day").set({hour: 8})`. Bam. 8am "tomorrow" at of this exact moment. All we need to do is "event" at that time, and we're set!

## Store the Time in the Queue Itself - aka The SQS Experiment

The purest of serverless ambitions would be to never persist anything under our ownership. To that end, [Amazon SQS](https://aws.amazon.com/sqs/) and lambdas are an excellent fit. The platform has limitations, but those could be worked around with a bit of duct tape.

First, the maximum delay on a lambda is 15 minutes, so SQS would need to send the job to a lambda at the end of the maximum delay, which would only report success if it could requeue the job for another 15 minutes. We'd repeat this ad-nauseum until we reached our magical "8am tomorrow" requirement and then run our job.

This worked really well (and was insanely cheap). Each job would require a maximum of 96 SQS calls per day in a pending state, and it's only $0.50 per million requests. That's 10,000 jobs per month, delaying every job up to a maximum of one day, with just AWS tools. In practice, the system has a lot of opportunity to fail. Specifically 672 opportunities for every day the job sits in SQS and must be constantly requeued,

Honestly, if SQS didn't have an arbitrary 15 minute delay, we'd be done. But it doesn't so we're not.

## Tables, Indexed by Time

It turns out that databases are really good at time stuff, and not just because [ISO-8601](https://en.wikipedia.org/wiki/ISO_8601) is both readable and easy to sort by. But also because really smart people made time (and its storage) one of the primitives in their db systems.

Databases are also really good and indexing non-time things. So imagine you have a table

| column | type |
| :--- | :--- |
| id | any |
| failed | boolean = false |
| visible | time, nullable |
| deleted | time, nullable |
| ack | any, nullable, unique (on non-null) |
| payload | text, nullable |

Now it's easy to describe the operations in order to take, process, and report on jobs.

* Your next jobs are at `visible <= now() AND deleted IS NULL`
* Your failed jobs are at `deleted IS NOT NULL AND failed = TRUE`
* Your completed jobs are at `deleted IS NOT NULL AND failed = FALSE`
* You can retry a job by changing its `visible` time
* You can complete/fail a job by setting `deleted` and `failed` in the same transaction
* A unique `ack` value lets you find a single job in the table so that you are complete/fail/retrying only one specific job

As an added bonus, using just-a-database means that we have separated our queue (what do we need to run) from our worker (the actual running). The pattern was simple enough that I bundled it up as [DocMQ](https://github.com/jakobo/docmq). It has a persistent worker if that's something you want, or you can just run DocMQ for adding jobs to your queue database and process them on your own time.

## Time to Return to Events

The beautiful part about using a database is that once it's in the database, it's _there_. We don't need persistence unless we want it, and polling is good enough for most use cases. All we need to do is poll in response to an event; that event is _time itself changed_. 🤯

Stick with me here. Imagine if we have a cron job via [Netlify Scheduled Functions](https://docs.netlify.com/netlify-labs/experimental-features/scheduled-functions/) or [lambda cron](https://docs.aws.amazon.com/lambda/latest/dg/services-cloudwatchevents-expressions.html) that runs once per minute (or per-second if you'd like to YOLO). A single query tells you what capacity you need:

```sql
SELECT count(*) FROM your_table WHERE visible <= now() AND deleted IS NULL
```

You get 20 rows back, spin up 20 serverless functions, each to claim 1 row:

```sql
UPDATE your_table SET ack = gen_random_uuid(), visible = visible + interval '30 seconds'
WHERE visible <= now() AND deleted IS NULL
LIMIT 1
```

💥Instant scalability. If you need more granularity, tighten the cron. If you need sub-second control then you probably do want a server. Real talk though, if you're sending emails the latency isn't your job system.

## How This Relates to Taskless

At [Taskless](https://taskless.io) we ran on the cron job / polling design until we were invoking an unreasonable number of serverless functions on the minute and customers wanted sub-minute responses. Since our scheduled function infrastructure didn't let us go to per-second, we switched to persistence. In short, we ran the servers so you could go 100% serverless. 🎉

Because [DocMQ](https://github.com/jakobo/docmq) is on GitHub, you can replicate it yourself. There's even a [postgres prototype](https://github.com/jakobo/docmq/tree/labs-postgres) that will get merged down soon in case you're a fan of Neon, Supabase, or other serverless-friendly postgres instances.

### Some Quick Answers

Added because these came up in casual conversation

* Does the DB matter? - No, as long as your DB understands time
* Biggest drawback? - Performance. A relational db will be slower than an in-memory solution in almost every use case. Unless you're building a real-time application such as chat, the latency of a DB touching disk is not going to be why things are slow
* What DB does Taskless use? - We use MongoDB Atlas currently. We like their time series support



Nerding out on time-ordered priority queues

thead

tbody

table

SELECT count(*) FROM your_table WHERE visible <= now() AND deleted IS NULL


UPDATE your_table SET ack = gen_random_uuid(), visible = visible + interval '30 seconds'
WHERE visible <= now() AND deleted IS NULL
LIMIT 1


Designing A Time-Based Queue for Serverless

```yaml
---
slug: relay-cursors-for-database-queries
published: 2021-06-07T00:00:00.000Z
description: You need more than a Primary Key
changelog: ~
---
```

[Cursors](https://relay.dev/graphql/connections.htm) are just strings, at least as far as GraphQL clients are concerned. It has absolutely no meaning beyond identifying a specific result in a set of edges. There’s no limits on its length; most are base64 encoded to emphasize that point. I'll come back to that in a moment..

Currently Code Drift is a Ghost shop, using their excellent [content API](https://ghost.org/docs/content-api/), which depends on `page` and `limit` for accessing and paging through records. However, Ghost’s `id` isn’t sufficient for implementing a cursor. The Ghost API doesn’t have a “all posts after `id:aedf0001...`” call, only access to those `page` and `limit` values. We could in theory get back to a given post in Ghost as long as we knew those values.

Which brings us back to the importance of cursors being opaque to a client. What if instead of using the content’s id, we also included the page and limit values that got us here?

    id:aedf001bcbc|page:2|limit:10

This works because while the client assumes the cursor to be opaque, the specification makes no such claims about the server. The server can (and should) care deeply about the cursor. In this example, our cursor can tell us how to reconstruct our query or API calls.

We can store our query’s state in our cursor. I promise, it isn’t as bad as it sounds.

## Metadata In the Cursor

By definition, cursors in Relay are a string-serializable value. While anything can go into the string, I’d recommend the [GraphQL Foundation’s advice](https://graphql.org/learn/pagination/#pagination-and-edges) of base64 encoding the value. For example, here’s a [query with a cursor](https://github.com/jakobo/codedrift/blob/c79a249/src/pages/index.js#L10) on Code Drift:

```gql
query {
  posts(first: 1) {
    edges {
      cursor
    }
  }
}
```

```json
{
  "data": {
    "posts": {
      "edges": [
        {
          "cursor": "eyJ0eXBlIjoiUG9zdCIsImZpbHRlciI6bnVsbCwib3JkZXJCeSI6InB1Ymxpc2hlZF9hdCBERVNDIiwiaWQiOiI2MDc3MzE1ZTU0OTBkNjAwM2IxM2FiNjEiLCJvZmZzZXQiOjB9"
        }
      ]
    }
  }
}
```

Our client shouldn’t care what’s in the cursor since it’s supposed to be opaque. But it’s **long** and starts with `ey` which usually means we’re going to see some JSON. Decoding the cursor shows us that it is just a JSON blob.

```js
new Buffer("eyJ0eXBlIjoiUG9zdCIsImZpb...", 'base64').toString();

/*
{
  "type": "Post",
  "filter": null,
  "orderBy": "published_at DESC",
  "id": "6077315e5490d6003b13ab61",
  "offset": 0
}
*/
```

It contains everything the server would need to reconstruct the original query including the `offset`. The next result is predictably `offset + 1`, assuming our query parameters such as filter and `orderBy` didn’t change.

## Encoding the ID

The second requirement for Relay is that our IDs need to be globally unique. For a small project like Code Drift, I trust Ghost to not double up my unique IDs. If I ever was concerned though, I'd probably just follow Hasura's model of encoding the ID as JSON arrays (which guarantees the order on serialization):

```js
const id = Buffer.from(
  JSON.stringify([
    1, // version id (for back-compat)
    "Post", // type of record
    ["aedf0..."], // keys array (uniquely ID this record)
  ])
).toString("base64");
```

If you do implement these custom IDs, you'll want to update any resolvers to spot these JSON encoded keys and unpack them.

## Reconstructing the Query

The biggest limitation of storing query reconstruction in the cursor is that you must revalidate the parameters. In our example, if the `orderBy` variable changed, then the `offset` is probably no longer correct. In most environments, you can probably just [add checks to ensure these parameters don’t fluctuate](https://github.com/Jakobo/codedrift/blob/5f74f80/src/graphql/resolvers/Query/posts.js#L26). In a complex case, you can implement custom business logic to “find” your result by its cursor and adjust your pagination accordingly.

Since most websites only deal with forward / backward pagination of the same result set (and therefore the same query parameters), you likely won’t need the complex case. It’s significantly more code.

It should also go without saying that your cursor should only have metadata in it. Don’t shove private values in there like an AWS key. Don’t make someone’s email address part of your cursor. In general, don’t put anything in there that you wouldn’t be willing to display on the client or expose in your GraphQL endpoints.

## Encoding Cursors on Return

The final step is to generate our special cursors. Every cursor needs the same data structure, allowing us to reconstruct the query regardless of which cursor the client sends along in the next request. The trickiest part is calculating the offset, but in most cases that will be `offset + N + 1` where `offset` was in your original cursor, and `N` is the Nth edge in your result set.

If it helps clean up the code, use a pair of `encodeCursor` and `decodeCursor` functions for consistency in your cursor payload. Because it’s base64 (and JSON) these cursors will be larger than they probably needed to be, but come with the added benefit of making it easier to reconstruct the query later.

This pattern is [used pretty heavily on Code Drift](https://github.com/jakobo/codedrift/blob/5f74f80/src/graphql/resolvers/Query/posts.js), specifically because every cursor must result in a RESTful query to Ghost’s content API. If Ghost ever implemented their own opaque cursors, I could switch over to those with a small GraphQL change. Until then though, this provides a crucial link between a Relay API and a traditional REST endpoint for an individual record regardless if it comes from an API, Firebase, or even a Postgres database.


query {
  posts(first: 1) {
    edges {
      cursor
    }
  }
}


strong

new Buffer("eyJ0eXBlIjoiUG9zdCIsImZpb...", 'base64').toString();

/*
{
  "type": "Post",
  "filter": null,
  "orderBy": "published_at DESC",
  "id": "6077315e5490d6003b13ab61",
  "offset": 0
}
*/


const id = Buffer.from(
  JSON.stringify([
    1, // version id (for back-compat)
    "Post", // type of record
    ["aedf0..."], // keys array (uniquely ID this record)
  ])
).toString("base64");


Relay Cursors for Database Queries

```yaml
---
slug: complex-hasura-permissions-via-views
published: 2021-04-15T00:00:00.000Z
description: How you can solve complex Hasura permissions using postgres views
changelog: ~
---
```

As we continue to explore the space of mentorship & career success at [Aibex](https://aibex.com), one of our prototypes tackled the idea of focused group messaging; think Slack, but without the signing in to seventeen spaces just to get something done. To make a messaging app that focused on the needs of mentors and mentees required unique permissions. We wanted users to have access to any messages they were present for, even if they later ended up leaving the group. To use our Slack analogy, leaving a private channel shouldn’t cut you off from the information you were originally invited to see.

The prototype is currently running on [Hasura](https://hasura.io), generating our GraphQL API from an existing Postgres database. We chose Hasura to both reduce the amount of API code we needed to write and to take advantage of the systems’ in-built webhook actions. When it came time to implementing our permissions via Hasura’s JSON language, we ran afoul of Hasura’s undocumented Rules for Rules (my term, not theirs).

- Every Hasura user in the same "role" must also use the same rules
- You cannot use a computed column or (most) function calls in a rule
- The only dynamic variables you have at permission time is the Hasura session variables (such as `X-Hasura-User-Id`)

In a messaging app, any message could have one or more recipients. In the Aibex prototype specifically, we also wanted someone who joined a group chat to gain access to all prior messages in the chat. After a user left a chat, they could continue to see all the messages during their time in the group.

There is no amount of `or`, `and`, nor `_exists` that solves this by default. If the title didn't give it away, the solution lies in offloading all computational work to a [postgres view](https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/views.html), simplifying the Hasura permission JSON.

## The Computed Conditional Problem

We're going to begin with a set of tables, defining a relationship between users, groups, and the messages within:

```sql
CREATE TABLE users (id uuid PRIMARY KEY);

CREATE TABLE groups (id uuid primary key);

CREATE TABLE memberships (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  joined_at timestamp NOT NULL DEFAULT now(),
  departed_at timestamp
);

CREATE TABLE messages (
  id uuid PRIMARY KEY,
  from_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  created_at timestamp NOT NULL DEFAULT now(),
  content text
);
```

![An Entity Relationship diagram showing a user belongs to many groups and that a group has many messages](https://user-images.githubusercontent.com/1795/146840050-5fc68478-b223-44ad-8c1b-b27e1aa17835.png)
_A user has many groups and that group has many messages_

The problem arises when we want to add our permissions to the `messages` table. Hasura's permission system allows us to access `(messages).group.memberships` via its inbuilt relationship system.

```json
{
  "group": {
    "memberships": {
      "user_id": {
        "_eq": "X-Hasura-User-Id"
      }
    }
  }
} 
```

Setting up our permissions this way gets us halfway; a user who has a relationship to the message (via the groups table), would be allowed to see the message in question. However, if we want to use `joined_at` and `departed_at`:

```json
{
  "group": {
    "memberships": {
      "_and": {
        "user_id": {
          "_eq": "X-Hasura-User-Id"
        },
        "departed_at": {
          "_lte": "<< CANNOT ACCESS messages.created_at >>"
        }
      }
    }
  }
}
```

Accessing the group through an Object Relationship will only let us test that the user was in the group at some point. By the time we've traversed the relationship to check the `departed_at` column, we've lost the ability to check the original row's `created_at` value. Variations that use `_exists` suffer from the same problem; currently you cannot use a row's dynamic value in permissions.

## Enter the View

To make our permission problem with Hasura, we need some **precomputed** version of our messages table. One that doesn't need `(messages).group.memberships.departed_at / created_at` to work. Even better, if we have one row for every recipient of a message, we can then just ask *that* version of the data instead.

One option would be to denormalize the data, creating copies of the message for every recipient, but doing so will create integrity problems down the line. Enter the view:

```sql
CREATE VIEW message_recipients AS
  SELECT msg.id, msg.group_id, mbr.user_id as recipient
  FROM (
    (messages msg JOIN groups grp ON (grp.id = msg.group_id))
      JOIN memberships mbr ON (mbr.group_id = grp.id)
  )
  WHERE (
    mbr.departed_at IS NULL OR msg.created_at <= mbr.departed_at
  );
```

The end result will be a view with a row for every message recipient, containing a message id and a user id, filtered such that the member is either active in the group (`NULL`) or their `departed_at` happened after the message's `created_at` timestamp. Now our Hasura permissions will work as expected. When we tell Hasura there is a **manual relationship** between `messages` and our new `message_recipients` view, it will link both objects together. We can then use the new relationship in our permission JSON.  🎉

```json
{
  "_or": {
    "from_id": {
      "_eq": "X-Hasura-User-Id"
    },
    "message_recipients": {
      "recipient": {
        "_eq": "X-Hasura-User-Id"
      }
    }
  }
}
```

## Where Views Go Wrong?

In practice, large datasets will make conditional permissions via views expensive. This is because a view is just a prior known subquery under the hood. There are optimizations you can do with heavy read data such as a [materialized view](https://www.postgresql.org/docs/9.3/rules-materializedviews.html), but those situations imply you’ve got both a substantial amount of traffic and a substantial amount of data. Before you throw all of this away though, take advantage of Hasura’s “Analyze” button. Missing an important index can have a huge performance cost and for most applications, views are perfectly ok.

And if you do outgrow the view, then you should consider denormalizing the data. Hasura Actions can take care of the denormalization for you. In our final example, there’s nothing that stops you from writing to a recipient table in response to new messages and users joining the group. For now though, you probably won’t need those optimizations.

Ultimately, views give Hasura’s rule engine superpowers. If you can write a query that selects for your permission, you can make a view. And if you can make a view, Hasura can understand it.

## Additional Reading

- [Hasura Row + Column Permission Tuples (GitHub)](https://github.com/hasura/graphql-engine/issues/3442)
- [Multiple Column + Row Permissions for the Same Role (Hasura Docs)](https://hasura.io/docs/latest/graphql/core/auth/authorization/role-multiple-rules.html)
- [Create View (Postgres Docs)](https://www.postgresql.org/docs/current/sql-createview.html)
- [Object and Array Relationships (Hasura Docs)](https://hasura.io/docs/latest/graphql/core/databases/postgres/queries/nested-object-queries.html)


How you can solve complex Hasura permissions using postgres views

CREATE TABLE users (id uuid PRIMARY KEY);

CREATE TABLE groups (id uuid primary key);

CREATE TABLE memberships (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  joined_at timestamp NOT NULL DEFAULT now(),
  departed_at timestamp
);

CREATE TABLE messages (
  id uuid PRIMARY KEY,
  from_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  created_at timestamp NOT NULL DEFAULT now(),
  content text
);


CREATE VIEW message_recipients AS
  SELECT msg.id, msg.group_id, mbr.user_id as recipient
  FROM (
    (messages msg JOIN groups grp ON (grp.id = msg.group_id))
      JOIN memberships mbr ON (mbr.group_id = grp.id)
  )
  WHERE (
    mbr.departed_at IS NULL OR msg.created_at <= mbr.departed_at
  );


Databases

2022

Designing A Time-Based Queue for Serverless

2021

Relay Cursors for Database Queries

Complex Hasura Permissions Via Views

2006

Transacting Items on Gaia Online

Transacting Items on Gaia Online, Part 2