Educational & Cultural Programs — Implementation Blueprint

Architect Agent 4 of the Pancha · 2026-05-02 · Module 8 of 17 in the Aayojana catalog.

1. Module summary

• Name: Educational & Cultural Programs
• Slug: education
• Kind: ERP
• Status: planned (subpackage src/aayojana/education/ exists with stub router only).

The institution's formal teaching wing. Vedapathashalas, Sanskrit schools, Pareeksha boards, music academies, lecture series — they have admission registers, courses, examiners (with sambhavana), examinations with a results pipeline, cultural performances, and certificates. Students are members (member.id FK) with a student_admissions row that adds scholastic data — scholar number, joining date, previous school TC, blood group, parents, encrypted Aadhaar. Examiners are also members with examiner_profiles carrying designation and encrypted sambhavana bank details. Sambhavana payments flow through Vitta's payroll module (a category distinct from salary).

2. Data model

# src/aayojana/education/models.py
from datetime import date, datetime, time
from sqlalchemy import (
    Boolean, Date, DateTime, ForeignKey, Index, Integer, JSON, LargeBinary, Numeric,
    String, Text, Time, UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from aayojana.models.base import AuditMixin, Base, TenantMixin, uuid4_str


class StudentAdmission(Base, AuditMixin, TenantMixin):
    """A scholastic record attached to a member.
    The member.id is the canonical person identity; this row holds all
    education-specific data. One member may have multiple admissions
    (e.g. re-admission after a gap, or multiple courses).
    """

    __tablename__ = "student_admissions"
    __table_args__ = (
        UniqueConstraint("tenant_id", "scholar_number", name="uq_scholar_no"),
        Index("ix_student_status", "status"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=False, index=True
    )
    scholar_number: Mapped[str] = mapped_column(String(40), nullable=False)
    admission_no: Mapped[str | None] = mapped_column(String(40), nullable=True)
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True
    )
    institution_type: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'vedapathashala' | 'sanskrit-school' | 'music-academy' | 'pareeksha-board' | 'general'
    joining_date: Mapped[date] = mapped_column(Date, nullable=False)
    expected_completion_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    actual_exit_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    status: Mapped[str] = mapped_column(String(20), default="active", nullable=False)
    # 'active' | 'completed' | 'discontinued' | 'transferred-out' | 'expelled' | 'on-leave'
    previous_school_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    previous_school_tc_no: Mapped[str | None] = mapped_column(String(63), nullable=True)
    previous_school_tc_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    blood_group: Mapped[str | None] = mapped_column(String(8), nullable=True)
    medical_conditions: Mapped[str | None] = mapped_column(Text, nullable=True)
    emergency_contact_name: Mapped[str | None] = mapped_column(String(127), nullable=True)
    emergency_contact_phone: Mapped[str | None] = mapped_column(String(40), nullable=True)
    emergency_contact_relation: Mapped[str | None] = mapped_column(String(40), nullable=True)
    father_name: Mapped[str | None] = mapped_column(String(127), nullable=True)
    mother_name: Mapped[str | None] = mapped_column(String(127), nullable=True)
    guardian_name: Mapped[str | None] = mapped_column(String(127), nullable=True)
    family_address: Mapped[str | None] = mapped_column(Text, nullable=True)
    aadhaar_encrypted: Mapped[bytes | None] = mapped_column(LargeBinary, nullable=True)
    aadhaar_last_4: Mapped[str | None] = mapped_column(String(4), nullable=True)
    photo_path: Mapped[str | None] = mapped_column(String(511), nullable=True)
    accommodation_room_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("rooms.id"), nullable=True
    )
    fee_waiver: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    fee_concession_pct: Mapped[float | None] = mapped_column(Numeric(5, 2), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class CourseCatalog(Base, AuditMixin, TenantMixin):
    __tablename__ = "course_catalog"
    __table_args__ = (
        UniqueConstraint("tenant_id", "code", name="uq_course_code"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    name_sanskrit: Mapped[str | None] = mapped_column(Text, nullable=True)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    institution_type: Mapped[str] = mapped_column(String(40), nullable=False)
    level: Mapped[str | None] = mapped_column(String(40), nullable=True)
    # 'beginner' | 'intermediate' | 'advanced' | 'mahavidvat' | 'vedanta-pravesha'
    duration_months: Mapped[int | None] = mapped_column(Integer, nullable=True)
    prerequisites: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # [{course_code: "VEDA-101", or qualification_required: "Sandhya Vandanam"}]
    primary_instructor_member_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=True
    )
    syllabus: Mapped[str | None] = mapped_column(Text, nullable=True)
    fee_amount: Mapped[float | None] = mapped_column(Numeric(12, 2), nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class CourseEnrollment(Base, AuditMixin, TenantMixin):
    __tablename__ = "course_enrollments"
    __table_args__ = (
        UniqueConstraint("student_id", "course_id", "academic_year", name="uq_enrol_per_year"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    student_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("student_admissions.id"), nullable=False
    )
    course_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("course_catalog.id"), nullable=False
    )
    academic_year: Mapped[str] = mapped_column(String(9), nullable=False)
    # '2026-2027'
    enrolled_on: Mapped[date] = mapped_column(Date, nullable=False)
    status: Mapped[str] = mapped_column(String(20), default="enrolled", nullable=False)
    # 'enrolled' | 'completed' | 'dropped' | 'failed'
    completion_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    instructor_override_member_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=True
    )
    fee_paid: Mapped[float] = mapped_column(Numeric(12, 2), default=0, nullable=False)
    fee_due: Mapped[float] = mapped_column(Numeric(12, 2), default=0, nullable=False)


class ExaminerProfile(Base, AuditMixin, TenantMixin):
    """Members who serve as examiners. Sambhavana bank details encrypted."""

    __tablename__ = "examiner_profiles"
    __table_args__ = (
        UniqueConstraint("member_id", name="uq_examiner_member"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=False
    )
    designation: Mapped[str] = mapped_column(String(127), nullable=False)
    # 'Mahavidvat' | 'Pradhana Pareekshaka' | 'Sahaaya Pareekshaka' etc
    qualifications: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # ['Veda-Bhashyam', 'Mimamsa-Sutra', 'Ghana-Patha-Mukha-Pareeksha']
    specialisation: Mapped[str | None] = mapped_column(Text, nullable=True)
    years_of_experience: Mapped[int | None] = mapped_column(Integer, nullable=True)
    sambhavana_rate_per_exam: Mapped[float | None] = mapped_column(Numeric(12, 2), nullable=True)
    sambhavana_rate_per_day: Mapped[float | None] = mapped_column(Numeric(12, 2), nullable=True)
    bank_details_encrypted: Mapped[bytes | None] = mapped_column(LargeBinary, nullable=True)
    bank_account_last_4: Mapped[str | None] = mapped_column(String(4), nullable=True)
    pan_encrypted: Mapped[bytes | None] = mapped_column(LargeBinary, nullable=True)
    is_panel_member: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class ExaminationSchedule(Base, AuditMixin, TenantMixin):
    __tablename__ = "examination_schedules"
    __table_args__ = (
        UniqueConstraint("tenant_id", "exam_code", name="uq_exam_code"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    exam_code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    course_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("course_catalog.id"), nullable=True
    )
    examination_kind: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'mid-term' | 'final' | 'pareeksha' | 'mukha-pareeksha' | 'practical' | 'recital'
    academic_year: Mapped[str] = mapped_column(String(9), nullable=False)
    exam_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    start_time: Mapped[time | None] = mapped_column(Time, nullable=True)
    duration_minutes: Mapped[int | None] = mapped_column(Integer, nullable=True)
    venue_room_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("rooms.id"), nullable=True
    )
    max_marks: Mapped[int | None] = mapped_column(Integer, nullable=True)
    pass_marks: Mapped[int | None] = mapped_column(Integer, nullable=True)
    examiner_panel: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # [{examiner_id, role: 'pradhana' | 'sahaaya', sambhavana_amount}]
    status: Mapped[str] = mapped_column(String(20), default="scheduled", nullable=False)
    # 'scheduled' | 'in-progress' | 'completed' | 'results-published' | 'cancelled'
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class PareekshaResult(Base, AuditMixin, TenantMixin):
    __tablename__ = "pareeksha_results"
    __table_args__ = (
        UniqueConstraint("examination_id", "student_id", name="uq_result_per_exam_student"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    examination_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("examination_schedules.id"), nullable=False
    )
    student_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("student_admissions.id"), nullable=False
    )
    enrollment_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("course_enrollments.id"), nullable=True
    )
    marks_obtained: Mapped[float | None] = mapped_column(Numeric(6, 2), nullable=True)
    grade: Mapped[str | None] = mapped_column(String(10), nullable=True)
    # 'O' | 'A+' | 'A' | 'B' | 'C' | 'F' or 'Uttirna' | 'Anuttirna'
    rank: Mapped[int | None] = mapped_column(Integer, nullable=True)
    status: Mapped[str] = mapped_column(String(20), default="pending", nullable=False)
    # 'pending' | 'passed' | 'failed' | 'absent' | 'malpractice'
    examiner_remarks: Mapped[str | None] = mapped_column(Text, nullable=True)
    examiner_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("examiner_profiles.id"), nullable=True
    )
    result_published_on: Mapped[date | None] = mapped_column(Date, nullable=True)
    is_revaluation: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)


