Fwd: Modelling a web CMS in Postgres ... a little advice needed

From: Tony Shelver <tshelver(at)gmail(dot)com>
Date: Thu, 11 Aug 2022 at 11:47
Subject: Re: Modelling a web CMS in Postgres ... a little advice needed
To: Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch>

On Thu, 11 Aug 2022 at 09:35, Laura Smith <
n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> wrote:

> Hi
>
> I'm looking at using pgsql as a backend to a web CMS but could do with a
> little advice from the crowd on the wiseness of my schema thinking.
>
> TL;DR the design is centered around two tables "pages" and "page_content",
> where "pages" has a jsonb column that refers to "page_content" in a
> key-value style (key for content block location ID on the web page, value
> for the database lookup). Probably both, but certainly "page_content"
> would need to be versioned.
>
> My present thinking is along the following lines (table columns minimised
> for this post):
>
> create table pages (
> page_id uuid primary key not null,
> page_metadata jsonb not null
> );
>
> create table page_content(
> content_id uuid not null,
> content_version_id uuid not null
> content_valid tstzrange not null default tstzrange(now(),'infinity'),
> content_data text,
> EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE
> INITIALLY DEFERRED
> );
> create unique index if not exists on page_content(content_version_id);
> CREATE OR REPLACE VIEW current_content AS select * from page_content where
> content_valid @> now();
>
>
> An example "page_metadata" entry might look something like :
> {
> "page":"foo",
> "description":"bar",
> "content":[
> "pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
> "pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
> ]
> }
>
>
> So I guess my questions here are :
> Am i nuts with this thinking ? Is there a saner way to do this ? Should I
> be using pgsql at all for this, e.g. the cool kids will probably say I
> should be using a graph database ? (N.B. I did consider a pure old-school
> relational model with no jsonb, but I considered it too difficult to model
> the dynamic nature of the fields, i.e. unknown many-many relationship
> between page content locations and content ... but I'm willing to be proven
> wrong by wiser minds)
>
> Then, on a more technical level .... what would an optimal query for
> looping through the json content array look like ? I have always been
> pretty useless when it comes to CTE expressions !
>
> Thanks all
>
>
> Off the top of my head, it seems like you are reinventing the wheel.

There are some very good CMS systems out there, such as Strapi, which is an
open source, headless CMS, and which can use Postgresql as the underlying
DB.

It could be worth a while to install this and see how it generates the
underlying pages. As a benefit, it also generates the UI to maintain the
underlying data, and also provides a set of APIs that you can call to
access the data from many different front-ends.

Tony