Gateway database tables

Internal, subject to change without notice. What this page describes belongs to the operation of a deployment, not to the contract the gateway offers to a third-party program. The vendor makes no commitment about it: these names can change from one version to the next, with no deprecation step. A change then shows up at startup or at upgrade time, as a message naming what is missing.

The part the vendor does commit to is the relay.

The gateway and the admin console read the same database. This page describes its schema as the installation file creates it, plus the registry table that the migration script adds for its own use.

The schema carries almost no machine-readable comments. This page therefore lists the columns, their type, whether they are required and their default value, without explaining what each one is for. Once the schema is annotated, the descriptions will appear here without anyone having to rewrite this page.

A fresh database contains no provider, no model, no endpoint and no account: the operator declares them from the admin console.

Columns to watch

A data protection officer looks at these first. The classification is based on the column name, which keeps it true for columns added later without anyone thinking about it.

Column	Nature
users.username	personal data
access_keys.key_hash	secret
access_keys.key_hint	secret
authorization_decisions.subject	personal data
identity_sessions.subject	personal data
subject_roles.subject	personal data
subject_roles.granted_by	personal data
authorization_policies.installed_by	personal data
subject_revocations.subject	personal data
subject_revocations.revoked_by	personal data
subject_revocations.lifted_by	personal data
administration_actions.actor	personal data
administration_actions.target	personal data
agent_acts.subject	personal data
agent_acts.target	personal data
agent_acts.decided_by	personal data
agent_session_revocations.revoked_by	personal data
organization_policies.installed_by	personal data
organization_policy_journal.actor	personal data
relay_usage.subject	personal data

No API key is stored in clear text: the database keeps a hash and the fragment the console displays so a key can be recognized without being read back.

The 23 tables

plans

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
name	TEXT	yes	none	unique
max_cost_euro	NUMERIC(10,4)	no	none

users

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
username	TEXT	yes	none	unique, personal data
active	BOOLEAN	yes	TRUE
created_at	TIMESTAMPTZ	yes	NOW()
plan_id	INTEGER	no	none	references plans(id)

access_keys

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
user_id	INTEGER	yes	none	references users(id)
key_hash	TEXT	yes	none	unique, secret
key_hint	TEXT	yes	none	secret
name	TEXT	yes	none
created_at	TIMESTAMPTZ	yes	NOW()
last_used_at	TIMESTAMPTZ	no	none
revoked_at	TIMESTAMPTZ	no	none

Constraints

Name	Rule
access_keys_name_not_blank	CHECK (btrim(name) <> '')

Indexes

Name	Unique	Columns	Restricted to
access_keys_by_account	no	user_id	no restriction

providers

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
slug	TEXT	yes	none	unique
name	TEXT	yes	none
base_url	TEXT	yes	none
api_key_env	TEXT	no	none
api_key_header	TEXT	no	none

models

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
slug	TEXT	yes	none	unique
name	TEXT	yes	none
provider_id	INTEGER	yes	none	references providers(id)
input_token_price_nano_euro	BIGINT	yes	0
output_token_price_nano_euro	BIGINT	yes	0

endpoints

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
name	TEXT	yes	none	unique
model_id	INTEGER	yes	none	references models(id)
enabled	BOOLEAN	yes	TRUE

request_logs

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
timestamp	TIMESTAMPTZ	yes	NOW()
user_id	INTEGER	no	none	references users(id)
endpoint_id	INTEGER	no	none	references endpoints(id)
model	TEXT	yes	none
latency_ms	INTEGER	yes	none
input_tokens	INTEGER	no	none
output_tokens	INTEGER	no	none
request_id	UUID	no	none

Indexes

Name	Unique	Columns	Restricted to
request_logs_request_id_unique	yes	request_id	request_id IS NOT NULL

authorization_decisions

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
decided_at	TIMESTAMPTZ	yes	clock_timestamp()
subject	TEXT	no	none	personal data
identity_source	TEXT	yes	none
resource	TEXT	yes	none
decision	TEXT	yes	none
reason	TEXT	no	none
request_id	TEXT	no	none

