api.logbook

Description

The logbook table stores vessel navigation entries with timestamps, locations, and trip metrics. RLS policies filter by vessel_id automatically.
These indexes optimize different query patterns while minimizing storage.

Columns

Name	Type	Default	Nullable	Parents	Comment
_from	text		true		Name of the location where the log started, usually a moorage name
_from_lat	double precision		true
_from_lng	double precision		true
_from_moorage_id	integer		true	api.moorages	Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES
_from_time	timestamp with time zone		false
_to	text		true		Name of the location where the log ended, usually a moorage name
_to_lat	double precision		true
_to_lng	double precision		true
_to_moorage_id	integer		true	api.moorages	Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES
_to_time	timestamp with time zone		true
active	boolean	false	false
avg_speed	double precision		true		avg speed in knots
distance	numeric		true		Distance in Nautical Miles converted mobilitydb meters to NM
duration	interval		true		Duration in ISO 8601 format
extra	jsonb		true		Computed SignalK metrics such as runtime, current level, etc.
id	integer		false
max_speed	double precision		true		max speed in knots
max_wind_speed	double precision		true		true wind speed converted in knots, m/s from signalk plugin
name	text		true
notes	text		true
trip	tgeogpoint		true		MobilityDB trajectory, speed in m/s, distance in meters
trip_awa	tfloat		true		AWA (Apparent Wind Angle) in degrees converted from radians by signalk plugin
trip_aws	tfloat		true		AWS (Apparent Wind Speed), windSpeedApparent in knots converted by signalk plugin
trip_batt_charge	tfloat		true		Battery Charge
trip_batt_voltage	tfloat		true		Battery Voltage
trip_cog	tfloat		true		COG - Course Over Ground True in degrees converted from radians by signalk plugin
trip_depth	tfloat		true		Depth in meters, raw from signalk plugin
trip_heading	tfloat		true		Heading True in degrees converted from radians, raw from signalk plugin
trip_hum_out	tfloat		true		Humidity outside
trip_notes	ttext		true
trip_pres_out	tfloat		true		Pressure outside
trip_sog	tfloat		true		SOG - Speed Over Ground in knots converted by signalk plugin
trip_solar_power	tfloat		true		solar powerPanel
trip_solar_voltage	tfloat		true		solar voltage
trip_status	ttext		true
trip_tank_level	tfloat		true		Tank currentLevel
trip_temp_out	tfloat		true		Temperature outside in Kelvin, raw from signalk plugin
trip_temp_water	tfloat		true		Temperature water in Kelvin, raw from signalk plugin
trip_twd	tfloat		true		TWD - True Wind Direction in degrees converted from radians, raw from signalk plugin
trip_tws	tfloat		true		TWS - True Wind Speed in knots converted from m/s, raw from signalk plugin
updated_at	timestamp with time zone	now()	false		Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync.
user_data	jsonb	‘{}’::jsonb	true		User-defined data Log-specific data including actual tags, observations, 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
logbook__from_moorage_id_fkey	FOREIGN KEY	FOREIGN KEY (_from_moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT
logbook__from_time_not_null	n	NOT NULL _from_time
logbook__to_moorage_id_fkey	FOREIGN KEY	FOREIGN KEY (_to_moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT
logbook_active_not_null	n	NOT NULL active
logbook_id_not_null	n	NOT NULL id
logbook_pkey	PRIMARY KEY	PRIMARY KEY (id)
logbook_updated_at_not_null	n	NOT NULL updated_at
logbook_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
logbook_vessel_id_not_null	n	NOT NULL vessel_id

Indexes

Name	Definition	Comment
logbook_active_idx	CREATE INDEX logbook_active_idx ON api.logbook USING btree (active)
logbook_from_moorage_id_idx	CREATE INDEX logbook_from_moorage_id_idx ON api.logbook USING btree (_from_moorage_id)
logbook_from_time_join_idx	CREATE INDEX logbook_from_time_join_idx ON api.logbook USING btree (_from_time DESC, vessel_id) INCLUDE (id, _to_moorage_id, _to) WHERE (_to_moorage_id IS NOT NULL)	Optimizes: stays_view join on departure timestamps Query pattern: JOIN logbook ON stays.departed = logbook._from_time Key strategy: _from_time FIRST (join condition), vessel_id for RLS filtering INCLUDE clause: Minimal join data (id, destination moorage, location name) Partial index: Only departures with known destinations Why time-first: Enables efficient merge join when stays are sorted by departed time Index-only scan: Avoids heap access for join operations Size: ~60 bytes per entry Used by: stays_view to correlate stays with subsequent log entries Performance: Critical for stays_view (~50ms execution time)
logbook_id_vessel_idx	CREATE INDEX logbook_id_vessel_idx ON api.logbook USING btree (vessel_id, id) INCLUDE (_from_time, _to_time)	Optimizes: Direct lookup by ID with RLS enforcement Query pattern: SELECT * FROM logbook WHERE vessel_id=X AND id=Y Key strategy: vessel_id first (RLS), then unique id INCLUDE clause: Timestamps for quick time-based filtering Size: ~32 bytes per entry Used by: Single log entry retrieval, foreign key lookups Performance: O(1) lookup with RLS filter
logbook_pkey	CREATE UNIQUE INDEX logbook_pkey ON api.logbook USING btree (id)
logbook_to_moorage_id_idx	CREATE INDEX logbook_to_moorage_id_idx ON api.logbook USING btree (_to_moorage_id)
logbook_to_time_idx	CREATE INDEX logbook_to_time_idx ON api.logbook USING btree (_to_time)
logbook_to_time_join_idx	CREATE INDEX logbook_to_time_join_idx ON api.logbook USING btree (_to_time DESC, vessel_id) INCLUDE (id, _from_moorage_id, _from)	Optimizes: stays_view join on arrival timestamps Query pattern: JOIN logbook ON stays.arrived = logbook._to_time Key strategy: _to_time FIRST (join condition), vessel_id for RLS filtering INCLUDE clause: Minimal join data (id, origin moorage, location name) Why time-first: Enables efficient merge join when stays are sorted by arrived time Index-only scan: Avoids heap access for join operations Size: ~60 bytes per entry Used by: stays_view to correlate stays with preceding log entries Performance: Critical for stays_view (~50ms execution time) Note: Complements logbook_from_time_join_idx for bi-directional stay correlation
logbook_vessel_active_idx	CREATE INDEX logbook_vessel_active_idx ON api.logbook USING btree (vessel_id, active, _from_time DESC) WHERE (active = true)	Optimizes: Active/in-progress trip lookups Query pattern: SELECT * FROM logbook WHERE vessel_id=X AND active=true Key strategy: vessel_id + active flag for current trip tracking Partial index: Only active entries (typically 0-1 per vessel) Size: Very small (~40 bytes per active trip) Used by: Current trip status, real-time navigation displays Performance: Sub-millisecond lookups due to high selectivity
logbook_vessel_from_moorage_idx	CREATE INDEX logbook_vessel_from_moorage_idx ON api.logbook USING btree (vessel_id, _from_moorage_id, _from_time DESC) INCLUDE (id, active) WHERE (_from_moorage_id IS NOT NULL)	Optimizes: Departure aggregations by moorage (WHERE did we leave from?) Query pattern: SELECT COUNT(*) FROM logbook WHERE vessel_id=X AND _from_moorage_id=Y Key strategy: vessel_id + moorage_id for grouping, time DESC for ordering INCLUDE clause: id for distinct counts, active for filtering Partial index: Only entries with known departure moorages Size: ~40 bytes per entry Used by: Moorage statistics (departures count), visit frequency analysis
logbook_vessel_id_idx	CREATE INDEX logbook_vessel_id_idx ON api.logbook USING btree (vessel_id)
logbook_vessel_time_idx	CREATE INDEX logbook_vessel_time_idx ON api.logbook USING btree (vessel_id, _from_time DESC, _to_time DESC) INCLUDE (name, distance, duration)
logbook_vessel_timeline_idx	CREATE INDEX logbook_vessel_timeline_idx ON api.logbook USING btree (vessel_id, _from_time DESC) INCLUDE (id, name, _from, _to, _to_time) WHERE ((_to_time IS NOT NULL) AND (name IS NOT NULL))	Optimizes: api.logs_view - Full timeline of completed, named log entries Query pattern: SELECT * FROM logs WHERE vessel_id=X AND _to_time IS NOT NULL AND name IS NOT NULL ORDER BY _from_time DESC Key strategy: vessel_id first (RLS filter), then time DESC (sort order) INCLUDE clause: Enables index-only scans for basic timeline queries Partial index: Filters out incomplete entries (NULL _to_time) and unnamed entries Size: ~200-300 bytes per entry Used by: Main logs list view, timeline displays
logbook_vessel_to_moorage_idx	CREATE INDEX logbook_vessel_to_moorage_idx ON api.logbook USING btree (vessel_id, _to_moorage_id, _to_time DESC) INCLUDE (id, active) WHERE (_to_moorage_id IS NOT NULL)	Optimizes: Arrival aggregations by moorage (WHERE did we arrive at?) Query pattern: SELECT COUNT(*) FROM logbook WHERE vessel_id=X AND _to_moorage_id=Y Key strategy: vessel_id + moorage_id for grouping, time DESC for ordering INCLUDE clause: id for distinct counts, active for filtering Partial index: Only entries with known arrival moorages Size: ~40 bytes per entry Used by: Moorage statistics (arrivals count), visit frequency analysis
logbook_vessel_trip_idx	CREATE INDEX logbook_vessel_trip_idx ON api.logbook USING btree (vessel_id, _from_time DESC) INCLUDE (id, _to_time, distance, duration, avg_speed, max_speed, max_wind_speed) WHERE ((_to_time IS NOT NULL) AND (trip IS NOT NULL))	Optimizes: api.log_view - Single log entry detail with trip metrics Query pattern: SELECT * FROM log_view WHERE vessel_id=X AND id=Y (with trip data) Key strategy: vessel_id + time ordering for trip-based queries INCLUDE clause: Core trip metrics (distance, duration, speeds) for analytics Partial index: Only entries with completed trips (trip IS NOT NULL) Size: ~80 bytes per entry Used by: Log detail view, trip statistics, performance analytics
logbook_vessel_updated_at_idx	CREATE INDEX logbook_vessel_updated_at_idx ON api.logbook USING btree (vessel_id, updated_at DESC)	Supports incremental sync: WHERE vessel_id = $1 AND updated_at > $cursor

Triggers

Name	Definition	Comment
logbook_delete_trigger	CREATE TRIGGER logbook_delete_trigger BEFORE DELETE ON api.logbook FOR EACH ROW EXECUTE FUNCTION logbook_delete_trigger_fn()	BEFORE DELETE ON api.logbook run function public.logbook_delete_trigger_fn to delete reference and logbook_ext need to deleted.
logbook_moddatetime	CREATE TRIGGER logbook_moddatetime BEFORE UPDATE ON api.logbook FOR EACH ROW EXECUTE FUNCTION moddatetime(‘updated_at’)	Automatic update of updated_at on table modification
logbook_update_user_data_trigger	CREATE TRIGGER logbook_update_user_data_trigger BEFORE UPDATE ON api.logbook FOR EACH ROW EXECUTE FUNCTION update_tbl_userdata_added_at_trigger_fn()	BEFORE UPDATE ON api.logbook 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.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.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.moorages" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.logbook" }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.metrics" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"
"api.stays" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_id) ON DELETE RESTRICT"

"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.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.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.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