Data Model

SeqDesk stores everything in one PostgreSQL database, accessed through Prisma. The single source of truth is prisma/schema.prisma in the application repository; this page explains what each model is for, which fields carry meaning, and which invariants the application relies on.

How to read this page

Five conventions run through the whole schema. Knowing them saves you from a lot of surprises:

• Identifiers are CUIDs. Every id is String @default(cuid()). Human-facing identifiers are separate columns: Order.orderNumber, PipelineRun.runNumber, Sample.sampleId.
• There are no database enums. Status and type columns are plain String columns with a default. The allowed values live in TypeScript constants (src/lib/sequencing/constants.ts and friends) and are enforced in application code, not by PostgreSQL. Values listed on this page are what the application writes and accepts — the column will physically hold anything.
• JSON is stored as String. Columns such as checklistData, customFields, config, results, metadata, payload, and barcodeMap hold serialized JSON text. They are parsed defensively: malformed JSON degrades to an empty object rather than throwing.
• Unique constraints are the concurrency contract. Several composite unique keys exist purely so that two code paths racing to materialize the same result cannot double-create it. They are called out where they matter.
• Deletes cascade down the ownership tree. Deleting an Order removes its samples, and deleting a Sample removes its reads, assemblies, and bins. Provenance links (pipelineRunId, createdById, sequencingRunId) are SetNull instead, so deleting a pipeline run never destroys the data it produced.

Entity map

A ─< B reads “one A has many B”. A ── B is one-to-one or many-to-one.

User ─┬─< Order ─┬─< Sample ─┬─< Read ──> Read      (supersede chain)
      │          │           ├─< Assembly
      │          │           └─< Bin
      │          ├─── Sampleset            (1:1, metadata form config)
      │          ├─< SequencingRun ─< SequencingRunSample >── Sample
      │          ├─< SequencingArtifact
      │          ├─< SequencingUpload
      │          ├─< StreamRun ─┬─< StreamIngestedFile
      │          │              └─< StreamRunEvent
      │          └─< StatusNote
      │
      ├─< Study ─┬─< Sample                (Sample.studyId, optional)
      │          └─── StudyFormConfig      (1:1, dynamic-studies module)
      │
      ├─< PipelineRun ─┬─< PipelineRunStep
      │   (targets an  ├─< PipelineRunEvent
      │    Order or a  ├─< PipelineArtifact
      │    Study)      └─── PipelineResultSelection  (one winner per pipeline+target)
      │
      ├─< Ticket ─< TicketMessage
      ├─< InAppNotification
      ├─── Department
      └─── AdminInvite

Singletons: SiteSettings, OrderFormConfig
Standalone: Submission, PipelineConfig, BackgroundWorkerProcess, DemoWorkspace
Workbench:  WorkbenchWorkspace ─┬─< WorkbenchAnalysis
                                ├─< WorkbenchImportJob
                                └─< WorkbenchWorkspaceDataset >── WorkbenchDataset

A Sample is the pivot: it belongs to exactly one Order and to at most one Study, which is how the ordering side and the analysis side meet.

Identity and access

User

The account record. One table holds both roles; there is no separate admin table.

role is the only authorization primitive in the app. Nearly every mutating API route checks session.user.role !== "FACILITY_ADMIN" directly; there is no permission table and no intermediate role.

Department

A flat grouping of users (name is unique, isActive soft-disables it without deleting history). Departments drive the optional department-sharing rule that lets colleagues see each other’s sequencing orders.

AdminInvite

Single-use invite codes for creating additional facility admins. code is unique, expiresAt is required, and usedById is unique — an invite can be consumed by exactly one user, and consuming it stamps usedAt.

The sequencing order side

Order — “Sequencing Order”

The customer’s request: who ordered, what they want sequenced, the samples, the billing details, and the sequencing data the facility later attaches. This is the busiest model in the schema.

Three status values, and the transitions matter:

