Database Schema

IsoFind uses a local SQLite database (isofind.db) composed of 41 tables organised across 12 functional domains. This page documents the key columns, primary keys, foreign keys and main business fields. The schema is verified and migrated automatically at application startup via db_migration.py, with no loss of existing data.

IsoFind: Migration is non-destructive. Missing columns are added (ALTER TABLE ADD COLUMN), absent tables are fully recreated, and additional columns are ignored to ensure downward backward compatibility.

Samples & isotopic data

samples is the pivot table of the model (all measurements, yields and treatments are linked to it by foreign key). It also carries data compartmentalisation (visibility, shared_with, owner_id), import provenance (provenance_id, data_origin), a certified-reference marker (is_crm) and an optional borehole link (borehole_id). isotope_data stores the measured ratios per element and ratio. purification_yields records the purification yield in memory per element, avoiding manual editing of raw isotopic ratios. sample_methods and sample_pipelines instantiate analytical protocols on a given sample, and sample_audit_trail logs column-level changes to a sample.

isofind.db - domain: samples

.
+-- samples                   -- main pivot table
|   +-- id                    PK
|   +-- name, material_type, sector
|   +-- project, classification
|   +-- collection_date, collection_location
|   +-- latitude, longitude
|   +-- owner_id, visibility, shared_with
|   +-- integrity_level, integrity_valid, signed_by, signed_at
|   +-- borehole_id, borehole_depth_m
|   `-- provenance_id, data_origin, is_crm
|
+-- isotope_data              -- raw isotopic measurements
|   +-- id                    PK
|   +-- sample_id             FK -> samples
|   +-- element, isotope_ratio
|   +-- ratio_value, uncertainty
|   +-- normalized_value, standard_used
|   +-- sample_type, collection_date
|   `-- depth_m, normalized_uncertainty
|
+-- purification_yields       -- in-memory yields per element
|   +-- id                    PK
|   +-- sample_id             FK -> samples  [UNIQUE (sample_id, element)]
|   +-- element, yield_percent
|   `-- operator, method_key, measured_at
|
+-- sample_methods            -- protocols applied to a sample
|   +-- id                    PK
|   +-- sample_id             FK -> samples
|   +-- method_id             FK -> method_library
|   +-- method_type, element, laboratory
|   `-- yield_percent, performed_date
|
+-- sample_pipelines          -- analytical pipelines applied
|   +-- id                    PK
|   +-- sample_id             FK -> samples
|   +-- pipeline_id           FK -> method_pipelines
|   +-- current_stage, status
|   `-- started_at, completed_at
|
`-- sample_audit_trail        -- column-level change history
    +-- id                    PK
    +-- sample_id             FK -> samples  [CASCADE]
    +-- action                  [INSERT | UPDATE | DELETE]
    +-- column_name, old_value, new_value
    `-- changed_by, changed_at

CRM & corrections

analyzed_crm represents a measurement session for a certified reference material (instrument, laboratory, quality score). analyzed_crm_values stores measured and certified values with their sigma deviations. crm_derived_shifts derives the corrections to apply to raw ratios. sample_crm_association explicitly links a CRM to the sample it controls.

isofind.db - domain: CRM & corrections

.
+-- analyzed_crm              -- CRM measurement session
|   +-- id                    PK
|   +-- crm_id, crm_name
|   +-- instrument, laboratory, operator
|   `-- quality_score, session_name, analysis_date
|
+-- analyzed_crm_values       -- measured vs certified values
|   +-- id                    PK
|   +-- analyzed_crm_id       FK -> analyzed_crm  [CASCADE]
|   +-- element, isotope_ratio
|   +-- measured_value, certified_value
|   `-- deviation, deviation_sigma, status
|
+-- crm_derived_shifts        -- corrections derived from CRM
|   +-- id                    PK
|   +-- analyzed_crm_id       FK -> analyzed_crm  [CASCADE]
|   +-- element, isotope_ratio
|   +-- shift_value, uncertainty
|   `-- is_active, source
|
`-- sample_crm_association    -- sample <-> CRM link
    +-- id                    PK
    +-- sample_id             FK -> samples  [CASCADE]
    +-- analyzed_crm_id       FK -> analyzed_crm  [CASCADE]
    `-- association_type        [UNIQUE (sample_id, analyzed_crm_id)]

Methods & pipelines

method_library is a library of reusable protocols (digestion, purification, separation, analysis), referenceable by local_key. method_pipelines chains methods into ordered sequences via a stages_json field. The two instantiation tables (sample_methods, sample_pipelines) link these protocols to a concrete sample.

