05. Database

Data Schema & Entities

Although IndexedDB is a NoSQL database, DIRD enforces a strict, explicit schema through TypeScript interfaces and Dexie table definitions. This acts as a contract for the frontend and ensures predictable data structures, migrations, and query performance.

Below are the core entities of the system, including their purpose and all defined indexes.

---

1. Patients

Represents a clinical patient. This is the root entity for most of the system’s data and is required for traceability, reporting, and longitudinal analysis.

Field	Type	Description	Indexes
id	number	Internal auto-increment ID	Primary Key
patientId	string	External or clinical identifier	Indexed
name	string	Full patient name	Indexed
status	'active' | 'archived'	Logical state of the patient	Indexed
dateOfBirth	Date	Date of birth	—
diabetes	boolean	Diabetes diagnosis flag	—
diabetesType	enum?	Type of diabetes	—
diabetesDuration	number?	Years since diagnosis	—
hta	boolean	Hypertension flag	—
dlp	boolean	Dyslipidemia flag	—
medications	string[]	Current medications	—
otherConditions	string?	Additional relevant conditions	—
metadata	Record<string, any>?	Free-form metadata	—
createdAt	Date	Creation timestamp	Indexed
updatedAt	Date	Last update timestamp	—

Indexes:
id, patientId, name, status, createdAt

---

2. Sessions

A session represents a clinical encounter or evaluation instance for a patient. It groups images, reports, and derived data.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
patientId	number	Reference to patients.id	Indexed
name	string?	Optional session label	Indexed
sessionNumber	number	Sequential session number	Indexed
date	Date	Session date	Indexed
locked	boolean	Prevents further edits	Indexed
lockedAt	Date?	Lock timestamp	—
type	'normal' | 'combined'	Session type	Indexed
combinedSessionIds	number[]?	Source sessions if combined	—
createdAt	Date	Creation timestamp	—
updatedAt	Date	Last update timestamp	—

Indexes:
id, patientId, name, sessionNumber, date, locked, type

---

3. Images

Stores original and processed retinal images linked to a session.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
sessionId	number	Reference to session	Indexed
filename	string	Original filename	—
eyeType	'OI' | 'OD'	Eye laterality	Indexed
originalBlob	Blob	Raw uploaded image	—
processedBlob	Blob?	Processed image	—
order	number?	Image order in gallery	—
width	number	Image width	—
height	number	Image height	—
uploadedAt	Date	Upload timestamp	Indexed

Indexes:
id, sessionId, eyeType, uploadedAt

---

4. Detections

Bounding-box level findings, either AI-generated or manually created.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
imageId	number	Reference to image	Indexed
type	'ai' | 'manual'	Source of detection	Indexed
modelVersion	string?	AI model version	—
bbox	object	Bounding box coordinates	—
class	string	Detected class	Indexed
confidence	number?	Model confidence	—
customLabel	string?	User-defined label	—
visible	boolean	UI visibility flag	Indexed
createdAt	Date	Creation timestamp	—

Indexes:
id, imageId, type, class, visible

---

5. Segmentations

Pixel-level annotations associated with an image.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
imageId	number	Reference to image	Indexed
type	'ai' | 'manual'	Source of segmentation	Indexed
modelVersion	string?	AI model version	—
maskData	string	Serialized mask data	—
class	string	Segmentation class	Indexed
confidence	number?	Model confidence	—
customLabel	string?	User-defined label	—
opacity	number	Rendering opacity	—
visible	boolean	UI visibility	Indexed
createdAt	Date	Creation timestamp	—

Indexes:
id, imageId, type, class, visible

---

6. Reports

Generated PDF reports linked to a session.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
sessionId	number	Reference to session	Indexed
type	'preview' | 'final'	Report lifecycle state	Indexed
reportCategory	'single' | 'combined'	Report scope	Indexed
pdfBlob	Blob	PDF file	—
evaluatorNotes	string	Final evaluator notes	—
originalNotes	string?	Pre-AI notes	—
areasOfInterest	Array	Image-linked comments	—
generatedAt	Date	Generation timestamp	Indexed

Indexes:
id, [sessionId+type], sessionId, type, reportCategory, generatedAt

---

7. Measurements

Distance or size measurements taken over images.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
imageId	number	Reference to image	Indexed
originX	number	Start X	—
originY	number	Start Y	—
destinationX	number	End X	—
destinationY	number	End Y	—
distancePixels	number	Pixel distance	—
distanceDD	number?	Real-world distance	—
visible	boolean	UI visibility	Indexed
createdAt	Date	Creation timestamp	Indexed

Indexes:
id, imageId, visible, createdAt

---

8. Image Classifications

High-level clinical classification per image, integrating AI output and clinical guidelines.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
imageId	number	Reference to image	Indexed
eyeType	'OD' | 'OI' | 'unknown'	Eye laterality	Indexed
severity	string	Clinical severity	Indexed
guideline	string?	Guideline ID	Indexed
urgency	'routine' | 'accelerated' | 'urgent'?	Clinical urgency	Indexed
manuallyModified	boolean?	Manual override flag	Indexed
createdAt	Date	Creation timestamp	Indexed
updatedAt	Date	Last update	Indexed

Indexes:
id, imageId, eyeType, severity, guideline, urgency, manuallyModified, createdAt, updatedAt

---

9. Pending Contributions

Tracks data pending external submission or review.

Field	Type	Description	Indexes
id	number	Internal ID	Primary Key
type	'image' | 'guideline' | 'conclusion'	Contribution type	Indexed
referenceId	number	Linked entity ID	Indexed
status	'pending' | 'submitted'	Submission state	Indexed
metadata	Record<string, any>?	Attached payload	—
createdAt	Date	Creation timestamp	Indexed

Indexes:
id, [type+referenceId], type, referenceId, status, createdAt

---

Logical Relationships

Patients own Sessions.
Sessions group Images and Reports.
Images are the anchor for Detections, Segmentations, Measurements, and Classifications.
PendingContributions reference entities indirectly for asynchronous workflows.

This structure favors local-first operation, explicit migrations, and predictable query paths.

Migrations & Local Persistence

DIRD uses Dexie as a thin, explicit layer over IndexedDB. While IndexedDB itself is schemaless, Dexie enforces a versioned schema via declared stores and indexes. Each version(n) defines a concrete database shape. Any change to tables, indexes, or data expectations increments the version and specifies how existing data is upgraded.

Migrations run automatically and locally in the browser the next time the app loads. The database is per-origin, requires no server, and persists across reloads and restarts. If the stored version matches the current one, nothing happens. If it is lower, Dexie applies each intermediate migration in order.

Structural changes are declared in .stores(). Data fixes or backfills are handled in .upgrade() callbacks, which run inside a transaction. These upgrades must be deterministic and idempotent, since they may be retried if interrupted.

The database is instantiated only at startup. The schema definition lives in src/lib/db/schema.ts. Dexie does not handle sync or replication; all reads and writes are local, using async IndexedDB primitives. Query performance is predictable because all access paths rely on explicitly defined indexes.

This keeps the system strictly local-first, offline-capable, and able to evolve safely without data loss.

(agregar uso de query)