Sequencing data can only be managed on SUBMITTED or COMPLETED orders (FILES_ASSIGNABLE_STATUSES); attempting it on a DRAFT fails with “Sequencing data can only be managed on submitted or completed sequencing orders”. The SUBMITTED → COMPLETED transition is automatic: whenever a read assignment changes, checkAndCompleteOrder() completes the order if every sample has at least one read with file1 set, and records a STATUS_CHANGE status note.

Sampleset

Per-order configuration of the sample metadata form. Exactly one per order (orderId is unique), deleted with the order.

This trio is how a facility tailors the MIxS form per order without forking the underlying checklist definition.

Sample

One biological sample. Belongs to exactly one order and optionally to one study; this is the join point between the ordering world and the analysis world.

facilityStatus takes one of six values (FACILITY_SAMPLE_STATUSES):

A sample carries two independent metadata bags. checklistData is the standards-compliant MIxS payload that ends up in an ENA submission; customFields is whatever the facility added in the form builder and never leaves SeqDesk. Do not merge them.

Read

A pair of FASTQ files (or a single file) belonging to one sample. This is the model that most pipeline behaviour hangs off, and it is more subtle than it looks because SeqDesk must be able to protect raw inputs while letting cleaning pipelines publish derived replacements.

Data class and the supersede chain

raw and unknown are protected classes (isProtectedReadDataClass). A pipeline may never stage a candidate that claims one of them — the promotion code downgrades such a candidate to cleaned — so a cleaning run can never overwrite an order’s canonical source reads. Promotion supersedes the previous active read rather than deleting it: the old row keeps its files, gets isActive = false, and gains a supersededByReadId pointer.

Indexes on (sampleId, isActive), dataClass, and supersededByReadId back the “active read for this sample” lookup that runs on nearly every sequencing screen.

SequencingRun — the physical run

The instrument run: which flow cell, when, with what QC. One order can have many.

Unique on (orderId, runId), so re-importing the same run plan updates the existing run instead of creating a duplicate.

SequencingRunSample

The barcode-to-sample assignment inside a run. Written by the barcode assignment UI and by the run-plan import.

Unique on (sequencingRunId, sampleId) and (sequencingRunId, barcode). The second constraint is what makes a barcode collision impossible within one run; the run-plan importer also pre-checks it so the operator sees a readable error instead of a constraint violation.

SequencingArtifact

Any facility-managed file that is not a read: QC reports, demultiplexing stats, sample sheets, delivery bundles, attachments. Distinct from PipelineArtifact, which is produced by a pipeline run.

SequencingUpload

One chunked upload session. Created when an admin starts uploading a read or an artifact and retired when the upload completes or is cancelled.

Status progresses PENDING → UPLOADING → READY → COMPLETED; cancelling sets CANCELLED. FAILED is also a declared value (SEQUENCING_UPLOAD_STATUSES). The transition to READY happens automatically once receivedSize >= expectedSize, and complete refuses to finalize unless the two match exactly. Indexes on (orderId, status) and (sampleId, status) keep the “uploads in flight” queries cheap.

StatusNote

The audit trail on a sequencing order. noteType is INTERNAL (default), STATUS_CHANGE, or SAMPLES_SENT. userId is optional because SeqDesk writes system notes itself — the automatic completion note has no author.

Studies and submission

Study

The analysis and publication grouping. Samples from several orders can belong to one study; the study owns the research context (checklist, metadata) and the ENA accession.

submitted and testRegisteredAt are deliberately separate. A study submitted to the ENA test server never sets submitted; only a production submission that returned both a study accession and accessions for every sample does.

StudyFormConfig

A per-study questionnaire schema, used only when the dynamic-studies module is enabled. 1:1 with Study (studyId is unique), the same way Sampleset is 1:1 with Order.

When no row exists for a study, the loaders fall back to the global study form stored in SiteSettings.extraSettings.studyFormFields / studyFormGroups.

