Client assigned IDs

[codyebberson]

At present, the Medplum FHIR server only allows server assigned ID's. The FHIR spec optionally allows client assigned ID's using "Update as Create" (reference).

Topics for discussion:

• Pros/cons of supporting client assigned IDs
• Technical considerations

Pros/Cons

Pros:

• Much easier to support FHIR server mirroring (i.e., system-to-system synchronization)
• Convenience URLs for metadata resources (i.e., StructureDefinition/Patient)
• Enables support for more of the FHIR spec overall

Cons:

• Performance: resource ID will be a TEXT or VARCHAR column, which will inherently have worse performance than UUID
• Database storage: this will increase the index size on disk.
• Migration: there are a number of places throughout the codebase that assume (1) IDs are UUIDs and (2) IDs are globally unique, which will both be invalidated

In my mind, the pros outweigh the cons, and this seems like an inevitable architectural decision. So the question is more "when" than "if".

Technical considerations

If we pursue client assigned IDs, here is a list of required changes:

• Add a separate primary key column - if we use UUID, then this could be the current id value, because they are all server assigned
• Define a new unique index on (projectId, fhirId) - this will be the main index for "read by id" operations
• Make a decision about TEXT vs VARCHAR
  • See the performance tip: "There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column."
  • It would probably be faster to let Postgres do the check rather than nodejs
  • However, it would still require extra checks in the try/catch block, so it's probably a wash
• Update the Redis cache key to explicitly include projectId
• Update lookup tables to use the new primary key value rather than the fhirId
  • This may be the most complicated migration
  • Because the internal database primary key may not be available in the current code paths

Open questions

• Should this be a project-level setting?
  • Boolean, allow client assigned IDs, yes or no
  • HAPI allows "NONE" (no client assigned IDs), "ANY" (any valid value), or "ALPHANUMERIC"
• Should we expose the internal primary ID in the API in any way?
  • My default answer is "no", because it seems like unnecessary complexity and there is no good clean place to put it
  • However, in debugging situations, it can be very useful

Experiments to run

• Should we continue to generate the primary key UUID in code? Or let Postgres generate the PK?
• Should we explicitly set the ASCII collation (C or POSIX) on the resource ID column?
• Would a md4sums index be better?
• What will be the expected performance impact during the migration? This will require replicating approximate size in a staging environment.

Recommended reading

• FHIR id type
• HAPI IdType
• Postgres Character Types
• StackOverflow: What are the pros and cons for choosing a character varying data type for primary key in SQL?
• StackExchange: Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
• Reddit: Any significant performance disadvantage to using uuid as primary key?

[codyebberson]

This will require a series of DB migrations:

1. Add projectId UUID and fhirId TEXT
   a. Start populating on all update operations
   b. fhirId can be populated with id column, because that will be true at this moment in time
   c. projectId cannot be populated directly in database migration because the current value is not directly accessible in a SQL query (it's in compartments and in content, both of which would require processing, see step 2)
   d. Continute to set id and fhirId to the same value
   e. Continue to set project ID in both projectId and compartments
2. Backfill projectId
   a. This will require a full reindex operation
3. Finalize projectId and fhirId columns
   a. Add NOT NULL constraints to projectId and fhirId
   b. Add UNIQUE constraint on the combination of (projectId, fhirId)
   c. Stop setting project ID in compartments
4. Update all queries in server to use fhirId rather than id where applicable
   a. Update the _id special case filters
   b. Update lookup tables to accept resourceId as a parameter (current code assumes that resource.id === the primary key)
5. At this point, we can enable client assigned IDs via API

[codyebberson]

In recent conversation, the team is now exploring using UUID v5 which is a hash of projectId and resource.id.

Due to the complex nature of these migrations, it will require a series of commits and deploys.

Part 1: Add projectId

1. Add projectId UUID
   a. ALTER TABLE [x] ADD COLUMN "projectId" UUID;
   b. Start writing projectId on every write operation
   c. At this point, projectId will be a mix of set and not set
2. Fully populate projectId
   a. UPDATE [x] SET "projectId"="compartments"[1];
   b. At this point, projectId will be fully set
   c. Start using projectId
3. Stop setting project ID in compartments (Optional - not strictly required?)

Part 2: Adopt v5 UUIDs

1. Add v5uuid UUID
   a. In migration, UPDATE [table] SET v5uuid=uuid_generate_v5(namespace uuid, name text)
   b. Add NOT NULL constraint to v5uuid
   c. Start populating v5uuid on all write operations
   d. At this point, id will be all v4 UUIDs
2. Start using v5uuid for all ID operations
   a. Stop using id for all ID operations
   b. At this point, id will be all v4 UUIDs
3. Start writing v5uuid into id
   a. At this point, id will be a mix of v4 and v5, so it cannot be used
4. Copy all v5uuid values into id
   a. UPDATE [table] SET id=v5uuid
   b. Continue writing v5 UUID into both id and v5uuid
   c. At this point, id should be all v5
5. Start using id, stop using v5uuid
6. Drop v5uuid

[leite08]

While this is not implemented, it could be a good idea to validate the incoming ID so the server doesn't respond with 500:

Searchable:

• Unhandled error error
• invalid input syntax for type uuid
• Internal Server Error

[codyebberson]

Thanks @leite08

Fixed in #2263, merged and deployed ✅

[jnak]

We do a lot of importing from other systems so feel free to reach out if you're looking for beta testers and feedback on the implementation.

[mattwiller]

I tried to sketch out a rough migration plan to allow for client-assigned IDs. This would add a new _id column (TEXT type) that will ultimately hold the "display" ID and serve as a search parameter, while migrating all the id values to a new V5 UUID format. Specifically, the "Patient".id column will now be effectively uuid_generate_v5(projectId, 'Patient/'||_id). This serves to namespace IDs to each resource type in a given project, while should ensure that they will be globally unique.

• Initial state: id column with V4 UUID
• Add _id TEXT column (empty)
• Transition state: All writes generate V5 UUID and populate _id column, all reads use _id if present (patching resource JSON on output); add sanity checks to both paths and log warnings if violated
• Migration: Copy existing V4 UUIDs to _id column and generate new V5 UUIDs for existing resources
• Loosen restrictions on IDs to allow arbitrary client-assigned strings for _id

1. Add new _id column to all resource type tables:

ALTER TABLE "Patient" ADD COLUMN IF NOT EXISTS _id TEXT NOT NULL;

2. Start dual-writing new V5 UUIDs alongside legacy V4 UUIDs, and reading display ID opportunistically
   On write:

const resourceID = resource.id ?? randomUUID();
const projectID = resource.meta.project;
await new InsertQuery('Patient', [{
  id: uuid.v5('Patient/'+resourceID, projectID),
  _id: resourceID,
  lastUpdated: new Date(),
  content: resource,
  projectId: projectID,
 // ...
}]).execute(db);

On read:

let resource = JSON.parse(row.content);
if (row._id) {
  resource = { ...resource, id: row._id };
}

4. Copy the existing V4 UUIDs over to the new column, and replace with a new V5 UUID in a data migration:

let hasRows = true;
while hasRows {
  await db.query('BEGIN')
  const rows = await db.query('SELECT id, projectId FROM "Patient" WHERE _id = '' LIMIT 1000 FOR UPDATE');
  for (const row of rows) {
    const newID = uuid.v5('Patient/'+row.id, row.projectId);
    await db.query('UPDATE "Patient" SET id = $1, _id = $2 WHERE id = $2', newID, row.id);
  }
  await db.query('COMMIT')
  hasRows = rows.length > 0;
}