class CulturalProgram(Base, AuditMixin, TenantMixin):
    __tablename__ = "cultural_programs"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    program_code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    name_sanskrit: Mapped[str | None] = mapped_column(Text, nullable=True)
    program_kind: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'concert' | 'lecture' | 'sammelan' | 'recital' | 'workshop' | 'demo' | 'discussion'
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True
    )
    program_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    start_time: Mapped[time | None] = mapped_column(Time, nullable=True)
    venue_room_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("rooms.id"), nullable=True
    )
    performers: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # [{name, role, member_id?, external_org?, honorarium?}]
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    expected_audience: Mapped[int | None] = mapped_column(Integer, nullable=True)
    actual_audience: Mapped[int | None] = mapped_column(Integer, nullable=True)
    is_ticketed: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    ticket_price: Mapped[float | None] = mapped_column(Numeric(10, 2), nullable=True)
    related_event_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("event_instances.id"), nullable=True
    )
    # When a cultural program is part of a festival event.
    status: Mapped[str] = mapped_column(String(20), default="planned", nullable=False)
    public_summary: Mapped[str | None] = mapped_column(Text, nullable=True)
    photo_paths: Mapped[list | None] = mapped_column(JSON, nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class CertificateIssued(Base, AuditMixin, TenantMixin):
    __tablename__ = "certificates_issued"
    __table_args__ = (
        UniqueConstraint("tenant_id", "serial_number", name="uq_cert_serial"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    serial_number: Mapped[str] = mapped_column(String(40), nullable=False)
    certificate_type: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'course-completion' | 'examination-pass' | 'mahavidvat' | 'recognition' |
    # 'transfer-certificate' | 'character-certificate' | 'attendance' | 'cultural-participation'
    student_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("student_admissions.id"), nullable=True
    )
    member_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=True
    )
    # Certificates may be issued to non-students too (e.g. lecture-attendance).
    course_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("course_catalog.id"), nullable=True
    )
    examination_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("examination_schedules.id"), nullable=True
    )
    issued_date: Mapped[date] = mapped_column(Date, nullable=False)
    issued_by_user_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("users.id"), nullable=True
    )
    title_text: Mapped[str | None] = mapped_column(Text, nullable=True)
    body_text: Mapped[str | None] = mapped_column(Text, nullable=True)
    body_text_sanskrit: Mapped[str | None] = mapped_column(Text, nullable=True)
    grade_or_class: Mapped[str | None] = mapped_column(String(40), nullable=True)
    template_version: Mapped[str | None] = mapped_column(String(20), nullable=True)
    pdf_path: Mapped[str | None] = mapped_column(String(511), nullable=True)
    dispatched_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    dispatch_method: Mapped[str | None] = mapped_column(String(20), nullable=True)
    # 'email' | 'postal' | 'in-person' | 'whatsapp'
    dispatch_record_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("dispatch_records.id"), nullable=True
    )
    is_revoked: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    revocation_reason: Mapped[str | None] = mapped_column(Text, nullable=True)