Submission

One ENA submission attempt, and the record you consult when a submission goes wrong. Backs the Submissions Dashboard.

The submit flow computes the status itself: ACCEPTED when a study accession came back and every sample got an accession, PARTIAL when the study registered but samples did not, ERROR otherwise. A facility admin can override the status through the API, which accepts PENDING, SUBMITTED, ACCEPTED, REJECTED, ERROR, and CANCELLED — note that PARTIAL is written by the submit flow but is not accepted as a manual override.

A PostgreSQL advisory lock plus an existence check on status ∈ {PENDING, SUBMITTED} prevents two concurrent submissions of the same study.

Assembly

A genome assembly for one sample.

Unique on (createdByPipelineRunId, sampleId, assemblyFile). That constraint is load-bearing: output resolution can be triggered concurrently by the weblog workflow_complete event and by the run monitor, and without it the same assembly would be inserted twice. Sample.preferredAssemblyId points back at whichever assembly a researcher chose as canonical.

Bin

A metagenome-assembled genome (MAG) bin. Same idempotency story: unique on (createdByPipelineRunId, sampleId, binFile).

Pipelines

PipelineConfig

Per-pipeline enablement and settings. pipelineId is unique and config holds pipeline-specific settings as JSON.

The enabled column defaults to false, but the absence of a row does not mean disabled. Enablement is resolved as: if a PipelineConfig row exists, use its enabled value; otherwise, if the install profile wrote a pipeline allowlist into SiteSettings.extraSettings.installProfilePipelineAllowlist, the pipeline is enabled only if it is on that list; otherwise it is enabled. That is why a fresh install shows the whole catalog while a profiled install shows only part of it.

PipelineRun

One execution of one pipeline against one target. The row is created first and launched second, so most of its columns are empty until the run is staged.

The counter in NNN is computed by scanning today’s run numbers numerically — not lexicographically — so it keeps incrementing past 999. If two prepares race and compute the same number, the loser catches the unique violation, discards its run directory, and retries up to five times.

PipelineRunStep

One step of the run, keyed by the Nextflow process name. Unique on (pipelineRunId, stepId) so repeated events update rather than duplicate.

completed and failed are terminal. When one SeqDesk step maps to several Nextflow processes, a later process_start from a sibling process must not drag the step back to running, so the update logic refuses to regress out of a terminal state.

PipelineRunEvent

The event feed behind the run timeline.

Two properties are worth knowing before you build anything on this table. Events are capped at 100 rows per run — each weblog write deletes anything beyond the newest 100 in the same transaction. And an event identical to one already recorded within ±2 seconds is dropped, so a retried webhook does not double up the timeline.

PipelineArtifact

A file produced by a run, and the row the assembly browser, bin viewer, and QC report previewer read from.

Unique on (pipelineRunId, path), again so concurrent output resolution cannot double-create. outputId is what links a physical file back to the named output in the package manifest, which is how SeqDesk knows a given artifact is a read candidate awaiting promotion rather than just a report.

PipelineResultSelection

Records which run a facility admin declared the final result for one pipeline + target combination — the thing downstream consumers should use when a pipeline has been run five times.

Unique on (pipelineId, targetKey): exactly one winner per pipeline per target, enforced by the database rather than by the API.

Live stream (MinKNOW ingest)

These four models back live Oxford Nanopore ingest, where a monitor process watches a MinKNOW output directory and ingests FASTQ files as the run produces them. See the stream endpoints.

StreamRun

One live ingest session, bound to an order and a watched directory.

Only one ACTIVE run may watch a given directory. The API enforces this inside a SERIALIZABLE transaction, so two concurrent starts cannot both pass the check.

Stopping is a soft stop: the API sets STOPPING and emits a RUN_STOP_REQUESTED event; the monitor daemon closes its watcher on the next tick and writes STOPPED plus stoppedAt. The API never touches the watcher — it is a different process.