Constraints

Name	Rule
authorization_decisions_decision_connue	CHECK (decision IN ('accordee', 'refusee'))
authorization_decisions_source_connue	CHECK (identity_source IN ('annuaire', 'locale', 'inconnue'))
authorization_decisions_accord_nomme_son_sujet	CHECK (decision <> 'accordee' OR subject IS NOT NULL)

Indexes

Name	Unique	Columns	Restricted to
authorization_decisions_sujet_instant	no	subject, decided_at DESC	no restriction

identity_sessions

Column	Type	Required	Default
id	TEXT	yes	none	primary key
subject	TEXT	yes	none	personal data
identity_source	TEXT	yes	none
opened_at	TIMESTAMPTZ	yes	clock_timestamp()
expires_at	TIMESTAMPTZ	yes	none
ended_at	TIMESTAMPTZ	no	none
ended_reason	TEXT	no	none

Constraints

Name	Rule
identity_sessions_source_connue	CHECK (identity_source IN ('annuaire', 'locale'))
identity_sessions_fin_motivee	CHECK ((ended_at IS NULL) = (ended_reason IS NULL))

Indexes

Name	Unique	Columns	Restricted to
identity_sessions_sujet_vivantes	no	subject	ended_at IS NULL

convertion_rate

Column	Type	Required	Default
credits_per_eur	INTEGER	yes	none

subject_roles

Column	Type	Required	Default
id	SERIAL	yes	none	primary key
subject	TEXT	yes	none	personal data
role	TEXT	yes	none
scope	TEXT	no	none
pole	TEXT	no	none	computed
granted_at	TIMESTAMPTZ	yes	NOW()
granted_by	TEXT	yes	none	personal data

Constraints

Name	Rule
subject_roles_known_role	CHECK ( role IN ( 'utilisateur-standard', 'administrateur-de-projet', 'administrateur-d-instance', 'auditeur' ) )
subject_roles_scope_iff_project	CHECK ( (role = 'administrateur-de-projet') = (scope IS NOT NULL) )
subject_roles_attribution_unique	UNIQUE NULLS NOT DISTINCT (subject, role, scope)

Indexes

Name	Unique	Columns	Restricted to
subject_roles_by_subject	no	subject	no restriction

Triggers

Name	When	Effect
subject_roles_cumul_interdit	BEFORE INSERT OR UPDATE	FOR EACH ROW EXECUTE FUNCTION refuse_administration_audit_overlap()

authorization_policies

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
installed_at	TIMESTAMPTZ	yes	clock_timestamp()
installed_by	TEXT	yes	none	personal data
document	JSONB	yes	none
name	TEXT	no	none	computed

Constraints

Name	Rule
authorization_policies_document_est_un_objet	CHECK (jsonb_typeof(document) = 'object')
authorization_policies_auteur_non_vide	CHECK (btrim(installed_by) <> '')

subject_revocations

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
subject	TEXT	yes	none	personal data
revoked_at	TIMESTAMPTZ	yes	clock_timestamp()
revoked_by	TEXT	yes	none	personal data
reason	TEXT	yes	none
lifted_at	TIMESTAMPTZ	no	none
lifted_by	TEXT	no	none	personal data

Constraints

Name	Rule
subject_revocations_auteur_non_vide	CHECK (btrim(revoked_by) <> '')
subject_revocations_motif_non_vide	CHECK (btrim(reason) <> '')
subject_revocations_sujet_non_vide	CHECK (btrim(subject) <> '')
subject_revocations_levee_endossee	CHECK ((lifted_at IS NULL) = (lifted_by IS NULL))
subject_revocations_leveur_non_vide	CHECK (lifted_by IS NULL OR btrim(lifted_by) <> '')

Indexes

Name	Unique	Columns	Restricted to
subject_revocations_une_seule_en_vigueur	yes	subject	lifted_at IS NULL

