I have a multi-tenant Postgresql database with each schema belonging one of our customers (tenants). Each tenant has a table called
CustomerX.Messages contains all of the messages that are sent to
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) | | [PROCESSING] | | 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
What is the best way for me to model this? I need to ensure the following:
- There is exactly one message in "some" schema for each message in the public table.
- There is exactly one message in the public schema for each message in a customer schema.
- Given some message (in either the public or customer schema), I can find the other one.