:orphan:

# Database Schema Diagram

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

```{mermaid}
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