Postgresql – Reference a schema from public table column


I have a multi-tenant Postgresql database with each schema belonging one of our customers (tenants). Each tenant has a table called Messages. So CustomerX.Messages contains all of the messages that are sent to CustomerX.

All of the messages come in through a generic endpoint and are processed a bit to determine which customer should receive the message. So the message pipeline looks like this from a high level:

RAW MESSAGE (public.Messages)
DECRYPTED MESSAGE (some_tenant.Messages)

I would like to put the "pre-processed" messages into a public table containing the exact, unmodified input to the system. After going through a round of processing, I'd like to drop the final message into the customer's CustomerX.Messages table.

My Question

What is the best way for me to model this? I need to ensure the following:

  1. There is exactly one message in "some" schema for each message in the public table.
  2. There is exactly one message in the public schema for each message in a customer schema.
  3. Given some message (in either the public or customer schema), I can find the other one.

Best Answer

You can store everything in public.messages, add column that describes the actual schema and make views in every schema, that are selecting only messages that needs to be visible in particular schema. Depending on you needs you can create rules on views for data modification, partition that big table, add row level security.