Main Database Schema

This ERD shows all entities and relationships in the NPP Portal database, organized into four domains.

Design Decisions

Soft Deletes

All entities use active: BOOLEAN instead of hard deletes:

• Preserves historical data
• Maintains referential integrity
• Allows recovery of accidentally deleted records

JSONB for Flexible Data

activity_details.additional_details uses PostgreSQL JSONB:

• Different activity types may have different extra fields
• Avoids schema changes for new activity type attributes
• Queryable with PostgreSQL JSON operators

UUID for External References

users.uuid provides a stable external identifier:

• Primary key id is internal only
• UUID used in API responses and URLs
• Prevents enumeration attacks

Indexes