Persistence models

These are the tables in the persistence model, in reading form. Each heading is one table. Abstract data model is the picture. PostgreSQL schema explains why the tables are shaped this way. This page does not replace that explanation.

The MariaDB script is the same schema version. The model emits it as mariadb/schema.sql, that file’s own bytes. This page lists the PostgreSQL tables.

Schema version 32.

A required column must be present. Generated is a column the database fills. A directory name on a column is one the schema already states. A directory attribute does not otherwise become a column. Indexes, grants, and routines stay in the SQL file.

This page is rewritten whenever that TypeSpec model is written. Do not edit it by hand.

Extensions: pg_stat_statements, pg_trgm.

Tables

Table TypeSpec Columns
sts_ldap_entries StsLdapEntries 16
sts_ldap_counts StsLdapCounts 5
sts_ldap_count_deltas StsLdapCountDeltas 6
sts_ldap_dek_counts StsLdapDekCounts 3
sts_ldap_dek_deltas StsLdapDekDeltas 4
sts_realms StsRealms 7
sts_appconfig StsAppconfig 2
sts_keys StsKeys 3
sts_minted StsMinted 7
sts_sessions StsSessions 15
sts_minted_index StsMintedIndex 5
sts_changes StsChanges 6
sts_used_assertions StsUsedAssertions 14
sts_cluster_nodes StsClusterNodes 10
sts_cluster_leases StsClusterLeases 5
sts_cluster_claims StsClusterClaims 7
sts_cluster_secrets StsClusterSecrets 4
sts_cluster_locks StsClusterLocks 1
sts_cluster_counters StsClusterCounters 6
sts_cluster_windows StsClusterWindows 6
sts_cluster_budgets StsClusterBudgets 9
sts_node_snapshots StsNodeSnapshots 4
sts_change_readers StsChangeReaders 5
sts_risk_datasets StsRiskDatasets 7
sts_risk_dataset_versions StsRiskDatasetVersions 25
sts_risk_dataset_blobs StsRiskDatasetBlobs 5
sts_risk_geo_locations StsRiskGeoLocations 8
sts_risk_geo_ranges StsRiskGeoRanges 15
sts_risk_asn_ranges StsRiskAsnRanges 7
sts_risk_ip_lists StsRiskIpLists 7
sts_risk_fido_authenticators StsRiskFidoAuthenticators 12
sts_risk_assessments StsRiskAssessments 43
sts_risk_feature_counts StsRiskFeatureCounts 7
sts_risk_failures StsRiskFailures 11
sts_risk_session_context StsRiskSessionContext 12
sts_risk_subjects StsRiskSubjects 11
sts_risk_terms_acceptances StsRiskTermsAcceptances 10
sts_cell_routing StsCellRouting 5
sts_report_artifacts StsReportArtifacts 6
sts_report_artifact_pieces StsReportArtifactPieces 4
sts_schema StsSchema 2

sts_ldap_entries

TypeSpec model StsLdapEntries.

Column SQL type Required Notes
realm text yes  
dn_key text yes  
dn text yes  
attrs jsonb yes  
origin text no  
created_at text no  
modified_at text no  
parent_key text no Generated.
rdn_value text no Generated.
name_keys jsonb yes Directory names: uid.
mail_keys jsonb yes Directory names: mail.
uuid_keys jsonb yes Directory names: entryUUID, stsEntryUuidAlias.
class_keys jsonb yes Directory names: objectClass.
value_keys jsonb yes  
attr_names jsonb yes  
dek_keys jsonb no  

sts_ldap_counts

TypeSpec model StsLdapCounts.

Column SQL type Required Notes
realm text yes  
base_key text yes  
attribute text yes  
shard smallint yes  
n bigint yes  

sts_ldap_count_deltas

TypeSpec model StsLdapCountDeltas.

Column SQL type Required Notes
txid xid8 yes  
realm text yes  
parent_key text yes  
n bigint yes  
holding bigint yes  
fold boolean yes  

sts_ldap_dek_counts

TypeSpec model StsLdapDekCounts.

Column SQL type Required Notes
dek_id text yes  
shard smallint yes  
n bigint yes  

sts_ldap_dek_deltas

TypeSpec model StsLdapDekDeltas.

Column SQL type Required Notes
txid xid8 yes  
dek_id text yes  
n bigint yes  
fold boolean yes  

sts_realms

TypeSpec model StsRealms.

Column SQL type Required Notes
id text yes  
name text no  
description text no  
created_at bigint no  
overrides jsonb yes  
domain text no  
retiring_at bigint no  

sts_appconfig

TypeSpec model StsAppconfig.

Column SQL type Required Notes
key text yes  
value jsonb yes  

sts_keys

TypeSpec model StsKeys.

Column SQL type Required Notes
realm text yes  
material text yes  
written_at timestamptz yes  

sts_minted

TypeSpec model StsMinted.

