Engineering Blog

The Audit Log API

We recently added an endpoint to our public API that allows retrieving audit logs records for a specific project:

{
  "next": "https://api.cloudscale.ch/v1/project-logs?cursor=10225",
  "poll_more": "https://api.cloudscale.ch/v1/project-logs?cursor=10225",
  "results": [
    {
      "ip_address": "185.98.122.110",
      "action": "volume_create",
      "message": "Volume 'cache' has been created",
      "timestamp": "2025-09-03T11:15:19.391447Z",
      ...
    },
    ... // Up to 199 more records.
  ]
}

All actions performed in our control panel by logged-in users or using API tokens are recorded in the audit log. Because the audit log can contain an unbounded number of records, the API uses pagination to return the results.

Polling in the Audit Log API

The audit log API allows a client to poll for new records that were added since the last request. For this reason, it returns a URL in the response's poll_more field. Requesting that URL will return all records (possibly spread across multiple pages) that have not yet been returned.

The URLs in the poll_more field have a cursor parameter (I'll call those URLs cursor URLs). This parameter simply contains the primary key of the database row of the previous request's last record. In our case, the primary key column is called id:

dev=# \d log
                                 Table "public.log"
   Column   |           Type           | Nullable |             Default
------------+--------------------------+----------+----------------------------------
 id         | integer                  | not null | generated by default as identity
 ip_address | inet                     |          |
 action     | character varying(50)    | not null |
 timestamp  | timestamp with time zone | not null |
[some more fields omitted]
Indexes:
    "log_pkey" PRIMARY KEY, btree (id)

When the application receives a request for a cursor URL, it uses the cursor parameter to determine the set of records to return to the client.

The API guarantees that when polling for records using the poll_more URL, no records will be skipped or returned twice. This is easy to accomplish if the set of records have IDs that are monotonically increasing (foreshadowing): simply return all records with a primary key bigger than cursor, sorted by the primary key.

PostgreSQL Sequence Generators

As you have seen in the previous section, our primary key column id is set up to be filled in by Postgres automatically. The important bit is the generated by default as identity, which is documented in CREATE TABLE. This uses an implicitly created sequence generator, which is the standard way in Postgres to automatically fill an integer primary key column. Sequence generators are documented separately under CREATE SEQUENCE.

The implementation of Postgres' sequence generators needs to fulfill a few requirements:

1. The generated values need to be unique (meaning it can't e.g. use the current time, because due to granularity or system clock changes, it could return the same value twice).
2. The generated values have to fit in an integer or bigint column (meaning e.g. random UUIDs can't be used).
3. It needs to be fast (meaning it can't search the existing data for an unused ID).
4. It needs to be thread-safe without using locks that last until the end of the transaction (meaning select max(id) + 1 from ... can't be used).

To accomplish this, Postgres basically uses a global integer variable per sequence generator, accessed using a short-lived lock. Each database process can increment the corresponding variable and use the new value (the cache parameter of create sequence can be used to changes this to a more efficient but also more complicated scheme).

This implementation fulfills all the requirements listed above. There's a fifth requirement that the implementation appears to fulfill, but in reality doesn't: That generated IDs appear in increasing order in the database. Look at the following example of two concurrently processed transactions:

In the example, two transactions request a value from the sequence generator and are handed out consecutive values (which are then used to set the id column of rows inserted into our audit log record table). Both transactions commit, but in the reverse order than they requested a value from the sequence generator.

At the time indicated by the bold dashed line, the transaction that used ID "5" has already committed, and its result is visible to other transactions, but the transaction that used ID "4" has not yet committed. After that transaction has also committed, both rows will be visible.

If at the point in time of the bold dashed line, a client would request the most recent audit logs, the result's poll_more URL would have cursor set to "5", because that's the largest value in the id column. Even if the transaction that used ID "4" would later commit, that row would never be returned to the client. This would violate the guarantee that no rows are skipped when polling for records using the poll_more URLs.

Implementing truly Sequential IDs

So we went to look for a solution that matched the following criteria:

• Generate sequential IDs.
  Audit log records need to be assigned IDs in the order in which the transactions are committed, even if transactions are running in parallel. This is required by how we use cursor URLs to allow a client to poll for new records.

• Assign the sequential IDs within the transaction.
  Delaying assigning the sequential IDs to after committing the transaction would prevent requests immediately following the transaction from returning the new audit log records.

• Low contention between transactions.
  Some of our transactions can run for up to a few 100ms. This is because we have to talk to systems external to our control panel application within some transactions. Acquiring locks for the whole duration of the transaction would prevent processing these transactions in parallel, resulting in bad performance.

After looking at a lot of different approaches, we came up with the following solution. The implementation has 4 parts:

• An additional column sequence_id added to our log record table log.
• A sequence generator used to generate values for sequence_id.
• A table log_sequence_id_seq_lock, which is only used for locking and does not store data.
• A deferred trigger on log that populates log.sequence_id.

The implementation lives fully in the database schema. No code in our application that writes audit log records needed to be modified. This is the full implementation (explanations below):

-- Add new column used to store the sequential IDs.
alter table log add column sequence_id integer unique;

-- Sequence generator used to generated values for log.sequence_id.
create sequence log_sequence_id_seq;

-- Table that is only used for locking by
-- log_sequence_id_trigger_fn().
create table log_sequence_id_seq_lock();

-- Trigger function that assigns values to log.sequence_id.
create function log_sequence_id_trigger_fn()
    returns trigger
    language plpgsql
as $$ declare
begin
    -- Prevent the function from running in parallel.
    lock table log_sequence_id_seq_lock in exclusive mode;

    -- Populate sequence_id of the newly inserted row.
    update log l
    set sequence_id = nextval('log_sequence_id_seq')
    where l.id = new.id;

    -- Insert already happened, so no need to return the row.
    return null;
end $$;

-- Trigger that runs log_sequence_id_trigger_fn() at the
-- end of the transaction.
create constraint trigger log_sequence_id_trigger
    after insert on log
    initially deferred
    for each row
execute function log_sequence_id_trigger_fn();

log_sequence_id_trigger_fn() is set up to run whenever a row is inserted into log using a trigger. The trigger is set to deferred, so the function will run at the end of the transaction, instead of immediately when the row is inserted. The functions should complete very quickly, which is important because it acquires a lock that prevents the function from running in parallel.

The lock used by log_sequence_id_trigger_fn() is a relation-level lock on table log_sequence_id_seq_lock. That table is only used for this purpose of locking, no data is stored in that table. An alternative would have been to use Postgres' Advisory Locks, but these are less convenient because they only allow integer values to be used as lock names. Using a table with a descriptive name seemed less obtuse.

After acquiring the lock, log_sequence_id_trigger_fn() uses nextval() to get a value from the sequence generator log_sequence_id_seq and update sequence_id of the inserted row with it. The lock is automatically released when the transaction finally commits.

This sequence generator works exactly the same way as one that gets implicitly created by Postgres when creating a generated by default as identity column. But because of the explicit locking around its usage, the problems mentioned above are prevented.

When processing API requests, the implementation can reliably select records added since the last request using the values of sequence_id.

Conclusion

We've seen how Postgres' sequence generators, which work well in most circumstances, especially when high transaction throughput is necessary, can't directly be used to guarantee sequential IDs on inserted rows. Nonetheless, Postgres has all the tools necessary to implement a solution that provides this guarantee without sacrificing too much in terms of performance or complexity.

---

If you have comments or corrections to share, you can reach our engineers at engineering-blog@cloudscale.ch.