Database Design

Database-per-service pattern. Each microservice owns its PostgreSQL schema. Cross-service references use UUIDs only — no foreign keys across databases.

Naming: snake_case tables, UUID primary keys, timestamptz for audit fields, soft-delete via deleted_at where needed.

auth_db (auth-service)

users
  id, email, phone, password_hash, full_name, is_active
  last_login_at, created_at, updated_at

roles
  id, code (SYSTEM_ADMIN, BURSAR, PARENT, ...), name

user_roles
  user_id, role_id, organization_id, campus_id (nullable)

parent_profiles
  user_id PK, organization_id, preferred_language

refresh_tokens
  id, user_id, token_hash, expires_at, revoked_at

org_db (organization-service)

organizations
  id, name, tin, logo_file_id, settings JSONB

campuses
  id, organization_id, name, code, address, phone

academic_years
  id, organization_id, label, start_date, end_date, is_current

terms
  id, academic_year_id, name (Term 1/2/3), start_date, end_date

classes
  id, campus_id, academic_year_id, name, level, stream, capacity

student_db (student-service)

students
  id, organization_id, campus_id, admission_number
  first_name, last_name, date_of_birth, gender
  photo_file_id, boarding_type (DAY|BOARDING)
  status (ACTIVE|GRADUATED|TRANSFERRED|WITHDRAWN)
  created_at, updated_at, deleted_at

guardians
  id, organization_id, user_id (auth), full_name, phone, email, relationship

student_guardians
  student_id, guardian_id, is_primary, can_pickup

enrollments
  id, student_id, class_id, academic_year_id, enrolled_at, status

requirement_templates
  id, campus_id, class_level, item_name, is_mandatory

student_requirements
  id, student_id, template_id, status (PENDING|SUBMITTED|VERIFIED), verified_by

fee_db (fee-service) — payment claims

fee_structures
  id, organization_id, campus_id, name, academic_year_id
  class_level, boarding_type, amount, term_id

fee_accounts
  id, student_id, organization_id, campus_id
  balance DECIMAL(14,2), credit_balance DECIMAL(14,2)

fee_invoices
  id, fee_account_id, student_id, term_id, invoice_number
  total_amount, amount_paid, status (OPEN|PARTIAL|PAID|VOID)
  due_date, issued_at

fee_invoice_lines
  id, invoice_id, description, amount

payment_claims                    -- Option B parent submissions
  id, organization_id, campus_id, student_id, guardian_id
  invoice_id, amount_claimed, amount_approved
  currency, payment_method, transaction_id, payer_phone
  agent_number, bank_name, payment_date, notes
  proof_file_id, status, duplicate_of_claim_id
  reviewed_by, reviewed_at, rejection_reason
  version, created_at, updated_at

payment_claim_status_history
  id, claim_id, from_status, to_status, actor_id, note, created_at

payments
  id, organization_id, campus_id, student_id, claim_id
  source (PARENT_CLAIM|BURSAR_DIRECT), amount, payment_method
  transaction_id, receipt_number, status, void_reason
  created_by, created_at

payment_allocations
  payment_id, invoice_id, amount_applied

defaulter_snapshots
  id, campus_id, term_id, student_id, amount_outstanding, as_of_date

Indexes: payment_claims(status, campus_id), payment_claims(transaction_id) partial unique, payments(receipt_number) unique, fee_invoices(student_id, status).

finance_db (finance-service)

chart_of_accounts
  id, organization_id, code, name, account_type

journal_entries
  id, organization_id, campus_id, entry_date, reference, source_event_id

journal_lines
  id, journal_entry_id, account_id, debit, credit, description

academic_db (academic-service)

grading_schemes
  id, organization_id, name (CBC_AE, LEGACY), config JSONB

subjects
  id, organization_id, code, name

mark_entries
  id, student_id, subject_id, term_id, score, grade, entered_by

report_cards
  id, student_id, term_id, file_id, published_at, status

homework_assignments
  id, class_id, subject_id, title, due_date, file_id, created_by

notice_db (notice-service)

notices
  id, campus_id, title, body, published_at, audience_type, audience_ref JSONB

distributed_documents
  id, campus_id, title, document_type, file_id
  audience_type, audience_ref JSONB, published_at

document_recipients
  document_id, guardian_id, student_id, downloaded_at

file_db (file-service)

files
  id, organization_id, bucket, object_key, mime_type, size_bytes
  uploaded_by, created_at, virus_scan_status

notification_db (notification-service)

notification_templates
  id, organization_id, code, channel, body_template

notification_log
  id, recipient, channel, template_code, payload JSONB
  provider_ref, status, sent_at, delivered_at

health_db (health-service)

student_health_records
  id, organization_id, campus_id, student_id, status, version
  allergies JSONB, chronic_conditions JSONB, medications JSONB
  blood_group, disability_notes, immunisation_notes
  family_doctor_name, family_doctor_phone, hospital_preference
  emergency_action_plan_file_id
  parent_confirmed_at, parent_confirmed_by, parent_declaration_text
  nurse_reviewed_at, nurse_reviewed_by
  created_at, updated_at

health_documents
  id, health_record_id, document_type, file_id
  uploaded_by, uploaded_at, expires_at

health_nurse_notes
  id, health_record_id, note, author_id, created_at

health_access_audit
  id, health_record_id, student_id, actor_id, action
  ip_address, created_at

health_record_status_history
  id, health_record_id, from_status, to_status, actor_id, note, created_at

Full spec: Student health records

attendance_db · inventory_db · report_db

See module specs for attendance_records, stock_items, uniform_issues, and materialized views in report_db.

Outbox pattern (all write services)

outbox_events
  id, aggregate_type, aggregate_id, event_type, payload JSONB
  created_at, published_at NULL

Poller publishes to RabbitMQ; consumers acknowledge idempotently via processed_events table.