Multi-Tenant PostgreSQL for an internal Security Tool
Budget: €250 – €750 EUR
I’m building an internal security tool and need a solid PostgreSQL foundation that starts as a single, multi-tenant database yet can be cleanly split into separate databases—and separate servers—later on. The first release will concentrate on storing architectural components (a very well defined structure) alongside threat-intelligence data (mapped to the components metadata), so the schema must keep cross-tenant isolation strict while still allowing swift relational join matching across those two domains.
What I need from you
• A full relational schema (ERD and DDL) optimised for multi-tenant operation—think shared-table with isolated tenant-id or schema-per-tenant, whichever you can justify—and documented so the eventual extraction path is clear.
• Well-indexed tables with explain-analyse-verified join paths for common queries we’ll run on architectural and threat data.
• Migration tooling (Flyway, Liquibase, or native psql scripts) wired to a storage connector of your choice, making local-to-stage promotion painless.
• A hand-off guide that shows how the current layout can be refactored into per-tenant databases and, if needed, separate servers without a rewrite.
Acceptance criteria
– Schema installs cleanly on PostgreSQL 15+ with zero warnings.
– Multi-tenant rows remain isolated via row-level checks or equivalent.
– Sample queries on threat vs. architecture tables return in <200 ms on a 1 M-row seed set.
– Migration scripts apply forward and backward without data loss.
– Transition playbook details exact steps, expected downtime, and cut-over SQL.
If designing high-performance joins, partitioning strategies, and clean migration paths is your everyday territory, I’d love to review your approach and move quickly.
What I need from you
• A full relational schema (ERD and DDL) optimised for multi-tenant operation—think shared-table with isolated tenant-id or schema-per-tenant, whichever you can justify—and documented so the eventual extraction path is clear.
• Well-indexed tables with explain-analyse-verified join paths for common queries we’ll run on architectural and threat data.
• Migration tooling (Flyway, Liquibase, or native psql scripts) wired to a storage connector of your choice, making local-to-stage promotion painless.
• A hand-off guide that shows how the current layout can be refactored into per-tenant databases and, if needed, separate servers without a rewrite.
Acceptance criteria
– Schema installs cleanly on PostgreSQL 15+ with zero warnings.
– Multi-tenant rows remain isolated via row-level checks or equivalent.
– Sample queries on threat vs. architecture tables return in <200 ms on a 1 M-row seed set.
– Migration scripts apply forward and backward without data loss.
– Transition playbook details exact steps, expected downtime, and cut-over SQL.
If designing high-performance joins, partitioning strategies, and clean migration paths is your everyday territory, I’d love to review your approach and move quickly.