A Laravel 12 application that runs the procurement cycle end to end: Projects → Prep Lists → Request for Quotation → Quotation tracking → Purchase Orders → Receiving, plus the supplier / procuring-entity directory, Excel export, back-up tooling, and settings & privileges. Built for Philippine government-style procurement but works for any institution.
Status: actively developed and proven in a small-team deployment. The procurement chain is functional and database-backed, with role-based access control (None / View / Manage) enforced server-side and reflected in the UI, and activity logging wired across modules. Role × module matrix, settings, and the admin area are Administrator-only. Settings → Backup works. See Project status for an exact, honest breakdown.
- What this system includes
- Module tour
- Database schema
- ER diagram
- Table reference
- End-to-end flow
- Installation
- Default accounts and seeding
- Configuration details
- Security and access control
- Pagination
- Concurrency: multiple users on one box
- Back-ups and restore
- Testing
- Project status
- Known limitations
- Layout of the repository
- Documentation
- License
| Module | What it does | Status |
|---|---|---|
| Projects | List / search / create / edit / delete projects; award amount, delivery period, per-item fulfillment tracking; soft-delete; worksheet export (.xlsx) and print-ready sheet |
✅ Database-backed |
| Prep Lists | Compile one or more Project items into a prep list with minimum shelf-life requirements | ✅ Database-backed |
| Generate RFQ | Pick quoted/unquoted items per supplier, generate RFQ .xlsx files from a letterhead template (single file or ZIP for multiple suppliers) |
✅ Database-backed, tested |
| Quotation | Track supplier quotations grouped by prep list; upload the supplier's returned .xlsx; extract prices/brand/expiry per item; short-dated expiry flag; hands off to PO when complete |
✅ Database-backed |
| Purchase Order | Create a PO from an awarded quotation (draft); send it; receive lines (full qty per line); cancel with reason; admin-only delete of draft/cancelled; PO moves Draft → Sent → Partially received → Completed automatically | ✅ Database-backed |
| Entities | Directory of procuring entities (DOH offices, PGH, …) and suppliers with contact details | ✅ Database-backed |
| Notifications (bell) | Delivery-deadline alerts derived from due dates + project fulfillment progress | ✅ Real, data-driven |
| Role-based access control | Role × module matrix (None / View / Manage) enforced server-side on every module route; admin bypass; greyed/hidden write affordances for view-only roles; shared access-restricted dialog; friendly 403 page | ✅ Working |
| Item Catalog | One row per item on a fully-received PO line: order counts, qty, total spend, best/latest price, last supplier; item detail page with per-supplier comparison, price-over-time chart and full purchase history | ✅ Database-backed |
| Settings → Privileges | Administrator-only user management and the role × module access matrix (None/View/Manage); Administrator rows are locked full access | ✅ Working |
| Settings → Logs | Activity/log viewer — real rows from ActivityLogger on every module create/edit/delete, plus user/matrix changes; paginated at 50 rows per page; 365-day retention via scheduled activity:prune |
✅ Working |
| Settings → Backup | Full database backup (schema and contents) via App\Support\DatabaseDumper (through the app's own DB connection — no external mysqldump); one-click restore selected from the history list (DatabaseRestorer), with an automatic safety snapshot before every replay; download or delete any backup |
✅ Working |
| Excel export | Project summary export (.xlsx) produced by ProjectSummaryWorkbook, paired with a print-ready folio view |
✅ Working |
- Username/email + password.
- Accounts must be active (
is_active = 1) and have a role assigned. - Login is throttled (
throttle:6,1— 6 attempts per minute per IP). - Expired/inactive sessions are dropped by
EnsureSessionIsActivemiddleware. - Logout via a sidebar button; a confirmation modal prevents accidental logouts.
- Login page reports precisely which field was wrong on failure (no vague “invalid credentials” for known-good accounts).
- One project record per procurement job: title, reference number (
RF-YYYY-NNNN), procuring entity, mode of procurement (small value, competitive bidding, shopping, …), amount awarded, date awarded, delivery days, optional delivery due date, notes. - Per-item fulfillment: each project line has quantity, unit cost, awarded unit price, on-hand stock, and
fulfillment_status(pending/fulfilled). - Progress bar shows
fulfilled / total itemswith a percentage. - Search bar filters by title, reference no., or procuring entity (server-side
q); pagination shows 10 projects per page with the selection persisted across pages. - A display-filter row sits between the search bar and the View dropdown; it combines three optional filters that narrow the list server-side:
- Entity — shows only projects assigned to the selected procuring entity.
- Mode — shows only projects in the selected mode (Shopping, Small value, Competitive bidding, Direct contracting).
- Fulfillment status —
Open (unfulfilled items)shows projects with at least one unfulfilled line;Completed (all fulfilled)shows projects with no unfulfilled lines.
- Empty with filters returns “No projects match the current filters” with the Add button hidden so it doesn't mislead.
- View modes: Content (card list), Details (metadata columns), List (dense table) — all paged from the same paginated slice (
?page=). - A hidden, folio-sized print sheet renders in print preview (8.5 × 13 in officio, landscape) via a modal; clicking Print calls
window.print()with the correct@pagesize. The same content streams from/projects/{project}/download(projects.download) as an.xlsxworkbook built byProjectSummaryWorkbook. - Item lines already compiled into a prep list are locked in the form (read-only, greyed, lock icon, tooltip naming the prep list). Saving around locked lines works as usual; a locked line cannot be changed or removed here — do it in the prep list first. Removing a sourced line from the form is refused with a friendly message; nothing is saved until the prep list stops referencing the line.
- A prep list with only unprogressed linked quotations (no priced lines, no uploaded file, no PO) deletes cascade-style along with them: the confirmation modal lists the supplier RFQs that will also be removed. If any linked quotation is progressed, the delete is refused with per-supplier reasons — the same all-or-nothing pattern as the project edit lock.
- Soft-deleted (
deleted_at) rather than hard-deleted, so links from prep lists survive; cascade rules prevent deleting a sourced project. - The line-level field
project_items.fulfillment_statusis what progress counts; there is no project-level status enum.
- Compile one or more project items across projects into a single prep list.
- Each line carries a per-project minimum remaining shelf life (months), set at compile time and clamped against the item's
default_min_shelf_life_months. - A submitted prep list can only be deleted along with all linked quotations; it refuses (with per-supplier reasons) when any linked quotation is progressed — has priced lines, an uploaded file, or a PO reference. All-or-nothing, like the project edit lock.
- Sources are recorded in
preplist_item_sources(quantity sourced per project line) — the provenance chain from project → prep list is intact. - Generating the RFQ from a prep list (
/preplist/{preplist}/generate-rfq) validates that supplier candidates are chosen and that every item is either quoted or unquoted — nothing partial. - A display-filter dropdown (next to the search bar) narrows by created by (users who have compiled preplists), item category (drugs and medicines / supplies and equipments), and quotation status (Quoted / Awaiting quote); selection auto-submits and shows a badge count of active filters; empty filtered results say “No prep lists match the current filters”.
- Choose the suppliers to invite and mark which items are inquoted vs. unquoted.
- Produces one
.xlsxfile per selected supplier fromresources/templates/RFQ-Template.xlsx;- Single supplier → direct download.
- Multiple → a ZIP containing one
.xlsxper supplier.
- Per item, the file shows: item number, description, unit, Min. Shelf Life, Brand, Unit Price, Expiry, Remarks — all blanked for the supplier to fill in.
- Files are named
RFQ-YYYY-NNNN-<SUPPLIER>.xlsxand stored instorage/app/private/quotations/on generation. - The template explicitly contains no available-quantity column — suppliers are never asked for available stock.
- Service-time — Excel generation is pure OOXML via
ZipArchive(no PhpSpreadsheet dependency).
- Quotations are grouped by prep list; each quotation row shows the supplier, attached RFQ number, per-item response status, and upload status.
- A search bar (supplier / prep list name / reference no. / RFQ number) narrows the tracker server-side; a matching prep list group keeps its matching quotations.
- A display-filter dropdown (next to the search bar) narrows by supplier, created by (users who generated quotations), and quotation status (Quoted / Awaiting quote, matched on priced lines); selection auto-submits and shows a badge count of active filters; empty filtered results say “No quotations match the current filters.” The prep-list-groups-per-page choice moved inside it.
- Upload the supplier's returned
.xlsx(POST /quotation/{quotation}/document,quotation.store-document):- The file is validated against the RFQ template before parsing (header layout + column mapping), so garbage files are rejected with a clear message.
- The parser reads prices, brand, and expiry per item via
App\Support\QuotationWorkbookParser; expiry values in any format (MM/YYYY, month names, ISO) are normalized byApp\Support\ExpiryParser. - Zero or negative unit prices are rejected at upload time.
- Per-quotation status is computed from priced lines:
Quotedwhen at least one priced line exists;Awaiting quoteotherwise.- Short-dated lines (expired or expiring within 30 days) are highlighted in the item breakdown; per-project colour coding compares supplier expiry vs the project's delivery date + minimum shelf life.
- The document can be replaced (
POST /quotation/{quotation}/document,quotation.store-document) — a second file replaces the first. Refusing when a PO already references these quoted lines (same guard as the remove-document path): the file and its prices become immutable once a PO exists. - Delete the document only (
DELETE /quotation/{quotation}/document,quotation.destroy-document) — keeps the quotation row but removes the priced lines; blocked once a PO is generated from it. - View-only roles see a disabled "Submit quotation" affordance (inert button, access-restricted modal on click).
- Re-uploading a document is guarded by a state fingerprint — re-submitting the page against a file another user already replaced is rejected (see Concurrency).
- A PO can only be created from a quotation, never from nothing; evidence of winning the business lives on the quotation (priced lines), not on the PO itself.
- Allocation across project sources is proportional — the PO splits down to the project lines so you know exactly which project it fulfils — recorded in
purchase_order_item_allocations. - PO numbers come from the shared
number_sequencestable asPO-YYYY-NNNN. - Status flow:
draft → sent → partially_received → completed(cancel any time before completion; admin-only delete of draft/cancelled POs). - Receiving: each line has a Mark as received checkbox with a confirmation modal — confirming records the full line qty as received and moves the PO to
partially_received/completed. Once a line is received it is locked in the UI and un-marking is refused server-side;expected_receivedguards against stale pages and races. - Single-file Excel download at creation; no re-download in this build.
- People with view-only roles get the receive checkboxes disabled (greyed, access-restricted modal on click). Unauthorized modification attempts are still blocked server-side by the
module:purchase-ordermiddleware.
- Tabs for Procuring Entities and Suppliers (the same
/entitiesroute selects the tab). - CRUD with search; soft-delete so historical links survive.
- Only the fields the jurisdiction requires (name, type, address, contact person, contact number, email).
- One row per item that appears on at least one fully received purchase order line (receipt qty covers line qty), aggregated from the PO chain server-side: order counts, total qty purchased, total spend, weighted average, best/latest price, last supplier, last purchase date.
- List page KPIs (items tracked / total spend / suppliers used / lines bought), all filtered by the same criteria.
- Filters: search item or unit, category, supplier, from/to date range (on PO issue date); paging links preserve the filter state.
- Item detail (
/item-catalog/{item}): full purchase history (every completed line, linked to the PO), per-supplier comparison (orders/avg/best/latest/spend/share), weighted avg / best-price / last-purchase tiles, and an inline SVG price-over-time chart. - Supplier profile page (
/entities/supplier/{id}) gains a PURCHASE HISTORY section listing every item bought from that supplier with the same aggregates.
- Administrator-only user management: list / search / create / edit / toggle status / delete.
- The delete and toggle operations are protected from self-targeting (you cannot deactivate or delete your own account).
- Separate Role × Module matrix (
role_module_access) withNone / View / Managefor every seeded role. Administrator is bypassed everywhere — its grants are not editable. - Changing grants is logged to
activity_logs. - Ability to search users live (
GET /settings/privileges/search).
- Every module write (create/edit/delete) writes an
activity_logsrow:user_name,role_name,action,module,record_label,subject_type,subject_id. - Filters: module, user, action, from-date, to-date; results paginated 50 per page by default, selectable between 10 / 25 / 50 / 100.
- Nothing is written without an action — the
logspage is read-only. - Retention (pruning):
php artisan activity:prunedeletes entries older than a retention window (default 365 days).--days=Nsets a custom window;--dry-runprints how many entries would be deleted without deleting anything. The command is registered on the weekly schedule, but the server still needs a cron/task entry that runsphp artisan schedule:runfor it to fire:Without cron, run* * * * * cd /path/to/app && php artisan schedule:run >> /dev/null 2>&1activity:prune --days=365manually (e.g., weekly, from a task scheduler or as part of the backup routine). There is no archival — pruned entries are deleted, not exported first — so adopters who need a longer audit trail should pass a larger--daysor export before pruning.
- Back-up page gated by
access-settings(administrator-only). - One-click full database dump (schema and contents) produced through the application's own DB connection (no external
mysqldumpbinary required). - One-click restore — pick a back-up from the history list, type
RESTOREto confirm. Before any statement is replayed, the system takes a safety back-up of the current state automatically; the sessions table is left untouched so operators stay logged in. - Back-ups stored in
storage/app/private/backups/with timestamped names; the 15 newest are kept automatically. - Download or delete any backup from the page.
- Comprehensive in-page restore instructions for a non-technical admin remain as the manual fallback.
- No transactional guarantees — the safety snapshot is the recovery path if a restore fails mid-way.
- Static placeholder — not wired (front-end buttons don't mutate anything).
- Red: due within 2 days / already overdue.
- Amber: due within 7 days with 0 items fulfilled.
- Fuelled entirely by
projects.delivery_due_dateandproject_items.fulfillment_status; no stored notification rows. - The bell badge counts projects that are at risk — not “notifications you haven't read”.
The schema is 34 migrations deep and is described in full by the ER diagram below. Tables are grouped into auth/roles, catalog, procurement/project & prep lists, quotation tracker, purchase orders, and audit.
erDiagram
roles ||--o{ users : has
roles ||--o{ role_module_access : grants
users ||--o{ activity_logs : performs
users ||--o{ projects : creates
users ||--o{ preplists : creates
users ||--o{ quotations : creates
users ||--o{ purchase_orders : creates
users ||--o{ purchase_order_receipts : receives
users ||--o{ po_cart_items : holds
quotation_items ||--o{ po_cart_items : "staged in"
procuring_entities ||--o{ projects : owns
item_categories ||--o{ items : groups
items ||--o{ project_items : describes
items ||--o{ preplist_items : describes
items ||--o{ purchase_order_items : describes
projects ||--o{ project_items : contains
preplists ||--o{ preplist_items : consolidates
preplists ||--o{ quotations : "RFQ per supplier"
preplist_items ||--o{ preplist_item_sources : "sourced from"
project_items ||--o{ preplist_item_sources : feeds
suppliers ||--o{ quotations : receives
suppliers ||--o{ purchase_orders : supplies
quotations ||--o{ quotation_items : lists
preplist_items ||--o{ quotation_items : asked
quotations ||--o{ purchase_orders : awards
purchase_orders ||--o{ purchase_order_items : lines
quotation_items ||--o{ purchase_order_items : priced_by
purchase_order_items ||--o{ purchase_order_receipts : received_as
purchase_order_items ||--o{ purchase_order_item_allocations : split_to
project_items ||--o{ purchase_order_item_allocations : fulfils
roles {
bigint id PK
string name UK
string description
}
role_module_access {
bigint id PK
bigint role_id FK, UK "unique with module"
string module UK
string level "default none"
timestamp created_at
timestamp updated_at
}
users {
bigint id PK
bigint role_id FK
string name
string email UK
string password
boolean is_active
timestamp created_at
timestamp updated_at
}
activity_logs {
bigint id PK
bigint user_id FK
string user_name
string role_name
string action
string module
string record_label
string subject_type
bigint subject_id
timestamp created_at
}
number_sequences {
bigint id PK
string key UK
smallint year UK
int last_number
}
procuring_entities {
bigint id PK
string name
string type
string address
string contact_person
string contact_no
string email
timestamp deleted_at
}
projects {
bigint id PK
bigint procuring_entity_id FK
bigint created_by FK
string title
string reference_no
string mode_of_procurement
decimal amount_awarded
date date_awarded
smallint delivery_days
date delivery_due_date
text notes
timestamp deleted_at
}
item_categories {
bigint id PK
string name UK
}
items {
bigint id PK
bigint category_id FK
string name
string unit
smallint default_min_shelf_life_months
int box_size "nullable, units per box"
}
suppliers {
bigint id PK
string name UK
string address
string contact_person
string contact_no
string email
timestamp deleted_at
}
preplists {
bigint id PK
bigint created_by FK
string name
string reference_no UK
text notes
timestamp created_at
timestamp updated_at
timestamp deleted_at
}
project_items {
bigint id PK
bigint project_id FK
bigint item_id FK
int quantity
int in_stock
decimal unit_cost
decimal awarded_unit_price
string fulfillment_status
}
preplist_items {
bigint id PK
bigint preplist_id FK, UK "unique with item_id"
bigint item_id FK, UK
smallint min_shelf_life_months
}
preplist_item_sources {
bigint id PK
bigint preplist_item_id FK
bigint project_item_id FK
int qty
}
quotations {
bigint id PK
bigint preplist_id FK
bigint supplier_id FK
bigint created_by FK
string rfq_number UK
timestamp generated_at
timestamp sent_at
string document_path
string document_name
int document_size
}
quotation_items {
bigint id PK
bigint quotation_id FK, UK "unique with preplist_item_id"
bigint preplist_item_id FK, UK
smallint min_remaining_months
decimal unit_price
int supplier_box_size "nullable"
string brand
date expiry
int available_qty
timestamp quoted_at
}
purchase_orders {
bigint id PK
bigint quotation_id FK
bigint supplier_id FK
bigint created_by FK
string po_number UK
string status
date issued_on
date expected_delivery_on
string payment_terms
string deliver_to
decimal delivery_fee
text notes
timestamp cancelled_at
string cancel_reason
}
purchase_order_items {
bigint id PK
bigint purchase_order_id FK
bigint quotation_item_id FK, UK "globally unique"
bigint item_id FK
int qty
decimal unit_price
smallint min_remaining_months
}
po_cart_items {
bigint id PK
bigint user_id FK, UK "unique with quotation_item_id"
bigint quotation_item_id FK, UK
int qty "default 1"
timestamp created_at
timestamp updated_at
}
purchase_order_receipts {
bigint id PK
bigint purchase_order_item_id FK
bigint received_by FK
int received_qty
string lot_no
date expiry_date
date received_on
}
purchase_order_item_allocations {
bigint id PK
bigint purchase_order_item_id FK
bigint project_item_id FK
int qty
}
Auth & roles (4 tables)
| Table | Purpose |
|---|---|
roles |
The three seeded roles (Administrator implicit full access; Procurement Officer, Viewer have matrix grants). |
role_module_access |
Per role per module grant rows; level is 'none' / 'view' / 'manage'. Administrator is implicit and has no rows. |
users |
is_active gate, role_id FK. |
number_sequences |
Counter for RFQ-, PL-, PO- sequences per year. |
Catalog (4)
| Table | Purpose |
|---|---|
item_categories |
Groups items (drugs and medicines, supplies and equipments, …). |
items |
The catalogue of orderable goods with default_min_shelf_life_months and nullable box_size (units per box). |
procuring_entities |
The buying institutions (DOH Region VII, PGH Manila, …). |
suppliers |
External suppliers with contact details. Soft-deleted. |
Projects / prep lists (5)
| Table | Purpose |
|---|---|
projects |
The procurement job. reference_no is indexed. |
project_items |
Lines inside a project: item, quantity, on-hand stock, awarded unit price. |
preplists |
Compiled prep lists (PL-YYYY-NNNN). |
preplist_items |
Unique (preplist_id, item_id) — an item appears at most once per prep list. |
preplist_item_sources |
Which project lines each prep-list line is sourced from; preserves provenance. |
Quotation tracker (3)
| Table | Purpose |
|---|---|
quotations |
One row per (prep list, supplier) pair — rfq_number unique. |
quotation_items |
The supplier's prices for each preplist_item; unique (quotation_id, preplist_item_id). Nullable supplier_box_size / available_qty capture the supplier's packaging and offered quantities. |
po_cart_items |
Per-user cart staged for checkout — quotation_item_id unique per user; a pre-checkout line from an awarded quotation. |
Purchase orders (4)
| Table | Purpose |
|---|---|
purchase_orders |
status: draft, sent, partially_received, completed, cancelled. |
purchase_order_items |
Lines from the RFQ to the PO; unique (purchase_order_id, quotation_item_id). |
purchase_order_receipts |
One row per receipt event; received_qty always records the full line quantity (the UI has no partial-qty field yet), and lot_no / expiry_date exist for a future capture step. Kept even after full receipt. |
purchase_order_item_allocations |
Pro-rated split of each PO line back onto the project lines it fulfils. |
Audit (1)
| Table | Purpose |
|---|---|
activity_logs |
Append-only audit trail, pruned by activity:prune (default 365 days); subject_type + subject_id identifies the affected record; used for the Settings → Logs viewer and role-matrix changes. |
Projects (items + quantities)
│
▼
Prep Lists (compile items across projects, set min. shelf life)
│
▼
Generate RFQ (per supplier; .xlsx letterhead template)
│
▼
Supplier returns the completed file → Quotation tracker → prices read in
│
▼
Proceed to PO → PO created (draft, PO-YYYY-NNNN) → Excel PO downloaded
│
▼
Send to supplier → lines received per line item → PO auto-moves Sent → Partially received → Completed
- Create a project — pick a procuring entity, items, per-item quantity and data. The project has no status enum; progress reads
project_items.fulfillment_status. - Compile a prep list — select project items across projects and set a minimum shelf life per line (clamped to the item default).
- Generate the RFQ — select the suppliers to solicit and click Generate. Single supplier → one
.xlsx; multiple → a.zip. The template hides available quantities and price columns from the supplier. - Upload the supplier's returned file — the tracker auto-matches each row by item description (case-insensitive, normalized). Unit price ≤ 0 is rejected. Expiry is parsed crash-proof (
09/2026,Sep 2026, ISO…). - Create the PO — one click from the Quotation screen (manager-only, viewers get the access-restriction modal). The PO is created in
draftand split proportionally across the projects' lines. - Send the PO — moves it to
sent. - Receive lines — tick the line's checkbox and confirm the modal; the status auto-moves to
partially_receivedorcompleted. Once received, the line is locked and cannot be unmarked. - Delete / cancel — delete only in
draft/cancelled(admin-only); cancel allowed anytime.
- PHP 8.2+ with OpenSSL, Mbstring, Zip, and MySQL (or MariaDB) support.
- Composer.
- Node.js for
npm run build(Tailwind v4 + Vite). - MySQL 8 or MariaDB 10.4+ running locally.
# 1. Clone
git clone https://github.com/islacchi/purchasing.git
cd purchasing
# 2. PHP dependencies
composer install
# 3. Frontend dependencies and build
npm install
npm run build
# 4. Environment
cp .env.example .env
php artisan key:generate
# 5. Configure your database connection in .env (MySQL):
# DB_CONNECTION=mysql
# DB_HOST=127.0.0.1
# DB_PORT=3306
# DB_DATABASE=purchasing_db
# DB_USERNAME=root
# DB_PASSWORD=
# 6. Migrate and seed
php artisan migrate
php artisan db:seed
# 7. Serve
php artisan serveThen visit http://127.0.0.1:8000. Log in as admin / admin and change the password immediately.
| Name | Role | Password | |
|---|---|---|---|
admin |
admin@example.com |
Administrator | admin |
user1 |
user1@example.com |
Procurement Officer | user1 |
user2 |
user2@example.com |
Viewer | user2 |
APP_ENVshould beproductionin any shared environment;APP_DEBUGmust befalsein production.APP_URLin multi-PC use should be the LAN hostname or IP of the box hosting the app (e.g.http://192.168.x.y:8000).- Sessions, cache, and queues all use the
databasedriver in this build (see.env.example). - File uploads (supplier-returned quotations, RFQs) are stored under
storage/app/private/. - Back-up files, if produced, land under
storage/app/private/backups/— see the Back-ups and restore section.
Seeded module grants: Procurement Officer → Manage on all six modules, Viewer → View on all six modules, Administrator bypasses the matrix entirely (no grant rows). Grants are editable under Settings → Privileges and every change is logged to the activity trail.
| Question | Answer |
|---|---|
| Who can log in? | Any active account with a role. Inactive users are refused at login. admin password must be changed on first deploy. |
| How do roles map? | Administrator → full access including Settings and PO delete (locked in the matrix, not editable). Procurement Officer / Viewer → access per module configured in Settings → Privileges. |
| How is module access configured? | Settings → Privileges → the Role module access matrix. None / View / Manage for each of the six modules. Officer defaults to Manage everywhere, Viewer to View everywhere. |
| How is the matrix enforced server-side? | module:{module} middleware (App\Http\Middleware\EnsureModuleAccess) wraps the six module route groups: GET/HEAD/OPTIONS require View, any other verb requires Manage. Administrator bypasses. |
| What if a URL is blocked? | Direct URL hits without access get the friendly 403 — Access restricted page. |
| How is the matrix reflected in the UI? | View-only roles see inert greyed buttons (cursor-not-allowed); modules they cannot view are hidden from the sidebar; blocked actions open a shared amber access-restricted modal. |
| What does Settings gate? | access-settings gate (administrator only). |
| Settings → Logs | Real activity rows: module writes and matrix/user changes; browsable with module/user filters. |
| Session lifetime | Inactivity + absolute lifetime enforced by custom middleware. |
| Login throttling | throttle:6,1 on the login POST route. |
| PO deletion | Restricted to Administrator; only draft and cancelled orders may be deleted. |
| Prep list deletion | Blocked while any quotation references it. |
| Project deletion | Blocked while any prep-list source references its items. |
| Quotation cancellation | Unattached quotations (never used in a PO) may be deleted by anyone with Manage access; once used in a PO, only Administrators may delete. |
The shared amber dialog (components/access-restricted-modal.blade.php) appears for view-only roles on blocked actions. It is decorative — the actual enforcement is server-side regardless. The dialog exists so the button can still be explored without taking the action.
Every list that could contain more than a page of data is paginated server-side; per-page selection is available on every paginated screen.
| Screen | Default page size | per_page selector |
|---|---|---|
Projects (/projects) |
10 | 10 / 25 / 50 / 100 |
Prep Lists (/preplist) |
10 | 10 / 25 / 50 / 100 |
Quotation tracker (/quotation) |
10 | 10 / 25 / 50 / 100 |
Purchase Orders (/purchase-order) |
10 | 10 / 25 / 50 / 100 |
Settings → Logs (/settings/logs) |
50 | 10 / 25 / 50 / 100 |
Selection persists via the query string (?page= preserves q and sort).
This is a single-box, multi-user deployment (a LAN box accessed by several staff at once). There is deliberately no real-time sync / live push — every page reads fresh from MySQL on load, which is the correct model for this scale.
- No silent overwrites on hot shared writes. Three flows carry an optimistic checkpoint:
- Receiving a PO line — opening a line's confirmation modal and posting
expected_receivedagainst a receipt that already changed on the server is refused with a refresh-page prompt, and an already-received line stays marked. - Editing a project — the form posts
expected_project_fingerprint(md5 over all editable fields and line rows); a stale submit is refused with a clear message. - Uploading a replacement quotation file — posts
expected_document_fingerprint(md5 of path/name/size); if another user already replaced the file, the upload is refused.
- Receiving a PO line — opening a line's confirmation modal and posting
- Every page reads fresh from MySQL on load. Reloading (browser refresh) always shows the latest state; the stale-fingerprint guards refuse edits against out-of-date data instead of overwriting silently.
- Receiving + tracking are transactional behind the scenes (
DB::transaction()), so a half-saved toggle cannot corrupt the PO state. - Number sequences (
number_sequences) uselockForUpdate()when allocatingRFQ-,PL-,PO-numbers — two concurrent allocations cannot collide. - Unique constraints on junction tables enforce no-duplicate-rows at the database level.
- View-only roles are blocked from writes both at the middleware and the controller layer — a read-only role cannot mutate state even by guessing the route.
- Last writer wins on edit forms — only edit forms with no optimistic checkpoint behave this way (Prep Lists, Entities, RFQ regeneration). Two users editing the same prep list simultaneously will see only one's changes after save; the lose is silent but recoverable via the other tab's refresh.
- Sessions are per-user and persisted in the database. Logging out actually clears the session entry, not just the cookie.
- Print + export regenerate from live data at request time — never read stale snapshots.
One-click, full-database schema + contents — no external mysqldump binary required; the dump goes through the app's own connection.
storage/app/private/backups/*.sql. The filename is purchasing-<YYYY>-<MM>-<DD>-<HHMM>-<SS>.sql. Only the 15 newest are kept.
- Create: click Create back-up on
/settings/backup. - Download: click the Download icon next to a backup file in the history table.
- Delete: click the Delete button; accept the confirmation prompt.
- Restore: pick a backup and click the Restore pill in its row — the modal asks for the confirmation phrase
RESTORE(safety snapshot of the current state is taken automatically before the replay starts).
Administrators can restore the database from any back-up in the history list, without touching the command line.
- On
/settings/backup, pick the back-up you want and click Restore on that row. - Type
RESTOREexactly, then confirm. A safety back-up of the current state is created first, and a failed restore leaves it available for rollback. The MySQLsessionstable is not replayed, so acting administrators stay logged in. - The back-up file itself is validated (must end with
-- Dump completedmarker) before the replay starts; a truncated or foreign file is rejected. - After the restore, verify counts in the panel on
/settings/backup— all logs and modules show the restored counts.
The command-line path below stays valid as an alternate restore route, and remains necessary for installations whose DB driver cannot replay the statement list directly.
The restore is a one-way door: it replaces the current database with the backed-up one. Even so, the in-app flow takes a safety back-up of the current database automatically before anything is replayed, so the pre-restore state is always recoverable as a new back-up in the history list — restored with one more click.
- Projects, prep lists, quotations, POs visible in lists (counts match expectations).
/settings/logsshows the restored entries with today's timestamp for the back-up entry itself./purchase-ordershows the restored orders.
Ask any earlier operator which back-up they need; the history table on the page is authoritative.
composer run test # or: php artisan testEvery suite feature-tests through RefreshDatabase on in-memory SQLite. Current baseline: 248 passed / 1430 assertions. Quotation upload tests patch worksheet XML inside the generated workbook to simulate supplier submissions.
Role-access tests (ModuleAccessTest) cover the seeded grants, server-side middleware enforcement on all module routes, the privileges matrix update flow, sidebar visibility, viewer affordances (inert buttons, restricted dialogs, 403 page), and the admin-bypass rule.
Legend: ✅ done · 🚧 in progress / not started.
- Authentication — username/email + password, account activation, session inactivity & absolute lifetime enforcement, throttled login, clear login-error messages
- Entities — procuring entities and suppliers: list/search/create/edit/delete, soft-delete, stored contact details
- Projects — list/search/create/edit/delete, delivery due-date tracking, per-item fulfillment status, progress bar, soft-delete, Excel export & print-ready folio sheet
- Prep Lists — list/search/create/edit/delete, item compilation with source quantities, per-project expiry/shelf-life rules, grouped by source project
- RFQ Excel generation — letterhead template, dynamic item rows,
.xlsxdownload, ZIP for multiple suppliers, expiry/brand/unit price/shelf-life fields pre-labelled - Quotation — database-backed tracker (grouped by prep list), supplier-file upload with strict template validation, per-item price/brand/expiry extraction, per-project expiry-vs-delivery colour coding, short-dated warning, delete/replace the uploaded file (doesn't delete the entry) — blocked end-to-end once a PO references those quoted lines, optimistic fingerprint guard on re-uploads
- Purchase Order — create from awarded quotation (proportional allocation across project sources), draft → Sent → Partially received → Completed, cancel with reason, admin-only delete of draft/cancelled POs, Excel download on generation
- Pagination — every row-based list paginates server-side with adjustable per-page size (Projects, Prep Lists, Quotation, Purchase Orders, Settings → Logs)
- Optimistic concurrency — three hot paths guarded: PO receipt toggle (state snapshot), project edit (fingerprint), quotation document re-upload (fingerprint)
- Project edit lock —
syncItems()reconciles instead of wiping: existing lines are matched and updated, new lines created, removed lines deleted only when unsourced; lines sourced into a prep list are immutable end-to-end (read-only in the form; refused server-side; the save rolls back with a friendly message if a sourced line is removed) - Tests — 250 passing (1436 assertions)
- Delivery notifications — header bell feed from due dates + fulfillment progress (red: due ≤ 2 days or overdue, amber: due ≤ 7 days — or ≤ 14 with nothing fulfilled yet); clears when the risk resolves; empty projects never alert
- Excel export — project summary as
.xlsx(plus printable folio view) - Data-integrity guards — a project cannot be deleted while sourced into a prep list; a prep list cannot be deleted while any of its linked quotations is progressed (prices, document, or PO); deleting a prep list cascades its linked quotations; a quoted quotation requires its uploaded file removed first; document delete is separate from quotation delete.
- Unique junction indexes — composite unique constraints on
preplist_items/quotation_items/purchase_order_items; duplicate key violations surface as validation errors - Expiry normalization — supplier
.xlsxexpiry cells (MM/YYYY, month names, ISO dates) parsed crash-proof byApp\Support\ExpiryParser; expiry columns are realDATEcolumns - Role-based access control —
role_module_accessmatrix (None / View / Manage) configured from Settings → Privileges; enforced by themodule:{module}middleware - Role-aware UI affordances — view-only roles see inert greyed buttons and access-restricted dialogs; sidebar hides modules a role cannot view
- Activity logging — module writes recorded; Settings → Logs viewer is live (filterable, paginated)
- Activity log retention — automatic pruning:
php artisan activity:prunedeletes entries older than 365 days (--days=Ncustom window,--dry-runpreviews, refuses--days < 1); registered on the weekly schedule (runs with cron/taskphp artisan schedule:run) - Settings → Backup — administrator-only full-database backup with one-click in-app restore (automatic pre-restore safety snapshot, confirmation phrase, session preservation) plus plain-language restore instructions
- Item Catalog —
/item-catalog: one row per item on a fully-received PO line, aggregated server-side (order counts, total qty/spend, weighted avg, best/latest price, last supplier); KPIs (items tracked / spend / suppliers used / lines bought); filters (search, category, supplier, from/to date range); item detail page (/item-catalog/{item}) with full purchase history, per-supplier comparison table, weighted avg / best-price / last-purchase tiles, and an inline SVG price-over-time chart; nav sidebar,role_module_accesskey, sidebar icon, README docs, and feature tests updated.
- PhpSpreadsheet migration — if
ext-gdis enabled later, the manual workbook generator can be replaced
- A quotation whose prep list is missing can be locked out of normal editing but NOT of restoration. The UI now offers a one-click Restore prep list button on the banner for Manage-level roles; the row (and its items, allocations, and this quotation) is un-trashed intact, and the quotation becomes fully editable again. If restoration fails or the role is View-only, the banner still offers "Delete quotation" as the permanent escape hatch, and
Preplist::withTrashed()->find($id)->restore()remains the DB-level path as a last resort. - Viewers can't Manage their own profile. Password change/update is admin-only in the current build; viewers cannot reset their own passwords.
Accepted trade-offs in the current build — worth knowing before relying on the numbers:
-
Requested quantity is an upper bound. It is the sum of quantities in non-deleted projects; it is not reduced by received PO quantities, stock on hand, or fulfillment status.
-
Savings estimates exclude real-world costs. They use the requested quantity × (average − best) from current quotes and ignore freight, taxes, lead time, minimum order quantities, unit conversions, and delivery windows. Supplier-stated brand/expiry are shown but not enforced against a project's delivery date.
-
Deleting an uploaded quotation file deletes its priced lines. The price history derived from those lines cannot be rebuilt from the file afterwards.
-
Supplier colour is not persisted. Colours are assigned by sorted supplier order, so adding or removing a supplier can reassign chart colours.
-
Soft-deleted suppliers keep their historical priced lines visible because the index reads submitted quotation rows. If a supplier's history must remain, restore it under Entities rather than deleting it.
-
Ties count as a win for every tied supplier in the competitiveness scorecard.
-
Notifications are live by design. The bell badge shows current delivery risks only, and it clears when the risk is resolved (all lines fulfilled or covered by stock) — there is deliberately no persisted notification log or read state; projects with no items never raise an alert.
purchasing/
├── app/
│ ├── Console/Commands/ # activity:prune — retention prune (weekly schedule; --days / --dry-run); db:dump — dev snapshot helper
│ ├── Http/Controllers/ # Auth, Entity, Entities, Projects, Preplist, ItemCatalog, PurchaseOrder, Quotation, Settings
│ ├── Http/Middleware/ # EnsureSessionIsActive, EnsureModuleAccess (module:{module})
│ ├── Models/ # 21 models + casts, scopes, relationships
│ ├── Support/ # RfqQuotationWorkbook, PurchaseOrderWorkbook, QuotationWorkbookParser,
│ │ # ExpiryParser, DeliveryNotifications, ActivityLogger, ModuleAccess,
│ │ # ProjectSummaryWorkbook, DatabaseDumper, DatabaseRestorer, BackupStorage, Pagination
│ └── Providers/ # access-settings gate; view composer feeds the bell icon
├── database/
│ ├── migrations/ # 34 migrations covering the whole domain (seeded role grants, activity logs,
│ │ # unique junction indexes, expiry → DATE columns)
│ ├── seeders/ # AdminUserSeeder (admin / user1 / user2 + seeded module grants)
│ └── purchasing_db.sql # committed dump for the dev snapshot
├── resources/
│ ├── css/ # compiled CSS entry (Tailwind v4 via Vite)
│ ├── js/ # compiled JS entry (chart.js, Alpine alternative none — vanilla)
│ ├── templates/ # RFQ-Template.xlsx and PO-Template.xlsx (letterhead documents)
│ └── views/ # Blade modules mirroring the sidebar; role-aware affordances via canModule()
│ ├── components/ # access-restricted-modal (shared amber dialog)
│ └── errors/ # 403 — friendly module-access page
├── docs/ # RUNBOOK.md (back-up & DR); Teaching-Guide-Purchasing-System.md (staff training)
├── routes/
│ └── web.php # All routes (login/logout public; rest behind auth + session.active;
│ # six module groups additionally behind module:{module})
├── storage/app/private/ # Uploaded supplier files and back-ups live here
├── tests/Feature/ # Feature suites per module (incl. ModuleAccessTest, PreplistTest, QuotationTest, …)
└── public/ # Vite build, favicon, robots.txt, .htaccess
docs/Teaching-Guide-Purchasing-System.md— onboarding guide for new users (this README extends it)docs/RUNBOOK.md— disaster recovery, back-up/restore drills, and unmanaged-server operations (start here when replacing the PC)
The Teaching Guide covers staff onboarding; the RUNBOOK covers operations and disaster recovery. This README is the architecture and feature reference.
The Laravel framework is open-sourced under the MIT license.