Move a table from UUID to integer ids

A table's primary-key type is fixed once the table exists, and a table can't be renamed. To give existing records integer ids (1, 2, 3, ...), you copy them into a new table whose key is "type": "integer", then repoint everything that used the old ids.

The examples move orders (UUID id) to orders_v2 (integer id), with one child table, order_items, whose order_id references orders.

Protect stored data

Affected resource and cascade: Every record id changes. Record URLs, foreign keys, graph edges, policies, event subscribers and any id stored outside TaruviBase (other systems, emails, bookmarks) still hold the old UUIDs.
Reversibility: The old table stays untouched until the last step, so you can stop at any point before you delete it.
Authorization: Schema changes require an organization owner or admin, or another cloud user with access to the site.
Backup or export: Export orders and every child table first: open the table's menu in Datatables and select Export data.
Recovery: Until you delete orders, switch your application back to it. After that, restore from the export.

Integer ids are sequential, so they are easy to guess. Make sure your authorization rules, not id secrecy, protect these records before you switch.

1. Find everything that uses the old ids#

Before you change anything, list:

tables with a foreign key to orders (here, order_items);
graph edges between orders records;
policies, functions, event subscribers and jobs that store or compare an order id;
ids kept outside TaruviBase: links, other services, analytics exports.

2. Create the new table#

Create orders_v2 with the same fields, an integer id, and a legacy_id that keeps each record's old UUID. legacy_id must be unique: the copy matches on it, and you use it to translate old ids later.

{
  "fields": [
    { "name": "id", "type": "integer" },
    { "name": "legacy_id", "type": "string", "format": "uuid", "constraints": { "unique": true } },
    { "name": "customer_name", "type": "string" },
    { "name": "total", "type": "number" }
  ],
  "primaryKey": ["id"]
}

Copy every other field, constraint, index and foreign key from orders. Check Definition: id should show "x-generated": true.

3. Stop writes to the old table#

Rows written to orders after the copy starts are not copied. Put the features that write orders into maintenance mode, or deny create, update and delete on orders in its policy, until step 6.

4. Copy the records#

Upsert each record into orders_v2 with its old id in legacy_id and no id, so the new table generates one. Matching on legacy_id makes the copy safe to run again after a failure.

import os

from taruvi import Client

client = Client(
    api_url=os.environ["TARUVI_SITE_URL"],
    app_slug=os.environ["TARUVI_APP_SLUG"],
    api_key=os.environ["TARUVI_API_KEY"],
    mode="sync",
)

page = 1
while True:
    rows = (
        client.database.from_("orders")
        .sort("created_at").page_size(500).page(page)
        .execute()["data"]
    )
    if not rows:
        break
    copies = [{**row, "legacy_id": row["id"]} for row in rows]
    for copy in copies:
        copy.pop("id")
    client.database.from_("orders_v2").upsert(copies, unique_fields=["legacy_id"]).execute()
    page += 1

Copy system-managed fields such as created_at only if orders_v2 declares them. New ids follow the copy order, so sort by the field that should decide it.

Check that orders and orders_v2 have the same number of records.

5. Repoint the child tables#

For each child table, add an optional integer foreign key to the new table next to the old one:

{ "name": "order_v2_id", "type": "integer" }

{
  "fields": ["order_v2_id"],
  "reference": { "resource": "orders_v2", "fields": ["id"] }
}

Then fill it from legacy_id:

id_map = {}
page = 1
while rows := (
    client.database.from_("orders_v2").page_size(500).page(page).execute()["data"]
):
    id_map.update({row["legacy_id"]: row["id"] for row in rows})
    page += 1

page = 1
while items := (
    client.database.from_("order_items").page_size(500).page(page).execute()["data"]
):
    updates = [
        {"id": item["id"], "order_v2_id": id_map[item["order_id"]]}
        for item in items
        if item["order_id"]
    ]
    if updates:
        client.database.from_("order_items").update(updates).execute()
    page += 1

Check that every order_items row with an order_id now has an order_v2_id. Recreate graph edges between orders with the new ids the same way.

6. Switch your application#

Release code that reads and writes orders_v2 and order_v2_id. For old links that still carry a UUID, look the record up with ?legacy_id=<uuid> and redirect to its new id. Update policies, functions, event subscribers and jobs found in step 1, then allow writes again.

7. Remove the old table#

Once nothing uses the old ids:

Remove order_id and its foreign key from each child table.
Optionally, in a second update, rename order_v2_id to order_id: name the field order_id, add "x-rename-from": "order_v2_id", and use order_id in its foreign key. The rename must come after step 1, because a field can't take the name of a field that is still in the table.
Delete orders. Remove every foreign key to it first, including those in child tables you no longer use.

Keep legacy_id for as long as old links or other systems may send a UUID.

Continue with: