BiblioViva Documentazione
Documentazione tecnica

Database

Technical reference of the BiblioViva database, for developers and coding agents. The source of truth is src/lib/server/db/schema.ts (Drizzle) plus src/lib/server/db/fts.ts (FTS5); the meaning of the columns is specified in PLAN.md §4. When this file and the code disagree, the code wins and this file must be updated.

1. Overview

2. Diagrams

Solid lines are declared foreign keys; dotted lines are logical references without a foreign key.

2.1 All tables

logs in

has copies

is lent

borrows

is the class of

hosts

level_code

history

contains

class pictures

class pictures

frames

chosen for

records

is seen

asked for

rowid = id

acts

imports

writes, archives

writes

staff_user

session

biblio

item

loan

patron

class_group

class_group_level

school_level

loan_event

print_list

print_list_entry

class_photo_sheet

class_photo_frame

inventory

inventory_scan

wish

biblio_fts

audit_log

import_batch

note

calendar_event

sequence

text

name

PK

metadata_cache

text

key

PK

reading_level

text

code

PK

school_setting

text

key

PK

Every *_by, by_user_id and user_id column except session.user_id is a nullable foreign key to staff_user (created_by, updated_by, issued_by, returned_by, started_by, closed_by, scanned_by, wish.created_by, note.created_by, note.archived_by, calendar_event.created_by, calendar_event.updated_by); they are left out of the area diagrams below for readability. sequence, metadata_cache, reading_level and school_setting stand alone (biblio.reading_level and item.reading_level hold codes of reading_level without a foreign key, as patron.level_code and class_group_level.level_code hold codes of school_level: those tables are mirrors of the configuration). The view patron_class (§3.3b) joins patron, class_group, school_level and school_setting.

2.2 Catalogue

biblio_id (RESTRICT)

rowid = id

biblio

integer

id

PK

text

title

text

subtitle

text

original_title

text

responsibility

text

author

text

publisher

text

publication_place

integer

publication_year

text

isbn

UK

text

issn

text

ean

text

physical_description

text

series

text

series_number

text

dewey

text

item_type

text

kind

fiction | nonfiction | NULL

text

genre_code

text

reading_level

text

class_code

text

call_text

text

subjects

JSON array

text

accessible_formats

JSON array

text

notes

text

cover_path

text

cover_source

text

source

text

source_id

integer

created_by

FK

integer

updated_by

FK

text

created_at

text

updated_at

item

integer

id

PK

integer

biblio_id

FK

text

barcode

UK

text

call_number

text

call_head

text

call_text

text

reading_level

text

enumeration

text

status

available | lost | damaged | in_repair | withdrawn

integer

not_for_loan

0/1

text

acquisition_date

text

notes

text

inventory_number

UK

text

origin

integer

value_cents

text

withdrawn_on

text

withdrawn_reason

text

barcode_label_printed_at

text

spine_label_printed_at

text

created_at

text

updated_at

biblio_fts

integer

rowid

biblio.id

text

title

text

original_title

text

subtitle

text

author

text

responsibility

text

publisher

text

subjects

text

isbn

text

series

2.3 Patrons and circulation

class_group_id

group_id (CASCADE)

level_code

level_code

patron_id

item_id

loan_id (CASCADE)

class_group_id

sheet_id (CASCADE)

patron_id

class_group

integer

id

PK

text

kind

students | teachers

text

name

text

stage

text

notes

text

archived_at

text

created_at

text

updated_at

patron

integer

id

PK

text

card_number

UK

text

external_id

UK

text

first_name

text

last_name

text

search_name

text

patron_type

student | teacher

integer

class_group_id

FK

text

level_code

text

email

text

photo_path

text

card_printed_at

text

status

active | anonymized

text

circulation_note

text

notes

text

anonymized_at

text

created_at

text

updated_at

class_group_level

integer

group_id

PK

text

level_code

PK

school_level

text

code

PK

integer

position

text

label

text

name

text

stage

text

next_code

text

class_label

loan

integer

id

PK

integer

item_id

FK

UK while open

integer

patron_id

FK

text

issued_at

text

due_date

text

returned_at

NULL = open

integer

renewals

text

last_renewed_at

integer

issued_by

FK

integer

returned_by

FK

text

patron_type_snapshot

text

level_snapshot

integer

level_position_snapshot

text

stage_snapshot

integer

group_id_snapshot

text

group_snapshot

text

class_label_snapshot

text

school_year

text

notes

text

created_at

text

updated_at

item

integer

id

PK

text

barcode

UK

loan_event

integer

id

PK

integer

loan_id

FK

text

type

text

at

integer

by_user_id

FK

text

details

JSON

class_photo_sheet

integer

id

PK

integer

class_group_id

FK

or print_list_id

integer

print_list_id

FK

CASCADE

text

path

integer

width

integer

height

text

kind

photo | scan

text

detected_at

integer

uploaded_by

FK

text

created_at

text

updated_at

class_photo_frame

integer

id

PK

integer

sheet_id

FK

integer

position

text

corners

JSON

text

crop_path

text

state

pending | ignored | saved | erased

integer

patron_id

FK

text

assigned_at

text

saved_at

text

painted_at

text

created_at

text

updated_at

2.4 Staff, sessions and audit

user_id (CASCADE)

user_id

created_by, archived_by

created_by, updated_by

staff_user

integer

id

PK

text

username

UK

text

password_hash

text

display_name

text

roles

JSON array

integer

active

0/1

integer

must_change_password

0/1

text

source

config | database

text

last_login_at

text

created_at

text

updated_at

session

text

id

PK

sha256 of the token

integer

user_id

FK

text

created_at

text

expires_at

text

last_seen_at

text

ip

text

user_agent

audit_log

integer

id

PK

text

at

integer

user_id

FK

text

action

text

entity

text

entity_id

text

details

JSON

note

integer

id

PK

text

body

text

color

integer

pinned

0/1

integer

created_by

FK

text

archived_at

integer

archived_by

FK

calendar_event

integer

id

PK

text

starts_on

text

ends_on

text

title

text

notes

integer

created_by

FK

integer

updated_by

FK

2.5 Support tables

list_id (CASCADE)

entity_id (kind patrons)

entity_id (kind items)

inventory_id (CASCADE)

item_id (CASCADE)

by_user_id

print_list

integer

id

PK

text

kind

patrons | items

text

name

integer

created_by

FK

text

last_printed_at

text

created_at

text

updated_at

print_list_entry

integer

list_id

FK

UK with entity_id

integer

entity_id

UK with list_id

integer

position

text

added_at

patron

integer

id

PK

item

integer

id

PK

inventory

integer

id

PK

text

name

text

status

open | closed

text

started_at

integer

started_by

FK

text

closed_at

integer

closed_by

FK

text

notes

text

summary

JSON

inventory_scan

integer

inventory_id

FK

UK with item_id

integer

item_id

FK

UK with inventory_id

text

scanned_at

integer

scanned_by

FK

text

shelf

integer

misplaced

0/1

integer

found

0/1

staff_user

integer

id

PK

import_batch

integer

id

PK

text

kind

patrons | catalog | loans

text

filename

text

stored_path

text

mode

text

started_at

text

finished_at

text

summary

JSON

integer

by_user_id

FK

sequence

text

name

PK

integer

value

metadata_cache

text

key

PK

text

provider

text

payload

JSON

text

fetched_at

reading_level

text

code

PK

integer

position

text

label

school_setting

text

key

PK

text

value

3. Tables

In the column tables, "now" is the SQL default strftime('%Y-%m-%dT%H:%M:%fZ', 'now') (§5.1). Every INTEGER PRIMARY KEY AUTOINCREMENT id is omitted from the notes. A foreign key without an ON DELETE clause uses the SQLite default (NO ACTION): with foreign_keys = ON the parent row cannot be deleted while it is referenced.

3.1 biblio — bibliographic record

One row per title, aligned with the Koha fast cataloguing framework (PLAN.md §4.1, Q73).

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
titletextnoUNIMARC 200$a
subtitletextyes200$e
original_titletextyesthe title of the original work, only for a translation (Q123): UNIMARC 500$a when 101$c differs from 101$a, else typed by hand
responsibilitytextyes200$f (+$g), statement as printed
authortextnomain author "Surname, Name"; special values from catalog.specialAuthors (Anonimo, AA. VV.)
publishertextnocatalog.unknownPublisher (s.n.) when unknown
publication_placetextyescatalog.unknownPlace (s.l.) when unknown
publication_yearintegeryes
isbntextyesnormalised ISBN-13, unique when present
issntextyesperiodicals
eantextyesEAN-13 of non-book material when the code is not an ISBN
physical_descriptiontextyesthe whole 215$a string, e.g. 120 p. : ill. ; 24 cm
seriestextyesnarrative series (Harry Potter), never the UNIMARC 225/410 collana (Q84)
series_numbertextyesnumber as printed (3, 3.5, II)
deweytextyesnumeric part of 676$a, reference only
item_typetextno'libro'code from library.itemTypes (loan rules, not-for-loan), the same for every copy of the title (Q139); a new record takes the first configured type
kindtextyesCHECK fiction, nonfiction; NULL while the record waits to be classified (step 9.21): no genre, class or reading level then
genre_codetextyesfiction genre from classification.genres
reading_leveltextyesfiction reading level, a code of classification.readingLevels; NULL when classification.classPerItem is on (then on item)
class_codetextyesnon-fiction local class from classification.nonfiction
call_texttextyesthe title's written part of the segnatura, {{text}} of the scheme (Q143); NULL follows the proposal (the author's initials), an empty string is no text
subjectstext (JSON)no'[]'JSON array of free keywords
accessible_formatstext (JSON)no'[]'Accessible formats (9.66): names of catalog.accessibleFormats, in the list's order; the catalogue filter reads them with json_each
notestextyes
cover_pathtextyesrelative to DATA_DIR, e.g. uploads/covers/07/1207-main-<version>.webp
cover_sourcetextyesprovenance: photo, upload, googlebooks, amazon, openlibrary, mock (CoverSource in covers.ts)
sourcetextno'manual'provenance of the record: manual, import, or the metadata provider id (sbn, sbn-json, openlibrary, googlebooks, mock)
source_idtextyesprovider record id, e.g. SBN BID
created_byintegeryesFK staff_user.id
updated_byintegeryesFK staff_user.id
created_attextnonow
updated_attextnonow

3.2 item — physical copy

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
biblio_idintegernoFK biblio.id ON DELETE RESTRICT
barcodetextnothe configured copy format for new copies (default YYYY-CNNNN); older formats, legacy plain counters and YYYY-NNNN kept as-is (§5.4)
call_numbertextyesthe "segnatura", e.g. B 4 DEL: computed by the server from classification.callNumber, the record and the copy, never typed (Q143); on the copy, because the level per copy changes it (Q139); NULL while the record waits to be classified
call_headtextyesits shelf part (B 4): the spine band's large line, the inventory's shelf
call_texttextyesthe copy's written part, {{copytext}} of the scheme (the free scheme); NULL otherwise
reading_leveltextyesonly when classification.classPerItem is on
enumerationtextyesissue designation of periodicals, e.g. 2026, n. 3
statustextno'available'CHECK available, lost, damaged, in_repair, withdrawn; "on loan" is derived from open loans, never stored
not_for_loanintegerno0boolean, reference copies; set on every copy when the record takes a not-for-loan type
acquisition_datetextyesYYYY-MM-DD
notestextyes
inventory_numbertextyesthe school inventory (9.64, catalog.inventory.enabled): the copy's number in the register, unique when set; new copies get the next one after the highest made of digits only
origintextyeswhere the copy came from, a value of catalog.inventory.origins (or of an import)
value_centsintegeryesthe copy's value in euro cents
withdrawn_ontextyesYYYY-MM-DD in library.timezone, set when the copy becomes withdrawn (cleared when it comes back): the list of the copies withdrawn in a period does not depend on the audit log, which is purged
withdrawn_reasontextyesthe reason given with the withdrawal
barcode_label_printed_attextyesNULL = barcode label still to print
spine_label_printed_attextyesNULL = spine band still to print
created_attextnonow
updated_attextnonow

3.3 patron

Students and teachers only; staff are not patrons.

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
card_numbertextyesthe configured card format for new cards (default CNNNNNN); older formats and legacy counters kept (§5.4)
external_idtextyesstable id from the school register, matches imports
first_nametextno
last_nametextno
search_nametextno''normalizeSearchText("<last> <first>"): lower case, diacritics and punctuation dropped, for prefix search
patron_typetextnoCHECK student, teacher
class_group_idintegeryesFK class_group.id: the class of a student, the group of a teacher (Phase 12); NULL when the class upgrade left a student without one
level_codetextyesthe student's year, a code of school.levels hosted by the group; NULL for teachers
emailtextyesthe school's own address of the student or teacher (internal mail), shown on the patron's page; no phone (Q122)
photo_pathtextyesrelative to DATA_DIR, e.g. uploads/patrons/42/142-main-<version>.webp
card_printed_attextyeslast card print ("senza tessera stampata" filter)
statustextno'active'CHECK active, anonymized; no block state (Q79)
circulation_notetextyesalert shown at checkout
notestextyes
anonymized_attextyes
created_attextnonow
updated_attextnonow

3.3a class_group and class_group_level — the classes (Phase 12)

A class of the school is its own row, not two attributes of the patron (Q94). A students group hosts one level or several (a pluriclasse) and is persistent: at the class upgrade 3 B becomes 4 B by changing its level, not by moving its pupils (Q100). A teachers group hosts no level (Q102). Only admin creates, renames and archives groups (Amministrazione → Configurazione → Classi, Q99).

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
kindtextnoCHECK students, teachers
nametextnothe section or the name of the class (B, Coccinelle, Docenti)
stagetextyesteachers groups only: a stage of school.stages (filters of a comprensivo)
notestextyes
archived_attextyesset when an empty group is archived (by hand or by the class upgrade)
created_attextnonow
updated_attextnonow

class_group_level: group_id (FK class_group.id ON DELETE CASCADE) and level_code (a code of school.levels), primary key on both; index class_group_level_level (level_code).

3.3b school_level, school_setting and the view patron_class

school_level (code PK, position, label, name, stage, next_code, class_label) mirrors school.levels: next_code is the level of the next year (the first choice where the path splits, 9.41) and class_label the level's template (its stage's classLabel, else school.classLabel). school_setting (key PK, value) holds the settings SQL needs (class_label: the template of school.classLabel). Both are rewritten at startup and on every reload of the configuration (src/lib/server/db/config-mirror.ts), like reading_level; never write them by hand.

The view patron_class gives every patron's class as screens and reports need it: patron_id, group_id, group_kind, group_name, level_code, level_position, level_label, stage (the level's, or the teachers group's) and class_label (the level's template applied and trimmed: 3 B, Q1 for a template without {group}; a teachers group's name; NULL without a group). It is live data: a renamed group or level shows its new name. History uses the loan snapshots instead.

3.4 loan

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
item_idintegernoFK item.id
patron_idintegernoFK patron.id
issued_attextnonowtimestamp
due_datetextnoYYYY-MM-DD, computed in library.timezone
returned_attextyesNULL = open loan
renewalsintegerno0
last_renewed_attextyes
issued_byintegeryesFK staff_user.id
returned_byintegeryesFK staff_user.id
patron_type_snapshottextnopatron type at issue time (statistics)
level_snapshottextyesthe student's level code at issue time
level_position_snapshotintegeryesits position in the scale then (orders history reports)
stage_snapshottextyesits stage then
group_id_snapshotintegeryesthe class (group id) then; no foreign key, groups are never deleted
group_snapshottextyesthe group's name then
class_label_snapshottextyesthe class as printed then (3 B)
school_yeartextnolabel 2026/2027 (schoolYearFor, start library.schoolYearStart)
notestextyes
created_attextnonow
updated_attextnonow

3.5 loan_event — circulation history

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
loan_idintegernoFK loan.id ON DELETE CASCADE
typetextnoCHECK issue, renew, return, lost, found, override, notice_printed
attextnonow
by_user_idintegeryesFK staff_user.id
detailstext (JSON)yessee below

3.6 staff_user — library operators

Accounts are defined in config.json (security.users) and mirrored at startup and on every configuration reload by mirrorConfigUsers (src/lib/server/auth/config-users.ts).

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
usernametextno
password_hashtextnoargon2 hash (never the clear password)
display_nametextyes
rolestext (JSON)no'[]'JSON array of role names from the configuration
activeintegerno1boolean; accounts removed from the configuration are deactivated, never deleted
must_change_passwordintegerno0boolean
sourcetextno'config'CHECK config (mirrored from security.users), database (created from the admin UI)
last_login_attextyes
created_attextnonow
updated_attextnonow

3.7 session

ColumnTypeNullDefaultNotes
idtextnoPK; SHA-256 hex of the random cookie token (bv_session); the token itself is never stored
user_idintegernoFK staff_user.id ON DELETE CASCADE
created_attextnonow
expires_attextnosliding expiration
last_seen_attextnonow
iptextyes
user_agenttextyes

3.8 audit_log

