# Backend instructions

These rules apply to all work in `backend/`. Read them together with `backend/AGENTS.md` and [the project instructions](PROJECT_INSTRUCTIONS.md). The goal is a maintainable API that saves data correctly and can grow through measured improvements.

## Current scope

Stage 1 provides Laravel 13, versioned API routing, JSON errors, and a database-independent health endpoint. Stage 2 adds Arabic/English Laravel administration for directorates, schools, staff users, fixed user types, and historical school/supervisor assignments. MySQL, login/logout, and CLI creation of a protected main administrator remain in use. Public registration is disabled. See [the Stage 2 record](STAGE_2_MANAGEMENT.md#local-implementation-and-verification) and [the root README](../README.md#backend-administrator). Counselors now have assigned-school student viewing, creation, and Excel import. The counselor interviews register now supports scoped creation, editing, viewing, and soft deletion. Interviews, parent meetings, vocational guidance, bulletins and studies include supervisor review; the remaining counseling forms, background workers, and production capacity remain future work.

## Code structure

- Keep routes in `routes/api.php` and API controllers under `app/Http/Controllers/Api/V1`.
- Controllers coordinate requests and responses. Put input validation in Form Requests, access rules in Policies, and response fields in API Resources.
- Use Eloquent models for relationships, casts, and focused reusable query scopes. Put multi-step business workflows in focused action/service classes, grouped by feature when needed within Laravel's existing structure.
- Simple CRUD may use Eloquent directly in a controller. Extract a class when logic becomes complex or shared; do not add a repository/interface for every model or create empty architectural layers.
- Reuse existing code, use descriptive names and explicit PHP types, and keep dependencies directed toward shared infrastructure rather than coupling unrelated features.
- Keep business rules out of routes, response resources, and generic helpers. Avoid hidden database writes or external calls in accessors and serialization.
- Preserve the `/api/v1` contract. Return deliberate fields and consistent validation/error responses; never expose models wholesale or leak SQL and credentials to clients.

## Administration screens and school contacts

- Follow [the administration UI contract](PROJECT_INSTRUCTIONS.md#administration-ui-and-ux): sidebar/navbar/avatar shell, table-based modules, modal create/edit, preserved filters after saving, and hyperlinks to related details.
- Keep full-page and modal form rendering on the same authorized routes and reuse the same Form Requests and domain services. HTML requests may retain fallback redirects; modal writes return JSON success or field validation errors. A fragment header must never bypass authentication, authorization, CSRF, or record-version checks.
- Keep user-facing interface code separate from business behavior. The admin layout composes shell/theme/styles partials; `backend/public/js/admin.js` handles dialogs, language/theme controls, and lookups. Shared colors and dimensions remain in root `style-theme.config.json`.
- Treat school manager name, phone, and email as nullable contact fields on the school. Require a manager name when manager phone/email is supplied, validate email, and store phone as text. These fields must not create a `users` record, role, password, or login capability.
- Keep school detail relationships bounded and eager-loaded. Use named, authorized routes for school/directorate/counselor/supervisor links. Super-admin school details retain a reserved Students section. Student records now exist in a separate table managed only through authorized counselor endpoints; do not invent student accounts or unscoped counts.

## Backend login access

The backend login portal must accept only active `super_admin` accounts. Reject directorate heads, supervisors, and counselors even with valid passwords or a forged role in the request. Protect every administration route with authentication and server-side super-admin/status checks. Seeded staff accounts do not receive backend login access. Public registration remains disabled.

## Public content and staff portal

The separate React staff portal uses the `staff` session guard and `/staff-auth/session`, `/staff-auth/login`, and `/staff-auth/logout` web endpoints. Accept only active directorate heads, supervisors, and counselors; super admins use the backend portal. Retain CSRF, generic errors, login throttling, session regeneration, role/status rechecks, and no-store session responses. Future staff data routes must use `staff-only` middleware and record-level authorization; frontend route checks alone are insufficient. Staff logout preserves an independently signed-in backend administrator.

Public news/articles use read-only throttled API routes, allowlisted type/locale, and bounded pagination (six items, deterministic date/ID order). Exclude drafts/future publications and private fields. Render bodies as plain text. `PublicationSeeder` provides labeled bilingual sample content, refuses production, and preserves edits.

Frontend requests use the `VITE_BACKEND_URL` Laravel base URL, including staff authentication and XLSX transfers. Maintain credentialed CORS on `api/*` and `staff-auth/*`, allowlisting exact frontend origins through `CORS_ALLOWED_ORIGINS`. Preserve CSRF/session protection on all staff writes. Never use wildcard origins for private responses. Keep local frontend/backend hostnames consistent; document HTTPS/SameSite requirements for cross-site cookies in [deployment instructions](DEPLOYMENT.md).

## Directorate-head management

Directorate-head management API routes (`/api/v1/directorate/*`) live in `routes/web.php` to receive session and CSRF protection. Require both `staff-only` and `directorate-head`; keep all middleware aliases in the same `alias()` call because later calls replace the map. Apply scope to lists, details, counts, lookup results, related filters, and every write. Return 404 for inaccessible records and reject privileged roles or supplied directorate fields. Inherit the actor's directorate server-side. Recheck the head's active role and scope under transaction locks before saving. Reuse the administration save service, validation, versions, audit records, and assignment-history rules. Response resources must exclude credentials and private fields. Keep frontend query caches separated by staff ID/directorate and clear them on logout.

## Arabic and English localization

- Follow [the project language requirements](PROJECT_INSTRUCTIONS.md#arabic-and-english-support). The Laravel login, super-admin dashboard, and future backend screens must support Arabic and English, with Arabic as the default.
- Resolve the request locale centrally and allow only `ar` and `en`. Persist the preference so guests can choose a language and keep it after login and logout. Invalid or missing preferences must safely fall back to the documented default.
- Use Laravel language files and stable translation keys for views, validation, authentication feedback, and user-facing error responses. Keep both languages complete; do not expose raw translation keys or internal exception details to users.
- Set the rendered page's `lang`/`dir` and translate titles, controls, and accessibility labels. The backend must use shared theme configuration for locale-specific font choices and logical layout rules for RTL/LTR.
- If an API returns localized human-readable messages, define and validate its locale-selection contract, such as `Accept-Language`. Keep machine-readable fields, error codes, canonical roles, and payload structure unchanged. Include locale in cache keys for localized responses and use the corresponding `Vary` header when response caching depends on a language header.
- Do not translate user-entered records or duplicate database rows per interface language. MySQL must continue to store Arabic and English correctly using `utf8mb4`.
- Test both locales, unsupported-locale fallback, preference persistence through authentication/logout, translated validation, unchanged authorization, and stable API contracts. CLI operational messages may remain English unless a command explicitly offers localization.

## MySQL and schema design

- Use MySQL for application data in development and production. `backend/.env.example`, the database fallback, and this workspace's local environment now use MySQL; keep credentials in ignored environment files.
- Use InnoDB, strict SQL mode, and `utf8mb4` for Arabic text. Record the deployed MySQL version and collation and exercise database-dependent features against that version.
- Design migrations around actual access patterns. Use appropriate types, foreign keys, unique constraints, nullability, and intentional deletion rules. Keep related IDs the same type.
- Store commonly filtered, joined, sorted, and validated fields as typed columns. Use JSON only for genuinely flexible content; avoid a universal key/value schema for all counseling data.
- Add indexes for real queries. Choose composite index column order from filtering and ordering patterns; inspect existing indexes to avoid duplicates. Extra indexes also increase write cost and storage.
- Keep dates consistent, preferably UTC with conversion at the application boundary. Define identifier uniqueness and Arabic search/collation behavior explicitly.
- Use reviewed migrations for schema changes. For large tables, plan lock duration, staged backfills, rollout compatibility, and recovery before applying changes.

## Efficient queries and bounded requests

- Apply authorization scope, filtering, sorting, and pagination in SQL before retrieving records. A user-supplied school or owner ID is not proof of access.
- Paginate all growing lists. Default to 25 records and enforce a maximum of 100 per page unless a documented endpoint needs a different limit. Allowlist filters and sort columns and validate request sizes.
- Select only fields needed for each response, including keys required by relationships. Eager-load required relationships to avoid N+1 queries. When adding domain models, enable lazy-loading prevention in development/tests and cover important list endpoints with query-count checks.
- Use SQL aggregates, `exists`, `withCount`, and `withExists` where appropriate. Do not load all records to count, filter, or summarize them in PHP, and do not issue repeated queries inside loops.
- Use deterministic ordering with a unique tie-breaker. Prefer cursor pagination for large sequential lists when the ordering and product flow support it; use ordinary pagination when page numbers/totals are required.
- Use bounded `chunkById`/`lazyById` processing for large exports, backfills, and imports. Review memory use, batch size, model events, and validation when using bulk operations.
- Use parameter bindings for values. Allowlist SQL identifiers and ordering; never interpolate user input into raw SQL.
- Inspect `EXPLAIN` with representative data for important or slow queries. Use `EXPLAIN ANALYZE` only in a controlled environment because it executes the query. Measure query time, query count, rows examined, and response size before claiming an improvement.
- Avoid leading-wildcard searches and functions on indexed filter columns for large interactive lists unless measurements justify them. Choose an indexed or full-text search strategy after testing actual Arabic search behavior.

## Reliable saving and concurrent edits

- Validate and authorize every write on the server. Explicitly select writable fields; never mass-assign the entire request or trust client-provided ownership, approval state, or role fields.
- Treat MySQL as the source of truth. Return a successful save only after the required transaction commits. Browser drafts and caches do not replace durable records.
- Wrap related writes in short transactions. Enforce uniqueness and relationships in the database as well as validation; validation alone cannot prevent races.
- Use atomic SQL updates for counters. Use row locks or a version-based conditional update when concurrent changes can break a rule; keep lock order consistent and retries bounded.
- For autosave and shared editing, use a revision/version check so stale requests cannot silently overwrite newer work. Return a conflict that the frontend can resolve and preserve the user's draft. Test out-of-order saves and two editors.
- Use persisted idempotency keys with unique constraints for operations that must tolerate duplicate submissions or retries. Scope keys to the actor and operation, check payload consistency, and return the original outcome for a valid replay.
- Keep transactions free of email, network calls, and lengthy file processing. Dispatch jobs after commit. Where delivery must survive a process crash between commit and dispatch, use a transactional outbox or equivalent durable delivery design.
- Coordinate attachment storage with database state using explicit pending/complete states and cleanup of failed/orphaned uploads. A database rollback does not roll back a filesystem write.
- Define retention and audit requirements for counseling records. Record who changed sensitive records and when without duplicating private case content in application logs. Verify backups through restore exercises before production use.

## Background work, caching, and scaling

- Move expensive reports, exports, imports, and notifications to background jobs. Return a job identifier/status and authorize access to generated results.
- Jobs must tolerate retries, have bounded attempts/backoff/timeouts, and expose failures for recovery. Set worker timeouts below the queue retry interval to reduce overlapping execution; still make side effects idempotent.
- Cache only measured repeated work. Define TTL, invalidation after committed writes, and keys that include the relevant school/user/permission scope. Avoid caching private data in shared public entries and control cache stampedes for expensive rebuilds.
- Local file cache/sessions and the synchronous queue are foundation defaults only. Before running multiple application instances, provision a shared cache/session store and a durable asynchronous queue, with monitored workers. Redis is an option, not an installed or verified service in this stage.
- Keep request handling independent of one server's local disk; use appropriate shared/private object storage for attachments when scaling horizontally.
- Rate-limit login, expensive reads, exports, and writes by the relevant actor and operation. Bound uploads and bulk requests. Choose worker concurrency from measured database connection, CPU, memory, and I/O limits rather than maximizing worker count.
- Consider replicas, summaries, partitioning, or additional infrastructure only after measurements justify them. Critical reads after saving must observe the committed write.

## Verification and performance evidence

- Test authorization boundaries, validation, transaction rollback, uniqueness races, conflicting saves, and job retries where the feature introduces those behaviors.
- Database feature tests must run on an isolated MySQL test database matching the intended server. SQLite tests may remain for database-independent checks but cannot establish MySQL correctness.
- Before any migration, database test reset, or seeding, verify the resolved connection, host, database, and environment. Test commands must never target a development or production database containing real records.
- For growing list endpoints, verify bounded payloads and that query count does not grow with the number of returned records. Cover Arabic storage and search with representative examples.
- Establish a load-test profile before claiming capacity: dataset size, concurrent active users, request mix, duration, hardware, database version, and cache state. Agree on endpoint latency/error targets and report achieved throughput, p95/p99 latency, errors, query latency, memory, and queue lag.
- Monitor slow queries, database connections, request errors/latency, and job failures in production without logging sensitive payloads or SQL bindings. The existing health endpoint checks application liveness only.
- Run relevant PHPUnit tests and Pint for changed PHP. Record what was verified and what remains pending. No fixed user-capacity promise is valid without a representative load test.

## MySQL setup and verification

From `C:/laragon/www/masar`, use these application settings in `backend/.env` (credentials shown are local placeholders):

```dotenv
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=masar
DB_USERNAME=root
DB_PASSWORD=
```

Use a dedicated least-privilege account in production. If `DB_URL` exists, review it because it can override individual connection settings. Confirm that the selected PHP executable loads `pdo_mysql`.

Have the database administrator create the intended database with `utf8mb4` and the agreed collation. Existing SQLite data, if any, requires a separate reviewed transfer with row counts, relationships, and Arabic text verification; changing configuration does not transfer data.

Clear cached configuration, then inspect the resolved non-secret target:

```powershell
node scripts/backend.mjs config:clear --no-interaction
```

Verify `database.default` is `mysql` and the MySQL host/database match the intended target using application configuration without printing passwords. After the server/database are provisioned, use a read-only connection check such as `db:show --database=mysql --no-interaction`. This is the connection gate; a successful health response does not prove MySQL works.

Review the pending migrations and take a backup if the target contains data. Only after confirming the target and schema plan should you run:

```powershell
node scripts/backend.mjs migrate:status --no-interaction
node scripts/backend.mjs migrate --no-interaction
```

An empty database can report a missing migrations table during the status check. Never use `migrate:fresh`, reset, or destructive test traits against an unverified target. The local application database `masar` has been created and migrated; no SQLite data transfer was needed. PHPUnit now uses a separately guarded local MySQL database, `masar_auth_test`, which is disposable and must contain test data only. Authentication tests verify the resolved target before resetting tables.

## Framework references

- [Laravel database queries, transactions, and monitoring](https://laravel.com/docs/13.x/database)
- [Laravel relationship loading and N+1 prevention](https://laravel.com/docs/13.x/eloquent-relationships)
- [Laravel queues, retries, timeouts, and transaction dispatch](https://laravel.com/docs/13.x/queues)
- [MySQL index optimization](https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html)


## Supervisor read-only workspace

`/api/v1/supervisor/*` uses staff sessions, the supervisor role/access policy, no-store responses, and throttling. Provide GET-only overview, school, and counselor lists/details; supervisors must not gain access to management writes through any API or admin route. Interview review is a specific exception: the scoped review endpoint accepts only a decision, note and version, as defined below. The actor must be active, with an active directorate.

Use current supervision assignments (`ended_at IS NULL`) to scope counselors. A visible school must have a current school assignment for a counselor supervised by the actor, and belong to the same directorate. No independent supervisor-to-school assignment table is introduced. Apply these restrictions in SQL to lists, details, counts, related links, and filters. Sharing a visible school does not authorize another counselor. Exclude cross-directorate school links, privileged users, ended assignments, credentials, and unrelated counts. Reject inaccessible IDs with 404. Retain prefix search, deterministic pagination, bounded related results, eager loading, and tests proving query counts stay bounded. Never trust a supplied supervisor/directorate ID to grant access.

The supervisor Students tab remains reserved. A separate student table now serves counselor student management; no supervisor student access or student login is granted by this increment. Frontend read-only controls supplement server authorization; hiding an Edit button alone is insufficient. Separate supervisor and directorate query caches by role/user/directorate, and clear both at logout.


## Counselor self-profile access

The GET-only `/api/v1/counselor/profile` endpoint uses `staff-only`, the counselor role policy, web sessions, throttling, and no-store responses. Require an active counselor in an active directorate. Always derive the profile from the staff session; prohibit supplied record/scope selector IDs and expose no arbitrary-user route. Use an explicit resource allowlist for account fields, directorate names/code, and current supervisor name/email/mobile. Exclude password hashes, remember tokens, and unrelated account data. Resolve only an unended supervision assignment whose supervisor is active, has the supervisor role, and belongs to the same directorate. Return null for absent/ended/invalid assignments. No profile update, password change, staff management, or other-role access is provided by this increment. Cover role/status boundaries, selector tampering, assignment changes, credential exclusions, and rejected writes with isolated MySQL tests.


## Counselor student management and Excel

Students are records in `students`, separate from `users`, with no login. Counselors can list/view and add students only for active same-directorate schools with a current school assignment. Scope schools, lists, details, template downloads, previews, and imports on the server. Recheck actor role/status, directorate, school and current assignment under transaction locks before writes. Keep profile/supervisor pages read-only. Other roles' student sections remain reserved until explicitly implemented.

Required student fields: student number, full name, grade, section. Optional: birth date, gender, guardian name and phone; guardian name is required when a guardian phone is supplied. Store identifiers/phones as text and dates as dates. Enforce school/student-number uniqueness in MySQL and never overwrite records on duplicate imports. Use foreign keys, bounded pagination, prefix search, eager school loading, and indexes matching school/name/grade access. Audit creation/import counts without storing student values in audit fields. No editing, deleting, school transfer, national-ID collection, or student accounts are included.

Use the approved OpenSpout 4.x library on PHP 8.3 for `.xlsx` only. Keep templates bilingual, with a frozen header, text-formatted blank input rows and a separate Instructions sheet. Import the first sheet; accept canonical, Arabic or English field headings. Preview up to 10 rows, then require an explicit Import action in the frontend. Re-submit and revalidate the same selected workbook on confirmation rather than storing uploads or trusting preview data. Apply a 2 MB upload limit, 1,000-record limit, bounded ZIP entry/uncompressed-size checks, typed validation, formula rejection, row-numbered errors, and duplicate detection within the file and school. Invalid imports must save zero records. Use transactions and bulk chunks for successful imports; constrain upload and import work to the scoped/throttled staff endpoints.

Test real XLSX generation/round-trip and import, Arabic, leading zeros, dates, wrong/missing headings, formulas, duplicates, invalid rows, size/row limits, authorization loss, and query bounds on disposable MySQL. Run migrations normally; never reset the application database. Uploads and fixture workbooks remain outside version control.


## Counselor interviews register

Follow [the forms inventory](COUNSELOR_FORMS.md#first-implemented-form--2026-10-24) for current scope. Store interviews in typed MySQL columns with counselor, school and student foreign keys. Apply creator ownership and current school access to counselor lists/details and all content writes; other counselors sharing the school cannot access the notes. Supervisor access is limited by the separate review policy below. Recheck active actor/directorate/school and current assignment under transaction locks, and verify the student belongs to the locked school. Do not accept client-supplied ownership or review state.

Keep lists bounded at 25 with deterministic date/ID ordering, SQL month ranges, prefix student-name/number search, allowlisted type/follow-up filters, and eager student/school names. Preserve Arabic/English user data without translating it. Creation uses a unique counselor/request UUID and payload hash for replay safety; stale edits/deletes require a version conflict response. Soft deletion must not delete the student. Audit action and field names without private note content. New records use pending review; actual content changes reset the status and preserve previous review history.


## Supervisor form review

For interviews, use `SupervisorInterviewPolicy` to scope SQL by current supervision of an active counselor, current assignment of that exact counselor to the record school, active same-directorate school, valid student-school membership and non-deleted interview. Do not infer counselor ownership from a shared school or accept a supplied supervisor/directorate as authority. Existing school/user/student management permissions remain unchanged.

The supervisor review request allowlists `status` (`reviewed` or `approved`), optional `note` (maximum 5,000 characters), and `lock_version`. Reject answer/ownership/version-selector tampering. Recheck scope under locks on directorate, users in deterministic ID order, school, record and current assignments before saving. Both content and review writes advance a shared optimistic version. Actual answer edits advance `content_version` and set `review_status` to pending; a no-op answer save does not clear approval. Review creation appends a history row tied to the reviewed content version. Keep note text out of general audits/logs. Eager-load the latest review and reviewer on bounded lists/details; expose only selected student/counselor/reviewer names and IDs. Return current status plus latest note/version/date, so clients can identify a review of older content.


## Demo interviews seeder

Keep `DemoInterviewSeeder` opt-in through `masar:seed-demo-interviews` (preview by default; `--apply` to write), restricted to local/testing MySQL. Target only known prototype demo school codes, matching demo counselor emails and existing demo student numbers within active current assignments. Add review samples only through the valid current demo supervisor and the normal review service. Missing prerequisites must produce a useful failure or explicit skipped-slot counts, not invented ownership or account creation. Reuse scoped save/review services under a transaction. Stable UUIDs identify consumed slots, including soft-deleted records; preserve edited content, existing review history, accounts, students and assignments. Never reseed over user changes or restore deleted samples. Keep this separate from `DatabaseSeeder`, and verify dry-run behavior, reruns, edits/reviews/deletions, denied prerequisites/scope, production refusal and resulting workspace visibility on isolated MySQL tests.


## Parent meetings register

Apply the interview authorization, bounded SQL filters, draft/version, replay-safety, audit privacy and review-history rules to parent meetings through their separate models/policies/services. Typed parent fields are student/date/type/follow-up/reason/action; do not store interview-only referral/impact/concept fields in this form or accept them from counselor writes. Use canonical meeting types `in_person`, `phone`, `school_invitation`; dates use the MySQL range and optional reason/action are limited to 5,000 characters. The counselor inherits current school/ownership server-side. Supervisor access requires current supervision of that exact active counselor and current school assignment; review writes accept status/note/lock_version only. Keep management writes and student-detail access denied for supervisors. Parent reviews have their own foreign key/history and cannot alter interview status or history. Do not create guardian user accounts or include these records in existing interview seeders.

## Vocational guidance register

Treat vocational guidance as school/class activity records, without individual student links. Use typed topic/date/grade/section/session-count/beneficiary-count/course/evaluation columns in separate record/review tables. Topic/date are required; optional grade/section use bounded school-specific text, not a fixed prototype class list. Optional session counts are whole numbers 1–10,000 and beneficiaries 0–100,000; preserve null versus explicit zero. Bound topic to 255 characters, class fields to 50 and each narrative to 5,000. Apply the established counselor ownership, current school assignment, exact supervisor assignment, transaction-lock order, version/replay checks and review history. Supervisors save only status/note/version. Search topics with escaped prefix matching, filter dates by ranges, paginate and eager-load bounded relationships. Do not reuse student/parent-only fields, expose another counselor's records through shared schools, or include activities in existing interview seeders. See [the form implementation contract](COUNSELOR_FORMS.md#vocational-guidance--2026-10-25).

## Bulletins register

Keep counselor bulletins private and separate from public `Publication` news/articles. Typed record fields are name/date/target/copies/goals in `counselor_bulletins`, with separate review history in `bulletin_reviews`. Require name (max 255), MySQL-range date and a target from `all_students`, `grade_9`, `grade_10`, `parents`, `teachers`; UI defaults to All students. Optional copies are whole numbers 0–100,000, preserving null versus zero; objectives are optional up to 5,000 characters. There is no student/class/attachment relation. Apply existing ownership, active same-directorate school/current assignment, exact supervisor assignment, version/replay and transactional review rules. Filter escaped name prefixes and SQL date ranges plus audience/status, paginate and eager-load bounded response relationships. Do not expose bulletin contents through public publication APIs or add them to existing interview seeders. Supervisor writes accept decision/note/version only. See [the form contract](COUNSELOR_FORMS.md#bulletins-register--2026-10-25).

## Studies register

Store school-level studies in typed `counselor_studies` rows and separate `study_reviews` history. Require title (max 255) and MySQL-range date; optional sample description is bounded to 255, purpose (`goal`) and results to 5,000 each. Normalize omitted/blank optional text to null. No student, audience, copy-count or public publication relationship is introduced. Apply the existing private-owner/current-school/exact-supervisor scope, transaction locks, replay UUIDs and version checks. Search escaped title prefixes and date ranges, filter review status, paginate and eager-load bounded relationships. Supervisors may save only decision/note/version, and actual answer changes reset Pending while preserving prior review history. Do not publish private studies through the public article APIs or add them to existing seeders. See [the form contract](COUNSELOR_FORMS.md#studies-register--2026-10-25).

## Counseling committee register

Keep typed committee activity records and separate versioned review history. Require activity name (max 255), MySQL-range date and evaluation from excellent/very_good/good/acceptable; evaluation defaults to Excellent in the UI and remains independent of supervisor review status. Committee/external/teacher counts are optional whole numbers 0–100,000; preserve null versus zero and do not invent overlap/subset rules. Objectives are optional up to 5,000 characters. No student/teacher-account link or committee membership module is introduced. Apply existing private creator/current school/exact current supervisor scope, transactional assignment rechecks, optimistic versions, UUID replay protection and privacy-preserving audits. Filter activity prefixes/month/evaluation/review status in SQL, paginate and eager-load bounded relationships. Supervisors save only decision/note/version. No automatic seeding or public publication is added. See [the form contract](COUNSELOR_FORMS.md#counseling-committee-register--2026-10-25).

## Home visits register

Follow the student-linked register scope and workflow in separate `home_visits`/`home_visit_reviews` tables. Require a student from the authorized school, MySQL-range date, canonical visit type (preventive/remedial/absence_follow_up) and follow-up (none/ongoing/completed); UI defaults to Preventive/None. The only optional narrative is combined visit/action `course` (max 5,000). Do not accept parent-meeting reason/action or interview-only fields. Derive class from the student, and do not introduce guardian accounts, student login or location tracking. Enforce creator/current school/exact supervisor assignments in SQL and under existing transaction locks; retain versions, retry UUIDs, privacy-preserving audits, soft deletion and content-version review history. Filter student prefixes/date ranges/type/follow-up/status with bounded pagination/eager loading. Supervisors save decision/note/version only and receive no student-detail permission. Do not add these records to existing seeders. See [the form contract](COUNSELOR_FORMS.md#home-visits-register--2026-10-25).

## Case studies

Store case scalars as typed columns and ordered goals/sessions in separate relational tables, with a separate versioned review table. Require authorized school/student/date, canonical referral/type/employment; support partial optional sections and bounded rows (50 goals, 100 sessions). Bound short text to 255, narratives to 5,000, family size to 1–100 and working hours to 0–24 with two decimals. Enforce MySQL dates, birth <= registration <= dated sessions/closing, and sessions <= closing if supplied. Reject work details for non-working students and changing student identity on an existing case. Read current name/class from the student without editing student data.

Case progress is derived server-side from sessions/closing date, independent of Pending/Reviewed/Approved review. Apply exact creator/current active school/exact current supervisor scope, transactional assignment rechecks and ordered locks. Commit the case and child-row changes together; retry UUID hashes include the complete draft and child order. Shared optimistic versions cover content and review writes; real scalar/child/order changes reset Pending while unchanged saves preserve approval. Retain children/reviews on soft deletion. Do not log private field values or expose health/family narratives/child collections in list responses; select summary columns and paginate/eager-load bounded relationships. Full details alone load child rows. PUT replaces the complete draft, including optional sections/collections.

The frontend serializes autosaves after the first explicit creation, keeps edits made while a request is in flight, retries lost creation acknowledgements with the same UUID/payload and pauses automatic retry on failures/conflicts. Saved means acknowledged by MySQL through the API; unsaved in-memory drafts are not durable and must never be presented as saved or persisted in browser local storage. Guard unsaved close/refresh and preserve drafts on locale changes/refetches. No formal submission, attachments, digital signature, print/export, bulk review, restore UI or automatic seeding is introduced. See [the case-study contract](COUNSELOR_FORMS.md#case-study-implementation--2026-10-25).

## Demo examples for newer counselor forms

Keep `DemoCounselorFormSeeder` opt-in through `masar:seed-demo-forms`: preview by default, explicit `--apply`, optional validated `--form`, local/testing MySQL only. Do not include it in `DatabaseSeeder`, enable it in staging/production, reset the database or create accounts/students/assignments to fill missing prerequisites. Target only canonical active demo school/counselor markers, exact current assignments, existing same-school demo students where required, and the canonical current supervisor for review examples. Report actual target and missing/existing/skipped slots without credentials or sensitive narrative values.

Reuse each form's normal typed save/review services under an outer transaction, preserving independent schemas and versioned history. Use stable UUID slots including soft-deleted records to preserve user edits, review changes and deletions. Generate six bounded fictional examples per eligible school per form, with Pending/Reviewed/Approved variation. Cases should include typed section data, relational goals/sessions and all progress states; explicitly label private narratives fictional and use non-dialable contacts. Keep existing interviews/publications and account passwords unchanged.

Verify request-rule validity for all example variants, preview read-only behavior, API visibility, missing prerequisites, environment guards, transactional rollback, and exact rerun preservation including modified/deleted records and case children on isolated MySQL tests. Document any locally applied counts separately from production readiness.


## Directorate-head form viewing

Expose GET-only lists/details for all eight counselor forms under `/api/v1/directorate/{form}` with staff session, directorate-head middleware, throttling and private no-store responses. `DirectorateFormPolicy` must constrain both the form school and counselor to the active head's directorate in SQL. Inaccessible/deleted details return 404. Preserve existing paginated filters, deterministic date/ID ordering, explicit resources and summary-only case lists. Full case details may load the bounded goals/sessions, but do not grant general student-detail access.

Heads can read saved forms regardless of Pending/Reviewed/Approved status, including the latest supervisor note, reviewer and date. Keep historical forms visible when school/supervision assignments end; the school and counselor must still belong to the head's directorate. This differs deliberately from current-assignment supervisor scope. Reuse read handlers, but retain the original supervisor policy and service authorization on every review write. Heads must not receive create/edit/delete/review routes or UI actions for forms. Existing school/staff management remains available. Keep frontend cache keys scoped to role, actor and directorate, localize read-only instructions and use head-workspace hyperlinks.


## Multiple counselor records per student

Allow many independent records of the same form type for one student and counselor, including on the same date, and multiple different types. Never add uniqueness on student, student/type or student/date. Each deliberate Create operation gets a new request UUID; network retries of that operation reuse it and must not create duplicates. Preserve per-record review, version, soft-delete and case child history independently.

Interviews, parent meetings, home visits and case studies support a validated positive `student_id` list filter, applied in SQL alongside the existing counselor ownership/current school scope and other filters. A student ID must never expand access to other schools/counselors. Reuse the composite school/student/date indexes and bounded pagination. Student detail history must load one selected form type/page at a time, with create dialogs preselecting the current student. School-level vocational guidance, bulletins, studies and committee activities also allow multiple records but do not acquire artificial individual student links.


## Private form attachments

`FormAttachmentPolicy::FORMS` allowlists studies, bulletins, vocational guidance and committee activities. Attachment metadata uses four explicit nullable foreign keys and a database CHECK requiring exactly one parent; never accept an arbitrary model/table/path from the client. All list/download calls reauthorize the parent through the existing counselor, exact supervisor or directorate-head policy. Only the owning authorized counselor may upload/remove. Keep staff sessions, CSRF, throttling, no-store responses and inaccessible-parent/file 404 behavior; no public storage links or unsigned asset URLs.

Validate actual file content/MIME against its extension, max 10 MB, PDF/DOCX/XLSX/JPG/PNG only, and five non-deleted ready/pending files per parent. Office packages require bounded content-type/main-part inspection and must exclude macros. Store random extensionless paths on the dedicated private `form_attachments` disk, outside the public web root. Sanitize bounded download names, expose metadata only, and force binary attachment downloads with nosniff. Do not claim malware scanning or inline rendering.

Reserve a pending metadata row under the normal directorate/user/school/assignment/record lock order. Stream bytes outside the transaction, then recheck authorization and the parent version under locks before marking ready. Never expose pending files or return success before finalization. Upload UUID + parent/name/content hash makes ready retries idempotent; different content or stale versions return 409. Attachment changes advance both parent lock/content versions, reset Pending and add a privacy-preserving audit without filenames or content. Failed uploads remove their reservation/file when possible; `masar:prune-pending-attachments` previews abandoned pending rows older than 24 hours and `--apply` removes them with a locked state recheck. Retain metadata when disk cleanup fails.

Removal soft-deletes the attachment and retains its private bytes, consistent with form/history retention; removed files and deleted/inaccessible parents must not download. The pending cleanup command must never remove ready or retained files. Back up MySQL and private storage together, preserve storage across deployments, and define a reviewed retention policy before purging historical ready/removed files. Verify failure/replay/concurrency/limit/type validation, role scopes, cross-parent IDs, review invalidation, cleanup and private downloads with isolated MySQL and fake private disks.
