​
Introduction

When auto-incrementing / sequential IDs are used on the backend database, the ID can only be generated on the backend database, and not on the client while offline. To handle this, you can use a secondary UUID on the client, then map it to a sequential ID when performing an update on the backend database. This allows using a sequential primary key for each record, with a UUID as a secondary ID.
This mapping must be performed wherever the UUIDs are referenced, including for every foreign key column.
To illustrate this, we will use the React To-Do List demo app and modify it to use UUIDs on the client and map them to sequential IDs on the backend database (Supabase in this case).

​
Overview

Before we get started, let’s outline the changes we will have to make:
1

Schema

Update the lists and todos tables
2

Create SQL triggers

Add two triggers that will map the UUID to the integer ID and vice versa.
3

Update Sync Rules

Update the Sync Rules to use the new integer ID instead of the UUID column.
4

Update client to use UUIDs.

The following components/files will have to be updated:
  • Files:
    • AppSchema.ts
    • fts_setup.ts
    • SupabaseConnector.ts
  • Components:
    • lists.tsx
    • page.tsx
    • SearchBarWidget.tsx
    • TodoListsWidget.tsx

​
Schema

In order to map the UUID to the integer ID, we need to update the
  • lists table by adding a uuid column, which will be the secondary ID, and
  • todos table by adding a uuid column, and a list_uuid foreign key column which references the uuid column in the lists table.
create table public.lists (
    id serial,
    uuid uuid not null unique,
    created_at timestamp with time zone not null default now(),
    name text not null,
    owner_id uuid not null,
    constraint lists_pkey primary key (id),
    constraint lists_owner_id_fkey foreign key (owner_id) references auth.users (id) on delete cascade
) tablespace pg_default;

create table public.todos (
    id serial,
    uuid uuid not null unique,
    created_at timestamp with time zone not null default now(),
    completed_at timestamp with time zone null,
    description text not null,
    completed boolean not null default false,
    created_by uuid null,
    completed_by uuid null,
    list_id int not null,
    list_uuid uuid not null,
    constraint todos_pkey primary key (id),
    constraint todos_created_by_fkey foreign key (created_by) references auth.users (id) on delete set null,
    constraint todos_completed_by_fkey foreign key (completed_by) references auth.users (id) on delete set null,
    constraint todos_list_id_fkey foreign key (list_id) references lists (id) on delete cascade,
    constraint todos_list_uuid_fkey foreign key (list_uuid) references lists (uuid) on delete cascade
) tablespace pg_default;
With the schema updated, we now need a method to synchronize and map the list_id and list_uuid in the todos table, with the id and uuid columns in the lists table. We can achieve this by creating SQL triggers.

​
Create SQL Triggers

We need to create triggers that can look up the integer ID for the given UUID and vice versa. These triggers will maintain consistency between list_id and list_uuid in the todos table by ensuring that they remain synchronized with the id and uuid columns in the lists table; even if changes are made to either field. We will create the following two triggers that cover either scenario of updating the list_id or list_uuid in the todos table:
  1. update_integer_id, and
  2. update_uuid_column

Trigger 1: update_integer_id

Trigger 2: update_uuid_column

We now have triggers in place that will handle the mapping for our updated schema and can move on to updating the Sync Rules to use the UUID column instead of the integer ID.

​
Update Sync Rules

As sequential IDs can only be created on the backend database, we need to use UUIDs in the client. This can be done by updating both the parameters and data queries to use the new uuid columns. The parameters query is updated by removing the list_id alias (this is removed to avoid any confusion between the list_id column in the todos table), and the data query is updated to use the uuid column as the id column for the lists and todos tables. We also explicitly define which columns to select, as list_id is no longer required in the client.
sync_rules.yaml
bucket_definitions:
  user_lists:
    # Separate bucket per todo list
    parameters: select id from lists where owner_id = request.user_id()
    data:
    # Explicitly define all the columns
      - select uuid as id, created_at, name, owner_id from lists where id = bucket.id
      - select uuid as id, created_at, completed_at, description, completed, created_by, list_uuid from todos where list_id = bucket.id
With the Sync Rules updated, we can now move on to updating the client to use UUIDs.

​
Update Client to Use UUIDs

