Immutable Data

CREATE TABLE blog.article (
       slug TEXT PRIMARY KEY,
       title TEXT NOT NULL,
       content TEXT NOT NULL
);

CREATE SEQUENCE logblog.revision_id_seq;
CREATE TABLE logblog.article_revision (
       revision_id INTEGER PRIMARY KEY DEFAULT nextval('logblog.revision_id_seq'),
       timestamp TIMESTAMP NOT NULL DEFAULT now(),
       slug TEXT NOT NULL,
       title TEXT NOT NULL,
       content TEXT NOT NULL
);

CREATE SCHEMA logblog;

CREATE TABLE logblog.article (
       slug TEXT NOT NULL PRIMARY KEY CHECK(slug SIMILAR TO '[-a-z]+')
);

CREATE SEQUENCE logblog.revision_id_seq;
CREATE TABLE logblog.article_revision (
       revision_id INTEGER PRIMARY KEY DEFAULT nextval('logblog.revision_id_seq'),
       timestamp TIMESTAMP NOT NULL DEFAULT now(),
       slug TEXT NOT NULL REFERENCES logblog.article,
       title TEXT NOT NULL,
       content TEXT NOT NULL
);

CREATE TABLE logblog.article_publish (
       timestamp TIMESTAMP NOT NULL DEFAULT now(),
       revision_id INTEGER REFERENCES logblog.article_revision
);

CREATE TABLE logblog.article_deletion (
       timestamp TIMESTAMP NOT NULL DEFAULT now(),
       slug TEXT NOT NULL REFERENCES logblog.article
);

CREATE TYPE logblog.tag_event_type AS ENUM ('add', 'remove');
CREATE TABLE logblog.tag_event (
       timestamp TIMESTAMP NOT NULL DEFAULT now(),
       slug TEXT NOT NULL REFERENCES logblog.article,
       event logblog.tag_event_type NOT NULL,
       tag TEXT NOT NULL
);

CREATE VIEW logblog.last_deleted_view AS
     SELECT DISTINCT ON (slug) timestamp AS deleted_on, slug
     FROM logblog.article_deletion
     ORDER BY slug, timestamp DESC;

CREATE VIEW logblog.last_published_view AS
     SELECT DISTINCT ON (rev.slug)
            rev.revision_id,
            pub.timestamp AS last_updated_on,
            rev.slug,
            rev.title,
            rev.content
     FROM logblog.article_publish AS pub
     INNER JOIN logblog.article_revision AS rev ON rev.revision_id = pub.revision_id
     ORDER BY rev.slug, timestamp DESC;

CREATE VIEW logblog.first_published_view AS
     SELECT DISTINCT ON (rev.slug)
            rev.revision_id,
            pub.timestamp AS first_published_on
     FROM logblog.article_publish AS pub
     INNER JOIN logblog.article_revision AS rev ON rev.revision_id = pub.revision_id
     ORDER BY rev.slug, timestamp;

CREATE VIEW logblog.article_tag_view AS
       WITH last_tag_event AS
         (SELECT DISTINCT ON (slug, tag) *
          FROM logblog.tag_event
          ORDER BY slug, tag, timestamp DESC)
       SELECT slug, array_agg(tag) AS tags
       FROM last_tag_event
       WHERE event = 'add'
       GROUP BY slug;

CREATE VIEW logblog.public_article_view AS
       SELECT last_pub.slug,
              first_pub.first_published_on,
              last_pub.last_updated_on,
              last_pub.title,
              last_pub.content,
              COALESCE(tags.tags, '{}'::TEXT[]) AS tags
       FROM logblog.last_published_view AS last_pub
       LEFT JOIN logblog.last_deleted_view AS del
            ON del.slug = latest_pub.slug
       LEFT JOIN logblog.first_published_view AS first_pub
            ON first_pub.slug = latest_pub.slug
       LEFT JOIN logblog.article_tag_view AS tags
            ON tags.slug = last_pub.slug
       WHERE NOT COALESCE(del.timestamp > last_pub.timestamp, false)
       ORDER BY first_pub.timestamp;

CREATE VIEW logblog.article_history_view AS
       WITH
        revision_events AS
        (SELECT timestamp,
                slug,
                ('Created article revision ' || revision_id)::TEXT AS event
         FROM logblog.article_revision),
        publish_events AS
        (SELECT pub.timestamp,
                rev.slug,
                ('Published revision ' || pub.revision_id)::TEXT AS event
         FROM logblog.article_publish AS pub
         INNER JOIN logblog.article_revision AS rev
               ON rev.revision_id = pub.revision_id),
        deletion_events AS
        (SELECT timestamp,
                slug,
                'Deleted article'::TEXT AS event
         FROM logblog.article_deletion),
        tag_events AS
        (SELECT timestamp,
                slug,
                (CASE 
                 WHEN event = 'add' THEN ('Added tag ' || tag)
                 WHEN event = 'remove' THEN ('Deleted tag ' || tag)
                 END)::TEXT AS event
         FROM logblog.tag_event)
       (SELECT * FROM revision_events)
       UNION (SELECT * FROM publish_events)
       UNION (SELECT * FROM deletion_events)
       UNION (SELECT * FROM tag_events);

