Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed - Mailing list pgsql-general

Hi Tony

The reason I'm looking to do it from scratch is that its a case of "once bitten, twice shy".

This CMS will be replacing a Joomla based CMS.

I can't quite say I'm enamoured by the option of trading one "baggage included" opinionated CMS for another.  Also in
thisday and age, removing the excess baggage means an improved security footprint. 

Laura
------- Original Message -------
On Thursday, August 11th, 2022 at 10:49, Tony Shelver <tshelver@gmail.com> wrote:


> From: Tony Shelver <tshelver@gmail.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@protonmail.ch>
>
>
>
> On Thu, 11 Aug 2022 at 09:35, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.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
wisenessof my schema thinking. 
> >
> > TL;DR the design is centered around two tables "pages" and "page_content", where "pages" has a jsonb column that
refersto "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
coolkids will probably say I should be using a graph database ? (N.B. I did consider a pure old-school relational model
withno jsonb, but I considered it too difficult to model the dynamic nature of the fields, i.e. unknown many-many
relationshipbetween 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
usePostgresql 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
generatesthe UI to maintain the underlying data, and also provides a set of APIs that you can call to access the data
frommany different front-ends. 
>
> Tony