StreamIngestedFile

The ledger of every file ingested into a run.

Unique on (streamRunId, filePath). This is the idempotency mechanism for the whole subsystem: filesystem watchers re-emit events for renames, atomic writes, and duplicate add/change pairs. The monitor upserts here before incrementing StreamRun totals, and skips the increment when the upsert was a no-op — so a re-emitted event cannot double-count reads.

StreamRunEvent

Append-only log per stream run.

Clients page on seq, not on ts, so a backlog is delivered in order and nothing in the middle is skipped.

Operations and configuration

SiteSettings

The configuration singleton. id is always the literal string "singleton". Most settings a facility admin edits end up here rather than in a file.

extraSettings is the catch-all and is worth knowing by name, because a surprising number of behaviours are keyed off it: studyFormFields, studyFormGroups, pipelineExecution, sequencingTechConfig, telemetry, notifications, ena.brokerAccount, ena.centerName, sequencingFiles, allowUserAssemblyDownload, allowDeleteSubmittedOrders, departmentSharing, orderNotesEnabled, accountValidationSettings, billingSettings.

Database settings have the lowest priority: a value present in settings.json or an environment variable wins over the database value. See Configuration sources.

OrderFormConfig

The order questionnaire schema, also a singleton (id is "singleton").

BackgroundWorkerProcess

One row per spawn of a long-running helper process — the stream monitor, the pipeline monitor — behind the Background Workers admin page. The important thing to understand is that this table is a record, not a lock. The worker set itself is a static list in code (src/lib/workers/registry.ts); the rows only say what was started, when, by whom, and where its log went.

Three consequences follow from “record, not lock”:

• The operating system is the source of truth, not status. Reading a worker’s state takes the newest row by startedAt and checks the PID with process.kill(pid, 0). A row that claims RUNNING over a dead PID — the normal outcome of a server restart or a crash — is rewritten to STOPPED inline, during the read. Never treat a stored RUNNING as proof a worker is alive.
• Single-instance is enforced in the API route, not the schema. There is no unique index on name; the indexes are (name, status) and (name, startedAt). The Start route and the boot-time autostart both look for an existing RUNNING/STOPPING/ZOMBIE row with a live PID and refuse if they find one. Writing rows directly bypasses that guard and you will get two monitors ingesting the same files.
• Exit is captured twice, on purpose. While the Next.js process that spawned the child is still alive, the child’s exit event writes exitCode and the terminal status. After an app restart that listener is gone, which is exactly why the liveness reconciliation above exists.

Stopping sends SIGTERM, waits ten seconds while polling liveness, then SIGKILLs — so a row can sit in STOPPING for a few seconds legitimately.

InAppNotification

One row per notification per recipient — a fan-out event such as a pipeline run reaching a terminal state writes one row for each facility admin, not one shared row. That is what makes readAt and archivedAt meaningful as plain columns.

The unique dedupeKey is the whole deduplication mechanism, and it works because the writer never inserts one row at a time: it builds all recipients’ rows and issues a single createMany with skipDuplicates, so a repeat is silently dropped instead of raising. Whether an event can fire twice is therefore a property of its key, and the two order events are deliberately different: order.created:<orderId>:<userId> has no time component, so an order announces itself exactly once no matter how often the emitter re-runs, while order.updated:<orderId>:<eventId>:<userId> mixes in a fresh eventId so every edit is its own notification.

Three behaviours surprise people:

• The actor never notifies themselves. Where a caller passes the acting user — the order events do — that user is filtered out of the recipient list, so the admin who changed an order status does not get a notification about their own change. Pipeline events pass no actor and go to the run’s owner plus every facility admin.
• E2E fixtures are silent. Any order or study flagged generatedByE2E is skipped before recipients are resolved, which is why test runs do not bury real notifications.
• Disabling in-app notifications hides history, it does not delete it. Both the writer and the list query short-circuit when the feature is off, so existing rows stay in the table and reappear when it is switched back on.