isofind.db - domain: methods & pipelines

.
+-- method_library            -- protocol library
|   +-- id                    PK
|   +-- name, type               [digestion | purification | separation | analysis]
|   +-- element_target, equipment, laboratory
|   +-- doi, reference_text, author
|   `-- local_key               [UNIQUE] reference identifier
|
+-- method_pipelines          -- ordered method sequences
|   +-- id                    PK
|   +-- name, element
|   +-- stages_json             stage sequence
|   +-- total_duration_hours, is_template
|   `-- local_key               [UNIQUE]
|
+-- sample_methods            -- protocol instantiation on a sample
|   +-- id                    PK
|   +-- sample_id             FK -> samples  [CASCADE]
|   +-- method_id             FK -> method_library  [SET NULL]
|   +-- method_type, element, laboratory
|   `-- yield_percent, performed_date, performed_by
|
`-- sample_pipelines          -- pipeline instantiation on a sample
    +-- id                    PK
    +-- sample_id             FK -> samples  [CASCADE]
    +-- pipeline_id           FK -> method_pipelines  [CASCADE]
    +-- current_stage, status    [pending | in_progress | completed | failed]
    `-- started_at, completed_at

Geochemistry & physico-chemistry

sample_geochem stores elemental concentrations per sample, both as a normalised internal value (value_normalized) and as the value and unit as entered by the user (display_value, display_unit). sample_physicochemistry records physico-chemical parameters (pH, Eh, redox, temperature, conductivity, dissolved oxygen, etc.), one value per parameter and per sample by default. Both tables carry an optional sampling depth (depth_m) for borehole contexts.

isofind.db - domain: geochemistry & physico-chemistry

.
+-- sample_geochem            -- elemental / geochemical concentrations
|   +-- id                    PK
|   +-- sample_id             FK -> samples  [CASCADE]
|   +-- element                 (e.g. Sb, Ca, Li)
|   +-- value_normalized, uncertainty
|   +-- display_value, display_unit, method
|   `-- depth_m
|
`-- sample_physicochemistry   -- physico-chemical parameters
    +-- id                    PK
    +-- sample_id             FK -> samples  [CASCADE]
    +-- parameter               [UNIQUE (sample_id, parameter)]
    +-- value, uncertainty, method
    `-- measured_at, depth_m, created_at, updated_at

Molecular geochemistry

user_molecules is the user catalogue of molecules (CAS, SMILES, formula, regulatory thresholds, LOD/LOQ). ref_molecules_isotopes is a reference library of isotopic discrimination ranges per molecule and element (Pro tier). sample_molecules stores molecular concentrations measured on a sample, linked to a sample and, optionally, to a catalogued molecule.

isofind.db - domain: molecular geochemistry

.
+-- user_molecules            -- user molecule catalogue
|   +-- id                    PK
|   +-- nom, cas                [cas UNIQUE]
|   +-- smiles, formule, masse_molaire
|   +-- famille, type_polluant
|   +-- seuil_eau, seuil_sol, lod, loq
|   `-- reglementation, actif
|
+-- ref_molecules_isotopes    -- isotopic discrimination reference (Pro)
|   +-- id                    PK
|   +-- molecule_id           FK -> ref_molecules  [SET NULL]
|   +-- molecule_nom, famille, element, isotope_type
|   +-- gamme_source_1_min/max, gamme_source_2_min/max
|   +-- discrimination_score, methode_analytique
|   `-- faisabilite, licence, actif
|
`-- sample_molecules          -- molecular concentrations per sample
    +-- id                    PK
    +-- sample_id             FK -> samples  [CASCADE]
    +-- molecule_id           FK -> user_molecules  [SET NULL]
    +-- nom, cas, famille
    +-- valeur, unite, valeur_ug_l, incertitude
    +-- lod, loq, detecte, conforme
    `-- depth_m, created_at, updated_at

Boreholes (IsoStrata)

Borehole management module, exposed in the IsoStrata edition. boreholes holds the collar position and geometry (azimuth, dip, total depth). borehole_intervals records lithological intervals with core-quality indicators (recovery_pct, rqd_pct). borehole_surveys stores deviation surveys (azimuth and dip per depth). A sample links to a borehole via samples.borehole_id. The tables exist at schema level in all editions, but are only populated when the module is enabled.

isofind.db - domain: boreholes

