user_booklet_clip_rbac.sql

CREATE TABLE Users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255)
);

CREATE TABLE Booklets (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE Clips (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TYPE RoleType AS ENUM ('EDITOR', 'COMMENTER', 'VIEWER', 'OWNER');

CREATE TABLE UserBookletRoles (
  id SERIAL PRIMARY KEY,
  role RoleType NOT NULL,
  userId INT NOT NULL,
  bookletId INT NOT NULL,
  FOREIGN KEY (userId) REFERENCES Users(id),
  FOREIGN KEY (bookletId) REFERENCES Booklets(id),
  UNIQUE (userId, bookletId)
);

CREATE TABLE UserClipRoles (
  id SERIAL PRIMARY KEY,
  role RoleType NOT NULL,
  userId INT NOT NULL,
  clipId INT NOT NULL,
  FOREIGN KEY (userId) REFERENCES Users(id),
  FOREIGN KEY (clipId) REFERENCES Clips(id),
  UNIQUE (userId, clipId)
);