Who (account) did what to which entity. Written by audit() (src/lib/server/services/audit.ts) inside the transaction of every mutating service.

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
attextnonow
user_idintegeryesFK staff_user.id; NULL for system actions (startup, reload, command line)
actiontextno<area>.<verb>, e.g. auth.login, patron.anonymize, item.status, loan.undo
entitytextnoa table name (patron, item, print_list, config, …)
entity_idtextyesthe id as text; NULL for actions on no single row
detailstext (JSON)yessanitised, see §5.7

3.9 sequence — atomic counters

ColumnTypeNullDefaultNotes
nametextnoPK
valueintegerno0last value handed out

Counters (src/lib/server/db/sequence.ts, named by src/lib/server/barcodes/counters.ts): one per format, card:<pattern> (e.g. card:CNNNNNN) and item:<pattern>:<year> (e.g. item:YYYY-CNNNN:2026, the counter of the copy barcodes of that year). nextValue is an INSERT … ON CONFLICT DO UPDATE SET value = value + 1 RETURNING value, always called inside the transaction that stores the number, so a rollback does not burn it. ensureAtLeast moves a counter past legacy or imported codes (MAX(value, minimum)). Counters never go back, so a retired card number is never reused.

3.9b reading_level — the reading levels of the configuration (step 12.6)

ColumnTypeNullDefaultNotes
codetextnoPK; the code stored in biblio.reading_level / item.reading_level
positionintegernoorder of the scale, from 0
labeltextnothe name shown

A mirror of classification.readingLevels, rewritten as a whole at startup and on every reload of the configuration (src/lib/server/db/config-mirror.ts), so that reports can order and label the levels in plain SQL. The configuration is the source of truth: never write the table by hand.

3.10 metadata_cache

ColumnTypeNullDefaultNotes
keytextnoPK, see below
providertextnoprovider id: sbn, sbn-json, openlibrary, googlebooks, mock
payloadtext (JSON)nothe provider's successful answer (ProviderAnswer); errors are never cached
fetched_attextnonow

3.11 import_batch

Traceability of committed imports (the import itself is one transaction, PLAN.md §3.22).

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
kindtextnoCHECK patrons, catalog, loans
filenametextnooriginal file name
stored_pathtextyescopy of the file, relative to DATA_DIR: imports/<id>/<filename>
modetextyespatron imports only: upsert or sync
started_attextnonow
finished_attextyes
summarytext (JSON)yescounts written at the end (e.g. created, updated, unchanged, removed)
by_user_idintegeryesFK staff_user.id

3.12 print_list and print_list_entry

Named, ordered baskets of patrons (kind = 'patrons') or copies (kind = 'items') that feed the print routes through list=<id>. Lists are shared by every account with the matching permission; there is no owner or visibility column.

print_list:

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
kindtextnoCHECK patrons, items
nametextno
created_byintegeryesFK staff_user.id (informative only)
last_printed_attextyes
created_attextnonow
updated_attextnonowtouched on every change; the most recent list of a kind is the "current" one

print_list_entry:

ColumnTypeNullDefaultNotes
list_idintegernoFK print_list.id ON DELETE CASCADE
entity_idintegernopatron.id or item.id depending on the list's kind; no foreign key
positionintegernoorder in the list, renumbered after removals
added_attextnonow

3.12a class_photo_sheet and class_photo_frame — class pictures of the photo census (Phase 13)