3. Reuse map

4. API surface

Routes under /api/education/.

5. Service layer

src/aayojana/education/services.py:

• admit_student(member_id, scholar_no, branch_id, joining_date, parents, blood_group, aadhaar_plain) -> StudentAdmission — encrypts Aadhaar at rest; stores last-4 in clear; tags member with 'student' tag.
• link_member_or_create(name, dob, parent_phone, gender) -> Member — for fresh admissions where the person is not yet a member; idempotent with phone match; creates member then admission.
• transfer_student(student_id, new_branch_id, transfer_date) -> StudentAdmission — closes current row with status='transferred-out' and creates fresh row at new branch.
• exit_student(student_id, exit_date, reason, generate_tc=True) -> CertificateIssued | None — closes admission; optionally issues TC certificate.
• enroll_student(student_id, course_id, academic_year, instructor_override=None) -> CourseEnrollment — validates prerequisites; rejects if not met (overridable with reason).
• register_examiner(member_id, designation, qualifications, sambhavana_rate, bank_details_plain) -> ExaminerProfile — encrypts bank details; tags member with 'examiner'.
• assign_examiner_panel(examination_id, examiner_assignments) -> list — populates panel JSON; reserves sambhavana liability in Vitta Payroll.
• record_examination_result(examination_id, student_id, marks, grade, status, examiner_id) -> PareekshaResult — validates marks ≤ max_marks; auto-status from pass_marks if not provided.
• bulk_submit_results(examination_id, results_csv) -> list[PareekshaResult] — bulk import.
• publish_results(examination_id) -> int — sets exam.status='results-published'; emits Comms message to each student; auto-issues course-completion certificates if applicable.
• issue_certificate(certificate_type, student_id, course_id=None, examination_id=None, issuer) -> CertificateIssued — assigns next serial via sequence_numbers; renders PDF using template (Sanskrit-aware, VijayaDV-preserved); stores at pdf_path.
• dispatch_certificate(certificate_id, method, recipient) -> DispatchRecord — links to existing dispatch infrastructure.
• revoke_certificate(certificate_id, reason, approver) -> CertificateIssued — sets is_revoked; audit_event.
• compute_sambhavana_payable(examiner_id, period) -> float — sums sambhavana from completed exams in period; for Vitta payroll batch.
• cultural_program_register(program_id, performers, related_event_id=None) — captures performers list; if related to event, links it.

