Data Model

SeqDesk uses Prisma ORM with PostgreSQL. This page describes the core entities and their relationships.

Entity Relationship Overview


User ──< Order ──< Sample >── Study
  │                  │           │
  │                  ├── Read    ├── PipelineRun ──< PipelineRunStep
  │                  │                │
  │                  ├── Assembly     ├── PipelineRunEvent
  │                  │                │
  │                  └── Bin          └── PipelineArtifact
  │
  ├── Department
  └── AdminInvite

Core Models

User

Field	Type	Description
id	String	Primary key (CUID)
email	String	Unique login identifier
password	String	bcrypt hash
firstName, lastName	String	Display name
role	String	`RESEARCHER` or `FACILITY_ADMIN`
researcherRole	String?	PI, POSTDOC, PHD_STUDENT, MASTER_STUDENT, TECHNICIAN, OTHER
institution	String?	Research institution
departmentId	String?	Foreign key to Department

Order

Field	Type	Description
id	String	Primary key (CUID)
orderNumber	String	Unique, format: `ORD-YYYYMMDD-XXXX`
name	String?	Descriptive name
status	String	`DRAFT`, `SUBMITTED`, or `COMPLETED`
platform	String?	ILLUMINA, OXFORD_NANOPORE, PACBIO, etc.
libraryStrategy	String?	WGS, RNA-Seq, AMPLICON, etc.
librarySource	String?	GENOMIC, METAGENOMIC, etc.
customFields	String?	JSON for form builder fields
userId	String	Foreign key to User

Sample

Field	Type	Description
id	String	Primary key (CUID)
sampleId	String	Internal ID (format: `S-{timestamp}-{random}`)
sampleAlias	String?	User-friendly alias
scientificName	String?	Taxonomic name
taxId	String?	NCBI taxonomy ID
checklistData	String?	JSON — MIxS metadata
checklistUnits	String?	JSON — units for metadata
customFields	String?	JSON — custom form fields
orderId	String	Foreign key to Order
studyId	String?	Foreign key to Study (optional)

Study

Field	Type	Description
id	String	Primary key (CUID)
title	String	Study title
alias	String?	Unique alias for ENA
checklistType	String?	MIxS checklist type
studyAccessionId	String?	ENA accession (PRJEB…)
submitted	Boolean	Whether submitted to ENA
readyForSubmission	Boolean	Marked ready by user
userId	String	Foreign key to User (owner)

Read

Field	Type	Description
id	String	Primary key (CUID)
file1	String?	Forward reads path (R1)
file2	String?	Reverse reads path (R2)
checksum1, checksum2	String?	MD5 checksums
experimentAccessionNumber	String?	ENA experiment (ERX…)
runAccessionNumber	String?	ENA run (ERR…)
sampleId	String	Foreign key to Sample
sequencingRunId	String?	Foreign key to SequencingRun

PipelineRun

Field	Type	Description
id	String	Primary key (CUID)
runNumber	String	Unique, format: `{PIPELINE}-{DATE}-{NNN}`
pipelineId	String	Pipeline identifier (e.g., `mag`)
status	String	pending, queued, running, completed, failed, cancelled
progress	Int	0–100
studyId	String	Foreign key to Study
userId	String	Foreign key to User (who started it)
runFolder	String	Path to run directory

Assembly

Field	Type	Description
id	String	Primary key (CUID)
assemblyName	String	Assembly identifier
assemblyFile	String	Path to FASTA file
assemblyAccession	String?	ENA accession
sampleId	String	Foreign key to Sample
createdByPipelineRunId	String	Foreign key to PipelineRun

Bin

Field	Type	Description
id	String	Primary key (CUID)
binName	String	Bin identifier
binFile	String	Path to bin FASTA
completeness	Float	CheckM completeness (0–100)
contamination	Float	CheckM contamination (0–100)
sampleId	String	Foreign key to Sample
createdByPipelineRunId	String	Foreign key to PipelineRun

Supporting Models

Model	Purpose
Department	User grouping with name, description, isActive
AdminInvite	Invite codes for admin registration
StatusNote	Audit trail on orders (STATUS_CHANGE, SAMPLES_SENT, INTERNAL)
SequencingRun	Run-level metadata and QC metrics
PipelineConfig	Per-pipeline enabled flag and settings
PipelineRunStep	Individual process status within a run

The remaining supporting models are documented in detail below — each is either touched by automation, surfaced in a dashboard, or has structure that the table-summary doesn’t capture.

Sampleset

Per-order metadata form configuration. One Sampleset per Order (orderId is unique).

Field	Type	Description
`id`	String	Primary key (CUID)
`checklists`	JSON string	Array of enabled MIxS checklist accessions, e.g. `["ERC000022"]`
`selectedFields`	JSON string?	Subset of checklist fields the order surfaces, when narrower than the full checklist
`fieldOverrides`	JSON string?	Per-field overrides keyed by field id — typically `{ label, required, helpText }` to localize defaults
`sampleType`	Int	Discriminator (default `1` for standard biological samples)
`orderId`	String, unique	Foreign key to Order

The combination of checklists, selectedFields, and fieldOverrides is how a facility tailors the MIxS metadata form per order without forking the underlying checklist definition.

Submission

ENA submission tracking per entity. Drives the Submissions Dashboard.