administration_actions

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
performed_at	TIMESTAMPTZ	yes	clock_timestamp()
action	TEXT	yes	none
actor	TEXT	yes	none	personal data
actor_assurance	TEXT	yes	none
target	TEXT	no	none	personal data
role	TEXT	no	none
scope	TEXT	no	none
outcome	TEXT	yes	none
refusal_reason	TEXT	no	none
policy_id	BIGINT	no	none

Constraints

Name	Rule
administration_actions_acte_connu	CHECK (action IN ( 'attribution-de-role', 'retrait-de-role', 'revocation-de-sujet', 'levee-de-revocation', 'installation-de-politique', 'installation-de-politique-d-organisation', 'revocation-de-session-d-agent' ))
administration_actions_issue_connue	CHECK (outcome IN ('effectue', 'sans-effet', 'refuse'))
administration_actions_assurance_connue	CHECK (actor_assurance IN ('declaree', 'etablie'))
administration_actions_acteur_non_vide	CHECK (btrim(actor) <> '')
administration_actions_cible_nommee	CHECK ( action NOT IN ( 'attribution-de-role', 'retrait-de-role', 'revocation-de-sujet', 'levee-de-revocation', 'revocation-de-session-d-agent' ) OR (target IS NOT NULL AND btrim(target) <> '') )
administration_actions_role_ssi_acte_de_role	CHECK ( (action IN ('attribution-de-role', 'retrait-de-role')) = (role IS NOT NULL) )
administration_actions_perimetre_suppose_un_role	CHECK (scope IS NULL OR role IS NOT NULL)
administration_actions_politique_ssi_installation	CHECK ( ( policy_id IS NULL OR action IN ( 'installation-de-politique', 'installation-de-politique-d-organisation' ) ) AND ( action NOT IN ( 'installation-de-politique', 'installation-de-politique-d-organisation' ) OR outcome <> 'effectue' OR policy_id IS NOT NULL ) )
administration_actions_refus_motive	CHECK ((outcome = 'refuse') = (refusal_reason IS NOT NULL))

Indexes

Name	Unique	Columns	Restricted to
administration_actions_cible_instant	no	target, performed_at DESC	no restriction
administration_actions_acteur_instant	no	actor, performed_at DESC	no restriction

agent_execution_announcements

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
announced_at	TIMESTAMPTZ	yes	clock_timestamp()
launcher	TEXT	no	none
guarantee_held	BOOLEAN	yes	none
reservations	JSONB	yes	none

Constraints

Name	Rule
agent_execution_announcements_reserves_ssi_non_tenue	CHECK (guarantee_held = (jsonb_array_length(reservations) = 0))
agent_execution_announcements_reserves_en_tableau	CHECK (jsonb_typeof(reservations) = 'array')
agent_execution_announcements_sans_lanceur_sans_garantie	CHECK (launcher IS NOT NULL OR guarantee_held = FALSE)

Indexes

Name	Unique	Columns	Restricted to
agent_execution_announcements_instant	no	announced_at DESC	no restriction

agent_acts

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
received_at	TIMESTAMPTZ	yes	clock_timestamp()
session	TEXT	yes	none
workstation	TEXT	no	none
subject	TEXT	no	none	personal data
subject_assurance	TEXT	yes	'declaree'
surface	TEXT	yes	none
agent	TEXT	yes	none
at	TIMESTAMPTZ	yes	none
rank	INTEGER	yes	none
kind	TEXT	yes	none
tool	TEXT	no	none
target	TEXT	no	none	personal data
decision	TEXT	no	none
decided_by	TEXT	no	none	personal data
outcome	TEXT	no	none
reason	TEXT	no	none
duration_ms	INTEGER	no	none
mode	TEXT	no	none
rule_count	INTEGER	no	none
tools	JSONB	no	none

Constraints