.
+-- boreholes                 -- borehole collar & geometry
|   +-- id                    PK
|   +-- name, project
|   +-- latitude, longitude, elevation_m
|   +-- collar_azimuth_deg, collar_dip_deg, total_depth_m
|   +-- status, drill_method, driller, client
|   `-- owner_id, visibility, shared_with, deleted_at
|
+-- borehole_intervals        -- lithological intervals
|   +-- id                    PK
|   +-- borehole_id           FK -> boreholes  [CASCADE]
|   +-- depth_from_m, depth_to_m
|   +-- lithology, description
|   `-- recovery_pct, rqd_pct, weathering
|
`-- borehole_surveys          -- deviation surveys
    +-- id                    PK
    +-- borehole_id           FK -> boreholes  [CASCADE]
    +-- depth_m, azimuth_deg, dip_deg
    `-- measured_at, instrument

E&M prospecting

prospection_sessions stores E&M prospecting analysis sessions. Inputs and results are kept as JSON (inputs_json, results_json), alongside a global score, the best match, the maturity stage and a run status. An optional sample_id links the session to a sample.

isofind.db - domain: E&M prospecting

.
`-- prospection_sessions      -- E&M prospecting analysis sessions
    +-- id                    PK
    +-- name, project
    +-- inputs_json, results_json
    +-- score_global, best_match, maturity_stage
    +-- status, sample_id
    `-- created_at, updated_at

Isotopic standards & geographic references

isotope_standards centralises reference standards (NIST, IAEA, etc.) and custom standards. isotopic_shifts encodes shifts between standard pairs for inter-laboratory normalisation. reference_standards designates the active standard per element. geographic_zones and reference_data provide the regional geochemical background data used in isotopic correspondence; reference_data also carries its import provenance (provenance_id, data_origin).

isofind.db - domain: standards & references

.
+-- isotope_standards         -- analytical standards (NIST, IAEA, custom)
|   +-- id                    PK
|   +-- name, element, isotope_ratio
|   +-- reference_value, uncertainty
|   +-- is_reference, is_custom
|   +-- batch_number, is_default_batch
|   `-- reference_standard_id    self-ref (parent standard)
|
+-- isotopic_shifts           -- shifts between standard pairs
|   +-- id                    PK
|   +-- source_standard_id    FK -> isotope_standards
|   +-- target_standard_id    FK -> isotope_standards
|   +-- element, isotope_ratio
|   `-- shift_value, uncertainty, source
|
+-- reference_standards       -- active standard per element
|   +-- id                    PK
|   +-- element                 [UNIQUE]
|   `-- standard_id           FK -> isotope_standards
|
+-- geographic_zones          -- geochemical reference zones
|   +-- id                    PK
|   +-- name, country, region
|   +-- latitude, longitude, radius_km
|   `-- geological_context
|
`-- reference_data            -- regional geochemical background data
    +-- id                    PK
    +-- geographic_zone_id    FK -> geographic_zones
    +-- element, isotope_ratio
    +-- mean_value, std_deviation
    +-- sample_count, confidence_level, source
    `-- provenance_id, data_origin

Import provenance

import_provenance records the origin of imported data: community catalogue, official reference base, local ISOF file, or direct URL. It keeps the imported file fingerprint (isof_sha256), the signature status at import time (signer, fingerprint, integrity level and validity) and a summary of imported rows. The samples and reference_data tables reference it through provenance_id, while data_origin separates locally entered data from imported data. A pack can be soft-archived via archived without deleting it.

isofind.db - domain: import provenance

.
`-- import_provenance         -- traceability of imported data
    +-- id                    PK
    +-- origin_kind             [community | reference | file | url]
    +-- contribution_id, title, author, doi
    +-- source_url, license
    +-- isof_filename, isof_sha256, isof_format_version
    +-- signed, signer_cn, signer_fingerprint, signature_algorithm
    +-- integrity_level, integrity_valid
    +-- sample_count, analysis_count
    `-- imported_at, imported_by, archived

Publications & archive

publications centralises bibliographic references (DOI, authors, journal). Two junction tables connect it to methods (method_publication_link) and to samples (sample_publications). The archive tables (archive_samples, archive_isotope_data, archive_geochem, archive_molecules, archive_physicochemistry) preserve a frozen snapshot of the data at the archiving date, isolated from the main graph. original_id maintains traceability back to the source record.

isofind.db - domain: publications & archive

