beteran-server-service/migrations/202206191200_role/up.sql

CREATE TABLE IF NOT EXISTS roles (
  id              UUID DEFAULT uuid_generate_v4(),
  parent_id       UUID,
  name            TEXT NOT NULL,
  key             TEXT NOT NULL,
  description     TEXT,
  can_use         BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      BIGINT NOT NULL DEFAULT (extract(epoch from now()) * 1000),
  updated_at      BIGINT NOT NULL DEFAULT (extract(epoch from now()) * 1000),
  PRIMARY KEY (id),
  UNIQUE (key),
  CONSTRAINT fk_roles_parent_id
    FOREIGN KEY(parent_id) 
	    REFERENCES roles(id)
);

CREATE UNIQUE INDEX uidx_roles_key ON roles (key);
CREATE INDEX idx_roles_parent_id ON roles (parent_id);


-- trigger (updated_at)
CREATE TRIGGER tg_roles_updated_at
  BEFORE UPDATE
  ON roles
  FOR EACH ROW
  EXECUTE PROCEDURE update_updated_at_column();