15  Database

The source of truth for the Marine Sensitivity Toolkit is DuckDB — a columnar, embedded analytic database. It is fast enough to answer live per-cell queries while remaining a single-file artifact that can be copied, versioned and served read-only to many processes at once.

The pipeline writes a working database (sdm.duckdb) on the machine that runs it; a release is the published, immutable form of that database’s tables. Since the marine-atlas release format, each version is published as partitioned Parquet on S3 under marine-atlas/{ver}/tables/, and serving reads it through a tiny view-only DuckDB (serve.duckdb, kilobytes) whose tables are views over those files — never a multi-gigabyte copy.

Everything below is read from the release this build documents (v3), so the tables, columns and row counts are those of that version rather than of whichever version happened to be current when the page was written.

Note

Why DuckDB and not PostgreSQL? Earlier versions of the stack used PostgreSQL + PostGIS as the primary store. As the analysis stabilized around a fixed 0.05° cell grid and a small set of zone layers, live geometry operations and concurrent writes stopped being needed — only fast analytic reads. DuckDB scans tens of millions of rows in well under a second with no separate server process. PostGIS is no longer part of the serving path (see Chapter 14).

15.1 Table and column naming conventions

  • Table names are singular and lower case.
  • Unique identifier columns are suffixed with:
    • *_id for unique integer keys (stable across rebuilds);
    • *_key for unique string keys (human-readable and stable across releases);
    • *_seq for auto-incrementing sequence integer keys (internal join keys, not stable across rebuilds).
  • Column names are singular and use snake_case.
  • Foreign keys use the singular form of the referenced table, followed by the primary-key suffix (_id, _key, or _seq).
Importantvalue is a reserved word in DuckDB

Cell values are stored in a column named val, not value. The serving views expose val AS value for backward compatibility with callers written against the older schema.

Table 15.1: Tables published by v3, with row counts read from the release itself.
Table Rows Purpose
cell 661,372 Grid cells of the study area, with environmental covariates and membership flags.
taxon 17,561 One row per taxon: identity, category, extinction risk and validity flags.
model 30,430 Registry of every distribution model, keyed by the stable model identifier.
metric 35 Metric registry: key and description for each scored quantity.
cell_metric 21,990,083 Per-cell score for each metric — the surface the maps draw.
zone 76 Zone registry keyed by source table, field and value, so every spatial unit shares one schema.
zone_cell 4,626,531 Zone membership: which cells belong to which zone.
zone_metric 774 Pre-aggregated per-zone score for each metric — what drives the choropleth.
zone_taxon 61,219 Per-zone species list with each species’ contribution to the zone score.
dataset 8 Source datasets, with citations, vintages and whether each fed the scores.
taxon_model 29,805 Which models fed which taxon — the taxon to model relation.
listing 718 US federal listing lookup (ESA status, MMPA and MBTA protection).
NoteNot published by v3

model_asset, native_asset. Sections that depend on these are omitted from this build rather than described from another release.

15.2 Schema

The columns of each published table, read from the release:

15.2.1 cell

Column Type
cell_id INTEGER
depth_mean DOUBLE
depth_min DOUBLE
depth_max DOUBLE
oxy_b_mean DOUBLE
oxy_mean DOUBLE
prim_prod_mean DOUBLE
ice_con_ann DOUBLE
salinity_b_mean DOUBLE
salinity_mean DOUBLE
sbt_an_mean DOUBLE
sst_an_mean DOUBLE
fao_area_m DOUBLE
area_km2 DOUBLE
width_km2 DOUBLE

15.2.2 taxon

Column Type
taxon_id DOUBLE
taxon_authority VARCHAR
n_ds INTEGER
ms_merge INTEGER
sp_cat VARCHAR
mdl_seq INTEGER
scientific_name VARCHAR
common_name VARCHAR
worms_id INTEGER
redlist_code VARCHAR
extrisk_code VARCHAR
er_score INTEGER
is_mmpa BOOLEAN
is_mbta BOOLEAN
is_bcc BOOLEAN
worms_is_marine BOOLEAN
worms_is_extinct BOOLEAN
esa_code VARCHAR
esa_source VARCHAR
is_ok BOOLEAN
worms_taxonomic_status VARCHAR

