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)))
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
erDiagram
"auth.otp" |o--|| "auth.accounts" : "FOREIGN KEY (user_email) REFERENCES auth.accounts(email) ON DELETE RESTRICT"
"auth.vessels" }o--|| "auth.accounts" : "FOREIGN KEY (owner_email) REFERENCES auth.accounts(email) ON DELETE RESTRICT"
"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"
integer id ""
text last "User last name with CONSTRAINT CHECK"
text pass ""
jsonb preferences ""
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 can be optional but if present must be a valid one and unique but must be in numeric range between 100000000 and 800000000"
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 ""
text ip "Store vessel ip address"
double_precision length ""
text mmsi "Maritime Mobile Service Identity (MMSI) number associated with the vessel, link to public.mid"
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 including vessel polar (theoretical performance), make/model, and preferences"
text vessel_id FK "Link auth.vessels with api.metadata via FOREIGN KEY and REFERENCES"
}