boreholes.swissgeol.ch data specification — data model and schema documentation

Ansorge, Christian; Gysi, Michael

doi:10.60695/swisstopo.boreholes.datamodel.2026

Data model and schema documentation

boreholes.swissgeol.ch
data specification

The data model behind Switzerland’s national borehole repository — 56 tables, 464 attributes, 75 referenced code lists.

Version: 2.1.1748 (boreholes.swissgeol.ch application). Not every application release changes the database schema.
Published: 18 August 2026
Status: Published
Publisher: Federal Office of Topography swisstopo
Authors: Christian Ansorge, Data Architect Landesgeologie
Michael Gysi, Application Owner boreholes.swissgeol.ch
Contact: swissgeol@swisstopo.ch
DOI: 10.60695/swisstopo.boreholes.datamodel.2026
Licence: Creative Commons Attribution 4.0 International (CC BY 4.0)
Cite as: Ansorge, C. and Gysi, M. (2026): boreholes.swissgeol.ch data specification — data model and schema documentation. Version 2.1.1748. Federal Office of Topography swisstopo. https://doi.org/10.60695/swisstopo.boreholes.datamodel.2026

Federal Office of Topography swisstopo
Wabern, Switzerland

Scope and introduction

The boreholes.swissgeol.ch database is swisstopo's central national repository for storing, managing, and publishing borehole information. It integrates geological, hydrogeological, petrophysical, and borehole engineering data, together with associated borehole logs, documents, photographs, and other supporting information.

The purpose of this document is to provide a structured overview of the database schema. It describes the individual database tables, their attributes, foreign key relationships, and relevant code lists, thereby serving as a reference for understanding the data model and its underlying structure.

The tables EFMigrationsHistory and config haven't been included in this document as they serve solely application specific purposes and carry no borehole relevant content.

Scope of this specification

This specification describes the database schema as of application version 2.1.1748. It is maintained by swisstopo and published as this HTML document at https://download.swissgeol.ch/dm_boreholes/boreholes-data-specification.html, with the persistent identifier https://doi.org/10.60695/swisstopo.boreholes.datamodel.2026.

boreholes.swissgeol.ch runs as several instances — public, internal, external, integration and development — which share this schema. General information about the swissgeol platform is available at swissgeol.ch.

How to read this document

Every table is documented in the same order: a short description of what it stores, an entity-relationship excerpt showing its immediate neighbours, its attributes, its foreign keys, and the code lists its attributes draw from. Short code lists are listed in place; long ones are collected in the annex and linked from the attribute that uses them.

Tables are grouped into eight subject domains rather than listed alphabetically, so that related tables are read together. Use the search field in the contents panel to jump straight to a table by name.

Diagram notation

The entity-relationship diagrams are generated with GraphViz. Each box is a database table and lists its columns; each arrow is a foreign key constraint and points from the referencing table to the referenced table. The diagrams show structure only — they carry no information about cardinality, nor about what happens to referencing rows when a referenced row is deleted.

Reading the attribute tables

Attribute name
Column name as it appears in the database.
Data type
PostgreSQL data type.
Nullable
NO means a value is always present. Primary keys and unique constraints are not marked in this specification.
Code lists
Collapsed by default. Open one to see its permitted values, or follow the link to the annex for the longer lists.

Modelling conventions

The following conventions apply throughout the model and are not repeated in each table description.

Record identity and audit columns

Nearly every table carries id as its identifier and the four audit attributes creator, creation, updater and update, referencing the users table and the time of the change. These audit values are shown in the application and are visible to the public.

Code list references

Attributes ending in _id that are not structural foreign keys reference controlled values held in the generic codelist table. Multi-value properties are resolved through dedicated join tables whose names end in _codelist. Some vocabularies are not maintained in this database at all but in the swissgeol vocabulary service lexic.swissgeol.ch.

Depth, elevation and units

All lengths and depths are given in metres unless an attribute description states otherwise.

Measured depth is the depth as actually measured along the borehole — that is, the length of the borehole — and not a vertical projection. Attributes carrying a measured depth are marked with the suffix _md or described as measured depth.

Elevation in this model always means height above sea level.

The reference point is either the surface coordinate of the borehole in XY, or the zero point of the depth measurement. In most cases that zero point is ground level. Where a structure such as a protruding pipe stands above the terrain and depth is measured from there, that point is recorded separately as the reference elevation — see reference_elevation and reference_elevation_type_id in borehole.

The coordinate reference system and the height reference system are stored with the data rather than fixed by this specification: see srs_id and hrs_id in borehole. Coordinates are held both in the current national frame (location_x, location_y) and in the former one (location_x_lv03, location_y_lv03).

Languages

Code list labels are held in the codelist table in English, German, French and Italian (text_en, text_de, text_fr, text_it). Only the English labels are reproduced in this document.

Tables not documented

The tables __EFMigrationsHistory and config serve application purposes only and carry no borehole-relevant content. They are omitted deliberately.

Database structure

This diagram shows the overall database structure. In this diagram the table "user" has been removed for better overview and general clarity. All information on the table "user" can be found in the detailed table descriptions and from respective foreign key constraints.

Overall database structure. Zoom in and scroll inside the frame to read the table boxes.

The diagram is embedded as a vector graphic: text stays sharp at any zoom level and can be searched with the browser. The users table is omitted from the diagram because its relationships connect to nearly every other table.

The eight domains

Borehole location, general, section and trajectory
The central borehole record with its location and general properties, external identifiers, the drilled sections and their successive elements, and the calculated trajectory.
Stratigraphy and lithology
Layer-based geological interpretation of the borehole, plus the multi-value code lists that qualify each layer.
Completion and installation
What was built into the borehole after drilling: casings, backfill and sealing, and instrumentation.
Hydrogeology and field tests
Observations and measurements made in the borehole, from water ingress during drilling to hydraulic tests.
Logging
Geophysical logging: runs, the resulting log files, and the tool types used.
Profiles, photos and documents
Content attached to a borehole: geological profiles, photographs, and reports and other documents.
Code list infrastructure
The generic table that holds the controlled values referenced throughout the model.
Users, workgroups and workflow
Application tables governing who may see and edit which data, the publication workflow, and the execution log of automated maintenance tasks.

The grouping is editorial: it organises the reference section for reading and has no counterpart in the database itself.

Glossary and abbreviations

ERD: Entity-relationship diagram.
PK / FK: Primary key / foreign key.
Code list: A closed set of permitted values for an attribute, each with a stable numeric identifier.
con / uncon: Consolidated rock / unconsolidated deposits.
srs / hrs: Spatial reference system / height reference system.
USCS: Unified Soil Classification System.
lexic: The swissgeol vocabulary service at lexic.swissgeol.ch, which holds the geological vocabularies that are not maintained inside this database.

Borehole location, general, section and trajectory

The central borehole record with its location and general properties, external identifiers, the drilled sections and their successive elements, and the calculated trajectory.

5 tables

#`borehole`

Stores the primary borehole record, including general information such as name, location, type, purpose, top bedrock, and other fundamental borehole attributes.

48 attributes / 4 foreign keys / 14 code lists

Entity-relationship diagram excerpt for the table borehole, showing its columns and the referenced tables users, workgroups. — ERD excerpt for `borehole`. Select to enlarge.

Attributes