15.2.3 model

Column Type
mdl_seq INTEGER
ds_key VARCHAR
taxa VARCHAR
time_period VARCHAR
region VARCHAR
mdl_type VARCHAR
description VARCHAR
date_created DATE

15.2.4 metric

Column Type
metric_seq INTEGER
metric_key VARCHAR
description VARCHAR
date_created DATE
metric_title VARCHAR
metric_abbrev VARCHAR

15.2.5 cell_metric

Column Type
cell_id INTEGER
metric_seq INTEGER
value DOUBLE

15.2.6 zone

Column Type
zone_seq INTEGER
tbl VARCHAR
fld VARCHAR
value VARCHAR
date_created DATE

15.2.7 zone_cell

Column Type
zone_seq INTEGER
cell_id INTEGER
pct_covered INTEGER

15.2.8 zone_metric

Column Type
zone_seq INTEGER
metric_seq INTEGER
value DOUBLE

15.2.9 zone_taxon

Column Type
zone_tbl VARCHAR
zone_fld VARCHAR
zone_value VARCHAR
mdl_seq INTEGER
sp_cat VARCHAR
sp_common VARCHAR
sp_scientific VARCHAR
taxon_id DOUBLE
taxon_authority VARCHAR
rl_code VARCHAR
er_score INTEGER
is_mmpa BOOLEAN
is_mbta BOOLEAN
is_bcc BOOLEAN
esa_code VARCHAR
esa_source VARCHAR
area_km2 DOUBLE
avg_suit DOUBLE
suit_rl DOUBLE
suit_rl_area DOUBLE
cat_suit_rl_area DOUBLE
pct_cat DOUBLE

15.2.10 dataset

Column Type
ds_key VARCHAR
name_short VARCHAR
name_original VARCHAR
description VARCHAR
citation VARCHAR
source_broad VARCHAR
source_detail VARCHAR
regions VARCHAR
response_type VARCHAR
taxa_groups VARCHAR
year_pub INTEGER
date_obs_beg DATE
date_obs_end DATE
date_env_beg DATE
date_env_end DATE
link_info VARCHAR
link_download VARCHAR
link_metadata VARCHAR
links_other VARCHAR
spatial_res_deg DOUBLE
temporal_res VARCHAR
date_created DATE
name_display VARCHAR
value_info VARCHAR
is_mask BOOLEAN
sort_order INTEGER

15.2.11 taxon_model

Column Type
taxon_id DOUBLE
ds_key VARCHAR
mdl_seq INTEGER

15.2.12 listing

Column Type
spp_id DOUBLE
worms_id DOUBLE
botw_id DOUBLE
extrisk_code VARCHAR
er_score INTEGER
is_mmpa BOOLEAN
is_mbta BOOLEAN
is_bcc BOOLEAN
common_name VARCHAR

15.3 Asset store

NoteOld copies remain until the prune

Every published release (v7, v7b, v8, v9) has pointed only into the store since the asset-store migration of 2026-10-02. The copies the v8 and v9 releases carried before it still exist under their release prefixes (v8/native/, v9/native/) and on the file host, unreferenced, until a prune that follows a soak period. The migration’s listing on 2026-10-01 found 145,452 objects (33.08 GB) under those two prefixes, which collapse to 86,421 distinct keys (19.63 GB) in the store, because the two releases carry the same files for most datasets.

A release owns pointers, never distribution files. Every cloud-optimized distribution file lives exactly once in an unversioned, content-addressed store, and each release publishes tables whose rows point into it. Nothing ending in .tif or .pmtiles is written under a release’s {ver}/ prefix. A release prefix holds only its manifest, its tables, its app bundle and its serving files.

There are two stores, both in the project’s public S3 bucket:

Store Key Holds
cog/ cog/{grid_id}/{hash}.tif rasters on an analysis grid: each input’s gridded surface, merged models and score surfaces
native/ native/{ds_key}/{hash}.{tif\|pmtiles} each source dataset’s own resolution original: a raster (.tif) or a vector range (.pmtiles)

15.3.1 The key comes from the content

The hash in a key is computed before any file is built, from the source content of the model plus an encoding tag, and never from the bytes of the file or from the release:

  • for a gridded surface, the model’s (cell_id, val) rows;
  • for a native raster, the source grid’s (cell_id, val) rows;
  • for a native vector range, the normalized geometry and the attributes that reach the tile;
  • the encoding tag names how the file is written (data type, nodata, overviews, tile scheme), so a different encoding of the same content is deliberately a different object.

Two consequences follow. A surface that did not change between releases is found in the store by its key, so it costs neither a build nor an upload; a source dataset such as AquaMaps that is unchanged between releases is registered by a new pointer row and nothing else. And a merged model that equals its only input has the same rows, hence the same key, hence is the same object as that input, with no special case. A re-ingested dataset adds objects only for the models whose content changed; the old objects stay for the releases that still point at them. Scores are always new, because they are per-release tables.

Figure 15.1: A release’s pointer table refers to objects in the shared stores; the catalog lists every stored object.

15.3.2 The catalog and the pointer table

  • assets.parquet at the atlas root (beside the cog/ and native/ stores, under the same marine-atlas/ prefix as the releases) is the catalog, with one row per stored object (store, key, content hash, encoding tag, asset type, grid or dataset key, size, MD5, creation time and the first release that used it). “Is it already stored?” is an anti-join against this file, not a bucket listing.
  • native_asset is the per-release pointer table (Table 15.1). Its representation = model rows point into cog/, its native rows point into native/, and a merged model is a row with ds_key = ms_merge. Each row carries the content hash and the asset URL; a PMTiles row also carries source_key, the model key the tile’s features are tagged with, which is how a viewer filters one range out of the archive. Every published release has one in this shape; the earlier releases keep model_asset beside it so that old links still resolve.
  • An object no release points at is garbage. Nothing is deleted except through a check that joins the catalog against every release’s pointer tables.

15.3.3 Finding a file

The bucket denies anonymous listing, so a file is found through a pointer, not by browsing:

  1. read the catalog assets.parquet to see what exists and where;
  2. read a release’s native_asset table ({ver}/tables/native_asset.parquet) to see which objects that release uses for each model;
  3. or ask STAC: the searchable STAC API is built from the pointer table, so each Item’s model and native assets carry the same store URLs, and the static STAC catalog of a release links them once its tree is rebuilt from the pointer table (every published release today).

15.3.4 Verify before store

An object is admitted to the store under key K only if its decoded pixels equal the rows K was computed from. In the migration, a file that failed the check was repainted from the model’s current rows and uploaded under its key (2,934 of the raster objects; the rest were copied once their pixels had been verified). A publish applies the same check to every new file before it is uploaded. This keeps what a map draws identical to what the scores used.

See Section 16.3 for how a publish writes to the store and what the publish gate checks.

15.4 How the tables feed the serving tier

  • Score rasters — per-cell scores are published as Cloud-Optimized GeoTIFFs, one per metric × subregion, and the app reads the href and rescale range straight from the release manifest. Earlier releases rendered them by sending SQL to a tile factory per request; that path is retired (see Chapter 14).
  • Per-model distributions — model_cell is published partitioned by a dense integer model id, so a tile request reads exactly one partition as a point read rather than scanning the surface.
  • Zone choropleths — zone_metric joined to zone gives the per-zone score; the geometry comes from PMTiles published per zone-set vintage, and the manifest names the vintage each release used. Only the paint specification changes when the reader switches metric.
  • Per-cell and per-area species lists — the same rows partitioned by a spatial tile instead (cell_model), because a per-cell question against a model-partitioned surface would scan everything.

See Chapter 16 for the notebooks that build each of these tables.