From Bare-Metal Infrastructure to Multi-Tenant SaaS: Architecting a Django Platform for 1,800+ Users

Thanks for the thoughtful feedback! You hit right on some of the biggest tradeoffs we had to weigh when choosing JSONB.

Regarding the mid-term rubric edits: you are 100% right that arbitrary tenant edits would break historical data. To protect against this, we actually don't expose the blueprint configuration to the school admins in the UI. The assessment blueprint is configured exclusively by our dev team during onboarding. Schools don't change their grading rubrics arbitrarily and if they do need a structural overhaul, it's handled as a supervised, scheduled migration by the engineering team between academic sessions, precisely because it is a destructive action.

That being said, I really love your idea of storing a blueprint version (or a lightweight snapshot hash) alongside the Result payload. Even with dev-controlled blueprints, a school might legitimately change their policy from a 4-tier to a 5-tier assessment system three years from now. A version stamp would guarantee that historical report cards remain mathematically reproducible decades later without needing complex temporal tables.

On the database constraints and GIN indexing: this is the classic JSONB tradeoff! Because we lose DB-level schema enforcement, we rely heavily on application-level validation (Django forms/validation logic) to strictly sanitize the payload before insertion. As for indexing, we intentionally lift the most heavily queried and ranked data out of the JSON. For example, total_score and average_score are explicitly stored as DecimalFields on the Result model, allowing us to use standard B-Tree indexes and native ORM aggregations. The JSONB field is primarily treated as a storage/presentation mechanism rather than a deep query target, though adding a GIN index is absolutely the next logical step if we ever need to run analytics on specific sub-scores (like isolating 'homework' trends across a district).

Really appreciate the insights. Definitely adding the versioning snapshot idea to the roadmap!"

Keeping the ranked fields like total_score and average_score as DecimalFields while JSONB stays a storage/presentation layer is a clean split, and it makes the B-Tree indexes do real work. One small addition if you adopt the version stamp: a CHECK constraint or a validator that rejects payloads whose blueprint version doesn't match a known one would catch drift early, even without schema enforcement on the keys. Thanks for the detailed answer.