Every draft kept, exactly one of them live

Difficulty 4/5Subscribers

A help centre keeps articles. Editors revise them constantly, and nothing is ever thrown away.

The rules:

Every revision is kept. Editing an article adds a version; it never overwrites one.
Version numbers belong to the document. Article A and article B both have a version 1, and neither of them cares about the other.
At most one version of a document is published at any moment. Publishing version 3 is what unpublishes version 2, and readers must never be able to reach two live versions of the same article.
Two documents can obviously be published at the same time. The rule is per document.
Nothing is ever deleted, including versions nobody published.

documents already exists.

The contract

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

documents
idslug
document_versions
iddocument_idversionbodyis_published

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE documents (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

INSERT INTO documents (slug) VALUES ('refunds'), ('shipping');

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…