postgres

Separating entity identity from versioned content in Postgres

Initial setup

Over the last months, I’ve been building the MVP for ophlyne Activities, a platform where hosts can create workshops and guests are able to book them. My backend is a combination of Next.js and Supabase, where Supabase is used for Auth, Storage and its Database (PostgreSQL). My first attempt at creating the schema for persisting workshops was very straightforward, I had a workshops table, which holds the “content”, a.k.a. what the hosts enter about the workshop they want to offer.

CREATE TABLE IF NOT EXISTS workshops (
	id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
	title text NOT NULL,
	...
);

Connected to this table are several other tables, which represent when workshops are being offered (workshop_time_slots) and who is booking them (workshop_bookings).

-- tables/workshop_time_slots.sql
CREATE TABLE IF NOT EXISTS workshop_time_slots (
	id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
	workshop_id uuid NOT NULL,
	...
);

-- relationships/workshop_time_slots.sql
ALTER TABLE ONLY workshop_time_slots
ADD CONSTRAINT workshop_time_slots_workshop_id_fkey FOREIGN KEY (
    workshop_id
) REFERENCES workshops (id) ON UPDATE CASCADE ON DELETE SET NULL;
-- tables/workshop_bookings.sql
CREATE TABLE IF NOT EXISTS workshop_bookings (
	id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
	time_slot_id uuid NOT NULL,
	user_id uuid NOT NULL,
	...
);

-- relationships/workshop_bookings.sql
ALTER TABLE ONLY workshop_bookings
ADD CONSTRAINT workshop_bookings_time_slot_id_fkey FOREIGN KEY (
    time_slot_id
) REFERENCES workshop_time_slots (id);

ALTER TABLE ONLY workshop_bookings
ADD CONSTRAINT workshop_bookings_user_id_fkey FOREIGN KEY (
    user_id
) REFERENCES auth.users (id) ON DELETE CASCADE;

The general setup is quite simple, when someone has booked one or more tickets/seats for a particular workshop, a row in workshop_bookings is created, with a foreign key to workshop_time_slots, which in turn has a foreign key to workshops. Information about what a user has booked can be looked up with a simple join, so what’s the problem?

Updates and traceability

The problems appear once you think about what happens when a host decides to update any attributes of a workshop, which also include things like until when can bookings be canceled without penalty, changes to these attributes must not impact any already established contracts (i.e. purchasing agreement). The database relationships don’t reflect the actual relationship anymore. A guest booked a certain workshop, with certain attributes, a certain price, because the combination of all of these things were interesting enough to warrant spending money.

The logical tool you’d need is a snapshot at the time of purchase, to not lose any information when content is updated. Actually doing snapshots of the workshops table on each purchase would create an enormous amount of duplicate data and a lot of unnecessary overhead. Instead, we can effectively implement snapshots on “updates” by the host, by making the content data immutable.

The Solution

What I ultimately landed on, was splitting the workshops table into two separate tables, with distinct responsibilities. The workshops table is stripped of all “content”, and is purely responsible for meta data.

-- tables/workshops.sql
CREATE TABLE IF NOT EXISTS workshops (
	id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    slug text DEFAULT nanoid(8) NOT NULL,
    stripe_product_id text,
    status workshop_status_type NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,

    host_id uuid NOT NULL,

    -- Pointer to the currently published workshop version
    current_version_id uuid
);

-- relationships/workshops.sql
ALTER TABLE ONLY workshops
ADD CONSTRAINT workshops_host_id_fkey FOREIGN KEY (
    host_id
) REFERENCES user_profiles (id);

ALTER TABLE ONLY workshops
ADD CONSTRAINT fk_workshops_current_version FOREIGN KEY (
    current_version_id
) REFERENCES public.workshop_versions (id) ON DELETE SET NULL;

All content fields are removed, only identity and ownership data remains, likestripe_product_id, which will never change and status, which controls visibility of the workshop. What is new, is the current_version_id column, which references the newly created workshop_versions table, where the content is stored.

CREATE TABLE IF NOT EXISTS workshop_versions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    workshop_id uuid NOT NULL,

    title text NOT NULL,
	...

    -- Version lifecycle
    version_no integer NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

As you can see, this table does not have an updated_at column, as inserts into this table are treated as immutable, which means that “updates” by a host to their workshop, are not actual SQL level UPDATE statements, but instead new inserts into the workshop_versions table with a subsequent update of the workshops.current_version_id column. To guard against race conditions and other possible issues in this two-step process, the update to workshops.current_version_id happens inside a trigger, which makes it an atomic operation.

CREATE OR REPLACE FUNCTION public.set_current_version_on_publish()
RETURNS trigger AS $$
BEGIN
    UPDATE public.workshops
    SET current_version_id = NEW.id, updated_at = now()
    WHERE id = NEW.workshop_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER workshop_versions_set_current_on_publish
AFTER INSERT ON public.workshop_versions
FOR EACH ROW
EXECUTE FUNCTION public.set_current_version_on_publish();

With this in place, this the following update can be made to the workshop_bookings table:

ALTER TABLE workshop_bookings 
ADD COLUMN workshop_version_id uuid;

ALTER TABLE ONLY workshop_bookings
ADD CONSTRAINT fk_bookings_version FOREIGN KEY (
    workshop_version_id
) REFERENCES workshop_versions (id);

To ensure this reference is truly immutable after booking is created, a trigger enforces it at the database level. This means no application-level bug or future migrations can silently alter the reference to a different version.

CREATE OR REPLACE FUNCTION public.prevent_workshop_version_id_change()
RETURNS trigger AS
$$
BEGIN
    IF NEW.workshop_version_id IS DISTINCT FROM OLD.workshop_version_id THEN
        RAISE EXCEPTION 'workshop_version_id is immutable after creation';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER prevent_workshop_version_id_change
BEFORE UPDATE OF workshop_version_id
ON public.workshop_bookings
FOR EACH ROW
EXECUTE FUNCTION public.prevent_workshop_version_id_change();

This design allows us to track under which terms and conditions a guest made a purchase, and no information is lost while keeping the overhead minimal.

← Back to blog