Name	Rule
agent_acts_assurance_connue	CHECK (subject_assurance IN ('declaree', 'etablie'))
agent_acts_surface_connue	CHECK (surface IN ('cli', 'ide'))
agent_acts_rang_positif	CHECK (rank >= 1)
agent_acts_genre_connu	CHECK (kind IN ('tool-call', 'session-frame'))
agent_acts_decision_connue	CHECK (decision IS NULL OR decision IN ('allowed', 'refused'))
agent_acts_decideur_connu	CHECK (decided_by IS NULL OR decided_by IN ('human', 'automation'))
agent_acts_issue_connue	CHECK (outcome IS NULL OR outcome IN ('succeeded', 'failed', 'not-executed'))
agent_acts_motif_borne	CHECK (reason IS NULL OR char_length(reason) <= 300)
agent_acts_outils_en_tableau	CHECK (tools IS NULL OR jsonb_typeof(tools) = 'array')
agent_acts_appel_complet	CHECK (kind <> 'tool-call' OR (tool IS NOT NULL AND decision IS NOT NULL AND decided_by IS NOT NULL AND outcome IS NOT NULL))
agent_acts_cadre_complet	CHECK (kind <> 'session-frame' OR mode IS NOT NULL)
agent_acts_un_rang_par_session	UNIQUE (session, rank)

Indexes

Name	Unique	Columns	Restricted to
agent_acts_session_rang	no	session, rank	no restriction
agent_acts_instant	no	received_at DESC	no restriction

agent_session_revocations

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
session	TEXT	yes	none
revoked_at	TIMESTAMPTZ	yes	clock_timestamp()
revoked_by	TEXT	yes	none	personal data
revoked_by_assurance	TEXT	yes	none
reason	TEXT	yes	none

Constraints

Name	Rule
agent_session_revocations_session_non_vide	CHECK (btrim(session) <> '')
agent_session_revocations_auteur_non_vide	CHECK (btrim(revoked_by) <> '')
agent_session_revocations_assurance_connue	CHECK (revoked_by_assurance IN ('declaree', 'etablie'))
agent_session_revocations_motif_non_vide	CHECK (btrim(reason) <> '')
agent_session_revocations_une_par_session	UNIQUE (session)

organizations

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
slug	TEXT	yes	none	unique
name	TEXT	yes	none
created_at	TIMESTAMPTZ	yes	now()

organization_policies

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
organization_id	BIGINT	yes	none	references organizations(id)
revision	TEXT	yes	none
document	JSONB	yes	none
validity_seconds	INTEGER	yes	none
installed_at	TIMESTAMPTZ	yes	now()
installed_by	TEXT	yes	none	personal data

Constraints

Name	Rule
“	UNIQUE (organization_id, revision)

Indexes

Name	Unique	Columns	Restricted to
organization_policies_in_force	no	organization_id, id DESC	no restriction

organization_policy_journal

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
organization_id	BIGINT	no	none	references organizations(id)
at	TIMESTAMPTZ	yes	now()
actor	TEXT	yes	none	personal data
actor_assurance	TEXT	yes	none
outcome	TEXT	yes	none
revision	TEXT	no	none
refusal_reason	TEXT	no	none
document	JSONB	no	none

governance_announcements

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
announced_at	TIMESTAMPTZ	yes	clock_timestamp()
served_organization	TEXT	yes	none

relay_usage

Column	Type	Required	Default
id	BIGSERIAL	yes	none	primary key
recorded_at	TIMESTAMPTZ	yes	clock_timestamp()
subject	TEXT	yes	none	personal data
identity_source	TEXT	yes	none
model	TEXT	yes	none
input_tokens	INTEGER	no	none
output_tokens	INTEGER	no	none
latency_ms	INTEGER	no	none
request_id	TEXT	no	none

Indexes

Name	Unique	Columns	Restricted to
relay_usage_sujet_instant	no	subject, recorded_at DESC	no restriction

schema_migrations

Created by gateway-db/migrate.sh, not by the installation file.

Column	Type	Required	Default
version	TEXT	yes	none	primary key
applied_at	TIMESTAMPTZ	yes	NOW()

What this page does not cover

The migrations themselves, the order in which they apply, backup and restore are not described here. What is described is the state of the schema, not the path a database takes to reach it.