debt-pirate/api/migrations/20231221181946_create-tables.up.sql

CREATE FUNCTION uuidv7() RETURNS uuid
AS $$
  -- Replace the first 48 bits of a uuidv4 with the current
  -- number of milliseconds since 1970-01-01 UTC
  -- and set the "ver" field to 7 by setting additional bits
  SELECT encode(
    set_bit(
      set_bit(
        overlay(uuid_send(gen_random_uuid()) placing
	  substring(int8send((extract(epoch FROM clock_timestamp())*1000)::bigint) FROM 3)
	  FROM 1 FOR 6),
	52, 1),
      53, 1), 'hex')::uuid;
$$ LANGUAGE sql volatile;


CREATE TYPE
	public.status
AS ENUM
	('Active', 'Unverified', 'Removed', 'Quarantined');

CREATE TYPE
	public.account_type
AS ENUM
	('Asset', 'Equity', 'Expense', 'Liability', 'Revenue');

CREATE TYPE
	public.entry_type
AS ENUM
	('Debit', 'Credit');

CREATE TYPE
	public.permission_category
AS ENUM
	('Account', 'Budget');

CREATE TABLE IF NOT EXISTS
	public.user (
        id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        email TEXT NOT NULL,
		password TEXT NOT NULL,
        name TEXT NOT NULL,
        status status NOT NULL DEFAULT 'Unverified',
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
        updated_at TIMESTAMP WITH TIME ZONE NULL
    );

CREATE UNIQUE INDEX IF NOT EXISTS user_email_uniq_idx ON public.user(email);

CREATE TABLE IF NOT EXISTS
	public.permission (
		id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
		category permission_category NOT NULL,
		name TEXT NOT NULL,
		value INT NOT NULL,
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);

CREATE INDEX IF NOT EXISTS permission_category_idx ON public.permission(category);
CREATE INDEX IF NOT EXISTS permission_category_name_idx ON public.permission(category, name);

INSERT INTO
	public.permission (
		category,
		name,
		value
	)
	VALUES
		('Account', 'View', 1),
		('Account', 'Edit', 2),
		('Account', 'Delete', 4),
		('Account', 'Grant', 8),
		('Budget', 'View', 1),
		('Budget', 'Edit', 2),
		('Budget', 'Delete', 4),
		('Budget', 'Grant', 8);

CREATE TABLE IF NOT EXISTS
	public.account (
		id UUID PRIMARY KEY DEFAULT uuidv7(),
		account_type account_type NOT NULL,
		name TEXT NOT NULL,
		description TEXT NULL,
		currency_code TEXT NOT NULL,
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);

CREATE TABLE IF NOT EXISTS
	public.user_account_permission (
		id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
		user_id INT NOT NULL REFERENCES public.user(id),
		account_id UUID NOT NULL REFERENCES public.account(id),
		permission_id INT NOT NULL REFERENCES public.permission(id),
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);

CREATE INDEX IF NOT EXISTS user_account_permission_user_id_idx ON public.user_account_permission(user_id);
CREATE INDEX IF NOT EXISTS user_account_permission_account_id_idx ON public.user_account_permission(account_id);

CREATE TABLE IF NOT EXISTS
	public.budget (
		id UUID PRIMARY KEY DEFAULT uuidv7(),
		name TEXT NOT NULL,
		description TEXT NULL,
		icon TEXT NULL,
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);

CREATE TABLE IF NOT EXISTS
	public.user_budget_permission (
		id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
		user_id INT NOT NULL REFERENCES public.user(id),
		budget_id UUID NOT NULL REFERENCES public.budget(id),
		permission_id INT NOT NULL REFERENCES public.permission(id),
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);

CREATE INDEX IF NOT EXISTS user_budget_permission_user_id_idx ON public.user_budget_permission(user_id);
CREATE INDEX IF NOT EXISTS user_budget_permission_budget_id_idx ON public.user_budget_permission(budget_id);

CREATE TABLE IF NOT EXISTS
	public.transaction (
		id UUID PRIMARY KEY DEFAULT uuidv7(),
		description TEXT NOT NULL,
		budget_id UUID NOT NULL REFERENCES public.budget(id),
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);

CREATE INDEX IF NOT EXISTS transaction_budget_id_idx ON public.transaction(budget_id);

CREATE TABLE IF NOT EXISTS
	public.transaction_line_item (
		id UUID PRIMARY KEY DEFAULT uuidv7(),
		transaction_id UUID NOT NULL REFERENCES public.transaction(id),
		account_id UUID NOT NULL REFERENCES public.account(id),
		entry_type entry_type NOT NULL,
		value NUMERIC(12, 2) NOT NULL,
		status status NOT NULL DEFAULT 'Active',
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE NULL
	);