- Strapi Version: 3.2.5
- Operating System: multiple - dev: mac/win, docker; prod: heroku
- Database: postgres
- Node Version: 12
- NPM Version: multiple
- Yarn Version: ?
I wanted to ask if anyone can shed some light into the intricacies and caveats of manually altering PG tables.
- Managing data integrity is always a good thing, and postgres (as well as any db) is very well equipped to do so. Strapi does not support composite primary keys, nor composite unique constrains, both of which are integral in enforcing data integrity.
- Supporting a
shared tablesmulti-tenancy approach for those of us building a SaaS product. To explain it quickly, this approach entails adding a
tennantIDcolumn to almost every table, but in order to mitigate security risks and potential human error from uncareful devs, it would be prudent to leverage postgres’ row level security policies and validate that the data requests’ origin matches the tenant for each row.
- Scalability - given the multitenancy, it would be prudent to force the clustered index to include the tennantID - this way each tennant’s data is very likely to end up in a single shard - avoiding network io in joins, transactions, and foreign key constraints.
As someone who is not familiar with strapi internals, I’m asking the community for advice in this matter. Could anyone give me a heads up with pitfalls, gotchas, what to look out for, what to avoid?