Database Schema Diagram

This diagram is auto-generated from the SQLModel metadata. Field descriptions are extracted from model docstrings.

        erDiagram
  external_user_identifiers {
    INTEGER id PK
    VARCHAR nexuslims_username
    VARCHAR external_system
    VARCHAR external_id
    VARCHAR email
    VARCHAR created_at
    VARCHAR last_verified_at
    VARCHAR notes
  }

  instruments {
    VARCHAR_100 instrument_pid PK
    VARCHAR api_url
    VARCHAR calendar_url
    VARCHAR_100 location
    VARCHAR display_name
    VARCHAR_20 property_tag
    VARCHAR filestore_path
    VARCHAR harvester
    VARCHAR timezone
  }

  session_log {
    INTEGER id_session_log PK
    VARCHAR_36 session_identifier
    VARCHAR_100 instrument FK
    VARCHAR timestamp
    VARCHAR_17 event_type
    VARCHAR_18 record_status
    VARCHAR_50 user
  }

  upload_log {
    INTEGER id PK
    VARCHAR_36 session_identifier
    VARCHAR_100 destination_name
    BOOLEAN success
    VARCHAR timestamp
    VARCHAR_255 record_id
    VARCHAR_500 record_url
    VARCHAR error_message
    VARCHAR metadata_json
  }


  instruments ||--o{ session_log : "references via instrument"
    

Field Descriptions

Detailed descriptions for each table’s fields (extracted from model docstrings):

external_user_identifiers - Maps NexusLIMS usernames to external system user IDs.

Field	Description
created_at	When this mapping was created
email	User’s email for verification/matching (optional)
external_id	User ID/username in the external system
external_system	External system identifier (nemo, labarchives_eln, etc.)
id	Auto-incrementing primary key
last_verified_at	Last time this mapping was verified (optional)
nexuslims_username	Canonical username in NexusLIMS (from session_log.user)
notes	Additional notes about this mapping (optional)

instruments - Instrument configuration from the NexusLIMS database.

Field	Description
api_url	Calendar API endpoint URL for this instrument’s scheduler (e.g., https://<nemo_address>/api/tools/?id=<tool_id>)
calendar_url	URL to the instrument’s web-accessible calendar
display_name	Human-readable instrument name displayed in NexusLIMS records
filestore_path	Relative path under NX_INSTRUMENT_DATA_PATH where data is stored
harvester	Harvester module to use (“nemo” or “sharepoint”)
instrument_pid	Unique identifier for the instrument (e.g., “FEI-Titan-TEM-012345”)
location	Physical location (building and room number)
property_tag	Unique numeric identifier (for reference)
timezone_str	IANA timezone database string (e.g., “America/New_York”)

session_log - Individual session log entry (START, END, or RECORD_GENERATION event).

Field	Description
event_type	The type of log (START, END, or RECORD_GENERATION)
instrument	The instrument associated with this session (foreign key reference to instruments table)
record_status	The status for this record (defaults to WAITING_FOR_END)
session_identifier	A unique string consistent among a single record’s START, END, and RECORD_GENERATION events (often a UUID)
timestamp	The datetime representing when the event occurred
user	The username associated with this session (if known)

upload_log - Log of export attempts to destination repositories.

Field	Description
destination_name	Name of the export destination (e.g., “cdcs”, “labarchives”)
error_message	Error message if export failed
id	Auto-incrementing primary key
metadata_json	JSON-serialized metadata dict with destination-specific details
record_id	Destination-specific record identifier (if successful)
record_url	Direct URL to view the exported record (if successful)
session_identifier	Foreign key reference to session_log.session_identifier
success	Whether the export succeeded
timestamp	When the export attempt occurred

Key Relationships

1. instruments → session_log (One-to-Many)

   • Each instrument can have many session log entries

   • session_log.instrument references instruments.instrument_pid

2. session_log → upload_log (One-to-Many)

   • Each session can be exported to multiple destinations

   • upload_log.session_identifier references session_log.session_identifier

   • Multiple upload attempts per session per destination are tracked

3. external_user_identifiers (Independent - Star Topology)

   • Not directly related to other tables via foreign keys

   • Links session_log.user to external system identifiers

   • Supports bidirectional lookup between NexusLIMS and external systems

Unique Constraints

• session_log: session_identifier uniquely identifies a complete experimental session

• upload_log: No unique constraint (allows multiple export attempts)

• external_user_identifiers:

  • (nexuslims_username, external_system) - one external ID per user per system

  • (external_system, external_id) - one NexusLIMS user per external ID per system