api.stays

Description

The stays table records time spent at moorages. Each stay links a moorage with arrival/departure timestamps. RLS policies filter by vessel_id.

Columns

Name	Type	Default	Nullable	Parents	Comment
active	boolean	false	false
arrived	timestamp with time zone		false
departed	timestamp with time zone		true
duration	interval		true		Best to use standard ISO 8601
geog	geography(Point,4326)		true		postgis geography type default SRID 4326 Unit: degres
id	integer		false
latitude	double precision		true
longitude	double precision		true
moorage_id	integer		true	api.moorages	Link api.moorages with api.stays via FOREIGN KEY and REFERENCES
name	text		true
notes	text		true
stay_code	integer	1	true	api.stays_at	Link api.stays_at with api.stays via FOREIGN KEY and REFERENCES
updated_at	timestamp with time zone	now()	false		Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync.
user_data	jsonb	‘{}’::jsonb	true		User-defined data Stay-specific data including images and custom fields
vessel_id	text		false	api.metadata	Unique identifier for the vessel associated with the api.metadata entry

Constraints

Name	Type	Definition	Comment
stays_active_not_null	n	NOT NULL active
stays_arrived_not_null	n	NOT NULL arrived
stays_geog_consistent	CHECK	CHECK ((geog = geography(st_makepoint(longitude, latitude))))	Ensure geog column is consistent with longitude and latitude columns
stays_id_not_null	n	NOT NULL id
stays_moorage_id_fkey	FOREIGN KEY	FOREIGN KEY (moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT
stays_pkey	PRIMARY KEY	PRIMARY KEY (id)
stays_stay_code_fkey	FOREIGN KEY	FOREIGN KEY (stay_code) REFERENCES api.stays_at(stay_code) ON DELETE RESTRICT
stays_updated_at_not_null	n	NOT NULL updated_at
stays_vessel_id_fkey	FOREIGN KEY	FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT	Link api.stays with api.metadata via vessel_id using FOREIGN KEY and REFERENCES
stays_vessel_id_not_null	n	NOT NULL vessel_id

Indexes

Name	Definition	Comment
stays_arrived_idx	CREATE INDEX stays_arrived_idx ON api.stays USING btree (arrived)
stays_departed_id_idx	CREATE INDEX stays_departed_id_idx ON api.stays USING btree (departed)
stays_moorage_id_idx	CREATE INDEX stays_moorage_id_idx ON api.stays USING btree (moorage_id)
stays_pkey	CREATE UNIQUE INDEX stays_pkey ON api.stays USING btree (id)
stays_stay_code_idx	CREATE INDEX stays_stay_code_idx ON api.stays USING btree (stay_code)
stays_timeline_covering_idx	CREATE INDEX stays_timeline_covering_idx ON api.stays USING btree (arrived DESC, departed DESC) INCLUDE (id, name, moorage_id, stay_code, notes, vessel_id) WHERE ((departed IS NOT NULL) AND (name IS NOT NULL))	Optimizes: Time-range queries across all vessels (admin/reporting) Query pattern: SELECT * FROM stays WHERE arrived BETWEEN X AND Y (without vessel_id filter) Key strategy: Time-first ordering for global timeline queries INCLUDE clause: vessel_id in INCLUDE (filtered after index scan via RLS) Partial index: Named, completed stays Size: ~100 bytes per entry Used by: Admin dashboards, cross-vessel analytics, reporting When used: When RLS filter is applied AFTER time-based index scan Alternative to: stays_vessel_timeline_idx when vessel_id is not in WHERE clause Note: RLS policies will filter vessel_id from INCLUDE clause after index scan
stays_vessel_active_idx	CREATE INDEX stays_vessel_active_idx ON api.stays USING btree (vessel_id, active, arrived DESC) WHERE (active = true)	Optimizes: Current/active stay lookup Query pattern: SELECT * FROM stays WHERE vessel_id=X AND active=true Key strategy: vessel_id + active flag for current stay Partial index: Only active stays (typically 0-1 per vessel) Size: Very small (~40 bytes per active stay) Used by: Current location status, “where am I now” queries Performance: Sub-millisecond due to high selectivity (one row per vessel)
stays_vessel_arrived_idx	CREATE INDEX stays_vessel_arrived_idx ON api.stays USING btree (vessel_id, arrived DESC, departed DESC) INCLUDE (moorage_id, stay_code)
stays_vessel_id_idx	CREATE INDEX stays_vessel_id_idx ON api.stays USING btree (vessel_id)
stays_vessel_moorage_duration_idx	CREATE INDEX stays_vessel_moorage_duration_idx ON api.stays USING btree (vessel_id, moorage_id) INCLUDE (duration, active) WHERE (NOT active)	Optimizes: Total time spent at each moorage (aggregation queries) Query pattern: SELECT moorage_id, SUM(duration) FROM stays WHERE vessel_id=X AND NOT active GROUP BY moorage_id Key strategy: vessel_id (RLS) + moorage_id (GROUP BY key) INCLUDE clause: duration (for SUM), active (for additional filtering) Partial index: Completed stays only (active=false) Size: ~32 bytes per entry Used by: moorages_view aggregations, “total time at location” calculations Performance: Eliminates sequential scans for duration rollups Note: Complements stays_vessel_moorage_timeline_idx with different optimization target
stays_vessel_moorage_timeline_idx	CREATE INDEX stays_vessel_moorage_timeline_idx ON api.stays USING btree (vessel_id, moorage_id, arrived DESC, departed DESC) INCLUDE (id, name, duration, stay_code, active) WHERE (departed IS NOT NULL)	Optimizes: Per-moorage stay queries and aggregations Query pattern: SELECT * FROM stays WHERE vessel_id=X AND moorage_id=Y ORDER BY arrived DESC Key strategy: vessel_id (RLS) + moorage_id (grouping) + times (ordering) INCLUDE clause: Core stay attributes for index-only scans Partial index: Completed stays only (departed IS NOT NULL) Size: ~60 bytes per entry Used by: Moorage detail view, stay history at specific locations, duration aggregations Performance: Enables efficient GROUP BY moorage_id queries
stays_vessel_timeline_idx	CREATE INDEX stays_vessel_timeline_idx ON api.stays USING btree (vessel_id, arrived DESC) INCLUDE (id, departed, moorage_id, stay_code, name, duration, notes) WHERE ((departed IS NOT NULL) AND (name IS NOT NULL))	Optimizes: Full stay timeline sorted by arrival time Query pattern: SELECT * FROM stays_view WHERE vessel_id=X ORDER BY arrived DESC Key strategy: vessel_id (RLS) + arrived DESC (primary sort order for stays_view) INCLUDE clause: All columns needed for stays_view to enable index-only scans Partial index: Named, completed stays only (filters out incomplete/unnamed entries) Size: ~100 bytes per entry Used by: stays_view (primary index), timeline displays, stay history Performance: Reduces stays_view time by eliminating sort operation Note: This index directly supports the main stays_view query pattern
stays_vessel_updated_at_idx	CREATE INDEX stays_vessel_updated_at_idx ON api.stays USING btree (vessel_id, updated_at DESC)	Supports incremental sync: WHERE vessel_id = $1 AND updated_at > $cursor

Triggers

Name	Definition	Comment
stay_delete_trigger	CREATE TRIGGER stay_delete_trigger BEFORE DELETE ON api.stays FOR EACH ROW EXECUTE FUNCTION stay_delete_trigger_fn()	BEFORE DELETE ON api.stays run function public.stay_delete_trigger_fn to delete reference and stay_ext need to deleted.
stays_moddatetime	CREATE TRIGGER stays_moddatetime BEFORE UPDATE ON api.stays FOR EACH ROW EXECUTE FUNCTION moddatetime(‘updated_at’)	Automatic update of updated_at on table modification
stays_update_user_data_trigger	CREATE TRIGGER stays_update_user_data_trigger BEFORE UPDATE ON api.stays FOR EACH ROW EXECUTE FUNCTION update_tbl_userdata_added_at_trigger_fn()	BEFORE UPDATE ON api.stays run function public.update_tbl_userdata_added_at_trigger_fn to update the user_data field with current date in ISO format when polar or images change

Relations

erDiagram

"api.stays" }o--o| "api.moorages" : "FOREIGN KEY (moorage_id) REFERENCES api.moorages(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.moorages" }o--o| "api.stays_at" : "FOREIGN KEY (stay_code) REFERENCES api.stays_at(stay_code) 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.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.metadata" |o--|| "auth.vessels" : "FOREIGN KEY (vessel_id) REFERENCES auth.vessels(vessel_id) ON DELETE RESTRICT"
"api.logbook" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.metrics" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"

"api.stays" {
  boolean active ""
  timestamp_with_time_zone arrived ""
  timestamp_with_time_zone departed ""
  interval duration "Best to use standard ISO 8601"
  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.moorages" {
  text country ""
  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.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"
  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.stays_at" {
  text description ""
  integer stay_code ""
}
"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"
}
"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.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 ""
}

Generated by tbls

Keyboard shortcuts

Postgsail Documentation

api.stays

Description

Columns

Constraints

Indexes

Triggers

Relations