BEGIN;
        INSERT INTO logblog.article VALUES ('simple');
        INSERT INTO logblog.article_revision (revision_id, timestamp, slug, title, content) VALUES
          (1, '2015-01-01 00:00:00', 'simple',
          'A Simple Title',
          'This is a simple published article');
COMMIT;

INSERT INTO logblog.article_publish (timestamp, revision_id) VALUES
  ('2015-01-01 01:00:00', 1);
INSERT INTO logblog.tag_event (timestamp, slug, event, tag) VALUES
  ('2015-01-01 02:00:00', 'simple', 'add', 'simple-tag');

INSERT INTO logblog.article VALUES
  ('revised'), ('deleted'), ('unpublished'), ('unpublished-revision'), ('republished');
INSERT INTO logblog.article_revision (revision_id, timestamp, slug, title, content) VALUES
  (2, '2015-01-01 00:00:00', 'revised',
  'Revised',
  'You will not see this content because it has been revised.'),
  (3, '2015-01-01 02:00:00', 'revised',
  'Revised',
  'This is revised and published content.'),
  (4, '2015-01-01 00:00:00', 'deleted',
  'Deleted',
  'This is a deleted article. You should not see this.'),
  (5, '2015-01-01 00:00:00', 'unpublished',
  'Unpublished',
  'This content was never published :('),
  (6, '2015-01-01 00:00:00', 'unpublished-revision',
  'Unpublished Revision',
  'This article has revised content you can not see yet.'),
  (7, '2015-01-01 02:00:00', 'unpublished-revision',
  'Unpublished Revision',
  'This is revised content you can not see.'),
  (8, '2015-01-01 00:00:00', 'republished',
  'Republished',
  'This article was deleted then re-published.');
INSERT INTO logblog.article_publish (timestamp, revision_id) VALUES
  ('2015-01-01 01:00:00', 2),
  ('2015-01-01 03:00:00', 3),
  ('2015-01-01 01:00:00', 4),
  ('2015-01-01 01:00:00', 6),
  ('2015-01-01 01:00:00', 8),
  ('2015-01-01 03:00:00', 8);
INSERT INTO logblog.article_deletion (timestamp, slug) VALUES
  ('2015-01-01 02:00:00', 'deleted'),
  ('2015-01-01 02:00:00', 'republished');
INSERT INTO logblog.tag_event (timestamp, slug, event, tag) VALUES
  ('2015-01-01 01:00:00', 'revised', 'add', 'lots'),
  ('2015-01-01 02:00:00', 'revised', 'add', 'of'),
  ('2015-01-01 03:00:00', 'revised', 'add', 'tags'),
  ('2015-01-01 04:00:00', 'revised', 'add', 'deleted-tag'),
  ('2015-01-01 05:00:00', 'revised', 'remove', 'deleted-tag');

SELECT slug, first_published_on, content FROM logblog.public_article_view;

SELECT * FROM logblog.article_history_view ORDER BY slug, timestamp;

EXPLAIN SELECT * FROM logblog.public_article_view;

CREATE TABLE logblog.public_article_state (
       slug TEXT PRIMARY KEY REFERENCES logblog.article,
       title TEXT,
       content TEXT
);

CREATE FUNCTION logblog.insert_slug() RETURNS trigger AS $$
    BEGIN
        INSERT INTO logblog.public_article_state (slug) VALUES (NEW.slug);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_slug
AFTER INSERT ON logblog.article
FOR EACH ROW EXECUTE PROCEDURE logblog.insert_slug();

CREATE FUNCTION logblog.update_content() RETURNS trigger AS $$
    BEGIN
        UPDATE logblog.public_article_state
               SET title = rev.title, content = rev.content
               FROM logblog.article_revision AS rev
               WHERE rev.slug = logblog.public_article_state.slug
                 AND rev.revision_id = NEW.revision_id;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_content
AFTER INSERT ON logblog.article_publish
FOR EACH ROW EXECUTE PROCEDURE logblog.update_content();

SELECT * FROM logblog.public_article_state;

EXPLAIN SELECT * FROM logblog.public_article_state;