I have a multi-tenant SaaS app where each tenant gets their own schema. When a user creates an account, I create a new schema and run a bunch of migrations to create tables under that schema. Then, when the user wants to authenticate, they provide their tenant info (this can be their subdomain, e.g. mycompany.coolapp.com, or it can be a unique account ID), and the app routes subsequent queries to that tenant's schema.
However, certain pieces of tenants' data will be public. For instance, the user will need to create invoices and email their links (containing a UUID) to their customers. When the customer clicks the link, the app needs to be able to figure out which tenant the invoice belongs to, based on the UUID.
To do this, I created a "public.invoices" table, with two columns:
tenant. The latter contains the tenant's schema name, which I can then use to perform the subsequent queries (i.e. the invoice's actual data) to the appropriate schema.
Question 1: Is this design sound? Is there some other, more standard way of routing unauthenticated/pre-authenticated requests to specific schemas in situations where each tenant gets their own schema?
Question 2: How do I tell Postgres that, when the tenant deletes the invoice (the one in their schema), the public record (under "public.invoices") also should be deleted? I'm trying to avoid using two queries, if some database-provided option or feature exists. I looked at CASCADE, but it seems that only goes from parent to child, and as far as I can tell there isn't a way to define a foreign key relationship in this scenario where the tenant invoice is the parent record.