SYSTEM DESIGN AND REQUIREMENT TRACEABILITY

Purpose: track classroom key movement and faculty classroom inventory while
preserving the supplied keylog database and administrator credentials.

Architecture: Apache/LiteSpeed -> public/index.php -> Laravel web middleware
(CSRF, encrypted database sessions, headers) -> administrator/operator access
control -> controllers -> bound database queries or Movement service ->
InnoDB tables. Views use Blade, AdminLTE 4 and Bootstrap 5.3.8 local assets.

Identity boundaries:
Administrators authenticate against admins.username and password_hash.
password_verify accepts the original bcrypt hashes without resetting them.
Lecturers remain records in users, not newly created login accounts.
Kiosk access uses the existing shared system_settings PIN for 15 minutes.
The maintenance token authorises standalone utilities independently of an
administrator session, with a separate nonce and per-IP attempt counter.

Data model:
rooms 1 -> many key_items (fixed_room_id)
rooms 1 -> many resources
users 1 -> many key_transactions
key_items 1 -> many key_transactions
users many -> many class_sessions through user_sessions
key_transactions 1 -> many movement_notes
admins -> audit_events and optional recorded_by_admin_id on movements
key_items stores the current holder and due time; key_transactions preserves
the pickup/return event history and class details as recorded at movement time.
New audit records do not replace the original edit-history tables.

Movement rules:
Pickup requires an active available key, active holder, active assigned room,
future due date, usage and purpose. If a class is chosen it must be active or
replacement, and assigned to the chosen lecturer. A key-row FOR UPDATE lock
serialises concurrent attempts; insert and current-state update commit together.
Return requires the selected holder to match the locked key row. Inactive
holders/keys can still return. Key availability and current-holder fields are
cleared in the same transaction as the return event. Retries handle deadlocks.
Expected duration is descriptive; due_at is the operative overdue threshold.
New movements are immutable in the interface; correction notes are appended.

Management rules:
All catalog identifiers are allowlisted in Catalog. Unknown catalogs return
404. Field validation occurs on the server. Unique codes/usernames are checked
against their actual tables. Master record deletion is blocked when referenced.
History-bearing administrators are deactivated rather than deleted. Administrators
cannot disable/delete their own account. Editing a borrowed key is blocked.
Class assignments are replaced transactionally when a session is saved.

Requirements -> implementation / verification:
1 and 10,11: Laravel rebuild; catalog CRUD, movements, history, resource records,
class assignment and CSV import. Functional tests and source preservation checks.
1.1.2: original SQL retained byte-for-byte as the fresh file prefix; database
comparisons of legacy tables and hashes in integration tests.
1.2.1: / -> /login on either correctly mapped hostname. DNS/TLS require cPanel.
1.2.2: public/clear_cache.php; token, nonce, per-IP limiter; commands tested.
1.2.3: public/system_check.php; protected checks tested against local MariaDB.
1.3.1: supplied .env plus .inv reference; literal token verified.
2 and 11: create/edit/save/view/print/delete for master catalogs, shared Back and
Print controls. Referenced and financial-style event records retain audit history.
3 and 18: Laravel 13.31.0 locked; full vendor and platform checks included.
4,6,8,9: AdminLTE 4.0.0, Bootstrap 5.3.8 and custom faculty styling; responsive
sidebar and forms; local CSS/JS, without a CDN runtime dependency.
5: composer platform PHP 8.3.0; tested on PHP 8.3.6.
7: required labels *, mandatory notes, HTML attributes and server validation.
12: optional free Ollama adapter with aggregate context and tested local fallback.
13,15: additive legacy schema selected over destructive rewrite; approach
comparison in README. Locking selected over optimistic availability checks.
14: 68 official digital resources identified; 54 retrieved, per resource records.
16 and 19: README.txt/README.pdf, cPanel and C:\composer vendor instructions.
17: fresh keylog_db.sql and separate additive upgrade SQL.
20,21,22: measured test evidence, packaged source and dependency bundle. Exact
hosting acceptance, actual Ollama service, physical devices and DNS/TLS remain
external checks; no unsupported claim of 100% live production verification.

Operational constraints:
No scheduled work is needed for current synchronous features. There are no
email/SMS reminders, remote cPanel automation or physical key-cabinet sensors.
Quantities describe classroom stock; resources do not have a borrowing ledger.
The AI adapter cannot mutate the database. No hidden production QA account is
included in the export. Tests use a separate disposable database and account.