Listing returns unread first, then newest, with the limit clamped to 1–50 (default 20); the unread badge counts rows with readAt null and archivedAt null. Marking read and archiving are updateManys scoped by userId, so another user’s notification id is a silent no-op rather than an error.

Ticket and TicketMessage

The in-app support thread between a researcher and the facility. A ticket can optionally reference an order or a study, which is what makes “message about this order” work.

TicketMessage is deliberately thin — content, userId, ticketId, createdAt. Read state lives on the parent ticket, not per message, so marking a thread read is one write.

DemoWorkspace

A disposable, self-contained demo session. See Demo mode for the lifecycle.

Workbench (data imports)

The Workbench lets a researcher pull reference datasets into a private workspace and arrange them on an analysis canvas. It is independent of the ordering and pipeline models above — nothing here references an Order, a Sample, or a PipelineRun. The design decision worth carrying into any query you write is that privacy lives on the link, not on the data: workspaces are private, downloaded datasets are shared.

WorkbenchWorkspace and WorkbenchAnalysis

ownerId is unique, so a user has exactly one workspace and there is no “create workspace” action anywhere in the product — the first request that touches the Workbench upserts it (name "Private Workbench", isDefault true) and every later request finds the same row. A workspace holds WorkbenchAnalysis rows: named canvases whose canvas column is the serialized node graph, with a description, an isDefault flag (the auto-created first canvas), and a revision.

revision is optimistic concurrency, not a changelog. Saving a canvas issues an updateMany filtered on the client’s revision and increments it; if two browser tabs have the same analysis open, the second save matches zero rows and comes back flagged as a conflict with the current server state attached, rather than overwriting the first tab’s work. Anything that writes to canvas without carrying the caller’s revision — including the import-job progress writer — bypasses that protection by design, because it is updating one node rather than the whole graph.

WorkbenchDataset and WorkbenchWorkspaceDataset

WorkbenchDataset has no owner column. It is a content-addressed cache of downloaded reference data keyed by cacheKey (unique) — a SHA-256 of the provider id, the import request, and the exact accession list the preview resolved. Two researchers importing the same NCBI taxon under the same filters and cap converge on one row and one directory on disk; the second import short-circuits to “reuse” as soon as it finds a ready row with a storagePath, and never re-downloads. Change a filter and you get a different key, so caching never serves stale contents for a request that means something else.

Access is granted by WorkbenchWorkspaceDataset, unique on (workspaceId, datasetId) and optionally recording the import job that created the link. The link is upserted, so re-importing something a workspace already has is idempotent. It cascades on workspace delete while the dataset does not, which means deleting a user revokes their visibility and leaves the bytes in the shared cache for the next importer — reclaiming that disk space is a separate, deliberate act, and there is no API today that unlinks or deletes a dataset.

Every path is checked before use: a storagePath that resolves outside the computed cache directory raises instead of being read, and imports refuse to start at all until Admin → Infrastructure has a data base path configured.

WorkbenchImportJob

The asynchronous download. status moves queued → running → success or error, with phase and progress (0–100) carrying the finer-grained state the UI polls; logPath points at <data base path>/workbench/jobs/<jobId>/import.log. request and preview store the validated input and the preview the user approved, so a job can be re-executed from its own row without the original request context.

The two fields that do real work are analysisId and analysisNodeId: they are how a background download reports back into a canvas. Each status transition rewrites the matching node in place, which is why a placeholder card on the canvas turns into a dataset card without the page reloading. resultDatasetId is SetNull, so purging a cached dataset leaves the job history intact and merely un-links its result.

Related reading

• Architecture — where these rows live on disk and how they get written
• App API — the endpoints that read and write them
• Sequencing Orders & Samples — the workflow that produces Order, Sample, and SequencingRun
• Pipelines & Analysis — the workflow that produces PipelineRun and its artifacts