Field	Type	Description
`id`	String	Primary key (CUID)
`submissionType`	String	`STUDY`, `SAMPLE`, `READ`, `ASSEMBLY`, `BIN`
`status`	String	One of `PENDING`, `SUBMITTED`, `PARTIAL`, `ACCEPTED`, `REJECTED`, `ERROR`, `CANCELLED`
`xmlContent`	String?	Generated XML SeqDesk sent to ENA (kept for debugging)
`response`	JSON string?	ENA response plus a `steps[]` timeline rendered by the dashboard
`accessionNumbers`	JSON string?	Map of returned accessions, e.g. `{ "studyAccession": "PRJEB12345" }`
`entityType`	String	`study` or `sample`
`entityId`	String	FK into Study or Sample
`createdAt`, `updatedAt`	DateTime	Standard timestamps

SequencingUpload

Tracks an in-progress chunked upload session for a sequencing artifact (reads, checksums, etc.). Created by POST /api/orders/[orderId]/sequencing/uploads and finalized by the matching complete endpoint.

Field	Type	Description
`id`	String	Primary key (CUID)
`orderId`	String	Foreign key to Order
`sampleId`	String?	Optional; when the upload is a per-sample artifact
`targetKind`	String	`READS`, `ASSEMBLY`, `CHECKSUMS`, …
`targetRole`	String	Role within targetKind, e.g. `SAMPLE_READ_FORWARD`
`originalName`	String	Filename as submitted
`tempPath`	String	Server path receiving chunks
`finalPath`	String?	Promoted path once `complete` succeeds
`expectedSize`	BigInt	Bytes expected by initiate
`receivedSize`	BigInt	Bytes received so far
`status`	String	`PENDING`, `COMPLETED`, `ABORTED`
`checksumProvided`, `checksumComputed`	String?	Optional MD5 verification
`mimeType`	String?	MIME type if known
`metadata`	JSON string?	Caller-supplied metadata
`createdById`	String	User who initiated the upload
`createdAt`, `updatedAt`	DateTime	Standard timestamps

Indexes on (orderId, status) and (sampleId, status) keep the upload list queries fast.

PipelineRunEvent

Event-feed entry from a pipeline run, populated by the weblog endpoint and internal step transitions.

Field	Type	Description
`id`	String	Primary key (CUID)
`pipelineRunId`	String	Foreign key to PipelineRun
`eventType`	String	`run_started`, `process_completed`, `process_failed`, etc. — values are open, not gate-kept
`processName`	String?	Nextflow process or SLURM step identifier
`stepId`	String?	SeqDesk-side step id
`status`	String?	`RUNNING`, `COMPLETED`, `FAILED`, …
`message`	String?	Human-readable detail
`payload`	JSON string?	Full event body, trimmed to a reasonable size
`source`	String?	`weblog`, `trace`, `queue`, `process`
`occurredAt`	DateTime	Indexed alongside `pipelineRunId` for timeline queries

PipelineArtifact

Files produced by a pipeline run. Used by the assembly browser, bin viewer, and QC report previewer.

Field	Type	Description
`id`	String	Primary key (CUID)
`type`	String	`reads`, `assembly`, `bins`, `qc_report`, `alignment`
`name`	String?	Display name
`path`	String	Path relative to the configured data base path
`checksum`	String?	MD5 if computed
`size`	BigInt?	Bytes if known
`studyId`, `sampleId`	String?	Optional lineage links
`pipelineRunId`	String?	Foreign key to PipelineRun
`producedByStepId`	String?	Step that wrote the artifact
`metadata`	JSON string?	Tool-specific info (e.g., MultiQC summary stats)
`createdAt`	DateTime	Standard timestamp

DemoWorkspace

Disposable demo session record. See Demo mode for the full lifecycle.

Field	Type	Description
`id`	String	Primary key (CUID)
`tokenHash`	String, unique	SHA-256 of the bootstrap token; raw token only lives in the browser cookie
`userId`	String, unique	Foreign key to User (the demo researcher)
`adminUserId`	String?, unique	Foreign key to User (demo facility admin)
`seedVersion`	Int	Bumped when the seed schema changes
`lastSeenAt`	DateTime	Updated on each request
`expiresAt`	DateTime, indexed	Cleanup query target
`createdAt`, `updatedAt`	DateTime	Standard timestamps

SiteSettings

Singleton configuration row (id is always "singleton"). Stores the bulk of facility configuration; large or nested config is held in two JSON columns:

Column	Shape
`modulesConfig`	`{ "modules": { "sequencing-tech": true, … } }` — module enable map
`extraSettings`	Free-form JSON for everything else: `studyFormFields`, `studyFormGroups`, `pipelineExecution`, `telemetry`, `installProfile`, `ena`, `sequencingTechConfig`, etc.

The installProfile sub-object is written by scripts/apply-install-profile.mjs and exposes { id, version, name, appliedAt } — the source of the install profile metadata shown in admin and reported in telemetry heartbeats.

OrderFormConfig

Single-row table holding the order form schema as configured in the Order Form Builder.

Column	Shape
`schema`	JSON `{ fields: [...], groups: [...], version: "..." }` matching the response from `GET /api/form-schema`

The schema is merge-applied during install-profile application, so a profile can ship a default form without overwriting facility-local additions.

TicketMessage

Per-message row inside a Ticket thread. Tickets back the in-app messaging UI between researchers and facility admins.

Field	Type	Description
`id`	String	Primary key (CUID)
`ticketId`	String	Foreign key to Ticket
`senderId`	String	Foreign key to User
`body`	String	Message body
`readBy`	JSON string?	Array of user ids who have read this message
`createdAt`	DateTime	Standard timestamp