\connect postgres

drop database if exists fox;
create database fox;

\connect fox

create table roles (
  id integer generated by default as identity primary key,
  name text not null
);

create index on roles(name);

create table users (
  id integer generated by default as identity primary key,
  name text not null unique
);

create table user_roles (
  id integer generated by default as identity primary key,
  role_id integer not null references roles(id),
  user_id integer not null references users(id)
);

create unique index on user_roles(role_id, user_id);

create table posts (
  id integer generated by default as identity primary key,
  user_id integer not null references users(id),
  title text not null
);