0

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)
    |
    |
[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 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.
0

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.

  • This answer is unclear to me. Can you elaborate or provide examples? I don't believe there is a way to provide a rule to ensure a STRING column matches an existing schema. What did you have in mind? – wheresmycookie Feb 1 '19 at 13:31
  • @wheresmycookie You can make a table with the names of every user schema and update it every time you create or delete user schema. Then just FK to this table. Also schema name should be hardcoded in every view. – duschatten Feb 1 '19 at 14:07
  • Hm, this doesn't seem right to me. Then I have to keep that table up to date. I really don't want to make another table just to do that because I know I or somebody on the team will forget to update it. – wheresmycookie Feb 1 '19 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.