User Tables
users.users
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------------------------
id | uuid | | not null | public.uuid_generate_v7()
created | timestamp without time zone | | not null | LOCALTIMESTAMP
updated | timestamp without time zone | | not null | LOCALTIMESTAMP
deleted | timestamp without time zone | | |
name | text | | |
family_name | text | | |
given_name | text | | |
email | text | | |
picture | text | | |
email_verified | timestamp without time zone | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_key" UNIQUE, btree (email) WHERE deleted IS NULL
Referenced by:
TABLE "auth.credentials" CONSTRAINT "credentials_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "auth.oidc_auth_attempts" CONSTRAINT "oidc_auth_attempts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "auth.sessions" CONSTRAINT "sessions_userId_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
users.tenant_users
Column | Type | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+----------------
tenant_id | uuid | | not null |
user_id | uuid | | not null |
created | timestamp without time zone | | not null | LOCALTIMESTAMP
updated | timestamp without time zone | | not null | LOCALTIMESTAMP
deleted | timestamp without time zone | | |
roles | text[] | | |
email | text | | |
Indexes:
"tenant_users_pkey" PRIMARY KEY, btree (tenant_id, user_id)
Foreign-key constraints:
"tenant_users_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(id)
Referenced by:
TABLE "auth.tenant_oidc_auth_attempts" CONSTRAINT "tenant_oidc_auth_attempts_tenant_id_user_id_fkey" FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_users(tenant_id, user_id)
Authentication Tables
auth.sessions
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
expires_at | timestamp with time zone | | not null |
session_token | text | | not null |
user_id | uuid | | not null |
Indexes:
"sessions_pkey" PRIMARY KEY, btree (id)
"sessiontoken_unique" UNIQUE CONSTRAINT, btree (session_token)
Foreign-key constraints:
"sessions_userId_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
auth.credentials
Column | Type | Collation | Nullable | Default
---------------------+------------------------------+-----------+----------+---------------------------
id | uuid | | not null | public.uuid_generate_v7()
user_id | uuid | | not null |
created | timestamp without time zone | | not null | LOCALTIMESTAMP
updated | timestamp without time zone | | not null | LOCALTIMESTAMP
deleted | timestamp without time zone | | |
method | public.authentication_method | | not null |
provider | text | | not null | 'nile'::text
payload | jsonb | | |
provider_account_id | text | | |
Indexes:
"credentials_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"credentials_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
auth.verification_tokens
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------------------------------------
identifier | text | | not null |
token | text | | not null |
expires | timestamp without time zone | | not null | LOCALTIMESTAMP + '04:00:00'::interval
Indexes:
"verification_tokens_pkey" PRIMARY KEY, btree (identifier)
"token_identifier_unique" UNIQUE CONSTRAINT, btree (token, identifier)
auth.invites
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
tenant_id | uuid | | not null |
token | text | | not null |
identifier | text | | not null |
roles | text | | |
created_by | uuid | | |
expires | timestamp with time zone | | | LOCALTIMESTAMP + '7 days'::interval
Indexes:
"auth_invites_pkey" PRIMARY KEY, btree (tenant_id, id)
"unique_invite_per_email_per_tenant" UNIQUE, btree (tenant_id, identifier)
OIDC Provider tables
auth.oidc_providers
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------------------------
id | uuid | | not null | public.uuid_generate_v7()
created | timestamp without time zone | | not null | LOCALTIMESTAMP
updated | timestamp without time zone | | not null | LOCALTIMESTAMP
deleted | timestamp without time zone | | |
name | text | | not null |
auth_type | public.provider_auth_type | | not null |
enabled | boolean | | not null | false
config_url | text | | |
redirect_url | text | | |
config | jsonb | | |
ttl_sec | integer | | not null | 3600
Indexes:
"oidc_providers_pkey" PRIMARY KEY, btree (id)
"oidc_providers_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
TABLE "auth.oidc_relying_parties" CONSTRAINT "oidc_relying_parties_provider_fkey" FOREIGN KEY (provider) REFERENCES auth.oidc_providers(id)
auth.oidc_relying_parties
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------------------------
id | uuid | | not null | public.uuid_generate_v7()
provider | uuid | | not null |
created | timestamp without time zone | | not null | LOCALTIMESTAMP
updated | timestamp without time zone | | not null | LOCALTIMESTAMP
deleted | timestamp without time zone | | |
client_id | text | | not null |
client_secret | text | | not null |
enabled | boolean | | not null | true
Indexes:
"oidc_relying_parties_pkey" PRIMARY KEY, btree (id)
"oidc_relying_parties_provider_key" UNIQUE, btree (provider, (deleted IS NULL))
Foreign-key constraints:
"oidc_relying_parties_provider_fkey" FOREIGN KEY (provider) REFERENCES auth.oidc_providers(id)
Referenced by:
TABLE "auth.oidc_auth_attempts" CONSTRAINT "oidc_auth_attempts_relying_party_fkey" FOREIGN KEY (relying_party) REFERENCES auth.oidc_relying_parties(id)
Tenant override tables
auth.tenant_oidc_relying_parties
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------------------------
id | uuid | | not null | public.uuid_generate_v7()
tenant_id | uuid | | not null |
provider_name | text | | not null |
created | timestamp without time zone | | not null | LOCALTIMESTAMP
updated | timestamp without time zone | | not null | LOCALTIMESTAMP
deleted | timestamp without time zone | | |
enabled | boolean | | not null | true
config_url | text | | not null |
config | jsonb | | |
ttl_sec | integer | | not null | 3600
client_id | text | | not null |
client_secret | text | | not null |
domains | text[] | | not null |
Indexes:
"tenant_oidc_relying_parties_pkey" PRIMARY KEY, btree (id, tenant_id)
"tenant_oidc_provider_key" UNIQUE, btree (tenant_id, provider_name, (deleted IS NULL))
Foreign-key constraints:
"tenant_oidc_relying_parties_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(id)
Referenced by:
TABLE "auth.tenant_oidc_auth_attempts" CONSTRAINT "tenant_oidc_auth_attempts_registration_id_tenant_id_fkey" FOREIGN KEY (registration_id, tenant_id) REFERENCES auth.tenant_oidc_relying_parties(id, tenant_id)