Column SQL type Required Notes
handle text yes  
realm text yes  
key text yes  
body text yes  
written_at timestamptz yes  
expires_at bigint no  
key_sealed text yes  

sts_sessions

TypeSpec model StsSessions.

Column SQL type Required Notes
realm text yes  
key text yes  
expires_at bigint no  
last_seen_at bigint yes  
started_at bigint yes  
subject_key text yes  
username_key text yes  
chosen boolean yes  
authenticated boolean yes  
kind text yes  
rp_surface text yes  
parent_key text yes  
backs_console boolean yes  
credential_key text yes  
partner_relationship text yes  

sts_minted_index

TypeSpec model StsMintedIndex.

Column SQL type Required Notes
handle text yes  
realm text yes  
index_key text yes  
key text yes  
sort_at bigint no  

sts_changes

TypeSpec model StsChanges.

Column SQL type Required Notes
seq bigserial yes  
origin text yes  
kind text yes  
realm text yes  
key text yes  
at timestamptz yes  

sts_used_assertions

TypeSpec model StsUsedAssertions.

Column SQL type Required Notes
realm text yes  
key text yes  
format text yes  
used_as text yes  
issuer text yes  
identifier text yes  
client_id text yes  
subject text yes  
state text yes  
reservation text yes  
origin text yes  
used_at bigint yes  
spent_at bigint yes  
expires_at bigint yes  

sts_cluster_nodes

TypeSpec model StsClusterNodes.

Column SQL type Required Notes
node_id text yes  
name text yes  
mode text yes  
version text yes  
fingerprint text yes  
started_at bigint yes  
heartbeat_at bigint yes  
expires_at bigint yes  
left_at bigint yes  
info jsonb yes  

sts_cluster_leases

TypeSpec model StsClusterLeases.

Column SQL type Required Notes
name text yes  
holder text yes  
token bigint yes  
acquired_at bigint yes  
expires_at bigint yes  

sts_cluster_claims

TypeSpec model StsClusterClaims.

Column SQL type Required Notes
scope text yes  
realm text yes  
key text yes  
reservation text yes  
origin text yes  
claimed_at bigint yes  
expires_at bigint yes  

sts_cluster_secrets

TypeSpec model StsClusterSecrets.

Column SQL type Required Notes
name text yes  
material text yes  
created_by text yes  
created_at bigint yes  

sts_cluster_locks

TypeSpec model StsClusterLocks.

Column SQL type Required Notes
name text yes  

sts_cluster_counters

TypeSpec model StsClusterCounters.

Column SQL type Required Notes
scope text yes  
realm text yes  
key text yes  
value bigint yes  
origin text yes  
updated_at bigint yes  

sts_cluster_windows

TypeSpec model StsClusterWindows.

Column SQL type Required Notes
scope text yes  
realm text yes  
key text yes  
count bigint yes  
window_ends_at bigint yes  
origin text yes  

sts_cluster_budgets

TypeSpec model StsClusterBudgets.

Column SQL type Required Notes
scope text yes  
realm text yes  
key text yes  
period bigint yes  
amount bigint yes  
count bigint yes  
origin text yes  
expires_at bigint yes  
updated_at bigint yes  

sts_node_snapshots

TypeSpec model StsNodeSnapshots.

Column SQL type Required Notes
name text yes  
node_id text yes  
taken_at bigint yes  
body jsonb yes  

sts_change_readers

TypeSpec model StsChangeReaders.

Column SQL type Required Notes
origin text yes  
node_id text yes  
applied bigint yes  
started_at bigint yes  
reported_at bigint yes  

sts_risk_datasets

TypeSpec model StsRiskDatasets.

Column SQL type Required Notes
realm text yes  
dataset text yes  
kind text yes  
active_version text yes  
previous_version text yes  
state text yes  
updated_at bigint yes  

sts_risk_dataset_versions

TypeSpec model StsRiskDatasetVersions.

Column SQL type Required Notes
realm text yes  
dataset text yes  
version text yes  
format text yes  
provider text yes  
licence text yes  
attribution text yes  
source text yes  
source_uri text yes  
sha256 text yes  
byte_count bigint yes  
row_count bigint yes  
parameters jsonb yes  
verification text yes  
published_at bigint yes  
next_update_at bigint yes  
fetched_at bigint yes  
loaded_at bigint yes  
activated_at bigint yes  
superseded_at bigint yes  
rows_deleted_at bigint yes  
state text yes  
refusal text yes  
error_code text yes  
origin text yes  

sts_risk_dataset_blobs

TypeSpec model StsRiskDatasetBlobs.

Column SQL type Required Notes
realm text yes  
dataset text yes  
version text yes  
part integer yes  
content bytea yes  

sts_risk_geo_locations

TypeSpec model StsRiskGeoLocations.