Attributes of table borehole
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
update	Date and time of the last update.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
locked	Date and time when the record was locked.	timestamp with time zone	YES
locked_by	User who locked the record.	integer	YES
workgroup_id	Reference to the related workgroups record.	integer	YES
public	Legacy field retained from previous versions; always set to NULL.	boolean	YES
restriction_id	Reference to a codelist defining the restriction category.	integer	YES
restriction_until	States until when the access to a record is restricted .	date	YES
original_name	Original borehole name preserved from the source documentation.	character varying	YES
name	Borehole name intended for publication. May differ from the original name to exclude personal information (e.g. the owner's name or address).	character varying	YES
project_name	Name of the related project.	character varying	YES
borehole_type_id	Reference to the codelist defining the borehole type.	integer	YES
purpose_id	Reference to the codelist defining the purpose.	integer	YES
status_id	Reference to the codelist defining the status.	integer	YES
location_x	X coordinate of location in LV95 (EPSG:2056).	double precision	YES
location_y	Y coordinate of location in LV95 (EPSG:2056).	double precision	YES
location_x_lv03	X coordinate of location in LV03 (EPSG:21781).	double precision	YES
location_y_lv03	Y coordinate of location in LV03 (EPSG:21781).	double precision	YES
srs_id	Reference to the codelist defining the spatial reference system.	integer	YES
geometry	Spatial geometry representing the borehole location (point geometry).	geometry	YES
hrs_id	Reference to a codelist defining the height reference system.	integer	YES
elevation_z	Ground elevation at the borehole location, which may differ from the elevation of the borehole reference point.	double precision	YES
reference_elevation	Reference point used for all borehole depth measurements.	double precision	YES
reference_elevation_type_id	Reference to the codelist defining the reference elevation type.	integer	YES
total_depth	Total depth of the borehole.	double precision	YES
precision_location_x	Indicates the number of digits used to represent the X coordinate (LV95, EPSG:2056), allowing its precision to be visualised correctly.	integer	YES
precision_location_y	Indicates the number of digits used to represent the Y coordinate (LV95, EPSG:2056), allowing its precision to be visualised correctly.	integer	YES
precision_location_x_lv03	Indicates the number of digits used to represent the X coordinate (LV03, EPSG:21781), allowing its precision to be visualised correctly.	integer	YES
precision_location_y_lv03	Indicates the number of digits used to represent the Y coordinate (LV03, EPSG:21781), allowing its precision to be visualised correctly.	integer	YES
precision_location_id	Reference to the codelist defining the p recision of the coordinates.	integer	YES
precision_elevation_id	Reference to the codelist defining the elevation precision.	integer	YES
precision_reference_elevation_id	Reference to the codelist defining the reference point precision.	integer	YES
precision_depth_id	Reference to the codelist defining the depth precision.	integer	YES
country	Country in which the borehole is located.	text	YES
canton	Swiss canton in which borehole surface location is located.	text	YES
municipality	Swiss municipality in which borehole surface location is located.	text	YES
national_interest	Indicates whether the borehole is designated as being of national interest.	boolean	YES
groundwater	Whether or not groundwater has been encountered.	boolean	YES
top_bedrock_fresh_md	Depth at which the top of the fresh bedrock was first encountered.	double precision	YES
top_bedrock_weathered_md	Depth at which the top of the weathered bedrock was first encountered.	double precision	YES
top_bedrock_intersected	Indicates whether bedrock has been encountered.	boolean	YES
lithology_top_bedrock_id	Reference to the codelist defining the lithology of top bedrock.	integer	YES
lithostratigraphy_top_bedrock_id	Reference to the codelist defining the lithostratigraphy of top bedrock.	integer	YES
chronostratigraphy_top_bedrock_id	Reference to the codelist defining the chronostratigraphy of top bedrock.	integer	YES
remarks	Additional descriptive or contextual remarks.	character varying	YES

Foreign key relationships

Foreign keys of table borehole
Attribute Name	Foreign Table	Foreign Column
creator	users	id
updater	users	id
locked_by	users	id
workgroup_id	workgroups	id

Code lists

borehole_type_id7 values

Id Label (English)

Values of code list borehole_type_id
Id	Label (English)
20101001	borehole
30000307	virtual borehole
20101002	penetration test
20101003	trial pit
20101006	outcrop
20101004	other
20101005	not specified

srs_id2 values

Id Label (English)

Values of code list srs_id
Id	Label (English)
20104001	CH1903+ LV 95 (EPSG:2056)
20104002	CH1903 LV 03 (EPSG:21781)

hrs_id1 values

Id Label (English)

Values of code list hrs_id
Id	Label (English)
20106001	LN02

restriction_id4 values

Id Label (English)

Values of code list restriction_id
Id	Label (English)
20111001	free
20111002	restricted
20111003	restricted until
20111004	not specified

precision_location_id7 values

Id Label (English)

Values of code list precision_location_id
Id	Label (English)
20113001	100
20113002	50
20113003	10
20113004	5 (± GPS )
20113005	0.1 (± DGPS / Theodolit)
20113006	reconstructed
20113007	not specified

precision_elevation_id7 values

Id Label (English)

Values of code list precision_elevation_id
Id	Label (English)
20114001	10
20114002	5
20114003	1
20114004	0.5
20114005	0.1
20114006	reconstructed
20114007	not specified

purpose_id24 values · in annex

This codelist contains 24 values. Full values are documented in Annex — drilling_purpose.

status_id9 values

Id Label (English)

Values of code list status_id
Id	Label (English)
22104001	open, no completion
22104002	filled
22104003	decayed
22104004	covered, inaccessible
22104005	cased, measurements possible
22104006	cased, in use
22104007	cased, measurements impossible
22104008	other
22104009	not specified

precision_depth_id6 values

Id Label (English)

Values of code list precision_depth_id
Id	Label (English)
22108001	2
22108002	1
22108003	0.5
22108004	0.1
22108005	estimated
22108006	not specified

lithology_top_bedrock_idsee note

This codelist contains 643 values. Full values are documented in the annex.

lithostratigraphy_top_bedrock_idsee note

This codelist contains 1629 values. Full values are documented in Annex — lithostratigraphy.

chronostratigraphy_top_bedrock_idsee note

This codelist contains 140 values. Full values are documented in Annex — chronostratigraphy.

precision_reference_elevation_id7 values

Id Label (English)

Values of code list precision_reference_elevation_id
Id	Label (English)
20114001	10
20114002	5
20114003	1
20114004	0.5
20114005	0.1
20114006	reconstructed
20114007	not specified

reference_elevation_type_id7 values

Id Label (English)

Values of code list reference_elevation_type_id
Id	Label (English)
20117001	ground surface
20117002	casing top
20117003	rotary table
30000013	kelly bushing
20117004	manhole cover
20117005	other
20117006	not specified

#`borehole_identifiers_codelist`

Stores external and internal identifiers assigned to the borehole by cantons, information systems, and projects.

5 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table borehole_identifiers_codelist
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
identifier_id	Reference to the codelist defining the identifier origin.	integer	NO
identifier_value	The identifier value of the borehole record.	character varying	NO
comment	Comment regarding the identifier record (e.g. identifier of which canton).	text	YES

Foreign key relationships

Foreign keys of table borehole_identifiers_codelist
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id

Code lists

identifier_id6 values

Id Label (English)

Values of code list identifier_id
Id	Label (English)
100000004	original ID
100000000	GeODin ID
100000003	InfoGeol ID
100000005	canton ID
100000006	GeoQuat ID
100000007	GeoMol ID

#`section`

Stores information about individual drilled sections of a borehole.

7 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table section, showing its columns and the referenced tables borehole, users. — ERD excerpt for `section`. Select to enlarge.

Attributes

Attributes of table section
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
name	Section name.	text	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table section
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id

#`section_element`

Stores information about individual borehole section elements. Multiple section elements can be defined for the same borehole section to represent successive drilling phases, such as reaming or overcoring, thereby preserving the complete borehole history.

17 attributes / 3 foreign keys / 3 code lists

Entity-relationship diagram excerpt for the table section_element, showing its columns and the referenced tables section, users. — ERD excerpt for `section_element`. Select to enlarge.

Attributes

Attributes of table section_element
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
section_id	Reference to the related section record.	integer	NO
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
order	Sequence number of the section element based on the recording order.	integer	NO
drilling_method_id	Reference to the codelist defining the drilling method.	integer	YES
drilling_start_date	Start drilling date.	date	YES
drilling_end_date	End drilling date.	date	YES
cuttings_id	Reference to the code list defining whether the borehole was cored or drilled destructively.	integer	YES
drilling_diameter	Indicates the drilling diameter of the borehole section.	double precision	YES
drilling_core_diameter	Indicates the core diameter of the borehole section.	double precision	YES
mud_type_id	Reference to the codelist defining the mud type.	integer	YES
mud_subtype_id	Reference to the codelist defining the mud subtype.	integer	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table section_element
Attribute Name	Foreign Table	Foreign Column
section_id	section	id
creator	users	id
updater	users	id

Code lists

drilling_method_id15 values

Id Label (English)

Values of code list drilling_method_id
Id	Label (English)
22107006	manual drilling
22107003	dynamic probing
22107011	direct push soil sampling
22107013	percussion drilling
22107008	rotary drilling
22107015	reverse circulation drilling
22107004	rotary coring
22107016	wireline core drilling
22107007	shaft drilling
22107009	trench
22107012	grab drilling
22107014	piling
22107017	auger drilling
22107019	other
22107018	not specified

cuttings_id5 values

Id Label (English)

Values of code list cuttings_id
Id	Label (English)
22102001	core
22102002	cuttings
22102004	not drilled
22102007	not specified
22102006	other

mud_type_id / mud_subtype_id23 values · in annex

This code list contains 23 values representing both mud_type and mud_subtype. The distinction between the two is handled in the application frontend. The complete list of values is documented in Annex — drilling_mud_type.

#`borehole_geometry`

Stores the calculated borehole trajectory based on the minimum curvature algorithm. The trajectory may consist of multiple geometry segments.

8 attributes / 1 foreign keys

Entity-relationship diagram excerpt for the table borehole_geometry, showing its columns and the referenced tables borehole. — ERD excerpt for `borehole_geometry`. Select to enlarge.

Attributes

Attributes of table borehole_geometry
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
X	X coordinate relative to the borehole reference point.	double precision	NO
Y	Y coordinate relative to the borehole reference point.	double precision	NO
Z	Z coordinate relative to the borehole reference point.	double precision	NO
DEVI	Deviation angle of the borehole trajectory from the vertical.	double precision	YES
HAZI	Azimuth of the borehole trajectory.	double precision	YES
MD	Measured depth along the borehole trajectory.	double precision	NO

Foreign key relationships

Foreign keys of table borehole_geometry
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id

Stratigraphy and lithology

Layer-based geological interpretation of the borehole, plus the multi-value code lists that qualify each layer.

19 tables

#`stratigraphy`

Stores the common information shared by lithology, lithostratigraphy, and chronostratigraphy records.

9 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table stratigraphy, showing its columns and the referenced tables borehole, users. — ERD excerpt for `stratigraphy`. Select to enlarge.

Attributes

Attributes of table stratigraphy
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
name	Stratigraphy name.	text	NO
date	Date on which the stratigraphy was recorded.	timestamp with time zone	YES
is_primary	Whether or not it is the primary stratigraphy of the borehole.	boolean	NO
update	Date and time of the last update.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
creator	User who created the record.	integer	YES

Foreign key relationships

Foreign keys of table stratigraphy
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
updater	users	id
creator	users	id

#`lithological_description`

Stores the detailed lithological description of each borehole layer as originally documented.

9 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table lithological_description, showing its columns and the referenced tables stratigraphy, users. — ERD excerpt for `lithological_description`. Select to enlarge.

Attributes

Attributes of table lithological_description
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
stratigraphy_id	Reference to the related stratigraphy record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
description	Original lithological description of the layer.	text	YES
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO

Foreign key relationships

Foreign keys of table lithological_description
Attribute Name	Foreign Table	Foreign Column
stratigraphy_id	stratigraphy	id
creator	users	id
updater	users	id

#`lithology`

Stores detailed, structured, and harmonised lithological descriptions based on multiple attributes. The applicable attributes differ for consolidated and unconsolidated rock.

19 attributes / 3 foreign keys / 7 code lists

Entity-relationship diagram excerpt for the table lithology, showing its columns and the referenced tables stratigraphy, users. — ERD excerpt for `lithology`. Select to enlarge.

Attributes

Attributes of table lithology
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
stratigraphy_id	Reference to the related stratigraphy record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
unconsolidated	Indicates whether the layer is classified as unconsolidated. If false, the layer is classified as consolidated. If NULL, the consolidation status is unknown.	boolean	YES
bedding	Indicates whether the layer is classified as "Alternating bedding".	boolean	NO
bedding_share	Percentage of the first lithology within the alternating bedding of the layer.	integer	YES
alteration_degree_id	Reference to the codelist defining the alteration degree.	integer	YES
notes	Additional comments or contextual remarks.	text	YES
compactness_id	Reference to the codelist defining the compactness.	integer	YES
cohesion_id	Reference to the codelist defining the cohesion.	integer	YES
humidity_id	Reference to the codelist defining the humidity.	integer	YES
consistency_id	Reference to the codelist defining the consistency.	integer	YES
plasticity_id	Reference to the codelist defining the plasticity.	integer	YES
uscs_determination_id	Reference to the codelist defining the uscs determination.	integer	YES

Foreign key relationships

Foreign keys of table lithology
Attribute Name	Foreign Table	Foreign Column
stratigraphy_id	stratigraphy	id
creator	users	id
updater	users	id

Code lists

alteration_degree_id8 values

Id Label (English)

Values of code list alteration_degree_id
Id	Label (English)
100000172	fresh
100002996	weathered
100000173	slightly weathered
100000174	moderately weathered
100000175	highly weathered
100000176	completely weathered
100000177	other
100000178	not specified

compactness_id7 values

Id Label (English)

Values of code list compactness_id
Id	Label (English)
21102001	very loose
21102002	loose
21102003	moderately loose
21102005	dense
21102006	very dense
21102007	not specified
100000488	other

cohesion_id6 values

Id Label (English)

Values of code list cohesion_id
Id	Label (English)
21116001	non-cohesive
21116002	slightly cohesive
21116003	cohesive
21116004	very cohesive
21116005	not specified
100000489	other

humidity_id6 values

Id Label (English)

Values of code list humidity_id
Id	Label (English)
21105001	dry
21105002	earth-moist
21105003	moist
21105004	wet
21105005	not specified
100000490	other

consistency_id8 values

Id Label (English)

Values of code list consistency_id
Id	Label (English)
21103001	very soft
21103002	soft
21103003	medium stiff
21103004	stiff
21103008	hard
21103009	very hard
21103010	not specified
100000491	other

plasticity_id7 values

Id Label (English)

Values of code list plasticity_id
Id	Label (English)
21101001	non plasticity
21101002	slight plasticity
21101003	low plasticity
21101004	medium plasticity
21101005	high plasticity
21101006	not specified
100000492	other

uscs_determination_id5 values

Id Label (English)

Values of code list uscs_determination_id
Id	Label (English)
23107001	field
23107002	laboratory
23107003	third-party interpretation
23107004	not specified
100000493	other

#`lithology_description`

Stores detailed, structured, and harmonised lithological descriptions, allowing multiple records for the same layer to represent alternating bedding.

21 attributes / 3 foreign keys / 9 code lists

Entity-relationship diagram excerpt for the table lithology_description, showing its columns and the referenced tables lithology, users. — ERD excerpt for `lithology_description`. Select to enlarge.

Attributes

Attributes of table lithology_description
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
lithology_id	Reference to the related lithology record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
first	Indicates whether this record represents the first lithology in the alternating bedding (true) or the second lithology (false).	boolean	NO
color_primary_id	Reference to the codelist defining the primary color.	integer	YES
color_secondary_id	Reference to the codelist defining the secondary color.	integer	YES
lithology_uncon_main_id	Reference to the codelist defining the main unconsolidated lithology.	integer	YES
lithology_uncon_2_id	Reference to the codelist defining the second unconsolidated lithology.	integer	YES
lithology_uncon_3_id	Reference to the codelist defining the third unconsolidated lithology.	integer	YES
lithology_uncon_4_id	Reference to the codelist defining the fourth unconsolidated lithology.	integer	YES
lithology_uncon_5_id	Reference to the codelist defining the fifth unconsolidated lithology.	integer	YES
lithology_uncon_6_id	Reference to the codelist defining the sixth unconsolidated lithology.	integer	YES
striae	Indicates whether the pebbles or rubble exhibit glacial striations on their surfaces.	boolean	NO
lithology_con_id	Reference to the codelist defining the consolidated lithology.	integer	YES
grain_size_id	Reference to the codelist defining the grain size.	integer	YES
grain_angularity_id	Reference to the codelist defining the grain angularity.	integer	YES
gradation_id	Reference to the codelist defining the gradation.	integer	YES
cementation_id	Reference to the codelist defining the cementation.	integer	YES

Foreign key relationships

Foreign keys of table lithology_description
Attribute Name	Foreign Table	Foreign Column
lithology_id	lithology	id
creator	users	id
updater	users	id

Code lists

color_primary_id91 values · in annex

This codelist contains 91 values. Full values are documented in Annex — color.

color_secondary_id91 values · in annex

This codelist contains 91 values. Full values are documented in Annex — color.

lithology_uncon_main_id24 values · in annex

This codelist contains 24 values. Full values are documented in Annex — lithology_uncon_main.

lithology_uncon_2_id, lithology_uncon_3_id, lithology_uncon_4_id, lithology_uncon_5_id, lithology_uncon_6_id20 values

All 5 attributes use the same identical codelist. Id Label (English)

Values of code list lithology_uncon_2_id, lithology_uncon_3_id, lithology_uncon_4_id, lithology_uncon_5_id, lithology_uncon_6_id
Id	Label (English)
100000036	coarse blocky / with large blocks
100000037	blocky / with blocks
100000038	stony / with stones
100000039	gravelly
100000040	fine gravelly
100000041	fine to medium gravelly
100000042	medium gravelly
100000043	medium to coarse gravelly
100000044	coarse gravelly
100000045	sandy
100000046	fine sandy
100000047	fine to medium sandy
100000048	medium sandy
100000049	medium to coarse sandy
100000050	coarse sandy
100000051	silty
100000052	clayey
100000053	with organic inclusion
100000054	other
100000055	not specified

lithology_con_idsee note

This codelist contains 643 values. Full values are documented in Annex — lithology_con.

grain_size_id7 values

Id Label (English)

Values of code list grain_size_id
Id	Label (English)
100000497	very fine
21109001	fine
21109003	medium
21109005	coarse
100000498	very coarse
100000499	other
21109007	not specified

grain_angularity_id8 values

Id Label (English)

Values of code list grain_angularity_id
Id	Label (English)
21115001	very angular
21115003	angular
21115004	sub-angular
21115005	sub-rounded
21115006	rounded
21115007	well rounded
100000487	other
21115008	not specified

gradation_id7 values

Id Label (English)

Values of code list gradation_id
Id	Label (English)
30000015	very well-sorted
30000016	well sorted
30000017	moderately sorted
30000018	poorly sorted
30000019	very poorly sorted
100000494	other
100000495	not specified

cementation_id7 values

Id Label (English)

Values of code list cementation_id
Id	Label (English)
100000356	uncemented
100000357	weakly cemented
100000358	moderately cemented
100000359	well cemented
100000360	strongly cemented
100000361	other
100000362	not specified

#`facies_description`

Stores information about interpreted facies.

10 attributes / 3 foreign keys / 1 code lists

Entity-relationship diagram excerpt for the table facies_description, showing its columns and the referenced tables stratigraphy, users. — ERD excerpt for `facies_description`. Select to enlarge.

Attributes

Attributes of table facies_description
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
stratigraphy_id	Reference to the related stratigraphy record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
description	Original geological layer description.	text	YES
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
facies_id	Reference to the codelist defining the facies (depositional environments).	integer	YES

Foreign key relationships

Foreign keys of table facies_description
Attribute Name	Foreign Table	Foreign Column
stratigraphy_id	stratigraphy	id
creator	users	id
updater	users	id

Code lists

facies_id154 values · in annex

This codelist contains 154 values. Full values are documented in Annex — facies_con.

#`chronostratigraphy`

Stores chronostratigraphic interpretations assigned to geological intervals.

9 attributes / 3 foreign keys / 1 code lists

Entity-relationship diagram excerpt for the table chronostratigraphy, showing its columns and the referenced tables stratigraphy, users. — ERD excerpt for `chronostratigraphy`. Select to enlarge.

Attributes

Attributes of table chronostratigraphy
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
stratigraphy_id	Reference to the related stratigraphy record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	YES
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	YES
chronostratigraphy_id	Reference to the codelist defining the chronostratigraphical concept.	integer	YES

Foreign key relationships

Foreign keys of table chronostratigraphy
Attribute Name	Foreign Table	Foreign Column
stratigraphy_id	stratigraphy	id
creator	users	id
updater	users	id

Code lists

chronostratigraphy_idsee note

This codelist contains 140 values. Full values are documented in Annex — chronostratigraphy.

#`lithostratigraphy`

Stores lithostratigraphic interpretations assigned to geological intervals.

9 attributes / 3 foreign keys / 1 code lists

Entity-relationship diagram excerpt for the table lithostratigraphy, showing its columns and the referenced tables stratigraphy, users. — ERD excerpt for `lithostratigraphy`. Select to enlarge.

Attributes

Attributes of table lithostratigraphy
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
stratigraphy_id	Reference to the related stratigraphy record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
lithostratigraphy_id	Reference to the codelist defining the lithostratigraphy.	integer	YES

Foreign key relationships

Foreign keys of table lithostratigraphy
Attribute Name	Foreign Table	Foreign Column
stratigraphy_id	stratigraphy	id
creator	users	id
updater	users	id

Code lists

lithostratigraphy_idsee note

This codelist contains 1629 values. Full values are documented in Annex — lithostratigraphy.

#`lithology_description_component_con_mineral_codelist`

Association table (n:m) linking lithology_description to one or more accessory minerals of consolidated rock.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_component_con_mineral_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
component_con_mineral_id	Reference to the codelist defining the accessory minerals.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_component_con_mineral_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

component_con_mineral_id111 values · in annex

This codelist contains 111 values. Full values are documented in Annex — component_con_mineral.

#`lithology_description_component_con_particle_codelist`

Association table (n:m) linking lithology_description to one or more accessory components of consolidated rock.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_component_con_particle_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
component_con_particle_id	Reference to the codelist defining the assessory components.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_component_con_particle_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

component_con_particle_id66 values · in annex

This codelist contains 66 values. Full values are documented in Annex — component_con_particle.

#`lithology_description_component_uncon_debris_codelist`

Association table (n:m) linking lithology_description to one or more unclassifiable coarse components of unconsolidated rock.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_component_uncon_debris_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
component_uncon_debris_id	Reference to the codelist defining the unclassifiable coarse components.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_component_uncon_debris_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

component_uncon_debris_id7 values

Id Label (English)

Values of code list component_uncon_debris_id
Id	Label (English)
9100	erratic block
9101	rubble
9102	bed load
9103	fragments, splitters
9104	tufa
100002998	other
9105	not specified

#`lithology_description_component_uncon_organic_codelist`

Association table (n:m) linking lithology_description to one or more organic components of unconsolidated rock.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_component_uncon_organic_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
component_uncon_organic_id	Reference to the codelist defining the organic components.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_component_uncon_organic_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

component_uncon_organic_id11 values

Id Label (English)

Values of code list component_uncon_organic_id
Id	Label (English)
21108001	earth
21108002	humus
21108003	undifferenciated organic material
21108004	roots
21108005	remains of wood
21108006	remains of plants
21108007	coal
21108008	peat
21108009	varves
100002997	other
21108010	not specified

#`lithology_description_grain_angularity_codelist`

Association table (n:m) linking lithology_description to one or more grain angularity.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_grain_angularity_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
grain_angularity_id	Reference to the codelist defining the grain angularities.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_grain_angularity_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

grain_angularity_id8 values

Id Label (English)

Values of code list grain_angularity_id
Id	Label (English)
21115001	very angular
21115003	angular
21115004	sub-angular
21115005	sub-rounded
21115006	rounded
21115007	well rounded
100000487	other
21115008	not specified

#`lithology_description_grain_shape_codelist`

Association table (n:m) linking lithology_description to one or more grain shapes.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_grain_shape_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
grain_shape_id	Reference to the codelist defining the grain shapes.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_grain_shape_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

grain_shape_id5 values

Id Label (English)

Values of code list grain_shape_id
Id	Label (English)
21110002	cubic
21110003	platy
21110004	elongated
100000486	other
21110005	not specified

#`lithology_description_lithology_uncon_debris_codelist`

Association table (n:m) linking lithology_description to one or more lithologies of coarse components.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_lithology_uncon_debris_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
lithology_uncon_debris_id	Reference to the code list defining the lithologies of coarse components (the same code list as used for consolidated lithologies).	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_lithology_uncon_debris_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

lithology_con_idsee note

The codelist lithology_con_id is applicable to the attribute lithology_uncon_debris_id. This codelist contains 643 values. Full values are documented in Annex — lithology_con.

#`lithology_description_structure_post_gen_codelist`

Association table (n:m) linking lithology_description to one or more postgenetic structures.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_structure_post_gen_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
structure_post_gen_id	Reference to the codelist defining the post genetic structures.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_structure_post_gen_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

structure_post_gen_id43 values · in annex

This codelist contains 43 values. Full values are documented in Annex — structure_post_gen.

#`lithology_description_structure_syn_gen_codelist`

Association table (n:m) linking lithology_description to one or more syngenetic structures.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_description_structure_syn_gen_codelist
Attribute Name	Description	Data Type	Nullable
lithology_description_id	Reference to the related lithology description record.	integer	NO
structure_syn_gen_id	Reference to the codelist defining the syngenetic structures.	integer	NO

Foreign key relationships

Foreign keys of table lithology_description_structure_syn_gen_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_description_id	lithology_description	id

Code lists

structure_syn_gen_id60 values · in annex

This codelist contains 60 values. Full values are documented in Annex — structure_syn_gen.

#`lithology_rock_condition_codelist`

Association table (n:m) linking lithology to one or more rock conditions.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_rock_condition_codelist
Attribute Name	Description	Data Type	Nullable
lithology_id	Reference to the related lithology record.	integer	NO
rock_condition_id	Reference to the codelist defining the rock conditions.	integer	NO

Foreign key relationships

Foreign keys of table lithology_rock_condition_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_id	lithology	id

Code lists

rock_condition_id7 values

Id Label (English)

Values of code list rock_condition_id
Id	Label (English)
100000165	disaggregated
100000166	decomposed
100000167	subsided
100000168	displaced
100000169	glided
100000170	other
100000171	not specified

#`lithology_texture_meta_codelist`

Association table (n:m) linking lithology to one or more metamorphic texture / fabric.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_texture_meta_codelist
Attribute Name	Description	Data Type	Nullable
lithology_id	Reference to the related lithology record.	integer	NO
texture_meta_id	Reference to the codelist defining the metamorphic textures / fabrics.	integer	NO

Foreign key relationships

Foreign keys of table lithology_texture_meta_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_id	lithology	id

Code lists

texture_meta_id20 values

Id Label (English)

Values of code list texture_meta_id
Id	Label (English)
100000466	massive
100000467	layered
100000468	foliated
100000469	phyllitic
100000470	schistose
100000471	banded
100000472	platy
100000473	augen-textured
100000474	lenticular
100000475	flaser-textured
100000476	streaky
100000477	migmatitic
100000478	granoblastic
100000479	lepidoblastic
100000480	nematoblastic
100000481	porphyroblastic
100000482	xenoblastic
100000483	idioblastic
100000484	other
100000485	not specified

#`lithology_uscs_type_codelist`

Association table (n:m) linking lithology to one or more USCS class.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table lithology_uscs_type_codelist
Attribute Name	Description	Data Type	Nullable
lithology_id	Reference to the related lithology record.	integer	NO
uscs_type_id	Reference to the codelist defining the USCS classes.	integer	NO

Foreign key relationships

Foreign keys of table lithology_uscs_type_codelist
Attribute Name	Foreign Table	Foreign Column
lithology_id	lithology	id

Code lists

uscs_type_id29 values · in annex

This codelist contains 29 values. Full values are documented in Annex — uscs_type.

Completion and installation

What was built into the borehole after drilling: casings, backfill and sealing, and instrumentation.

5 tables

#`completion`

Stores the common information shared by casing, instrumentation, and sealing / backfilling records.

11 attributes / 3 foreign keys / 1 code lists

Entity-relationship diagram excerpt for the table completion, showing its columns and the referenced tables borehole, users. — ERD excerpt for `completion`. Select to enlarge.

Attributes

Attributes of table completion
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
is_primary	Whether or not this is the primary completion.	boolean	NO
name	Name of the completion.	text	YES
kind_id	Reference to a codelist regarding the type borehole architecture type.	integer	NO
notes	Additional comments or contextual remarks.	text	YES
abandon_date	Date when completed borehole has been abandoned.	date	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table completion
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id

Code lists

kind_id3 values

Id Label (English)

Values of code list kind_id
Id	Label (English)
16000000	no casing
16000001	telescopic
16000002	in parallel

#`casing`

Stores information about casing installations associated with borehole completion.

10 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table casing, showing its columns and the referenced tables completion, users. — ERD excerpt for `casing`. Select to enlarge.

Attributes

Attributes of table casing
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
completion_id	Reference to the related completion record.	integer	NO
name	Name of the casing.	text	NO
date_start	Start date of casing installation.	date	YES
date_finish	End date of casing installation.	date	YES
notes	Additional comments or contextual remarks regarding the casing.	text	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table casing
Attribute Name	Foreign Table	Foreign Column
completion_id	completion	id
creator	users	id
updater	users	id

#`casing_element`

Stores information regarding individual elements of the casing.

12 attributes / 3 foreign keys / 2 code lists

Entity-relationship diagram excerpt for the table casing_element, showing its columns and the referenced tables casing, users. — ERD excerpt for `casing_element`. Select to enlarge.

Attributes

Attributes of table casing_element
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
casing_id	Reference to the related casing record.	integer	NO
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
kind_id	Reference to the codelist defining the type of casing.	integer	NO
material_id	Reference to the codelist defining the material.	integer	YES
diameter_inner	Inner diameter of the casing element, in millimetres.	double precision	YES
diameter_outer	Outer diameter of casing element, in millimetres.	double precision	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table casing_element
Attribute Name	Foreign Table	Foreign Column
casing_id	casing	id
creator	users	id
updater	users	id

Code lists

kind_id12 values

Id Label (English)

Values of code list kind_id
Id	Label (English)
25000118	temporary protective casing
25000100	blank casing
25000101	screened casing
25000102	conductor pipe
30000014	liner hanger
25000121	liner
25000116	U-probe
25000119	U-probe with geotextile sock
25000117	double U-probe
25000120	double U-probe with geotextile sock
25000106	other
25000107	not specified

material_id8 values

Id Label (English)

Values of code list material_id
Id	Label (English)
25000108	PVC (polyvinyl chloride)
25000109	HDPE (high density polyethylene)
25000110	plastic (unspecified)
25000111	steel
25000112	stainless steel
25000113	concrete
25000115	not specified
25000114	other

#`backfill`

Stores information about borehole backfilling and sealing intervals, including the associated materials.

13 attributes / 4 foreign keys / 2 code lists

Entity-relationship diagram excerpt for the table backfill, showing its columns and the referenced tables completion, users, casing. — ERD excerpt for `backfill`. Select to enlarge.

Attributes

Attributes of table backfill
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
completion_id	Reference to the related completion record.	integer	NO
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	YES
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	YES
kind_id	Reference to the codelist defining the type of the sealing/backfilling.	integer	YES
material_id	Reference to the codelist defining the sealing/backfilling material.	integer	YES
notes	Additional comments or contextual remarks.	text	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
casing_id	Reference to the related casing record.	integer	YES
is_open_borehole	Indicates whether the borehole is cased. If true, casing_id references the associated casing; if false, the borehole is open.	boolean	NO

Foreign key relationships

Foreign keys of table backfill
Attribute Name	Foreign Table	Foreign Column
completion_id	completion	id
creator	users	id
updater	users	id
casing_id	casing	id

Code lists

kind_id5 values

Id Label (English)

Values of code list kind_id
Id	Label (English)
25000300	borehole plugging
25000301	annular sealing
25000302	casing plugging
25000303	other
25000304	not specified

material_id10 values

Id Label (English)

Values of code list material_id
Id	Label (English)
25000305	cuttings
25000306	filter gravel
25000307	filter sand
25000308	cement
25000314	cement-bentonite grout
25000309	clay pellets
25000313	resin
25000310	packer
25000311	other
25000312	not specified

#`instrumentation`

Stores monitoring and measurement instrumentation installed in boreholes.

14 attributes / 4 foreign keys / 2 code lists

Entity-relationship diagram excerpt for the table instrumentation, showing its columns and the referenced tables completion, users, casing. — ERD excerpt for `instrumentation`. Select to enlarge.

Attributes

Attributes of table instrumentation
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
completion_id	Reference to the related completion record.	integer	NO
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	YES
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	YES
name	Instrumentation device name.	text	YES
kind_id	Reference to the codelist defining the type of instrumentation.	integer	YES
status_id	Reference to the codelist defining the status of instrumentation.	integer	YES
notes	Additional comments or contextual remarks.	text	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
casing_id	Reference to the related casing record.	integer	YES
is_open_borehole	Indicates whether the borehole is cased. If true, casing_id references the associated casing; if false, the borehole is open.	boolean	NO

Foreign key relationships

Foreign keys of table instrumentation
Attribute Name	Foreign Table	Foreign Column
completion_id	completion	id
creator	users	id
updater	users	id
casing_id	casing	id

Code lists

kind_id13 values

Id Label (English)

Values of code list kind_id
Id	Label (English)
25000200	pump unspecified
25000201	submersible pump
25000202	suction pump
25000203	pressure sensor
25000204	pore water pressure sensor (filter cartridge)
25000205	pressure gauge
25000206	temperature sensor
25000207	pH probe
25000208	conductivity probe
25000209	oxygen probe
25000210	redox probe
25000211	other
25000212	not specified

status_id5 values

Id Label (English)

Values of code list status_id
Id	Label (English)
25000213	active
25000214	inactive
25000215	removed
25000216	other
25000217	not specified

Hydrogeology and field tests

Observations and measurements made in the borehole, from water ingress during drilling to hydraulic tests.

10 tables

#`observation`

Stores common information about hydrogeological measurements. Measurement-specific attributes are stored separately in dedicated tables.

19 attributes / 4 foreign keys / 3 code lists

Entity-relationship diagram excerpt for the table observation, showing its columns and the referenced tables borehole, users, casing. — ERD excerpt for `observation`. Select to enlarge.

Attributes

Attributes of table observation
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
observation_type	Application-internal code list defining the type of hydrogeological measurement.	integer	NO
time_start	Observation start time	timestamp with time zone	YES
time_end	Observation end time	timestamp with time zone	YES
duration	Observation duration time	double precision	YES
depth_from_m	Measured depth below the borehole reference point at which the interval begins (meter).	double precision	YES
depth_to_m	Measured depth below the borehole reference point at which the interval ends (meter).	double precision	YES
depth_from_masl	Measured absolute depth at which the interval begins (meter above sea level).	double precision	YES
depth_to_masl	Measured absolute depth at which the interval ends (meter above sea level).	double precision	YES
comment	Additional descriptive or contextual remarks.	text	YES
reliability	Reference to the codelist defining the reliability of the observation.	integer	YES
borehole_id	Reference to the related borehole record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
casing_id	Reference to the related casing record.	integer	YES
is_open_borehole	Indicates whether the borehole is cased. If true , casing_id references the associated casing; if false , the borehole is open.	boolean	NO
original_vertical_reference_system	Reference to the application-internal code list defining the source reference system used for transforming the values to the other reference system.	integer	NO

Foreign key relationships

Foreign keys of table observation
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id
casing_id	casing	id

Code lists

observation_type4 values

Application-internal code list

1: Water ingress
2: Groundwater measurement
3: Hydrotest
4: Field measurements

reliability4 values

Id Label (English)

Values of code list reliability
Id	Label (English)
15203156	reliable
15203157	uncertain
15203158	other
15203159	not specified

original_vertical_reference_system2 values

Application-internal code list

1: measured depth
2: meters above sea level

#`water_ingress`

Stores water ingress measurements associated with an observation.

3 attributes / 1 foreign keys / 2 code lists

Attributes

Attributes of table water_ingress
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
quantity	Reference to the codelist defining the water ingress quantity.	integer	NO
conditions	Reference to the codelist defining the water ingress conditions.	integer	YES

Foreign key relationships

Foreign keys of table water_ingress
Attribute Name	Foreign Table	Foreign Column
id	observation	id

Code lists

quantity5 values

Id Label (English)

Values of code list quantity
Id	Label (English)
15203160	small (< 30 l/min)
15203161	medium (30 - 120 l/min)
15203162	large (> 120 l/min)
15203163	other
15203164	not specified

conditions5 values

Id Label (English)

Values of code list conditions
Id	Label (English)
15203165	free flowing artesian
15203166	confined
15203167	unconfined
15203168	other
15203169	not specified

#`groundwater_level_measurement`

Stores groundwater level measurements associated with an observation.

4 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table groundwater_level_measurement
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
kind	Reference to the codelist of the methode of groundwater measurement.	integer	NO
level_m	Measured depth of encountered groundwater table (meter from the borehole reference point).	double precision	YES
level_masl	Measured altitude of encountered groundwater table (meter above sea level).	double precision	YES

Foreign key relationships

Foreign keys of table groundwater_level_measurement
Attribute Name	Foreign Table	Foreign Column
id	observation	id

Code lists

kind6 values

Id Label (English)

Values of code list kind
Id	Label (English)
15203203	light plummet
15203204	pressure probe
15203205	pressure gauge
15203206	sound
15203207	other
15203208	not specified

#`hydrotest`

Reference table linking hydrotest_result and observation. This table exists to support the mapping of the .NET object model to the database schema.

1 attributes / 1 foreign keys

Entity-relationship diagram excerpt for the table hydrotest, showing its columns and the referenced tables observation. — ERD excerpt for `hydrotest`. Select to enlarge.

Attributes

Attributes of table hydrotest
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO

Foreign key relationships

Foreign keys of table hydrotest
Attribute Name	Foreign Table	Foreign Column
id	observation	id

#`hydrotest_result`

Stores the results of a hydrotest. Valid combinations of hydrotest evaluation methods, flow directions, and hydrotest types are enforced in the application frontend and determine which type of parameter can be selected.

10 attributes / 3 foreign keys / 1 code lists

Entity-relationship diagram excerpt for the table hydrotest_result, showing its columns and the referenced tables hydrotest, users. — ERD excerpt for `hydrotest_result`. Select to enlarge.

Attributes

Attributes of table hydrotest_result
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
parameter	Reference to the codelist defining the type of parameter.	integer	NO
value	Actual measurement value.	double precision	YES
value_max	Maximum value of the measurement range.	double precision	YES
value_min	Minimal value of the measurement range.	double precision	YES
hydrotest_id	Reference to the related hydrotest record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table hydrotest_result
Attribute Name	Foreign Table	Foreign Column
hydrotest_id	hydrotest	id
creator	users	id
updater	users	id

Code lists

parameter10 values

Id Label (English)

Values of code list parameter
Id	Label (English)
15203194	kf value (saturated)
15203195	kf,u value (unsaturated)
15203196	transmissivity
15203197	flow dimension
15203198	static formation pressure
15203199	specific storage coefficient
15203200	Lugeon value
15203201	relevant thickness
15203202	other
15203223	not specified

#`hydrotest_evaluationmethod_codelist`

Association table (n:m) linking hydrotest_result to one or more hydrotest evaluation methods.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table hydrotest_evaluationmethod_codelist
Attribute Name	Description	Data Type	Nullable
hydrotest_id	Reference to the related hydrotest record.	integer	NO
codelist_id	Reference to the codelist defining the evaluation methode.	integer	NO

Foreign key relationships

Foreign keys of table hydrotest_evaluationmethod_codelist
Attribute Name	Foreign Table	Foreign Column
hydrotest_id	hydrotest	id

Code lists

codelist_id5 values

Id Label (English)

Values of code list codelist_id
Id	Label (English)
15203189	stationary
15203190	transient
15203191	numerical
15203192	other
15203193	not specified

#`hydrotest_flowdirection_codelist`

Association table (n:m) linking hydrotest_result to one or more flow directions.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table hydrotest_flowdirection_codelist
Attribute Name	Description	Data Type	Nullable
hydrotest_id	Reference to the related hydrotest record.	integer	NO
codelist_id	Reference to the codelist defining the flow direction.	integer	NO

Foreign key relationships

Foreign keys of table hydrotest_flowdirection_codelist
Attribute Name	Foreign Table	Foreign Column
hydrotest_id	hydrotest	id

Code lists

codelist_id3 values

Id Label (English)

Values of code list codelist_id
Id	Label (English)
15203186	injection
15203187	withdrawal
15203188	not specified

#`hydrotest_kind_codelist`

Association table (n:m) linking hydrotest_result to one or more hydrotest type.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table hydrotest_kind_codelist
Attribute Name	Description	Data Type	Nullable
hydrotest_id	Reference to the related hydrotest record.	integer	NO
codelist_id	Reference to a codelist defining the kind of hydrotest.	integer	NO

Foreign key relationships

Foreign keys of table hydrotest_kind_codelist
Attribute Name	Foreign Table	Foreign Column
hydrotest_id	hydrotest	id

Code lists

codelist_id12 values

Id Label (English)

Values of code list codelist_id
Id	Label (English)
15203170	pumping/injection test, constant rate
15203171	pumping/injection test, step drawdown test
15203172	pumping/injection test, variable rate
15203173	pumping/injection test, constant pressure head
15203174	abrupt change of pressure head (slug/bail, pulse)
15203175	pressure recovery in open borehole/rod/piezometer
15203176	pressure recovery in closed interval
15203177	packer test (Drillstem, Lugeon)
15203178	flowmeter/fluid logging test
15203179	infiltration test, unsaturated zone
15203184	other
15203185	not specified

#`field_measurement`

Reference table linking fieldmeasurement_result and observation. This table exists to support the mapping of the .NET object model to the database schema.

1 attributes / 1 foreign keys

Entity-relationship diagram excerpt for the table field_measurement, showing its columns and the referenced tables observation. — ERD excerpt for `field_measurement`. Select to enlarge.

Attributes

Attributes of table field_measurement
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO

Foreign key relationships

Foreign keys of table field_measurement
Attribute Name	Foreign Table	Foreign Column
id	observation	id

#`fieldmeasurement_result`

Stores field measurement results.

9 attributes / 3 foreign keys / 2 code lists

Entity-relationship diagram excerpt for the table fieldmeasurement_result, showing its columns and the referenced tables field_measurement, users. — ERD excerpt for `fieldmeasurement_result`. Select to enlarge.

Attributes

Attributes of table fieldmeasurement_result
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
sample_type	Reference to the codelist defining the sampling type.	integer	NO
parameter	Reference to the codelist defining the measurment parameter.	integer	NO
value	Field measurement value.	double precision	NO
fieldmeasurement_id	Reference to the related field measurement record.	integer	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table fieldmeasurement_result
Attribute Name	Foreign Table	Foreign Column
fieldmeasurement_id	field_measurement	id
creator	users	id
updater	users	id

Code lists

sample_type5 values

Id Label (English)

Values of code list sample_type
Id	Label (English)
15203209	pumped
15203210	scooped
15203211	in the borehole (in situ)
15203212	other
15203213	not specified

parameter9 values

Id Label (English)

Values of code list parameter
Id	Label (English)
15203214	temperature [°C]
15203215	pH value
15203216	electrical conductivity (ref. 20°C) [µS/cm]
15203217	electrical conductivity (ref. 25°C) [µS/cm]
15203218	redox potential [mV]
15203219	oxygen saturation [%]
15203220	dissolved oxygen [mg/L]
15203221	other
15203222	not specified

Logging

Geophysical logging: runs, the resulting log files, and the tool types used.

3 tables

#`log_run`

Stores information regarding the log runs.

15 attributes / 3 foreign keys / 2 code lists

Entity-relationship diagram excerpt for the table log_run, showing its columns and the referenced tables borehole, users. — ERD excerpt for `log_run`. Select to enlarge.

Attributes

Attributes of table log_run
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
run_number	Sequential number of the logging run.	text	NO
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
run_date	Date of the associated log run measurement.	date	YES
comment	Additional descriptive or contextual remarks.	text	YES
service_co	Name of the service company conducting the measurement.	text	YES
bit_size	Diameter of the borehole at the time of the measurement.	double precision	YES
conveyance_method_id	Reference to the codelist defining the conveyance method.	integer	YES
borehole_status_id	Reference to the codelist defining the borehole status.	integer	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table log_run
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id

Code lists

conveyance_method_id5 values

Id Label (English)

Values of code list conveyance_method_id
Id	Label (English)
100002999	WL
100003000	LWD
100003001	PCL
100003002	other
100003003	not specified

borehole_status_id5 values

Id Label (English)

Values of code list borehole_status_id
Id	Label (English)
100003004	OH
100003005	CH
100003006	OH & CH
100003007	other
100003008	not specified

#`log_file`

Stores information about files containing petrophysical measurements.

14 attributes / 3 foreign keys / 3 code lists

Entity-relationship diagram excerpt for the table log_file, showing its columns and the referenced tables log_run, users. — ERD excerpt for `log_file`. Select to enlarge.

Attributes

Attributes of table log_file
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
log_run_id	Reference to the related log run record.	integer	NO
name	Individual name of the log file.	text	NO
name_uuid	Automatically created UUID of the log file.	text	NO
pass_type_id	Reference to the codelist defining the pass type.	integer	YES
pass	Sequential number of the acquisition pass within the logging run.	integer	YES
data_package_id	Reference to the codelist defining the data package.	integer	YES
delivery_date	Date of delivery.	date	YES
depth_type_id	Reference to the codelist defining the depth type.	integer	YES
public	Indicates whether the information is publicly accessible.	boolean	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table log_file
Attribute Name	Foreign Table	Foreign Column
log_run_id	log_run	id
creator	users	id
updater	users	id

Code lists

pass_type_id7 values

Id Label (English)

Values of code list pass_type_id
Id	Label (English)
100003020	Main
100003021	Repeat
100003022	Main & repeat
100003023	Downlog
100003024	Stationary
100003025	other
100003026	not specified

data_package_id11 values

Id Label (English)

Values of code list data_package_id
Id	Label (English)
100003009	Rush data (WL)
100003010	Field data (WL)
100003011	Final data (WL)
100003012	Real-Time data (LWD)
100003013	Memory data (LWD)
100003014	Processed data
100003015	Interpreted data
100003016	Composite
100003017	Report
100003018	other
100003019	not specified

depth_type_id5 values

Id Label (English)

Values of code list depth_type_id
Id	Label (English)
100003027	MD
100003028	TVD
100003029	MD & TVD
100003030	other
100003031	not specified

#`log_file_tool_type_codelist`

Association table (n:m) linking log_file to one or more tool type.

2 attributes / 1 foreign keys / 1 code lists

Attributes

Attributes of table log_file_tool_type_codelist
Attribute Name	Description	Data Type	Nullable
logfile_id	Reference to the related log file record.	integer	NO
codelist_id	Reference to the codelist defining the tool types	integer	NO

Foreign key relationships

Foreign keys of table log_file_tool_type_codelist
Attribute Name	Foreign Table	Foreign Column
logfile_id	log_file	id

Code lists

codelist_id (log_tool_type)26 values · in annex

This codelist contains 26 values. Full values are documented in Annex — log_tool_type.

Profiles, photos and documents

Content attached to a borehole: geological profiles, photographs, and reports and other documents.

3 tables

#`profile`

Stores geological profiles associated with a borehole.

12 attributes / 3 foreign keys

ERD excerpt for profile. Select to enlarge.

Attributes

Attributes of table profile
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the profile.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
name	Individula name of the profile.	text	NO
name_uuid	Automatically created UUID of the profile.	text	NO
type	Multipurpose Internet Mail Extensions (MIME) type of the profile (e.g. application/pdf or image/tiff).	text	NO
description	Description of the profile.	text	YES
public	Indicates whether the profile is publicly accessible.	boolean	YES
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES
ocr_status	Indicates whether OCR was successfully applied	integer	NO

Foreign key relationships

Foreign keys of table profile
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id

#`photo`

Stores information about drill core and cutting photographs.

12 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table photo, showing its columns and the referenced tables borehole, users. — ERD excerpt for `photo`. Select to enlarge.

Attributes

Attributes of table photo
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
name	Individual name of the photo.	text	NO
name_uuid	Automatically created UUID of the photo.	text	NO
file_type	File type of the photo.	text	NO
depth_from	Measured depth below the borehole reference point at which the interval begins.	double precision	NO
depth_to	Measured depth below the borehole reference point at which the interval ends.	double precision	NO
public	Indicates whether the information is publicly accessible.	boolean	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table photo
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id

#`document`

Stores references (URLs) to documents related to the borehole.

9 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table document, showing its columns and the referenced tables borehole, users. — ERD excerpt for `document`. Select to enlarge.

Attributes

Attributes of table document
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
url	URL referencing the digital document.	text	NO
description	Description of the referenced document.	text	YES
public	Indicates whether the information is publicly accessible.	boolean	NO
creator	User who created the record.	integer	YES
creation	Date and time when the record was created.	timestamp with time zone	YES
updater	User who last updated the record.	integer	YES
update	Date and time of the last update.	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table document
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
creator	users	id
updater	users	id

Code list infrastructure

The generic table that holds the controlled values referenced throughout the model.

1 table

#`codelist`

Stores multilingual codelist entries and geological classification codes.

13 attributes

Attributes

Attributes of table codelist
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
geolcode	swisstopo internal geolcode.	integer	YES
schema	Domain of codelist value.	character varying	YES
code	Abbreviation of the value, where applicable.	character varying	NO
text_en	Codelist value description in English.	character varying	NO
text_de	Codelist value description in German.	character varying	YES
text_fr	Codelist value description in French.	character varying	YES
text_it	Codelist value description in Italian.	character varying	YES
text_ro	Codelist value description in Rhaeto-Romanic.	character varying	YES
order	Internal index order of codelist values belonging to the same domain resp. schema.	integer	YES
conf	Colour and/or pattern definition used for visualisation, specified either as an RGB array (e.g. {color:[180;180;180]}) or as the path to an SVG file (e.g. {"image":"15101001.svg"}). Multiple style definitions can be combined using commas.	json	YES
default	Legacy field retained from previous versions; always set to false.	boolean	YES
path	Hierarchical path within the same domain or schema, represented as a dot-separated list of IDs (e.g. 15300623.15300625.15300304).	USER-DEFINED	YES

Users, workgroups and workflow

Application tables governing who may see and edit which data, the publication workflow, and the execution log of automated maintenance tasks.

10 tables

#`users`

Stores user account information.

10 attributes

Entity-relationship diagram excerpt for the table users. — ERD excerpt for `users`. Select to enlarge.

Attributes

Attributes of table users
Attribute Name	Description	Data Type	Nullable
id	User identifier.	integer	NO
created	Date and time when user has been created.	timestamp with time zone	YES
admin	Whether the user holds administrator privileges.	boolean	YES
username	User name as displayed in the application.	character varying	NO
settings	Application specific configuration information in JSON format.	character varying	YES
firstname	User's first name.	character varying	YES
lastname	User's last name.	character varying	YES
disabled	Indicates when a user has been disabled.	timestamp with time zone	YES
subject_id	Technical identifier issued by the Cognito authentication provider, representing the unique identifier of the user.	text	NO
email	User's email adress	text	NO

#`users_roles`

Stores role assignments for users within specific workgroups by linking users, roles, and workgroups.

3 attributes / 3 foreign keys

Entity-relationship diagram excerpt for the table users_roles, showing its columns and the referenced tables users, roles, workgroups. — ERD excerpt for `users_roles`. Select to enlarge.

Attributes

Attributes of table users_roles
Attribute Name	Description	Data Type	Nullable
user_id	Reference to the related users record.	integer	NO
role_id	Reference to the related roles record.	integer	NO
workgroup_id	Reference to the related workgroups record.	integer	NO

Foreign key relationships

Foreign keys of table users_roles
Attribute Name	Foreign Table	Foreign Column
user_id	users	id
role_id	roles	id
workgroup_id	workgroups	id

#`roles`

Stores the roles that can be assigned to users.

3 attributes

Attributes

Attributes of table roles
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
name	Role name	character varying	NO
config	Technical configuration information	json	YES

#`workgroups`

Stores information about application internal workgroups.

5 attributes

Attributes

Attributes of table workgroups
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
creation	Date and time when the record was created.	timestamp with time zone	YES
disabled	Indicates when a workgroup has been disabled.	timestamp with time zone	YES
name	Workgroup name.	character varying	NO
settings	Application internal settings and configuration regarding the workgroup.	json	YES

#`workflow`

Stores informations about the review and publication status of borehole records.

7 attributes / 4 foreign keys / 1 code lists

Entity-relationship diagram excerpt for the table workflow, showing its columns and the referenced tables borehole, tab_status, users. — ERD excerpt for `workflow`. Select to enlarge.

Attributes

Attributes of table workflow
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
has_requested_changes	Whether changes to the borehole record have been requested.	boolean	NO
status	Application-internal code list defining the available workflow statuses: draft, review, reviewed, and published.	integer	NO
borehole_id	Reference to the related borehole record.	integer	NO
reviewed_tabs_id	Reference to the related tab status record indicating the reviewed status.	integer	NO
published_tabs_id	Reference to the related tab status record indicating the publication status.	integer	NO
assignee_id	Reference to the related users record.	integer	YES

Foreign key relationships

Foreign keys of table workflow
Attribute Name	Foreign Table	Foreign Column
borehole_id	borehole	id
reviewed_tabs_id	tab_status	tab_status_id
published_tabs_id	tab_status	tab_status_id
assignee_id	users	id

Code lists

status4 values

0: draft
1: review
2: reviewed
3: published

#`workflow_change`

Stores information about the record lifecycle, including status, review process, and approval/publication history.

8 attributes / 3 foreign keys

Attributes

Attributes of table workflow_change
Attribute Name	Description	Data Type	Nullable
workflow_change_id	Identifier of the workflow change record.	integer	NO
comment	Additional comments or contextual remarks.	text	NO
status_from	Previous status.	integer	NO
status_to	New assigned status.	integer	NO
workflow_id	Reference to the related workflow record.	integer	NO
created_by_id	Reference to the related users record.	integer	YES
created_at	Date and time when workflow has been changed.	timestamp with time zone	YES
assignee_id	Reference to the related users record.	integer	YES

Foreign key relationships

Foreign keys of table workflow_change
Attribute Name	Foreign Table	Foreign Column
workflow_id	workflow	id
created_by_id	users	id
assignee_id	users	id

#`tab_status`

Stores information about the borehole processing workflow related to review and approval/publication status.

20 attributes

Attributes

Attributes of table tab_status
Attribute Name	Description	Data Type	Nullable
tab_status_id	Identifier of tab status record.	integer	NO
location	Indicates the status of the location tab.	boolean	NO
identifiers	Indicates the status of the identifier tab.	boolean	NO
general	Indicates the status of the general subtab of the borehole tab.	boolean	NO
section	Indicates the status of the section subtab of the borehole tab.	boolean	NO
geometry	Indicates the status of the borehole geometry subtab of the borehole tab.	boolean	NO
lithology	Indicates the status of the lithology subtab of the stratigraphy tab.	boolean	NO
lithostratigraphy	Indicates the status of the lithostratigraphy subtab of the stratigraphy tab.	boolean	NO
chronostratigraphy	Indicates the status of the chronostratigraphy subtab of the stratigraphy tab.	boolean	NO
casing	Indicates the status of the casing subtab of the borehole architecture tab.	boolean	NO
instrumentation	Indicates the status of the instrumentation subtab of the borehole architecture tab.	boolean	NO
backfill	Indicates the status of the sealing/backfilling subtab of the borehole architecture tab.	boolean	NO
water_ingress	Indicates the status of the water ingress subtab of the hydrogeology tab.	boolean	NO
groundwater	Indicates the status of the groundwater measurement subtab of the hydrogeology tab.	boolean	NO
field_measurement	Indicates the status of the field measurement subtab of the hydrogeology tab.	boolean	NO
hydrotest	Indicates the status of the hydrotest subtab of the hydrogeology tab.	boolean	NO
log	Indicates the status of the log runs and log files.	boolean	NO
profile	Indicates the status of the profile subtab of the attachments tab.	boolean	NO
photo	Indicates the status of the photo subtab of the attachments tab.	boolean	NO
document	Indicates the status of the document subtab of the attachments tab.	boolean	NO

#`terms`

Stores custom terms used by the application.

9 attributes

Attributes

Attributes of table terms
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the record.	integer	NO
draft	Whether the terms are in draft status.	boolean	NO
text_en	Custom terms in English.	character varying	NO
text_de	Custom terms in German.	character varying	YES
text_fr	Custom terms in French.	character varying	YES
text_it	Custom terms in Italian.	character varying	YES
text_ro	Custom terms in Rhaeto-Romanic.	character varying	YES
creation	Date and time when the record was created.	timestamp with time zone	NO
expired	Date and time of expiration of legal terms	timestamp with time zone	YES

#`terms_accepted`

Stores information about the acceptance of the legal terms by individual users.

3 attributes / 2 foreign keys

Attributes

Attributes of table terms_accepted
Attribute Name	Description	Data Type	Nullable
user_id	Reference to the related users record.	integer	NO
term_id	Reference to the related terms record.	integer	NO
accepted	Date and time of acceptance of legal terms	timestamp with time zone	YES

Foreign key relationships

Foreign keys of table terms_accepted
Attribute Name	Foreign Table	Foreign Column
user_id	users	id
term_id	terms	id

#`maintenance_task_log`

Stores execution logs of automated maintenance tasks.

11 attributes / 1 foreign keys

Attributes

Attributes of table maintenance_task_log
Attribute Name	Description	Data Type	Nullable
id	Unique identifier of the maintenance task execution.	integer	NO
task_type	Type of maintenance task.	integer	NO
status	Execution status of the maintenance task.	integer	NO
affected_count	Number of records affected by the maintenance task.	integer	YES
message	Stores the error message if the task fails.	text	YES
parameters	JSON object containing task execution parameters.	jsonb	YES
is_dry_run	Indicates whether the task was executed as a simulation only.	boolean	NO
only_missing	Indicates whether maintenance task was limited to missing records.	boolean	NO
started_by_id	User who started the maintenance task.	integer	YES
started_at	Date and time when execution started.	timestamp with time zone	NO
completed_at	Date and time when execution completed.	timestamp with time zone	NO

Foreign key relationships

Foreign keys of table maintenance_task_log
Attribute Name	Foreign Table	Foreign Column
started_by_id	users	id

Annex — code lists

Code lists with many values, referenced from the attribute descriptions above. Each list can be filtered.

#`drilling_purpose`24 values

Referenced by borehole.purpose_id

Values of code list drilling_purpose
Id	Code	Label (English)
30000003	CCSExp	CCS exploration
30000004	CCSInj	CCS injection
30000005	CCSPro	CCS production
22103003	belS	contaminated sites
30000002	DHE	downhole heat exchanger
22103001	Gtec	geotechnics
22103002	GtheExp	geothermal exploration
30000000	GtheInj	geothermal injection
30000001	GthePro	geothermal production
30000007	Gw	groundwater
30000009	Hot	hotspring
22103007	HCExp	hydrocarbon exploration
30000010	HCInj	hydrocarbon injection
30000011	HCPro	hydrocarbon production
22103004	Hyd	hydrology
22103006	Roh	mineral resources
22103005	Min	mineral water
22103008	Nat	natural hazards
22103011	kA	not specified
22103010	a	other
30000008	Pw	potable water
22103009	F	research
30000012	SciExp	scientific exploration
30000006	TunExp	tunnel exploration

#`lithostratigraphy`

Referenced by borehole.lithostratigraphy_top_bedrock_id, lithostratigraphy.lithostratigraphy_id

The list of lithostratigraphy concepts is part of https://lexic.swissgeol.ch/

#`chronostratigraphy`

Referenced by borehole.chronostratigraphy_top_bedrock_id, chronostratigraphy.chronostratigraphy_id

The list of chronostratigraphy concepts is part of https://lexic.swissgeol.ch/

#`facies_con`154 values

Referenced by facies_description.facies_id

Values of code list facies_con
Id	Code	Label (English)
100001168		aeolian
100001176		aeolian dune field margins
100001169		aeolian dunes
100001170		aeolian dunes, barchan
100001171		aeolian dunes, ridge
100001173		aeolian dunes, slipface
100001172		aeolian dunes, toe
100001177		aeolian fans
100001175		aeolian flat
100001174		aeolian interdune
100001178		aeolian sheet sands
100001144		alluvial
100001154		alluvial braidplain
100001149		alluvial channel
100001153		alluvial channel, anastomosed
100001150		alluvial channel, braided
100001152		alluvial channel, multi-storey meandering
100001151		alluvial channel, single-storey meandering
100001145		alluvial fan
100001147		alluvial fan, arid
100001148		alluvial fan, delta
100001146		alluvial fan, humid
100001155		alluvial floodplain
100001156		alluvial floodplain, crevasse-splays
100001158		alluvial floodplain, fines
100001157		alluvial floodplain, paleosol
100001279		deep marine
100001294		deep marine, autochthonous
100001285		deep marine, channel/levee complex
100001287		deep marine, fan
100001288		deep marine, fan basin floor
100001291		deep marine, fan debris flow
100001292		deep marine, fan debris slump
100001290		deep marine, fan slope
100001289		deep marine, fan toe of slope
100001281		deep marine, hemipelagic
100001280		deep marine, pelagic
100001293		deep marine, slump
100001286		deep marine, submarine canyon
100001283		deep marine, thick-bedded turbidite
100001284		deep marine, thin-bedded turbidite
100001282		deep marine, turbidite
100001189		delta
100001190		deltaic river-dominated
100001201		deltaic river-dominated, abandoned distributary channel
100001200		deltaic river-dominated, active distributary channel
100001195		deltaic river-dominated, delta front
100001205		deltaic river-dominated, delta plain
100001194		deltaic river-dominated, distal prodelta
100001199		deltaic river-dominated, distributary channel
100001202		deltaic river-dominated, interdistributary bay
100001204		deltaic river-dominated, interdistributary bay, crevasse splay
100001203		deltaic river-dominated, interdistributary bay, fines
100001198		deltaic river-dominated, lower mouth-bar
100001196		deltaic river-dominated, mouth-bar
100001191		deltaic river-dominated, offshore
100001192		deltaic river-dominated, prodelta
100001193		deltaic river-dominated, proximal prodelta
100001197		deltaic river-dominated, upper mouth-bar
100001227		deltaic shelf edge
100001218		deltaic tide-dominated
100001221		deltaic tide-dominated, delta front
100001219		deltaic tide-dominated, offshore
100001220		deltaic tide-dominated, prodelta
100001226		deltaic tide-dominated, salt marsh
100001225		deltaic tide-dominated, supratidal flats
100001224		deltaic tide-dominated, tidal channel
100001223		deltaic tide-dominated, tidal flat
100001222		deltaic tide-dominated, tidal ridge
100001206		deltaic wave-dominated
100001212		deltaic wave-dominated, backshore/dunes
100001213		deltaic wave-dominated, barrier
100001211		deltaic wave-dominated, beach/foreshore
100001217		deltaic wave-dominated, coastal plain
100001214		deltaic wave-dominated, lagoon
100001215		deltaic wave-dominated, lagoon, fines
100001216		deltaic wave-dominated, lagoon, washover
100001208		deltaic wave-dominated, lower shoreface
100001209		deltaic wave-dominated, middle shoreface
100001207		deltaic wave-dominated, offshore
100001210		deltaic wave-dominated, upper shoreface
100001165		ephemeral-lacustrine
100001166		ephemeral-lacustrine, fines
100001167		ephemeral-lacustrine, sheet
100001179		fluvial-aeolian
100001181		fluvial-aeolian, distributary channel
100001185		fluvial-aeolian, dune field margins
100001186		fluvial-aeolian, fans
100001182		fluvial-aeolian, fines
100001184		fluvial-aeolian, flat
100001183		fluvial-aeolian, interdune
100001180		fluvial-aeolian, sheet
100001187		fluvial-aeolian, sheet sands
100001188		fluvio-glacial
100001160		fluvio-lacustrine
100001163		fluvio-lacustrine, distributary channel
100001162		fluvio-lacustrine, mouth-bar
100001161		fluvio-lacustrine, sheet
100001164		fluvio-lacustrine, turbidite
100001159		lacustrine
100001251		marginal marine
100001253		marginal marine, estuary
100001255		marginal marine, estuary, bay-head delta
100001256		marginal marine, estuary, central basin
100001254		marginal marine, estuary, fluvial
100001257		marginal marine, estuary, sand plug
100001258		marginal marine, estuary, tidal
100001259		marginal marine, incised valley fill
100001252		marginal marine, lagoon
100001231		marine
100001232		marine platform
100001239		marine platform, bank
100001240		marine platform, basin
100001236		marine platform, inner shelf
100001237		marine platform, outer shelf
100001235		marine platform, ramp
100001233		marine platform, rimmed
100001238		marine platform, shelf
100001234		marine platform, unrimmed
100001243		marine, back reef
100001247		marine, fore reef
100001250		marine, lower slope
100001241		marine, peritidal
100001245		marine, reef crest
100001244		marine, reef flat
100001246		marine, reef front
100001242		marine, reefs/mounds
100001248		marine, slope
100001249		marine, upper slope
100001296		not specified
100001295		other
100001229		playa
100001228		sabka
100001230		salina
100001260		shallow marine
100001272		shallow marine, barrier
100001274		shallow marine, flood/ebb tidal delta
100001271		shallow marine, foreshore/beach
100001276		shallow marine, lag deposit
100001266		shallow marine, lower shoreface
100001268		shallow marine, lower shoreface, gradationally based
100001267		shallow marine, lower shoreface, sharp-based
100001269		shallow marine, middle shoreface
100001261		shallow marine, offshore
100001263		shallow marine, offshore, inner shelf
100001262		shallow marine, offshore, outer shelf
100001264		shallow marine, offshore, tidal shelf ridge
100001278		shallow marine, regressive lag deposit
100001265		shallow marine, shoreface
100001275		shallow marine, tidal channel
100001273		shallow marine, tidal inlet
100001277		shallow marine, transgressive lag deposit
100001270		shallow marine, upper shoreface
100001143		terrestrial

#`color`91 values

Referenced by lithology_description.color_primary_id, lithology_description.color_secondary_id

Values of code list color
Id	Code	Label (English)
100000091		beige
100000080		beige grey
100000131		beige yellow
100000074		black
100000106		blackish brown
100000075		blackish grey
100000147		blue
100000142		bluish green
100000085		bluish grey
100000118		bluish red
100000104		brown
100000081		brownish grey
100000123		brownish orange
100000116		brownish red
100000133		brownish yellow
100000090		dark beige
100000146		dark blue
100000103		dark brown
100000136		dark green
100000076		dark grey
100000156		dark magenta
100000099		dark ochre
100000143		dark olive
100000120		dark orange
100000159		dark pink
100000113		dark red
100000151		dark violet
100000127		dark yellow
100000137		green
100000097		greenish beige
100000150		greenish blue
100000111		greenish brown
100000084		greenish grey
100000155		greenish violet
100000077		grey
100000093		greyish beige
100000149		greyish blue
100000107		greyish brown
100000139		greyish green
100000162		greyish pink
100000154		greyish violet
100000130		greyish yellow
100000092		light beige
100000148		light blue
100000105		light brown
100000138		light green
100000078		light grey
100000158		light magenta
100000101		light ochre
100000145		light olive
100000122		light orange
100000161		light pink
100000115		light red
100000153		light violet
100000129		light yellow
100000157		magenta
100000164		not specified
100000100		ochre
100000094		ochre beige
100000108		ochre brown
100000132		ochre yellow
100000144		olive
100000112		olive brown
100000141		olive green
100000135		olive yellow
100000121		orange
100000095		orange beige
100000163		other
100000160		pink
100000098		pinkish beige
100000086		pinkish grey
100000126		pinkish orange
100000119		pinkish red
100000089		pinkish white
100000114		red
100000109		reddish brown
100000082		reddish grey
100000102		reddish ochre
100000124		reddish orange
100000134		reddish yellow
100000152		violet
100000087		white
100000079		whitish grey
100000128		yellow
100000096		yellowish beige
100000110		yellowish brown
100000140		yellowish green
100000083		yellowish grey
100000125		yellowish orange
100000117		yellowish red
100000088		yellowish white

#`lithology_uncon_main`24 values

Referenced by lithology_description.lithology_uncon_main_id

Values of code list lithology_uncon_main
Id	Code	Label (English)
100000032	An	anthropogenic soil
100000033	Ba	backfill
100000013	Bo	boulder
100000028	Cl	clay
100000016	CGr	coarse gravel
100000022	CSa	coarse sand
100000014	Co	cobbles
100000020	FGr	fine gravel
100000026	FSa	fine sand
100000019	FMGr	fine-medium gravel
100000025	FMSa	fine-medium sand
100000015	Gr	gravel
100000031	Hu	humus
100000012	LBo	large boulder
100000018	MGr	medium gravel
100000024	MSa	medium sand
100000017	MCGr	medium-coarse gravel
100000023	MCSa	medium-coarse sand
100000035	ns	not specified
100000030	Or	organic soil
100000034	oth	other
100000029	Pt	peat
100000021	Sa	sand
100000027	Si	silt

#`lithology_con`

Referenced by lithology_description.lithology_con_id

The list of lithology concepts for consolidated rocks is part of https://lexic.swissgeol.ch/

#`component_con_mineral`111 values

Referenced by lithology_description_component_con_mineral_codelist.component_con_mineral_id

Values of code list component_con_mineral
Id	Code	Label (English)
100000245		actinolite
100000246		adularia
100000247		albite
100000248		almandine
100000249		amphibole
100000250		andalusite
100000251		andesine
100000252		anhydrite
100000253		ankerite
100000254		anorthite
100000255		anorthoclase
100000256		anthracite
100000257		antigorite
100000258		apatite
100000259		aragonite
100000260		arsenopyrite
100000261		asbestos
100000262		augite
100000263		baryte
100000264		bauxite
100000265		biotite
100000266		calcite
100000267		cassiterite
100000268		celestine
100000269		chalcopyrite
100000270		chlorite
100000271		chloritoid
100000272		chromite
100000273		chrysotile
100000274		coesite
100000275		copper
100000276		cordierite
100000277		corundum
100000278		diaspore
100000279		diopside
100000280		dolomite
100000281		enstatite
100000282		epidote
100000283		feldspar
100000284		fluorite
100000285		forsterite
100000286		fuchsite
100000287		galena
100000288		garnet
100000289		gibbsite
100000290		glauconite
100000291		glaucophane
100000292		goethite
100000293		gold
100000294		graphite
100000295		grossular
100000296		gypsum
100000297		halite
100000298		hematite
100000299		hornblende
100000300		illite
100000301		ilmenite
100000302		jadeite
100000303		kaolinite
100000304		K-feldspar
100000305		kyanite
100000306		leucite
100000307		lignite
100000308		limonite
100000309		magnesite
100000310		magnetite
100000311		mica
100000312		microcline
100000313		monazite
100000314		montmorillonite
100000315		muscovite
100000316		nepheline
100000355		not specified
100000317		oligoclase
100000318		olivine
100000319		omphacite
100000320		orthoclase
100000354		other
100000321		phengite
100000322		phlogopite
100000323		plagioclase
100000324		prehnite
100000325		pyrite
100000326		pyrope
100000327		pyrophyllite
100000328		pyroxene
100000329		quartz
100000330		rutile
100000331		sapphirine
100000332		scapolite
100000333		sericite
100000334		serpentine
100000335		siderite
100000336		sillimanite
100000337		silver
100000338		smectite
100000339		spessartite
100000340		sphalerite
100000341		spinel
100000342		staurolite
100000343		stilpnomelane
100000344		sulfur
100000345		talc
100000346		titanite
100000347		tourmaline
100000348		tremolite
100000349		vermiculite
100000350		wollastonite
100000351		zeolite
100000352		zircon
100000353		zoisite

#`component_con_particle`66 values

Referenced by lithology_description_component_con_particle_codelist.component_con_particle_id

Values of code list component_con_particle
Id	Code	Label (English)
100000179		algae
100000180		algal mats
100000181		ammonites
100000182		aptychi
100000215		ash
100000183		belemnites
100000184		bioclasts
100000185		biodetritus
100000216		bitumen
100000186		bivalves
100000187		brachiopods
100000188		bryozoans
100000217		calcareous concretion
100000218		calcareous oncoids
100000219		calcareous ooids
100000220		calcareous pisoids
100000189		calpionellids
100000190		cephalopods
100000221		chert
100000222		coal fragments
100000191		coccoliths
100000223		concretion
100000192		corals
100000193		crinoids
100000224		crystals
100000194		diatoms
100000195		dinoflagellates
100000196		echinoderms
100000197		echinoids
100000198		fish remains
100000199		foraminifera
100000200		fossils
100000201		gastropods
100000202		gryphaea
100000225		iron ooids
100000226		iron pisoids
100000227		lapilli
100000228		lithoclasts
100000203		molluscs
100000204		nautilids
100000244		not specified
100000205		nummulites
100000229		oncoids
100000230		ooids
100000231		organic matter
100000206		ostracods
100000243		other
100000207		oysters
100000232		pellets
100000233		pisoids
100000234		plant remains
100000235		plant root tubes
100000236		pollen
100000237		pyroclast
100000208		radiolarians
100000238		rootlets
100000209		rudists
100000239		sideritic concretion
100000240		silicified wood
100000210		Spicules
100000211		sponges
100000241		spores
100000212		stromatolites
100000213		tintinnids
100000214		vertebrates
100000242		wood fragments

#`structure_post_gen`43 values

Referenced by lithology_description_structure_post_gen_codelist.structure_post_gen_id

Values of code list structure_post_gen
Id	Code	Label (English)
100000457		anhydrite nodule layers (chicken-wire)
100000455		anhydrite vein
100000423		birdseye structure
100000452		blastomylonitic
100000449		boudinage
100000460		brecciated
100000430		caliche
100000447		cataclastic
100000458		collapse breccia
100000462		cone-in-cone
100000434		crystal ghosts
100000445		displaced
100000440		faulted
100000424		fenestral structure
100000456		fibrous gypsum
100000450		folded
100000435		fossil ghosts
100000441		fractured
100000444		glided
100000454		gypsum vein
100000431		hardground
100000463		horse-tailing
100000439		jointed
100000429		karst, completely filled
100000427		karst, empty
100000428		karst, partially filled
100000425		keystone vugs
100000451		mylonitic
100000433		neomorphism
100000465		not specified
100000436		ooid ghosts
100000464		other
100000437		pressure solution seams
100000432		pseudomorphosis
100000443		pull-apart structure
100000461		rauhwacke
100000448		sheared
100000442		slickensides
100000459		solution breccia
100000438		stylolites
100000446		subsided
100000453		vein
100000426		vugs

#`structure_syn_gen`60 values

Referenced by lithology_description_structure_syn_gen_codelist.structure_syn_gen_id

Values of code list structure_syn_gen
Id	Code	Label (English)
100000392		adhesion ripples
100000393		asymmetrical ripples
100000366		bedded
100000417		bioturbated
100000420		bored surface
100000419		boring
100000418		burrows
100000407		clay drape
100000403		convolute bedding
100000382		crinkled
100000386		cross-bedding
100000399		dish structure
100000405		drag fold (sedimentary)
100000406		dyke (sedimentary)
100000388		festoon cross-bedding
100000398		flame structure
100000379		flaser bedding
100000408		flute casts
100000416		geopetal fabric
100000383		graded bedding
100000410		groove casts
100000397		hummocky cross-stratification
100000385		inverse graded bedding
100000371		laminated (< 1 cm)
100000380		lenticular bedding
100000400		load casts
100000373		medium bedded (10 – 100 cm)
100000368		moderatley bedded
100000412		mudcracks
100000364		nodular
100000376		non-parallel bedding
100000384		normal graded bedding
100000422		not specified
100000421		other
100000401		overturning bedding
100000375		parallel bedding
100000415		pebble imbrication
100000381		pinch-and-swell bedding
100000390		planar cross-bedding
100000394		planar/parallel ripples
100000411		prod casts/bounce casts
100000402		ptygmatic fold
100000396		ripple-drift, climbing ripples
100000391		ripples
100000367		slightly bedded
100000404		slump/contorted bedding
100000409		striation casts
100000363		structureless
100000395		symmetrical ripples
100000413		syneresis cracks
100000389		tabular cross-bedding
100000414		tepee structure
100000374		thick bedded (>100 cm)
100000372		thin bedded (1 – 10 cm)
100000387		trough cross-bedding
100000365		varves
100000370		very well bedded
100000378		wavy bedding (irregular)
100000377		wavy bedding (parallel)
100000369		well bedded

#`uscs_type`29 values

Referenced by lithology_uscs_type_codelist.uscs_type_id

Values of code list uscs_type
Id	Code	Label (English)
23101006	GC	clayey gravel (GC)
23101020	SC	clayey sand (SC)
23101004	CL-ML	clayey silt (CL-ML)
23101035	MH	elastic silt (MH)
23101001	CH	fat clay (CH)
23101002	CM	lean clay (CM)
23101034	kA	not specified
23101017	OL	organic clay (OL)
23101016	OH	organic silt (OH)
100000496	O	other
23101018	Pt	peat (Pt)
23101009	GP	poorly graded gravel (GP)
23101010	GP-GC	poorly graded gravel with clay (GP-GC)
23101011	GP-GM	poorly graded gravel with silt (GP-GM)
23101023	SP	poorly graded sand (SP)
23101024	SP-SC	poorly graded sand with clay (SP-SC)
23101025	SP-SM	poorly graded sand with silt (SP-SM)
23101015	ML	silt (ML)
23101003	CL	silty clay (CL)
23101008	GM	silty gravel (GM)
23101022	SM	silty sand (SM)
23101021	SC-SM	silty, calyey sand (SC-SM)
23101007	GC-GM	silty, clayey gravel (GC-GM)
23101012	GW	well-graded gravel (GW)
23101013	GW-GC	well-graded gravel with clay (GW-GC)
23101014	GW-GM	well-graded gravel with silt (GW-GM)
23101029	SW	well-graded sand (SW)
23101030	SW-SC	well-graded sand with clay (SW-SC)
23101031	SW-SM	well-graded sand with silt (SW-SM)

#`log_tool_type`26 values

Referenced by log_file_tool_type_codelist.codelist_id

Values of code list log_tool_type
Id	Code	Label (English)
100003048	ABI	Acoustic Borehole Imager
100003032	CAL	Caliper
100003052	CBL	Cement Bond Log
100003037	RHOB	Density
100003044	DIE	Dielectric
100003045	ECS	Elemental Capture Spectroscopy
100003034	GR	Gamma Ray
100003033	GYRO	Gyroscope and inclinometer
100003043	MRES	Microresistivity
100003039	NPHI	Neutron Porosity
100003056	UNSPEC	not specified
100003046	NMR	Nuclear Magnetic Resonance
100003049	OBI	Optical Borehole Imager
100003055	OTHER	other
100003038	PEF	Photoelectric Factor
100003051	PLT	Production logging tools
100003054	PNL	Pulsed neutron logging tools
100003042	RES	Resistivity (Laterolog / Induction / Array)
100003047	RBI	Resistivity Borehole Imager
100003040	SIGMA	Sigma (Thermal Neutron Capture Cross-Section)
100003036	DT	Sonic (monopole & dipole)
100003035	SGR	Spectral Gamma Ray
100003041	SP	Spontaneous Potential
100003050	TEMP	Temperature
100003053	USIT / USI	Ultrasonic Cement and Casing Imager
100003057	VSP	Vertical seismic profile

#`drilling_mud_type`23 values

Referenced by section_element.mud_type_id, section_element.mud_subtype_id

Values of code list drilling_mud_type
Id	Code	Label (English)
22109004		air
22109002		all oil
22109009		bentonite, polymers
22109019		diesel
22109018		dispersed oil
22109007		foam
22109005		gas
22109017		invert emulsion
22109011		K-silicate, Na-silicate, polymer
22109013		lignite, lignosulfonate, bentonite, polymers, phosphate
22109014		lime, gypsum, NaCl, CaCl2, lignite, lignosulfonate, bentonite, polymers
22109016		low toxicity
22109006		mist
22109010		NaCl, KCl, bentonite, polymer
22109021		non-aqueous
22109023		not specified
22109015		oil-based
22109022		other
22109003		pneumatic
22109020		synthetic
22109001		water
22109012		water-based dispersed
22109008		water-based non-dispersed

About this document

This specification is published by the Federal Office of Topography swisstopo under CC BY 4.0. It describes the database schema of boreholes.swissgeol.ch at application version 2.1.1748; because not every release changes the schema, the version given here identifies the application state the description was taken from.

The permanent identifier of this document is https://doi.org/10.60695/swisstopo.boreholes.datamodel.2026; it resolves to the current published version. HTML is the only published format.

Questions, corrections and reuse enquiries: swissgeol@swisstopo.ch.

Revision history

Revision history
Version	Date	Changes
2.1.1748	18 August 2026	First public release.

Accessing the data

General information about the swissgeol platform and its applications is available at swissgeol.ch. The schema described here is shared by several instances of the application serving different audiences: public, internal, external, integration and development.

Access restrictions on an individual borehole are expressed through restriction_id and restriction_until in borehole; workgroup_id links a record to the workgroup that maintains it. The attribute borehole.public is a legacy field and is always NULL.

Scope and introduction

Scope of this specification

How to read this document

Diagram notation

Reading the attribute tables

Modelling conventions

Record identity and audit columns

Code list references

Depth, elevation and units

Languages

Tables not documented

Database structure

The eight domains

Glossary and abbreviations

#borehole

Attributes

Foreign key relationships

Code lists

#borehole_identifiers_codelist

Attributes

Foreign key relationships

Code lists

#section

Attributes

Foreign key relationships

#section_element

Attributes

Foreign key relationships

Code lists

#borehole_geometry

Attributes

Foreign key relationships

#stratigraphy

Attributes

Foreign key relationships

#lithological_description

Attributes

Foreign key relationships

#lithology

Attributes

Foreign key relationships

Code lists

#lithology_description

Attributes

Foreign key relationships

Code lists

#facies_description

Attributes

Foreign key relationships

Code lists

#chronostratigraphy

Attributes

Foreign key relationships

Code lists

#lithostratigraphy

Attributes

Foreign key relationships

Code lists

#lithology_description_component_con_mineral_codelist

Attributes

Foreign key relationships

Code lists

#lithology_description_component_con_particle_codelist

Attributes

Foreign key relationships

Code lists

#lithology_description_component_uncon_debris_codelist

Attributes

Foreign key relationships

Code lists

#lithology_description_component_uncon_organic_codelist

Attributes

Foreign key relationships

Code lists

#lithology_description_grain_angularity_codelist

Attributes

Foreign key relationships

Code lists

#lithology_description_grain_shape_codelist

Attributes

#`borehole`

#`borehole_identifiers_codelist`

#`section`

#`section_element`

#`borehole_geometry`

#`stratigraphy`

#`lithological_description`

#`lithology`

#`lithology_description`

#`facies_description`

#`chronostratigraphy`

#`lithostratigraphy`

#`lithology_description_component_con_mineral_codelist`

#`lithology_description_component_con_particle_codelist`

#`lithology_description_component_uncon_debris_codelist`

#`lithology_description_component_uncon_organic_codelist`

#`lithology_description_grain_angularity_codelist`

#`lithology_description_grain_shape_codelist`

#`lithology_description_lithology_uncon_debris_codelist`

#`lithology_description_structure_post_gen_codelist`

#`lithology_description_structure_syn_gen_codelist`

#`lithology_rock_condition_codelist`

#`lithology_texture_meta_codelist`

#`lithology_uscs_type_codelist`

#`completion`

#`casing`

#`casing_element`

#`backfill`

#`instrumentation`

#`observation`

#`water_ingress`

#`groundwater_level_measurement`

#`hydrotest`

#`hydrotest_result`

#`hydrotest_evaluationmethod_codelist`