A subset of a PostgREST schema

schema.sql

CREATE SCHEMA private;

CREATE TABLE private.person (
  id               serial PRIMARY KEY,
  email            varchar(128) NOT NULL UNIQUE CHECK ( email ~* '^.+@.+\..+$' ),
  given_name       varchar(64) NOT NULL CHECK ( LENGTH(given_name) >= 2 ),
  family_name      varchar(64) CHECK ( LENGTH(family_name) >= 2 ),
  image            varchar(128),
  created_at       timestamp NOT NULL DEFAULT NOW(),
  password_hash    char(60)
);

CREATE TABLE private.post (
  id               serial PRIMARY KEY,
  headline         varchar(128) NOT NULL CHECK ( LENGTH(headline) >= 3 ),
  author_id        integer NOT NULL REFERENCES private.person(id),
  is_based_on_url  varchar(128),
  created_at       timestamp NOT NULL DEFAULT NOW(),
  text             text
);

CREATE TABLE private.star (
  post_id          integer NOT NULL REFERENCES private.post(id),
  starrer_id       integer NOT NULL REFERENCES private.person(id),

  CONSTRAINT unique_starrer_per_star_group UNIQUE(star_group_id, starrer_id)
);

INSERT INTO private.person (id, given_name, family_name, email, image) VALUES
  (1, 'Sara', 'Smith', '[email protected]', 'https://s3.amazonaws.com/uifaces/faces/twitter/adellecharles/73.jpg'),
  (2, 'John', 'Smith', '[email protected]', 'https://s3.amazonaws.com/uifaces/faces/twitter/alexmarin/73.jpg'),
  (3, 'Jen', 'Villegas', '[email protected]', 'https://s3.amazonaws.com/uifaces/faces/twitter/ladylexy/73.jpg'),
  (4, 'Budd', 'Deey', '[email protected]', 'https://s3.amazonaws.com/uifaces/faces/twitter/marcosmoralez/73.jpg'),
  (5, 'Caleb', 'Meredith', '[email protected]', 'https://s3.amazonaws.com/uifaces/faces/twitter/calebmer/73.jpg');

INSERT INTO private.post (author_id, headline, is_based_on_url) VALUES
  (2, 'No… It''s a thing; it''s like a plan, but with more greatness.', 'https://hatena.ne.jp/semper/porta/volutpat/quam/pede.jsp'),
  (1, 'I hate yogurt. It''s just stuff with bits in.', 'http://usa.gov/porttitor/id/consequat/in/consequat/ut.xml'),
  (1, 'Is that a cooking show?', null),
  (1, 'You hit me with a cricket bat.', null),
  (5, 'Please, Don-Bot… look into your hard drive, and open your mercy file!', null),
  (3, 'Stop talking, brain thinking. Hush.', null),
  (1, 'Large bet on myself in round one.', 'https://cdbaby.com/pellentesque/at/nulla/suspendisse/potenti/cras/in.xml'),
  (2, 'It''s a fez. I wear a fez now. Fezes are cool.', 'https://state.tx.us/mauris/morbi/non.js'),
  (3, 'You know how I sometimes have really brilliant ideas?', 'https://ca.gov/sapien/arcu/sed/augue.png'),
  (2, 'What''s with you kids? Every other day it''s food, food, food.', null),
  (3, 'They''re not aliens, they''re Earth…liens!', null),
  (5, 'You''ve swallowed a planet!', null);

INSERT INTO private.star (post_id, starrer_id) VALUES
  (3, 1), (3, 4),
  (5, 1), (5, 2), (5, 3), (5, 5);

CREATE SCHEMA api;

GRANT USAGE ON SCHEMA api TO anonymous;

CREATE VIEW api.star AS
  SELECT post_id AS "postId", starrer_id as "starrerId"
    FROM private.star;

GRANT SELECT ON api.star TO anonymous;

CREATE FUNCTION api.star_count(post_id integer) RETURNS bigint
  AS $$ SELECT COUNT(*) FROM api.star WHERE "postId" = post_id; $$
  LANGUAGE sql;

GRANT EXECUTE ON FUNCTION api.star_count(integer) TO anonymous;

CREATE VIEW api.person AS
  SELECT id AS "id", given_name AS "givenName", family_name AS "familyName",
         image AS "image", created_at AS "createdAt"
    FROM private.person;

GRANT SELECT ON api.person TO anonymous;

CREATE VIEW api.post AS
  SELECT id AS "id", headline AS "headline", author_id AS "authorId",
        post_topic_id AS "postTopicId", is_based_on_url AS "isBasedOnUrl",
        api.star_count(id) AS "starCount",
        created_at AS "createdAt", text AS "text"
    FROM private.post;

GRANT SELECT ON api.post TO anonymous;