6. UI / Templates

• Admission Register — table view with scholar number, name, course, status; bulk actions; export to PDF for inspection.
• Admission Form — long form: personal (auto-fills from member if existing), parents, emergency contacts, medical, Aadhaar (masked input), photo, accommodation.
• Student detail page — tabbed: Profile / Enrollments / Exam History / Certificates / Accommodation.
• Course Catalog — card grid; admin edit.
• Examiner Panel selector — examination form modal: search examiners by qualification chips; assigns role (pradhana / sahaaya); auto-fills sambhavana from rate card.
• Examiner Registry — list with bank-details masked unless role permits view.
• Result Entry Sheet — examination-scoped grid: students × max_marks; bulk paste-from-Excel supported; per-row examiner remarks textarea.
• Result Publication preview — students see their marks before publish; admin reviews; one-click publish triggers Comms.
• Certificate Generator — template picker (Sanskrit body editor), preview, batch issue for course completion.
• Cultural Program calendar — annual view; clickable for detail.
• Education Dashboard — active students count, exams this month, results pending publish, certificates issued YTD, top examiners by load.

7. Migration plan

Single migration. Depends on Members at 0018 (member.id FK), Asset Mgmt rooms at 0019 (venue FK), Events at 0023 (related_event_id FK), Vitta payroll for sambhavana liabilities.

8. Cross-module dependencies

Reads from

• Members (Agent 3): members.id — students, examiners, instructors all are members.
• Asset Mgmt (this agent): rooms.id for exam venues and cultural program venues.
• Events (this agent): event_instances.id for cultural programs that belong to festivals.

Writes to

• Vitta Payroll & Sambhavana (Agent 1): every result-published triggers sambhavana payable for examiner panel; cultural program performer honorariums likewise.
• Comms (Agent 5): admission confirmation, result publication notice, certificate dispatch (email PDF or postal), examiner sambhavana acknowledgement.
• Newsletter (Agent 5): pareeksha pass-list and cultural program reports flow into newsletter.
• Audit Trail (Agent 2): Aadhaar reads, examiner bank-details reads, certificate revocations, malpractice marks → audit_event.
• Reports (Agent 5): student census, pass percentage by course, examiner utilisation, certificates-issued YTD.
• Dispatch (existing DISA): certificate physical postal dispatch reuses dispatch_records.

9. Implementation phases

• Phase A — Schemas + basic CRUD. Migration 0025. Admission CRUD, course catalog, examiner registry (with encryption), examination schedule, results entry, certificate issue, cultural program CRUD.
• Phase B — Cross-module integrations. Sambhavana liabilities → Vitta; certificate dispatch via Comms; Aadhaar audit_event; result publication → Comms; cultural programs ↔ Events linkage.
• Phase C — Workflows + dashboards. Bulk result import, certificate template editor (Sanskrit body), pass-list newsletter feed, examiner panel auto-suggestion, TC generation on exit.

10. Open questions

1. TC (transfer certificate) generation — auto on exit or manual? Recommend: prompt admin on exit; default to "Yes, generate"; admin can suppress.
2. Aadhaar encryption key management — KMS in GCP (Cloud KMS in aayojana project) or in-app symmetric? Recommend: Cloud KMS in asia-south1; envelope encryption.
3. Examiner bank details PII — same KMS? Recommend: yes, same KMS key set; per-tenant key isolation if Phase C.
4. Certificate template versioning — keep historical templates so re-issued/reprinted certs match originals? Recommend: yes; template_version field is for that.
5. Pareeksha grade scale variation — different boards use Uttirna/Anuttirna vs O/A+/A vs marks-only. Per-course grade_scale? Defer to Phase B.
6. Student fee receipts — captured via course_enrollments.fee_paid or via Vitta donation/receipt flow? Recommend: via Vitta as fee-tagged transactions; we mirror fee_paid for view.
7. Multi-course concurrent enrollment — one student in 3 courses simultaneously: enrollment unique on (student, course, year) handles it.
8. Examiner panel sambhavana split — pradhana 60%, sahaaya 40%, etc. Stored per-exam in panel JSON or computed from rate? Recommend: per-exam JSON for full audit; rate is the default.
9. Cultural program ticketing — Aayojana doesn't run a ticketing engine; ticket_price is informational only; capture via Vitta donations.
10. Performer profile depth — visiting acharya / external concert artist data — store inline JSON or temporary external_artist table? Recommend: JSON for v1; promote if patterns emerge.