torus-api/sql/users.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- UUID v7: 48-bit ms timestamp | 4-bit version (0111) | 12-bit random | 2-bit variant (10) | 62-bit random
CREATE OR REPLACE FUNCTION gen_uuid_v7()
RETURNS uuid
LANGUAGE plpgsql
AS $$
DECLARE
    unix_ms    bigint;
    uuid_bytes bytea;
BEGIN
    unix_ms    := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint;
    uuid_bytes := gen_random_bytes(16);
    uuid_bytes := overlay(uuid_bytes PLACING substring(int8send(unix_ms) FROM 3) FROM 1 FOR 6);
    uuid_bytes := set_byte(uuid_bytes, 6, (get_byte(uuid_bytes, 6) & x'0f'::int) | x'70'::int);
    uuid_bytes := set_byte(uuid_bytes, 8, (get_byte(uuid_bytes, 8) & x'3f'::int) | x'80'::int);
    RETURN encode(uuid_bytes, 'hex')::uuid;
END;
$$;

CREATE TABLE users (
    id         UUID        PRIMARY KEY DEFAULT gen_uuid_v7(),
    uuid_key   UUID        NOT NULL    DEFAULT gen_random_uuid(),
    email      TEXT        NOT NULL UNIQUE,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL    DEFAULT now(),
    pass_hash  TEXT        NOT NULL
);