Library circulation.

A school librarian opens her terminal at 07:15. The first thing she sees on /dashboard/library is the unpaid-fines KPI — which already includes the rows the 03:00 UTC cron created overnight. She sees three new acquisition receipts to enter, one boarder's lost-card report to handle, and fourteen students returning books between assembly and first period. None of those flows require her to leave the library cluster.

By 08:30 first-period students are dropping by. Each return triggers two things at the database layer that the librarian doesn't have to think about — the next reservation in the queue gets promoted to fulfilled (so the reader who was waiting gets a notification in the same transaction); and if the book was overdue, the nightly cron already wrote the fine row last night using the school's penalty rule. The librarian just clicks Return.

By 11:00 a department head's acquisition request is approved, marked ordered, and received — the receipt INSERT auto-creates the LIB-ACQ expense category on first run, posts the expense row, and writes the journal entry (DR 1400 / CR 1100). The CFO sees the spend on the finance dashboard before the lunch bell.

Eight chapters. One circulation engine. Read this one first.

Library's schema is wider than most SIS clusters because school librarianship is older than software. Thirteen tables across three migrations cover every workflow the librarian sees:

• Catalog — book_categories, library_books (with required_for_program_ids[] + required_for_level_ids[] + recommended_for_subject_ids[] for true per-program targeting), book_copies (per-physical with barcode + condition), and digital_resources (PDF / ePub / video).
• Membership — library_members (active / suspended / expired / revoked with full audit), library_cards (persistent qr_token + replaced_by chain), and library_subscription_tiers (max concurrent loans + duration + max renewals + digital access).
• Policy — library_penalty_rules with rule_type in (flat / per_day / tiered), grace_days, optional category targeting via applies_to_category_ids[], optional member-type targeting via member_types[], and a max_amount cap.
• Circulation — book_borrowings (with renewal_count + last_renewed_at from migration 00573), book_reservations (FIFO queue via queue_position), and library_fines (UNIQUE(borrowing_id, fine_type) from 00572 enables the cron upsert).
• Acquisition — book_acquisition_requests (approval workflow), book_acquisition_receipts (AFTER INSERT trigger auto-posts to ledger).

Every table has RLS scoped to public.get_school_id() and an updated_at trigger.

The member register at /dashboard/library/members tracks every student, staff member, parent (with a child), or external borrower with a formal lifecycle: active → suspended (with suspended_reason) → expired (when expires_at passes) → revoked (permanent, with revoked_reason). Re-activate from suspend; revoke is one-way.

Each active member holds at least one card at /dashboard/library/cards. The card carries a persistent qr_token (random 32-byte hex, generated by gen_random_bytes on INSERT — never re-rolled). When a card is lost, admin uses Replace which issues a new card AND links the old card's replaced_by back to the new one — so the borrowing history (which is attached to library_members.id, not the card) is preserved regardless of how many plastic cards the member has owned.

A school in Lagos charges 100 NGN/day with a 3-day grace; a school in Kigali uses a tiered scheme (250 RWF for days 1–7, 500 RWF for days 8–30); a school in Yaoundé waives all fines for textbooks but charges 50 XAF/day for fiction. One penalty-rule schema covers all three.

The SQL helper library_compute_fine(school, days, category, member_type) (migration 00569) finds the most specific matching rule, falls back to the default, applies grace_days, evaluates rule_type (flat | per_day | tiered), caps at max_amount if set, and returns the amount. Configure rules at /dashboard/library/policies.

Issue is the simple half — the Issue dialog on the catalog page or via mobile (screens)/library/scan-borrow.tsx — INSERT book_borrowings, UPDATE book_copies.status='borrowed', decrement library_books.available_copies.

Return is where the wiring matters. The mutation runs at /dashboard/library/borrowings: UPDATE the borrowing to status='returned', UPDATE the copy to 'available', re-increment available_copies. The AFTER UPDATE trigger book_borrowings_on_return_promote (migration 00574) sees the status change and calls promote_next_reservation(book_id) which finds the next pending reservation by queue_position and flips it to fulfilled — within the same transaction the librarian's click ran in. The waiting reader gets notified through the standard C5 dispatcher.

Renew is the RPC renew_borrowing(p_borrowing_id) (migration 00574). It validates the member's tier (allow_renewal + renewal_count < max_renewals), the reservation queue (refuses if anyone is waiting), and the current status. On success it pushes due_date = today + tier.loan_duration_days, increments renewal_count, stamps last_renewed_at, and waives any prior auto-generated overdue fine row (because the next cron run will recompute against the new due date).

At 03:00 UTC every day, the cron job library-overdue-fine-generation-daily calls run_library_overdue_fine_generation() (migration 00572). The function walks every book_borrowings row with status='borrowed' AND due_date < CURRENT_DATE AND returned_date IS NULL, looks up each row's book category, calls library_compute_fine, and UPSERTs the result into library_fines keyed on (borrowing_id, fine_type='overdue'). It also ratchets the borrowing's status to overdue — one-way; never demotes a returned or lost row.

Idempotency: rerunning the cron the next night doesn't duplicate rows — the UPSERT updates the existing fine's amount + days_overdue if the borrowing is still outstanding, and skips paid/waived rows entirely.

A reservation row exists in book_reservations with a queue_position integer per pending reservation per book. When a copy is returned, the trigger reads the book's pending queue ordered by queue_position NULLS LAST, reserved_at, picks the first, and flips it to fulfilled. The C5 communication dispatcher picks the row up and notifies the reader on their preferred channel.

Renewal interacts with the queue. If anyone is waiting, renewal is refused — the RPC raises with a count of waiting reservations so the librarian can explain to the member.

Acquisition is the cluster's other auto-ledger flow. A department head requests books on the Acquisition page; the librarian approves; the order is placed (offline); the books arrive; the librarian records a receipt with the total actual cost + supplier + invoice number. The INSERT fires library_receipt_post_expense (00569:319) which calls post_library_acquisition_expense(receipt_id):

1. Find-or-create the expense_categories(code='LIB-ACQ') row.
2. INSERT an expenses row with the total + supplier + invoice.
3. Call c4_post_journal_entry — DR 1400 Library books / CR 1100 Cash.

The CFO sees the spend on the finance dashboard atomically with the receipt. No reconciliation; no monthly batch.

• library.view — read every surface. Default for school admins, librarians, ops managers, and (scoped) students + parents.
• library.create — add books, copies, members, cards, reservations, acquisition requests, borrowings. Default for librarians.
• library.edit — suspend / reactivate / expire / revoke members, return + renew loans, pay + waive fines, mark cards lost / damaged / replace. Default for librarians and school admins.
• library.delete — hard delete books (soft-delete via deleted_at is preferred). Default for school admins.
• library.manage — configure penalty rules and subscription tiers. Default for school admins only (these affect every member's bill).