Census Schema

Census manages person and identity records used across modules.

Namespace

These tables should move into the census PostgreSQL schema.

• census.persons
• census.identities
• census.contacts

Education Records

Education is a separate Nexus product module. Its records currently share the census PostgreSQL namespace because they reference people directly.

• census.schools
• census.school_years
• census.school_calendars
• census.school_enrollments
• census.school_employments
• census.school_employment_assignments

Tables

census.persons

Person root object.

• Primary key: id
• Required field: tenant_id
• Optional field: current_identity_id
• Optional field: archived_at
• Foreign keys:
  • current_identity_id -> census.identities.id (ON DELETE SET NULL)
  • tenant_id -> platform.tenants.id

census.identities

Name identity records attached to persons.

• Primary key: id
• Required fields: person_id, first_name, last_name, tenant_id
• Optional field: middle_name
• Optional fields: deactivated_at, archived_at
• Foreign keys:
  • person_id -> census.persons.id (ON DELETE CASCADE)
  • tenant_id -> platform.tenants.id

census.contacts

Contact records attached to persons and optionally identities.

• Primary key: id
• Required fields: tenant_id, person_id, contact_type, value, sort_order
• Optional fields: identity_id, label
• Foreign keys:
  • person_id -> census.persons.id (ON DELETE CASCADE)
  • identity_id -> census.identities.id (ON DELETE CASCADE)
  • tenant_id -> platform.tenants.id

census.schools

• Primary key: id
• Required fields: tenant_id, name
• Unique field: name
• Foreign key: tenant_id -> platform.tenants.id

census.school_years

• Primary key: id
• Required fields: tenant_id, name, start_date, end_date
• Unique field: name
• Foreign key: tenant_id -> platform.tenants.id
• Check: end_date >= start_date

census.school_calendars

• Primary key: id
• Required fields: tenant_id, school_id, calendar_id, name
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • school_id -> census.schools.id (ON DELETE CASCADE)
  • calendar_id -> census.school_years.id (ON DELETE RESTRICT)

census.school_enrollments

• Primary key: id
• Required fields: tenant_id, person_id, calendar_id
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • person_id -> census.persons.id (ON DELETE CASCADE)
  • calendar_id -> census.school_calendars.id (ON DELETE CASCADE)

census.school_employments

• Primary key: id
• Required fields: tenant_id, person_id, start_date
• Optional field: end_date
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • person_id -> census.persons.id (ON DELETE CASCADE)
• Check: end_date is null or end_date >= start_date

census.school_employment_assignments

• Primary key: id
• Required fields: tenant_id, person_id, school_id, start_date
• Optional field: end_date
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • person_id -> census.persons.id (ON DELETE CASCADE)
  • school_id -> census.schools.id (ON DELETE CASCADE)
• Check: end_date is null or end_date >= start_date

Relationship Notes

• A person can have many identity records over time.
• A person can optionally point to a current identity for fast reads.
• Platform users no longer reference persons directly.
• Asset assignments and circulations can reference persons for custody flows.
• School enrollments and school employment tables reference persons for education context.
• School calendars bind schools to school years.
• Employment rows define employment windows; assignment rows place employment at a specific school.