116 lines
3.4 KiB
SQL
116 lines
3.4 KiB
SQL
CREATE TYPE post_state AS ENUM (
|
|
'draft',
|
|
'published'
|
|
);
|
|
|
|
CREATE TYPE post_format AS ENUM (
|
|
'markdown'
|
|
);
|
|
|
|
CREATE TYPE target_role AS ENUM (
|
|
'production'
|
|
);
|
|
|
|
CREATE TYPE target_type AS ENUM (
|
|
'netlify'
|
|
);
|
|
|
|
CREATE TYPE page_name_provenance AS ENUM (
|
|
'user',
|
|
'title',
|
|
'date'
|
|
);
|
|
|
|
CREATE TYPE page_role AS ENUM (
|
|
'index'
|
|
);
|
|
|
|
CREATE TABLE users (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE sites (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
owner_user_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL UNIQUE,
|
|
title TEXT NOT NULL,
|
|
theme TEXT NOT NULL,
|
|
props JSON NOT NULL,
|
|
|
|
FOREIGN KEY (owner_user_id) REFERENCES users (id)
|
|
);
|
|
|
|
-- Post role is used to describe a specific kind of post, such as a link.
|
|
-- When set, it specifies the layout to use for the page
|
|
CREATE TABLE post_types (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL,
|
|
layout_name TEXT NOT NULL,
|
|
|
|
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE posts (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL,
|
|
title TEXT,
|
|
post_type_id BIGINT,
|
|
format post_format NOT NULL DEFAULT 'markdown',
|
|
body TEXT NOT NULL,
|
|
state post_state NOT NULL,
|
|
props JSON NOT NULL,
|
|
publish_date TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
|
|
FOREIGN KEY (post_type_id) REFERENCES post_types (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE bundles (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
|
|
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE pages (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL,
|
|
bundle_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
name_provenance page_name_provenance NOT NULL,
|
|
format post_format NOT NULL DEFAULT 'markdown',
|
|
title TEXT,
|
|
post_type_id BIGINT,
|
|
body TEXT NOT NULL,
|
|
state post_state NOT NULL,
|
|
props JSON NOT NULL,
|
|
role page_role,
|
|
publish_date TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
|
|
UNIQUE (bundle_id, name),
|
|
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (post_type_id) REFERENCES post_types (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (bundle_id) REFERENCES sites (id) ON DELETE CASCADE
|
|
);
|
|
CREATE UNIQUE INDEX page_bundle_id_role ON pages (bundle_id, role) WHERE (role is NOT null);
|
|
|
|
CREATE TABLE publish_targets (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL,
|
|
role target_role NOT NULL,
|
|
target_type target_type NOT NULL,
|
|
url TEXT NOT NULL,
|
|
target_ref TEXT NOT NULL,
|
|
|
|
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
|
|
UNIQUE (site_id, role)
|
|
); |