Column SQL type Required Notes
dataset text yes  
version text yes  
location_id bigint yes  
continent text yes  
country text yes  
subdivision text yes  
city text yes  
time_zone text yes  

sts_risk_geo_ranges

TypeSpec model StsRiskGeoRanges.

Column SQL type Required Notes
dataset text yes  
version text yes  
range_start inet yes  
range_end inet yes  
location_id bigint yes  
continent text yes  
country text yes  
subdivision text yes  
city text yes  
registered_country text yes  
latitude double precision no  
longitude double precision no  
accuracy_km integer yes  
anonymous_proxy boolean yes  
satellite boolean yes  

sts_risk_asn_ranges

TypeSpec model StsRiskAsnRanges.

Column SQL type Required Notes
dataset text yes  
version text yes  
range_start inet yes  
range_end inet yes  
asn bigint yes  
as_org text yes  
as_domain text yes  

sts_risk_ip_lists

TypeSpec model StsRiskIpLists.

Column SQL type Required Notes
realm text yes  
dataset text yes  
version text yes  
range_start inet yes  
range_end inet yes  
category text yes  
note text yes  

sts_risk_fido_authenticators

TypeSpec model StsRiskFidoAuthenticators.

Column SQL type Required Notes
dataset text yes  
version text yes  
key_kind text yes  
authenticator_key text yes  
description text yes  
protocol_family text yes  
certification_level text yes  
latest_status text yes  
latest_status_at bigint yes  
compromised boolean yes  
status_reports jsonb yes  
metadata_statement jsonb yes  

sts_risk_assessments

TypeSpec model StsRiskAssessments.

Column SQL type Required Notes
realm text yes  
id text yes  
at bigint yes  
phase text yes  
door text yes  
subject text yes  
session_id text yes  
client_id text yes  
address_sealed text yes  
address_prefix cidr yes  
asn bigint yes  
as_org text yes  
country text yes  
subdivision text yes  
city text yes  
latitude double precision no  
longitude double precision no  
accuracy_km integer yes  
ip_lists text[] yes  
ua_hash text yes  
ua_family text yes  
ua_os text yes  
ua_platform text yes  
bot boolean yes  
ja4 text yes  
credential_kind text yes  
credential_hash text yes  
aaguid text yes  
authenticator_cert text yes  
backup_eligible boolean no  
backup_state boolean no  
jkt text yes  
cert_fingerprint text yes  
datasets jsonb yes  
signals jsonb yes  
score real yes  
level text yes  
decision text yes  
policy_id text yes  
error_code text yes  
origin text yes  
feedback text yes  
feedback_at bigint yes  

sts_risk_feature_counts

TypeSpec model StsRiskFeatureCounts.

Column SQL type Required Notes
realm text yes  
subject text yes  
feature text yes  
value text yes  
count bigint yes  
first_at bigint yes  
last_at bigint yes  

sts_risk_failures

TypeSpec model StsRiskFailures.

Column SQL type Required Notes
realm text yes  
id bigserial yes  
at bigint yes  
door text yes  
subject text yes  
name_hmac text yes  
address_sealed text yes  
address_prefix cidr yes  
asn bigint yes  
error_code text yes  
origin text yes  

sts_risk_session_context

TypeSpec model StsRiskSessionContext.

Column SQL type Required Notes
realm text yes  
session_id text yes  
subject text yes  
address_prefix cidr yes  
asn bigint yes  
country text yes  
ua_hash text yes  
ja4 text yes  
jkt text yes  
score real yes  
level text yes  
updated_at bigint yes  

sts_risk_subjects

TypeSpec model StsRiskSubjects.

Column SQL type Required Notes
realm text yes  
subject text yes  
score real yes  
level text yes  
previous_level text yes  
reason text yes  
last_assessment text yes  
crossed_at bigint yes  
actions jsonb yes  
feedback text yes  
updated_at bigint yes  

sts_risk_terms_acceptances

TypeSpec model StsRiskTermsAcceptances.

Column SQL type Required Notes
id bigserial yes  
provider text yes  
terms_digest text yes  
terms_text text yes  
accepted_by text yes  
accepted_via text yes  
deployment text yes  
page_digest text yes  
accepted_at bigint yes  
origin text yes  

sts_cell_routing

TypeSpec model StsCellRouting.

Column SQL type Required Notes
realm text yes  
kind text yes  
digest text yes  
cell text yes  
written_at bigint yes  

sts_report_artifacts

TypeSpec model StsReportArtifacts.

Column SQL type Required Notes
realm text yes  
job_id text yes  
bytes bytea yes  
sha256 text yes  
media_type text yes  
created_at bigint yes  

sts_report_artifact_pieces

TypeSpec model StsReportArtifactPieces.

Column SQL type Required Notes
realm text yes  
job_id text yes  
seq int yes  
bytes bytea yes  

sts_schema

TypeSpec model StsSchema.

Column SQL type Required Notes
version int yes  
applied_at timestamptz yes