.
+-- publications              -- central bibliography
|   +-- id                    PK
|   +-- doi                     [UNIQUE]
|   +-- title, authors, journal, year
|   `-- category, keywords, url, pdf_path
|
+-- method_publication_link   -- method <-> publication
|   +-- id                    PK
|   +-- method_id             FK -> method_library  [CASCADE]
|   +-- publication_id        FK -> publications  [CASCADE]
|   `-- link_type               [source | modification | validation]
|
+-- sample_publications       -- sample <-> publication
|   +-- id                    PK
|   +-- sample_id             FK -> samples  [CASCADE]
|   +-- publication_id        FK -> publications  [CASCADE]
|   `-- relation_type           [source | published_in | reference | cited]
|
+-- archive_samples           -- frozen snapshot of samples
|   +-- id                    PK
|   +-- name, material_type, sector, project
|   +-- original_id             traceability back to samples.id
|   `-- archived_at             archiving timestamp
|
+-- archive_isotope_data      -- frozen snapshot: isotopic measurements
|   +-- id                    PK
|   +-- sample_id             FK -> archive_samples
|   +-- element, isotope_ratio, ratio_value
|   `-- archived_at, standard_used
|
+-- archive_geochem           -- frozen snapshot: geochemistry
|   +-- id                    PK
|   +-- sample_id             FK -> archive_samples
|   +-- element, valeur, unite, methode
|   `-- depth_m, archived_at
|
+-- archive_molecules         -- frozen snapshot: molecules
|   +-- id                    PK
|   +-- sample_id             FK -> archive_samples
|   +-- nom, cas, famille, valeur, unite
|   `-- depth_m, archived_at
|
`-- archive_physicochemistry  -- frozen snapshot: physico-chemistry
    +-- id                    PK
    +-- sample_id             FK -> archive_samples
    +-- parameter, value, uncertainty, method
    `-- depth_m, archived_at

Credits & origin claims (ISOF v1.3)

Credit and origin-claim system of the ISOF v1.3 standard. credit_pools holds imported credit pools, each bound to an engine certificate fingerprint (engine_cert_fp) to prevent cross-instance use. credit_consumption_log is a hash-chained ledger of consumed credits (prev_hash, this_hash, machine signature), with uniqueness per pool and serial. origin_claims stores issued origin verdicts (decision, confidence), optionally paid by a logged consumption. claim_policies holds imported issuance policies.

isofind.db - domain: credits & origin claims

.
+-- credit_pools              -- imported credit pools
|   +-- pool_id               PK
|   +-- organisation, organisation_id
|   +-- issued_at, expires_at, issued_credits
|   +-- engine_cert_fp          bound engine certificate
|   `-- pool_json, imported_at, revoked
|
+-- credit_consumption_log    -- hash-chained consumption ledger
|   +-- seq                   PK
|   +-- pool_id               FK -> credit_pools
|   +-- credit_serial           [UNIQUE (pool_id, credit_serial)]
|   +-- claim_id, consumed_at
|   `-- prev_hash, this_hash, machine_sig_b64
|
+-- origin_claims             -- issued origin verdicts
|   +-- claim_id              PK
|   +-- claim_type, issued_at, expires_at
|   +-- subject_sample_id, target_kind, target_identifier
|   +-- verdict_decision, verdict_confidence
|   +-- pool_id, credit_serial     link to consumption
|   `-- claim_json, document_json
|
`-- claim_policies            -- imported issuance policies
    +-- policy_id             PK
    +-- organisation, name, version
    +-- valid_from, valid_until
    `-- policy_json, imported_at, revoked

Analyses & configuration

Standalone tables with no foreign keys to the rest of the model. analysis_history logs each analysis run (type, status, duration, number of samples involved), with soft-delete via deleted_at. custom_elements extends the configuration of available isotopic elements beyond the default values.

isofind.db - domain: analyses & configuration