With our Sync Rules updated, we no longer have the list_id column in the todos table. We start by updating AppSchema.ts and replacing list_id with list_uuid in the todos table.
AppSchema.ts
const todos = new Table(
  {
    list_uuid: column.text,
    created_at: column.text,
    completed_at: column.text,
    description: column.text,
    created_by: column.text,
    completed_by: column.text,
    completed: column.integer
  },
  { indexes: { list: ['list_uuid'] } }
);
The uploadData function in SupabaseConnector.ts needs to be updated to use the new uuid column in both tables.
SupabaseConnector.ts
export class SupabaseConnector extends BaseObserver<SupabaseConnectorListener> implements PowerSyncBackendConnector {
    // other code

    async uploadData(database: AbstractPowerSyncDatabase): Promise<void> {
        // other code
        try {
            for (const op of transaction.crud) {
                lastOp = op;
                const table = this.client.from(op.table);
                let result: any;
                switch (op.op) {
                    case UpdateType.PUT:
                        const record = { ...op.opData, uuid: op.id };
                        result = await table.upsert(record);
                        break;
                    case UpdateType.PATCH:
                        result = await table.update(op.opData).eq('uuid', op.id);
                        break;
                    case UpdateType.DELETE:
                        result = await table.delete().eq('uuid', op.id);
                        break;
                }
            }
        } catch (ex: any) {
          // other code
        }
    }
}
For the remaining files, we simply need to replace any reference to list_id with list_uuid.
fts_setup.ts
export async function configureFts(): Promise<void> {
  await createFtsTable('lists', ['name'], 'porter unicode61');
  await createFtsTable('todos', ['description', 'list_uuid']);
}
page.tsx
const TodoEditSection = () => {
    // code
    const { data: todos } = useQuery<TodoRecord>(
        `SELECT * FROM ${TODOS_TABLE} WHERE list_uuid=? ORDER BY created_at DESC, id`,
        [listID]
    );

    // code
    const createNewTodo = async (description: string) => {
        // other code
        await powerSync.execute(
          `INSERT INTO
                    ${TODOS_TABLE}
                        (id, created_at, created_by, description, list_uuid)
                    VALUES
                        (uuid(), datetime(), ?, ?, ?)`,
          [userID, description, listID!]
        );
    }
}
TodoListWidget.tsx
export function TodoListsWidget(props: TodoListsWidgetProps) {
    // hooks and navigation

    const { data: listRecords, isLoading } = useQuery<ListRecord & { total_tasks: number; completed_tasks: number }>(`
        SELECT
            ${LISTS_TABLE}.*, COUNT(${TODOS_TABLE}.id) AS total_tasks, SUM(CASE WHEN ${TODOS_TABLE}.completed = true THEN 1 ELSE 0 END) as completed_tasks
        FROM
            ${LISTS_TABLE}
        LEFT JOIN ${TODOS_TABLE}
            ON  ${LISTS_TABLE}.id = ${TODOS_TABLE}.list_uuid
        GROUP BY
            ${LISTS_TABLE}.id;
      `);

    const deleteList = async (id: string) => {
        await powerSync.writeTransaction(async (tx) => {
            // Delete associated todos
            await tx.execute(`DELETE FROM ${TODOS_TABLE} WHERE list_uuid = ?`, [id]);
            // Delete list record
            await tx.execute(`DELETE FROM ${LISTS_TABLE} WHERE id = ?`, [id]);
        });
    };
}
SearchBarWidget.tsx
export const SearchBarWidget: React.FC<any> = () => {
    const handleInputChange = async (value: string) => {
        if (value.length !== 0) {
            let listsSearchResults: any[] = [];
            const todoItemsSearchResults = await searchTable(value, 'todos');
            for (let i = 0; i < todoItemsSearchResults.length; i++) {
                const res = await powersync.get<ListRecord>(`SELECT * FROM ${LISTS_TABLE} WHERE id = ?`, [
                    todoItemsSearchResults[i]['list_uuid']
                ]);
                todoItemsSearchResults[i]['list_name'] = res.name;
            }
            if (!todoItemsSearchResults.length) {
                listsSearchResults = await searchTable(value, 'lists');
            }
            const formattedListResults: SearchResult[] = listsSearchResults.map(
              (result) => new SearchResult(result['id'], result['name'])
            );
            const formattedTodoItemsResults: SearchResult[] = todoItemsSearchResults.map((result) => {
                return new SearchResult(result['list_uuid'], result['list_name'] ?? '', result['description']);
            });
            setSearchResults([...formattedTodoItemsResults, ...formattedListResults]);
        }
    };
}