repos / pico

20220310_init.sql

 1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 2
 3CREATE TABLE IF NOT EXISTS app_users (
 4  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 5  name character varying(50),
 6  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
 7  CONSTRAINT unique_name UNIQUE (name),
 8  CONSTRAINT app_user_pkey PRIMARY KEY (id)
 9);
10
11CREATE TABLE IF NOT EXISTS public_keys (
12  id uuid NOT NULL DEFAULT uuid_generate_v4(),
13  user_id uuid NOT NULL,
14  public_key varchar(2048) NOT NULL,
15  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
16  CONSTRAINT user_public_keys_pkey PRIMARY KEY (id),
17  CONSTRAINT unique_key_for_user UNIQUE (user_id, public_key),
18  CONSTRAINT fk_user_public_keys_owner
19    FOREIGN KEY(user_id)
20  REFERENCES app_users(id)
21  ON DELETE CASCADE
22  ON UPDATE CASCADE
23);
24
25CREATE TABLE IF NOT EXISTS posts (
26  id uuid NOT NULL DEFAULT uuid_generate_v4(),
27  user_id uuid NOT NULL,
28  title character varying(255) NOT NULL,
29  text text NOT NULL DEFAULT '',
30  publish_at timestamp without time zone NOT NULL DEFAULT NOW(),
31  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
32  updated_at timestamp without time zone NOT NULL DEFAULT NOW(),
33  CONSTRAINT posts_pkey PRIMARY KEY (id),
34  CONSTRAINT unique_title_for_user UNIQUE (user_id, title),
35  CONSTRAINT fk_posts_app_users
36    FOREIGN KEY(user_id)
37  REFERENCES app_users(id)
38  ON DELETE CASCADE
39  ON UPDATE CASCADE
40);