REST API vs upserts and "updeletes"

Question

I am building an API that (I am deliberately simplifying the schema below to only focus on what is questionable):

I have a table that roughly looks like this:

CREATE TABLE IF NOT EXISTS some_table
(
    id              INTEGER GENERATED ALWAYS AS IDENTITY                                   NOT NULL PRIMARY KEY,
    user_ids        TEXT[]                                                                 NOT NULL,
    field_1         TEXT                                                                   NOT NULL,
    field_2         TEXT                                                                   NOT NULL,
    field_3         TEXT                                                                   NOT NULL,
    hash_id         TEXT GENERATED ALWAYS AS ( MD5(field_1 || field_2 || field_3)) STORED UNIQUE NOT NULL
)

The API is a bit trickier than a conventional CRUD in that:

(1) Inserting into the table depends on whether md5(field_1||field_2||field_3) already exists. If it does, I need to append the value user_id to the array field user_ids. Else, insert the row.

(2) Deleting a row also depends on the state of user_ids. Actually, my current implementation makes the database handle deletions in that there is a trigger that acts on updates and deletes rows whenever cardinality(user_ids) = 0.

CREATE OR REPLACE FUNCTION delete_row() RETURNS trigger AS
$$
BEGIN
    IF tg_op = 'UPDATE' THEN
        DELETE FROM some_table WHERE id = NEW.id;
        RETURN NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER some_table_delete_row
    AFTER UPDATE
    ON some_table
    FOR EACH ROW
    WHEN (CARDINALITY(NEW.user_ids) = 0)
EXECUTE PROCEDURE delete_row();

As you can see, there is no traditional deleting. What really happens is removing items from user_ids until the length of the array is 0 and then the database will autoremove the row.

1. I think that the PUT method is the best match for how I want to effectively implement upserts.

2. It's trickier with DELETE/PUT for decrementing user_ids. Ideally, that looks likes PATCH in that only one field is modified at a time and nothing is allowed to be deleted manually.

3. Using an auto-generated hash_id value is convenient. That said, I am not sure whether it's the best option when I think of how deletes should work. The endpoint for that is base_url/items/{hash_id}, but in this case I will also need to calculate the hashed value in code or, as another option, just always pass the object in the request so that I can do WHERE hash_id = md5($field_1 || $field_2 || $field_3).

What do you think?

Answer 1

Change your table. Make one row per user and get rid of this weird hash thing.

CREATE TABLE IF NOT EXISTS better_some_table
(
    id              INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
    user_id         TEXT NOT NULL,
    field_1         TEXT NOT NULL,
    field_2         TEXT NOT NULL,
    field_3         TEXT NOT NULL,
    UNIQUE INDEX (field_1, field_2, field_3, user_id)
)

Now adding a user is always adding a row, and deleting a user is always deleting a row.

If you need the hash_id you can just calculate it.

If you need to know all the users with the same field_1,2,3 you can just ask for them:

SELECT user_id FROM better_some_table WHERE field_1=? AND field_2=? AND field_3=?

Don't worry about the database doing too much work. Because you have a suitable index, it will optimize this query to jump directly to the needed field_1,2,3 values and then read the user_ids sequentially.

The downside of this design is that field_1,2,3 are duplicated. Depending on what they represent this new design may be denormalized (in a bad way). If they're really big values it may also waste storage space. If either of those is a problem then you might consider a different design - one that stores field_1,2,3 in a different table with only one row per unique combination.

Answer 2

Without knowing your requirements completely it's hard to tell but my feeling is that you want something like

• One table for the unique (determined by hash) set of fields
• A list of users that have those fields.

So store the fields in one table and the users in the other.
Your API :

• adds any incoming fields that it has not seen before (via the hash)
• adds a new user IDs it finds to the users_to_some_table
• updates an existing user ID with the hash (if it's changed).
• If you like you can remove if some_table if it has become orphaned (no users reference it).

Pseudo-schema here:

    CREATE TABLE IF NOT EXISTS some_table
    (
        field_1   TEXT  NOT NULL,
        field_2   TEXT  NOT NULL,
        field_3   TEXT  NOT NULL,
        hash_id   TEXT GENERATED ALWAYS AS ( MD5(field_1 || field_2 || field_3)) STORED 
                       PRIMARY KEY
    )

  CREATE TABLE IF NOT EXISTS users_to_some_table
    (
        user_id  TEXT PRIMARY KEY,    
        hash_id  FORIEGN KEY REFERENCES some_table(hash_ID)
    )