api.metadata
Description
Stores metadata received from vessel, aka signalk plugin. single-row-per-vessel store
Columns
| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| active | boolean | true | false | trigger monitor online/offline | ||
| available_keys | jsonb | true | Signalk paths with unit for custom mapping | |||
| beam | double precision | true | ||||
| configuration | jsonb | true | User-defined Signalk path mapping for metrics | |||
| created_at | timestamp with time zone | now() | false | |||
| height | double precision | true | ||||
| ip | inet | true | Store vessel ip address | |||
| length | double precision | true | ||||
| mmsi | text | true | MMSI as reported by the SignalK plugin. Stored as text because raw user input may be non-numeric or malformed. Silently nulled by metadata_upsert_trigger_fn if it does not match the 9-digit pattern ^\d{9}$. Intentionally differs from auth.vessels.mmsi (numeric, validated). Never JOIN this column to auth.vessels.mmsi — use vessel_id instead. | |||
| name | text | true | ||||
| platform | text | true | ||||
| plugin_version | text | ‘unknown’::text | false | |||
| ship_type | numeric | true | Type of ship associated with the vessel, link to public.aistypes | |||
| signalk_version | text | ‘unknown’::text | false | |||
| time | timestamp with time zone | now() | false | |||
| updated_at | timestamp with time zone | now() | false | |||
| user_data | jsonb | ‘{}’::jsonb | true | User-defined data for the vessel. Include vessel polar (theoretical performance), make/model, and Vessel-scoped operational state (alarms, alert_last_metric, alerting, windy.*) | ||
| vessel_id | text | current_setting(‘vessel.id’::text, false) | false | api.logbook api.metrics api.moorages api.stays | auth.vessels | Link auth.vessels with api.metadata via FOREIGN KEY and REFERENCES |
Constraints
| Name | Type | Definition | Comment |
|---|---|---|---|
| metadata_active_not_null | n | NOT NULL active | |
| metadata_created_at_not_null | n | NOT NULL created_at | |
| metadata_pkey | PRIMARY KEY | PRIMARY KEY (vessel_id) | |
| metadata_plugin_version_not_null | n | NOT NULL plugin_version | |
| metadata_signalk_version_not_null | n | NOT NULL signalk_version | |
| metadata_time_not_null | n | NOT NULL “time” | |
| metadata_updated_at_not_null | n | NOT NULL updated_at | |
| metadata_vessel_id_fkey | FOREIGN KEY | FOREIGN KEY (vessel_id) REFERENCES auth.vessels(vessel_id) ON DELETE RESTRICT | Link api.metadata with auth.vessels via vessel_id using FOREIGN KEY and REFERENCES |
| metadata_vessel_id_key | UNIQUE | UNIQUE (vessel_id) | |
| metadata_vessel_id_not_null | n | NOT NULL vessel_id |
Indexes
| Name | Definition |
|---|---|
| metadata_pkey | CREATE UNIQUE INDEX metadata_pkey ON api.metadata USING btree (vessel_id) |
| metadata_vessel_id_key | CREATE UNIQUE INDEX metadata_vessel_id_key ON api.metadata USING btree (vessel_id) |
Triggers
| Name | Definition | Comment |
|---|---|---|
| metadata_autodiscovery_trigger | CREATE TRIGGER metadata_autodiscovery_trigger AFTER INSERT ON api.metadata FOR EACH ROW EXECUTE FUNCTION metadata_autodiscovery_trigger_fn() | AFTER INSERT ON api.metadata run function metadata_autodiscovery_trigger_fn for later signalk mapping provisioning on new vessel |
| metadata_moddatetime | CREATE TRIGGER metadata_moddatetime BEFORE UPDATE ON api.metadata FOR EACH ROW EXECUTE FUNCTION api.metadata_moddatetime_fn() | Conditional updated_at refresh — skips user_data-only writes (cron/windy/vessel-settings). |
| metadata_update_configuration_trigger | CREATE TRIGGER metadata_update_configuration_trigger BEFORE UPDATE ON api.metadata FOR EACH ROW EXECUTE FUNCTION update_metadata_configuration_trigger_fn() | BEFORE UPDATE ON api.metadata run function api.update_metadata_configuration tp update the configuration field with current date in ISO format |
| metadata_update_user_data_trigger | CREATE TRIGGER metadata_update_user_data_trigger BEFORE UPDATE ON api.metadata FOR EACH ROW EXECUTE FUNCTION update_metadata_userdata_added_at_trigger_fn() | BEFORE UPDATE ON api.metadata run function public.update_metadata_userdata_added_at_trigger_fn to update the user_data field with current date in ISO format when polar or images change |
| metadata_upsert_trigger | CREATE TRIGGER metadata_upsert_trigger BEFORE INSERT OR UPDATE ON api.metadata FOR EACH ROW EXECUTE FUNCTION metadata_upsert_trigger_fn() | BEFORE INSERT OR UPDATE ON api.metadata run function metadata_upsert_trigger_fn |
Relations
erDiagram
"api.metadata" |o--|| "auth.vessels" : "FOREIGN KEY (vessel_id) REFERENCES auth.vessels(vessel_id) ON DELETE RESTRICT"
"auth.vessels" }o--|| "auth.accounts" : "FOREIGN KEY (owner_email) REFERENCES auth.accounts(email) ON DELETE CASCADE"
"api.logbook" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.logbook" }o--o| "api.moorages" : "FOREIGN KEY (_from_moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT"
"api.logbook" }o--o| "api.moorages" : "FOREIGN KEY (_to_moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT"
"api.metrics" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.moorages" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.stays" }o--o| "api.moorages" : "FOREIGN KEY (moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT"
"api.moorages" }o--o| "api.stays_at" : "FOREIGN KEY (stay_code) REFERENCES api.stays_at(stay_code) ON DELETE RESTRICT"
"api.stays" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.stays" }o--o| "api.stays_at" : "FOREIGN KEY (stay_code) REFERENCES api.stays_at(stay_code) ON DELETE RESTRICT"
"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"
}
"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 ""
}
"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 ""
}
"api.logbook" {
text _from "Name of the location where the log started, usually a moorage name"
double_precision _from_lat ""
double_precision _from_lng ""
integer _from_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
timestamp_with_time_zone _from_time ""
text _to "Name of the location where the log ended, usually a moorage name"
double_precision _to_lat ""
double_precision _to_lng ""
integer _to_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
timestamp_with_time_zone _to_time ""
boolean active ""
double_precision avg_speed "avg speed in knots"
timestamp_with_time_zone created_at ""
numeric distance "Distance in Nautical Miles converted mobilitydb meters to NM"
interval duration "Duration in ISO 8601 format"
jsonb extra "Computed SignalK metrics such as runtime, current level, etc."
integer id ""
double_precision max_speed "max speed in knots"
double_precision max_wind_speed "true wind speed converted in knots, m/s from signalk plugin"
text name ""
text notes ""
tgeogpoint trip "MobilityDB trajectory, speed in m/s, distance in meters"
tfloat trip_awa "AWA (Apparent Wind Angle) in degrees converted from radians by signalk plugin"
tfloat trip_aws "AWS (Apparent Wind Speed), windSpeedApparent in knots converted by signalk plugin"
tfloat trip_batt_charge "Battery Charge"
tfloat trip_batt_voltage "Battery Voltage"
tfloat trip_cog "COG - Course Over Ground True in degrees converted from radians by signalk plugin"
tfloat trip_depth "Depth in meters, raw from signalk plugin"
tfloat trip_heading "Heading True in degrees converted from radians, raw from signalk plugin"
tfloat trip_hum_out "Humidity outside"
ttext trip_notes ""
tfloat trip_pres_out "Pressure outside"
tfloat trip_sog "SOG - Speed Over Ground in knots converted by signalk plugin"
tfloat trip_solar_power "solar powerPanel"
tfloat trip_solar_voltage "solar voltage"
ttext trip_status ""
tfloat trip_tank_level "Tank currentLevel"
tfloat trip_temp_out "Temperature outside in Kelvin, raw from signalk plugin"
tfloat trip_temp_water "Temperature water in Kelvin, raw from signalk plugin"
tfloat trip_twd "TWD - True Wind Direction in degrees converted from radians, raw from signalk plugin"
tfloat trip_tws "TWS - True Wind Speed in knots converted from m/s, raw from signalk plugin"
timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.moorages" {
text country ""
timestamp_with_time_zone created_at ""
geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
boolean home_flag ""
integer id ""
double_precision latitude ""
double_precision longitude ""
text name ""
jsonb nominatim "Output of the nominatim reverse geocoding service, see https://nominatim.org/release-docs/develop/api/Reverse/"
text notes ""
jsonb overpass "Output of the overpass API, see https://wiki.openstreetmap.org/wiki/Overpass_API"
integer stay_code FK "Link api.stays_at with api.moorages via FOREIGN KEY and REFERENCES"
timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.metrics" {
double_precision anglespeedapparent ""
text client_id "Deprecated client_id to be removed"
double_precision courseovergroundtrue ""
double_precision latitude "With CONSTRAINT but allow NULL value to be ignored silently by trigger"
double_precision longitude "With CONSTRAINT but allow NULL value to be ignored silently by trigger"
jsonb metrics ""
double_precision speedoverground ""
text status ""
timestamp_with_time_zone time ""
text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
double_precision windspeedapparent ""
}
"api.stays" {
boolean active ""
timestamp_with_time_zone arrived ""
timestamp_with_time_zone created_at ""
timestamp_with_time_zone departed ""
interval duration "Computed stay duration (departed - arrived). GENERATED ALWAYS AS STORED — replaces inline expression in stats views."
geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
integer id ""
double_precision latitude ""
double_precision longitude ""
integer moorage_id FK "Link api.moorages with api.stays via FOREIGN KEY and REFERENCES"
text name ""
text notes ""
integer stay_code FK "Link api.stays_at with api.stays via FOREIGN KEY and REFERENCES"
timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
jsonb user_data "User-defined data Stay-specific data including images and custom fields"
text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays_at" {
text description ""
integer stay_code ""
}
Generated by tbls