FreedInk/db_definition.sql

create table
  public."Users" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    address text not null,
    username text null,
    idc text null,
    public_key text null,
    constraint Users_pkey primary key (id),
    constraint Users_address_key unique (address),
    constraint Users_username_key unique (username)
  ) tablespace pg_default;

create table
  public."Blogs" (
    id uuid not null default gen_random_uuid (),
    created_at timestamp with time zone not null default now(),
    title text not null,
    description text null,
    slug text not null,
    constraint Blogs_pkey primary key (id),
    constraint Blogs_slug_key unique (slug),
    constraint Blogs_title_key unique (title),
    constraint Blogs_slug_check check ((length(slug) < 500))
  ) tablespace pg_default;

create table
  public."BlogPosts" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    blog_id uuid not null,
    current_version bigint null,
    status public.post_status not null default 'draft'::post_status,
    constraint BlogPosts_pkey primary key (id),
    constraint BlogPosts_blog_id_fkey foreign key (blog_id) references "Blogs" (id) on update cascade on delete cascade,
    constraint BlogPosts_current_version_fkey foreign key (current_version) references "BlogPostVersions" (id) on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public."BlogPostVersions" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    post_id bigint not null,
    title text not null,
    content text not null,
    version text not null,
    proof json not null,
    slug text not null,
    published_at timestamp with time zone null,
    status public.post_status not null default 'draft'::post_status,
    constraint BlogPostVersions_pkey primary key (id),
    constraint BlogPostVersions_post_id_fkey foreign key (post_id) references "BlogPosts" (id) on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public."BlogPostVersionReview" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    post_version_id bigint not null,
    approved boolean not null default false,
    proof json not null,
    comment text null,
    constraint BlogPostVersionReview_pkey primary key (id),
    constraint BlogPostVersionReview_post_version_id_fkey foreign key (post_version_id) references "BlogPostVersions" (id) on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public."BlogOwners" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    blog_id uuid not null,
    owner_id bigint not null,
    constraint BlogOwners_pkey primary key (id),
    constraint BlogOwners_blog_id_fkey foreign key (blog_id) references "Blogs" (id) on update cascade on delete cascade,
    constraint BlogOwners_owner_id_fkey foreign key (owner_id) references "Users" (id) on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public."BlogAuthors" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    blog_id uuid not null,
    author_id bigint not null,
    constraint BlogAuthors_pkey primary key (id),
    constraint BlogAuthors_author_id_fkey foreign key (author_id) references "Users" (id) on update cascade on delete cascade,
    constraint BlogAuthors_blog_id_fkey foreign key (blog_id) references "Blogs" (id) on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public."BlogReviewers" (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    blog_id uuid not null,
    reviewer_id bigint not null,
    constraint BlogReviewers_pkey primary key (id),
    constraint BlogReviewers_blog_id_fkey foreign key (blog_id) references "Blogs" (id) on update cascade on delete cascade,
    constraint BlogReviewers_reviewer_id_fkey foreign key (reviewer_id) references "Users" (id) on update cascade on delete cascade
  ) tablespace pg_default;

public enum post_status	(draft, under_review, published, rejected)