.
+-- analysis_history          -- log of analysis runs
|   +-- id                    PK
|   +-- name, analysis_type
|   +-- samples_count, duration
|   +-- status                  [completed | ...]
|   +-- created_at, updated_at
|   `-- deleted_at              soft-delete
|
`-- custom_elements           -- custom isotopic elements
    +-- id                    PK
    +-- symbol                  [UNIQUE]
    +-- name, ratios            available ratios for this element
    `-- is_default              included in the base configuration

Legend & conventions

Notation	Meaning
PK	Primary key, unique row identifier
FK	Foreign key, reference to another table
[CASCADE]	Cascading deletion: child rows are deleted with the parent
[SET NULL]	The foreign key is set to NULL if the parent is deleted
[UNIQUE]	Uniqueness constraint on the column or column combination
a \| b \| c	Accepted values, CHECK constraint in the DDL

IsoFind: The engine is SQLite 3. The declared types (TEXT, REAL, INTEGER, TIMESTAMP) are indicative; SQLite applies dynamic typing by column affinity. Timestamped fields are stored as TEXT in ISO 8601 format via datetime('now'), unless explicitly typed as TIMESTAMP.

Data type dictionary

SQLite applies dynamic typing by column affinity. IsoFind nevertheless enforces strict conventions to guarantee the precision of isotopic calculations and interoperability with third-party tools. The table below documents the conventions used throughout the schema.

Declared type	SQLite affinity	Use in IsoFind	Example fields
TEXT	TEXT	Strings, identifiers, isotopic ratios, ISO 8601 dates	name, isotope_ratio, created_at
REAL	REAL	Isotopic values, uncertainties, geographic coordinates	ratio_value, uncertainty, latitude
FLOAT	REAL	Alias for REAL, inherited from the initial schema, identical behaviour	mean_value, std_deviation
INTEGER	INTEGER	Primary keys, counters, boolean flags (0/1)	id, samples_count, is_active
BOOLEAN	NUMERIC	Stored as INTEGER 0/1, never as literal true/false	is_default, is_reference, is_custom
TIMESTAMP	NUMERIC	System timestamps, stored as ISO 8601 TEXT despite NUMERIC affinity	archived_at, created_at (archive tables)
VARCHAR(n)	TEXT	ORM legacy, the length n is not enforced by SQLite but documents intent	name, sector, standard_used

IsoFind: Isotopic ratio values are always stored as REAL (64-bit IEEE 754 float), which guarantees sufficient precision for mass spectrometry measurements. Do not substitute TEXT for these fields; normalisation and sigma deviation calculations would silently produce incorrect results.

Audit trail & traceability

Traceability is a central requirement for forensic and defence applications. IsoFind records the history of modifications at multiple levels (operation timestamps, column-level change log, preservation of archived data, and analysis logging), without ever irreversibly deleting data.

Mechanism	Tables concerned	How it works
Dual timestamp	samples, analyzed_crm, method_library	Each record carries created_at and updated_at, updated automatically via datetime('now') on every write.
Column-level audit	sample_audit_trail	Every insert, update or delete on a sample is logged with the affected column, the old and new values, the author and the timestamp. Provides a field-level change history independent of the dual timestamp.
Soft-delete	analysis_history	Deleted analyses are not erased — the deleted_at field is set, and the record remains queryable for audit purposes.
Frozen archive	archive_samples, archive_isotope_data, ...	A complete snapshot of the data at the archiving date. The original_id field maintains the link to the source record in samples, even if that record is subsequently modified.
Import provenance	import_provenance	Each import records its origin, the ISOF file fingerprint and the signature status at import time, so imported data can always be traced back to a signed source and distinguished from local data.
CRM quality control	analyzed_crm, analyzed_crm_values	Each certified reference material measurement session is fully preserved, including sigma deviations from certified values. Allows reconstruction of instrument state at any given date.
Credit ledger	credit_consumption_log, origin_claims	Origin-claim issuance consumes credits recorded in a hash-chained ledger, which links accounting audit and cryptographic audit for ISOF v1.3 verdicts.

IsoFind: Cascading deletions (ON DELETE CASCADE) are limited to junction tables and child values directly dependent on a parent record. Primary data (samples, isotope_data) is never automatically deleted as a side effect.

Security & encryption

IsoFind distinguishes two protection perimeters: data integrity at export, and data confidentiality at rest. These two mechanisms are independent and complementary.

IsoFind: The isofind.db file itself is not encrypted at rest, it is protected by the filesystem permissions of the installation directory. Encryption applies to .isof exports and backups, not to the active SQLite database, unless the option is enabled (IsoFind Pro plan).

Mechanism	Scope	Technical detail
SHA-256 integrity	.isof exports	Each export file embeds a SHA-256 hash of its content. On import, IsoFind recomputes the hash and rejects altered files. Guarantees that data has not been modified in transit.
AES encryption	Encrypted .isof exports	Exports can be encrypted with a key managed via the system keyring (isofKeyringManage). The recipient must hold the corresponding key to decrypt the import.
Digital signature	Certified .isof files	Files from a certified laboratory carry a verifiable cryptographic signature via the ISOF standard. The signature attests to the origin and integrity of the complete analytical chain.
Filesystem permissions	isofind.db database	Access to the active database is controlled by filesystem permissions. On multi-user deployments, the data directory should be restricted to the user running IsoFind.