auth.accounts
Description
users account table
Columns
| Name | Type | Default | Nullable | Children | Comment |
|---|---|---|---|---|---|
| connected_at | timestamp with time zone | now() | false | ||
| created_at | timestamp with time zone | now() | false | ||
| citext | false | auth.otp auth.vessels | |||
| first | text | false | User first name with CONSTRAINT CHECK | ||
| last | text | false | User last name with CONSTRAINT CHECK | ||
| pass | text | false | |||
| preferences | jsonb | ‘{“language”: “gb”, “email_notifications”: true}’::jsonb | true | User-identity preferences. Always-present keys: email_notifications (bool), language (ISO 639-1, default “gb”). public_windy (bool) as user-controlled toggle. public_* visibility fields kept temporarily pending web UI rework. badges per account telegram, pushover_user_key, phone_notifications: user-identity notification credentials. | |
| role | name | false | |||
| updated_at | timestamp with time zone | now() | false | ||
| user_id | text | uuid_generate_v7() | false |
Constraints
| Name | Type | Definition |
|---|---|---|
| accounts_connected_at_not_null | n | NOT NULL connected_at |
| accounts_created_at_not_null | n | NOT NULL created_at |
| accounts_email_check | CHECK | CHECK ((email ~* ‘^.+@.+..+$’::citext)) |
| accounts_email_not_null | n | NOT NULL email |
| accounts_email_unique | UNIQUE | UNIQUE (email) |
| accounts_first_not_null | n | NOT NULL first |
| accounts_last_not_null | n | NOT NULL last |
| accounts_pass_not_null | n | NOT NULL pass |
| accounts_pkey | PRIMARY KEY | PRIMARY KEY (user_id) |
| accounts_role_not_null | n | NOT NULL role |
| accounts_updated_at_not_null | n | NOT NULL updated_at |
| accounts_user_id_key | UNIQUE | UNIQUE (user_id) |
| accounts_user_id_not_null | n | NOT NULL user_id |
| ensure_user_role_exists | TRIGGER | CREATE CONSTRAINT TRIGGER ensure_user_role_exists AFTER INSERT OR UPDATE ON auth.accounts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION auth.check_role_exists() |
| valid_email | CHECK | CHECK ((length((email)::text) > 5)) |
| valid_first | CHECK | CHECK (((length(first) > 1) AND (length(first) < 512))) |
| valid_last | CHECK | CHECK (((length(last) > 1) AND (length(last) < 512))) |
| valid_pass | CHECK | CHECK (((length(pass) > 4) AND (length(pass) < 512))) |
Indexes
| Name | Definition |
|---|---|
| accounts_email_unique | CREATE UNIQUE INDEX accounts_email_unique ON auth.accounts USING btree (email) |
| accounts_pkey | CREATE UNIQUE INDEX accounts_pkey ON auth.accounts USING btree (user_id) |
| accounts_user_id_key | CREATE UNIQUE INDEX accounts_user_id_key ON auth.accounts USING btree (user_id) |
Triggers
| Name | Definition | Comment |
|---|---|---|
| accounts_moddatetime | CREATE TRIGGER accounts_moddatetime BEFORE UPDATE ON auth.accounts FOR EACH ROW EXECUTE FUNCTION moddatetime(‘updated_at’) | Automatic update of updated_at on table modification |
| encrypt_pass | CREATE TRIGGER encrypt_pass BEFORE INSERT OR UPDATE ON auth.accounts FOR EACH ROW EXECUTE FUNCTION auth.encrypt_pass() | execute function auth.encrypt_pass() |
| ensure_user_role_exists | CREATE CONSTRAINT TRIGGER ensure_user_role_exists AFTER INSERT OR UPDATE ON auth.accounts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION auth.check_role_exists() | ensure user role exists |
| new_account_entry | CREATE TRIGGER new_account_entry AFTER INSERT ON auth.accounts FOR EACH ROW EXECUTE FUNCTION new_account_entry_fn() | Add new account in process_queue for further processing |
Relations
erDiagram
"auth.otp" |o--|| "auth.accounts" : "FOREIGN KEY (user_email) REFERENCES auth.accounts(email) ON DELETE CASCADE"
"auth.vessels" }o--|| "auth.accounts" : "FOREIGN KEY (owner_email) REFERENCES auth.accounts(email) ON DELETE CASCADE"
"api.metadata" |o--|| "auth.vessels" : "FOREIGN KEY (vessel_id) REFERENCES auth.vessels(vessel_id) ON DELETE RESTRICT"
"auth.accounts" {
timestamp_with_time_zone connected_at ""
timestamp_with_time_zone created_at ""
citext email ""
text first "User first name with CONSTRAINT CHECK"
text last "User last name with CONSTRAINT CHECK"
text pass ""
jsonb preferences "User-identity preferences. Always-present keys: email_notifications (bool),\n language (ISO 639-1, default #quot;gb#quot;).\n public_windy (bool) as user-controlled toggle.\n public_* visibility fields kept temporarily pending web UI rework.\n badges per account\n telegram, pushover_user_key, phone_notifications: user-identity notification\n credentials."
name role ""
timestamp_with_time_zone updated_at ""
text user_id ""
}
"auth.otp" {
text otp_pass ""
timestamp_with_time_zone otp_timestamp ""
smallint otp_tries ""
citext user_email FK ""
}
"auth.vessels" {
timestamp_with_time_zone created_at ""
numeric mmsi "MMSI as a validated numeric value with CHECK constraint enforcing range.\n Intentionally differs from api.metadata.mmsi (text, permissive).\n This is the authoritative MMSI — api.metadata.mmsi is the raw SignalK input."
text name ""
citext owner_email FK ""
name role ""
timestamp_with_time_zone updated_at ""
text vessel_id ""
}
"api.metadata" {
boolean active "trigger monitor online/offline"
jsonb available_keys "Signalk paths with unit for custom mapping"
double_precision beam ""
jsonb configuration "User-defined Signalk path mapping for metrics"
timestamp_with_time_zone created_at ""
double_precision height ""
inet ip "Store vessel ip address"
double_precision length ""
text mmsi "MMSI as reported by the SignalK plugin. Stored as text because raw user input\n may be non-numeric or malformed. Silently nulled by metadata_upsert_trigger_fn\n if it does not match the 9-digit pattern ^\d{9}$.\n Intentionally differs from auth.vessels.mmsi (numeric, validated).\n Never JOIN this column to auth.vessels.mmsi — use vessel_id instead."
text name ""
text platform ""
text plugin_version ""
numeric ship_type "Type of ship associated with the vessel, link to public.aistypes"
text signalk_version ""
timestamp_with_time_zone time ""
timestamp_with_time_zone updated_at ""
jsonb user_data "User-defined data for the vessel.\n Include vessel polar (theoretical performance), make/model, and \n Vessel-scoped operational state (alarms, alert_last_metric, alerting,\n windy.*)"
text vessel_id FK "Link auth.vessels with api.metadata via FOREIGN KEY and REFERENCES"
}
Generated by tbls