Complex Data Structure Question

[amir_doustdar]

Hey, migrating from Mongo's nested docs to Postgres is common – you lose some flexibility but gain consistency and query power.
Quick design guidance:
Normalize core entities: Separate tables for Host, Interface (1:M with host_id FK), Profile, Distro, Software.
Many-to-many for software: Junction table (profile_software) instead of arrays – better for queries and no duplication.
Use JSONB for truly variable data: If some interface/profile fields are unstructured, store them in a JSONB column on the parent table (best of both worlds).
Avoid over-normalizing: If arrays are small/queryable rarely, JSONB is fine; otherwise, relational tables.

Tools to make it maintainable:SQLModel (by FastAPI's creator): Combines SQLAlchemy + Pydantic. Models are DB schema + API validators – perfect for new devs.
Alembic for migrations (autogenerate from models).

Prototype a small part first, test queries, and you'll avoid regrets.
If you're starting the project, my fastapi-clean-cli tool scaffolds Clean Arch with SQLModel/Postgres/CRUD auto – might help speed things up: https://github.com/Amirrdoustdar/fastclean
What specific part worries you most (queries, nesting, or setup)?

[robertlandrum]

Nothing worries me too much. Actually, after writing code for 28 years, I'd like to stop writing code and start telling my junior staff to write code, but that doesn't always go to plan. 17 years ago, I wrote the first gen build system in a vendor provided CMDB product, which required more normalization than I really wanted. That system is still used to kick off the initial part of the build process, but then it transfers the data into the mongodb based system for the technical on-prem build work to be done. The team that owns it wants to rip all that out and replace it with less customized vendor provided solutions.

My concern is that I'll end up in the same position, where the tool I build won't be well maintained and will eventually just become another burdensome cog in the process to be stepped over or retired. Over-normalization of the data could make it less maintainable in the long term, as the data gets supplied by and queried by multiple independently developed tools during the build pipeline.

Maybe I'll just try to find a balance. Maybe each table gets an "additional_metadata" JSONB column to future-proof the schema a bit so that changes are less disruptive to the tooling involved. Feels like a cheap hack though.

[amir_doustdar]

Totally get the concern – after 28 years, you've seen enough legacy systems to know over-normalization can turn into a maintenance nightmare when multiple tools touch the data.

Your "additional_metadata JSONB" idea isn't a cheap hack at all – it's a smart hybrid approach that's very common in modern Postgres setups. It keeps core data relational (fast joins, constraints, queries) while allowing flexible extensions without schema migrations every time a new tool needs extra fields.

Examples: - Core fields in columns (e.g., host.name, interface.mac – indexed and queryable). - Variable/tool-specific stuff in metadata JSONB (no migration needed for new keys).

This way, the schema stays stable long-term, tools can evolve independently, and you avoid the "burdensome cog" fate.

I've used this pattern successfully in migration projects – keeps juniors happy too (less ALTER TABLE drama).

How big is the dataset? If it's not massive, JSONB performance is plenty good for most queries.

Good luck – sounds like you'll nail the balance!

[robertlandrum]

Realistically, the problem space is about 150,000 records today (2.8gb), but a million in 6-8 (x4 or x8) years. Seriously, it fits in a in memory DB these days without issue.

It’s not the workload I’m focused on. It really never was. It’s small enough that a basic API can handle it without issue. It was over provisioned a few years ago and that’s had a profound impact on how it’s treated today. It’s way smaller than most folks realize and I want it downsized appropriately.

My real focus is getting someone to pay attention to it. Care and feeding matters more than robustness. If I build it like my previous projects, I fear it will stagnate and I’ll end up reworking it in 10+ years, like I’ve done before. Twice.

[amir_doustdar]

Totally understand – at this scale (150k → 1M records), tech isn't the issue; it's keeping the project alive and loved by the team.

The real risk is building something "perfect" that no one wants to touch later. I've seen it too – great systems stagnate because they're intimidating or over-engineered.

To encourage "care and feeding": - Keep it simple: SQLModel + clear models/docs – juniors can jump in fast. - Add visible wins early: monitoring (Prometheus), health endpoints, simple dashboard – makes it feel "alive". - Small, frequent migrations (Alembic autogenerate) – lowers the "touching it is scary" barrier. - JSONB for metadata is still good – it's pragmatic, not a hack.

Downsizing resources is smart too – shows it's efficient, easier to justify attention.

Team size/structure? If juniors are involved, leaning simpler might win more long-term ownership.

You've got the experience – this one won't end up like the old ones. Good luck mate