A picture with many photos (the photographer's sheet, the class's cards on a table) for one class or one patron print list. The browser finds the photo frames (#lib/vision/frames.ts) and sends their corners; the server straightens each frame into a photo of the configured size (#lib/server/images/warp.ts) and keeps it next to the picture (services/class-sheets.ts). The patron chosen for a frame is a draft until "Salva le foto", which stores every chosen photo as the patron's photo in one transaction.

class_photo_sheet:

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
class_group_idintegeryesFK class_group.id; exactly one of this and print_list_id
print_list_idintegeryesFK print_list.id ON DELETE CASCADE (a patrons list)
pathtextnothe picture under DATA_DIR (§5.5), JPEG, longest side ≤ photos.sheet.maxPixels
width, heightintegernoof the stored picture, the space of the frames' corners
kindtextno'photo'photo (taken at an angle: a frame's corners follow the perspective) or scan (lying flat: every frame is a turned rectangle, #lib/shared/sheet-frames.ts); turning a picture into a scan squares its open frames
detected_attextyeswhen the frames were first set (NULL: the browser has not looked yet)
uploaded_byintegeryesFK staff_user.id
created_at, updated_attextnonow

class_photo_frame:

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
sheet_idintegernoFK class_photo_sheet.id ON DELETE CASCADE
positionintegernonumber in reading order; a drawn frame comes after the others, a removed one leaves a gap
cornerstext (JSON)no[{x, y}] × 4: top-left, top-right, bottom-right, bottom-left, in the picture's pixels
crop_pathtextnothe straightened photo under DATA_DIR, at photos.width × photos.height; empty once erased
statetextnopendingCHECK pending, ignored, saved, erased (its patron was removed: painted over, Q106)
patron_idintegeryesFK patron.id; the patron chosen (a draft until saved)
assigned_at, saved_attextyes
painted_attextyeswhen an erased frame was painted over in the picture; NULL while still to do (right after the removal)
created_at, updated_attextnonow

3.12b wish — the wishlist (step 9.15)

The books the library would like to have, noted at the desk (loans) or by the cataloguers (catalog) on /wishlist. Part of the catalog data part in backups and restores.

patron_id (SET NULL)

created_by

patron

wish

integer

id

PK

text

title

text

author

text

series

text

series_number

text

note

integer

patron_id

FK

who asked, optional

integer

requests

>= 1

integer

created_by

FK

text

created_at

text

updated_at

staff_user

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
titletextyesat least one of title, author, series (CHECK wish_something_check)
authortextyes
seriestextyessuggestions come from biblio.series
series_numbertextyesonly with a series (service rule)
notetextyesfree text (≤ 500)
patron_idintegeryesFK patron.id ON DELETE SET NULL; cleared by the anonymization; shown only to loans/patrons accounts, never exported
requestsintegerno1CHECK ≥ 1; the same book (normalised title, author, series, number) asked again adds one instead of a second row
created_byintegeryesFK staff_user.id
created_attextnonow
updated_attextnonow

3.12c note — the staff notes, "Segnalazioni" (step 9.18)

Post-its any account leaves for the others and for the administrator on /notes (Q113, ADR-029). Written once and never edited; pinning to the dashboard, archiving, restoring and deleting are the administrator's (admin). A data part of its own (notes) in backups and restores.

created_by

archived_by

staff_user

note

integer

id

PK

text

body

1-500 characters, line breaks kept

text

color

yellow | pink | green | blue | orange

integer

pinned

0/1, top of the dashboard

integer

created_by

FK

text

created_at

text

archived_at

null while open

integer

archived_by

FK

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
bodytextnotrimmed, at most 500 characters (service rule), CHECK not blank (note_body_check); free text read by every account, never written to the audit or the logs
colortextnoyellowCHECK note_color_check (NOTE_COLORS of #lib/shared/notes.ts); decoration only
pinnedintegerno01: shown at the top of the dashboard and of the desk of loans-only accounts; CHECK note_pinned_check: an archived note is never pinned
created_byintegeryesFK staff_user.id: the account that wrote it
created_attextnonow
archived_attextyesnull while the note is open
archived_byintegeryesFK staff_user.id

3.12d calendar_event — the notable dates of the calendar (step 9.25)

What the staff mark for everyone on /calendar (Q125): a reading in a class, the deadlines of the bookmark contest, a meeting with an author. Free text with a title, one day or a period; no types and no link to classes. Every account reads them, the accounts with loans write them; they never change a due date (the closures are configuration, §3.8 of PLAN.md). A data part of its own (dates) in backups and restores.

created_by

updated_by

staff_user

calendar_event

integer

id

PK

text

starts_on

YYYY-MM-DD

text

ends_on

null for one day

text

title

1-120 characters

text

notes

at most 500, line breaks kept

integer

created_by

FK

integer

updated_by

FK

text

created_at

text

updated_at

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
starts_ontextnothe day, YYYY-MM-DD in the library's time zone
ends_ontextyesthe last day of a period; null for one day (a period ending on its first day is stored as one day); CHECK calendar_event_period_check: after starts_on
titletextnotrimmed, at most 120 characters (service rule), CHECK not blank (calendar_event_title_check); read by every account, never written to the audit or the logs
notestextyesat most 500 characters
created_by, updated_byintegeryesFK staff_user.id
created_at, updated_attextnonow

3.13 inventory and inventory_scan — stocktaking

inventory is a stocktaking campaign: open while the shelves are scanned, closed with a summary. One campaign open at a time is a service rule (services/inventory.ts), not a database constraint.

ColumnTypeNullDefaultNotes
idintegernoautoincrementPK
nametextno
statustextno'open'CHECK open, closed
started_attextnonow
started_byintegeryesFK staff_user.id
closed_attextyes
closed_byintegeryesFK staff_user.id
notestextyes
summarytext (JSON)yeswritten at close (InventorySummary: expected, seen, lost, found, onLoan, misplaced, lostItems[{ id, from }]); cleared on reopen

inventory_scan holds one row per copy seen in a campaign:

ColumnTypeNullDefaultNotes
inventory_idintegernoFK inventory.id ON DELETE CASCADE
item_idintegernoFK item.id ON DELETE CASCADE
scanned_attextnonow
scanned_byintegeryesFK staff_user.id
shelftextyesshelf context of the run (e.g. B 4, 590)
misplacedintegerno0boolean: the copy does not belong to that shelf context
foundintegerno0boolean: the copy was marked lost and turned up with this scan

4. Full-text search: biblio_fts

Drizzle cannot model virtual tables, so src/lib/server/db/fts.ts creates the index with idempotent statements (CREATE … IF NOT EXISTS) after the Drizzle migrations. It is not exported from schema.ts, so drizzle-kit never generates SQL for it; a read-only biblioFts table object exists for queries.

CREATE VIRTUAL TABLE IF NOT EXISTS biblio_fts USING fts5(
  title, original_title, subtitle, author, responsibility, publisher, subjects, isbn, series,
  content='biblio', content_rowid='id',
  tokenize='unicode61 remove_diacritics 2'
);

5. Conventions

5.1 Timestamps and dates

5.2 Booleans and enumerations

5.3 JSON columns

Declared with text(…, { mode: "json" }), stored as JSON text, parsed by Drizzle: biblio.subjects and biblio.accessible_formats (string arrays, default '[]'), staff_user.roles (string array, default '[]'), loan_event.details, audit_log.details, import_batch.summary, inventory.summary (objects, nullable), metadata_cache.payload (provider answer), class_photo_frame.corners (four points).

5.4 Barcodes and card numbers

5.5 Image paths

Stored paths are relative to DATA_DIR, bucketed by id % 100 (two digits) and versioned by a timestamp: uploads/patrons/<bucket>/<id>-main-<version>.<ext> and uploads/covers/<bucket>/<id>-main-<version>.<ext> (ext from the configured format, WebP by default). Only the main path is stored; the thumbnail is the same path with -thumb-. Replacing or deleting an image removes the old files. A class picture of the census (Phase 13) and the photos of its frames live in one folder per picture: uploads/class-sheets/<sheet>/picture-<version>.jpg and uploads/class-sheets/<sheet>/frame-<frame>-<version>.<ext>; deleting the picture removes the folder.

5.6 Anonymization

Removing a patron (anonymizePatron, src/lib/server/services/anonymize.ts) is immediate and irreversible, and is refused while the patron has open loans. It sets:

Kept: id, patron_type, class_group_id, level_code, all loans with their snapshots and events. The patron's wishes stay on the wishlist with wish.patron_id set to NULL. Every frame of a class picture where the patron was chosen becomes erased (no patron, its photo file deleted) and is painted over in its picture right after the transaction (paintErasedFrames; Phase 13, Q106: the face must not survive anywhere, the picture stays). The audit row (patron.anonymize) carries only { patronType }, plus classSheetFrames when frames were erased.

5.7 Audit log contents

audit_log never holds personal data. sanitizeDetails drops these keys at any depth before writing: firstName, lastName, first_name, last_name, name, fullName, displayName, photo, photoPath, photo_path, cardNumber, card_number, email, phone, birthDate, birth_date, note, notes, circulationNote, circulation_note, password, passwordHash, token; strings longer than 200 characters are truncated. Rows identify patrons only by entity_id.

5.8 Deletion policy

No soft deletes, except patron anonymization. Hard deletes are limited to: a biblio with no copies, an item never lent, a same-day loan undo, print lists and their entries, wishes (deleted or catalogued), archived staff notes (by the administrator), class pictures of the census and their frames (§3.12a), expired sessions, old cache rows and audit rows past retention (the last three by the daily housekeeping job, services/housekeeping.ts). Staff accounts are deactivated, never deleted.

Indice A–Z