PostgSail

Description

PostgSail database documentation

Tables

Name	Columns	Comment	Type
api.eventlogs_view	6	Event logs view	VIEW
api.explore_view	3	explore_view web view	VIEW
api.log_view	21	Log web view	VIEW
api.log_view3	21	Log web materialized view with RLS applied	VIEW
api.logbook	42	Stores generated logbook	BASE TABLE
api.logs_geojson_view	4	List logs as geojson	VIEW
api.logs_view	11	Logs web view	VIEW
api.metadata	18	Stores metadata received from vessel, aka signalk plugin	BASE TABLE
api.metrics	11	Stores metrics from vessel	BASE TABLE
api.monitoring_humidity	3	Monitoring environment.%.humidity web view	VIEW
api.monitoring_live	23	Dynamic Monitoring web view	VIEW
api.monitoring_temperatures	3	Monitoring environment.%.temperature web view	VIEW
api.monitoring_view	18	Monitoring static web view	VIEW
api.monitoring_view2	2	Monitoring Last whatever data from json web view	VIEW
api.monitoring_view3	3	Monitoring Timeseries whatever data from json web view	VIEW
api.monitoring_voltage	3	Monitoring electrical.%.voltage web view	VIEW
api.moorage_view	18	Moorage details web view	VIEW
api.moorages	13	Stores generated moorages	BASE TABLE
api.moorages_geojson_view	3	List moorages as geojson	VIEW
api.moorages_stays_view	10	Moorages stay listing web view	VIEW
api.moorages_view	6	Moorages listing web view	VIEW
api.stats_logs_view	9	Statistics Logs web view	VIEW
api.stats_moorages_away_view	2	Statistics Moorages Time Spent Away web view	VIEW
api.stats_moorages_view	4	Statistics Moorages web view	VIEW
api.stay_explore_view	14	List moorages notes order by stays	VIEW
api.stay_view	18	Stay web view	VIEW
api.stays	14	Stores generated stays	BASE TABLE
api.stays_at	2	Stay Type	BASE TABLE
api.stays_geojson_view	1	List stays as geojson	VIEW
api.stays_view	16	Stays web view	VIEW
api.total_info_view	3	total_info_view web view	VIEW
api.versions_view	5	Expose as a table view app and system version to API	VIEW
api.vessels_view	9	Expose vessels listing to web api	VIEW
auth.accounts	11	users account table	BASE TABLE
auth.otp	4	Stores temporal otp code for up to 15 minutes	BASE TABLE
auth.vessels	7	vessels table link to accounts email user_id column	BASE TABLE
public.aistypes	2	aistypes AIS Ship Types, https://api.vesselfinder.com/docs/ref-aistypes.html	BASE TABLE
public.app_settings	2	application settings	BASE TABLE
public.badges	2	Badges descriptions	BASE TABLE
public.email_templates	5	email/message templates for notifications	BASE TABLE
public.geocoders	3	geo service nominatim url	BASE TABLE
public.iso3166	4	This is a complete list of all country ISO codes as described in the ISO 3166 international standard. Country Codes Alpha-2 & Alpha-3 https://www.iban.com/country-codes	BASE TABLE
public.mid	3	MMSI MID Codes (Maritime Mobile Service Identity) Filtered by Flag of Registration, https://www.marinevesseltraffic.com/2013/11/mmsi-mid-codes-by-flag.html	BASE TABLE
public.process_queue	6	process queue for async job	BASE TABLE

Stored procedures and functions

Name	ReturnType	Arguments	Type
api.counts_fn	jsonb		FUNCTION
api.delete_logbook_fn	bool	_id integer	FUNCTION
api.delete_trip_entry_fn	void	_id integer, update_string tstzspan	FUNCTION
api.email_fn	bool	token text	FUNCTION
api.export_logbook_geojson_linestring_trip_fn	jsonb	_id integer	FUNCTION
api.export_logbook_geojson_point_trip_fn	jsonb	_id integer	FUNCTION
api.export_logbook_geojson_trip_fn	jsonb	_id integer	FUNCTION
api.export_logbook_gpx_trip_fn	text/xml	_id integer	FUNCTION
api.export_logbook_kml_trip_fn	text/xml	_id integer	FUNCTION
api.export_logbook_metrics_trip_fn	jsonb	_id integer	FUNCTION
api.export_logbook_polar_csv_fn	text	_id integer	FUNCTION
api.export_logbooks_geojson_linestring_trips_fn	jsonb	start_log integer DEFAULT NULL::integer, end_log integer DEFAULT NULL::integer, start_date text DEFAULT NULL::text, end_date text DEFAULT NULL::text, OUT geojson jsonb	FUNCTION
api.export_logbooks_geojson_point_trips_fn	jsonb	start_log integer DEFAULT NULL::integer, end_log integer DEFAULT NULL::integer, start_date text DEFAULT NULL::text, end_date text DEFAULT NULL::text, OUT geojson jsonb	FUNCTION
api.export_logbooks_gpx_trips_fn	text/xml	start_log integer DEFAULT NULL::integer, end_log integer DEFAULT NULL::integer	FUNCTION
api.export_logbooks_kml_trips_fn	text/xml	start_log integer DEFAULT NULL::integer, end_log integer DEFAULT NULL::integer	FUNCTION
api.export_moorages_geojson_fn	jsonb	OUT geojson jsonb	FUNCTION
api.export_moorages_gpx_fn	text/xml		FUNCTION
api.export_moorages_kml_fn	text/xml		FUNCTION
api.export_stays_geojson_fn	jsonb	OUT geojson jsonb	FUNCTION
api.export_vessel_geojson_fn	jsonb	OUT geojson jsonb	FUNCTION
api.find_log_from_moorage_fn	jsonb	_id integer, OUT geojson jsonb	FUNCTION
api.find_log_to_moorage_fn	jsonb	_id integer, OUT geojson jsonb	FUNCTION
api.find_stay_from_moorage_fn	void	_id integer	FUNCTION
api.generate_otp_fn	text	email text	FUNCTION
api.ispublic_fn	bool	boat text, _type text, _id integer DEFAULT NULL::integer	FUNCTION
api.logbook_update_geojson_trip_fn	jsonb	_id integer	FUNCTION
api.login	jwt_token	email text, pass text	FUNCTION
api.logs_by_day_fn	jsonb	OUT charts jsonb	FUNCTION
api.logs_by_month_fn	jsonb	OUT charts jsonb	FUNCTION
api.merge_logbook_fn	void	id_start integer, id_end integer	FUNCTION
api.monitoring_history_fn	jsonb	time_interval text DEFAULT ‘24’::text, OUT history_metrics jsonb	FUNCTION
api.pushover_fn	bool	token text, pushover_user_key text	FUNCTION
api.pushover_subscribe_link_fn	json	OUT pushover_link json	FUNCTION
api.recover	bool	email text	FUNCTION
api.register_vessel	jwt_token	vessel_email text, vessel_mmsi text, vessel_name text	FUNCTION
api.reset	bool	pass text, token text, uuid text	FUNCTION
api.settings_fn	json	OUT settings json	FUNCTION
api.signup	jwt_token	email text, pass text, firstname text, lastname text	FUNCTION
api.stats_fn	jsonb	start_date text DEFAULT NULL::text, end_date text DEFAULT NULL::text, OUT stats jsonb	FUNCTION
api.stats_logs_fn	jsonb	start_date text DEFAULT NULL::text, end_date text DEFAULT NULL::text, OUT stats jsonb	FUNCTION
api.stats_stays_fn	json	start_date text DEFAULT NULL::text, end_date text DEFAULT NULL::text, OUT stats json	FUNCTION
api.status_fn	jsonb	OUT status jsonb	FUNCTION
api.telegram	jwt_token	user_id bigint, email text DEFAULT NULL::text	FUNCTION
api.telegram_fn	bool	token text, telegram_obj text	FUNCTION
api.telegram_otp_fn	text	email text, OUT otp_code text	FUNCTION
api.update_logbook_observations_fn	bool	_id integer, observations text	FUNCTION
api.update_metadata_userdata_fn	bool	userdata text	FUNCTION
api.update_trip_notes_fn	void	_id integer, update_string ttext	FUNCTION
api.update_user_preferences_fn	bool	key text, value text	FUNCTION
api.versions_fn	json		FUNCTION
api.vessel_details_fn	jsonb		FUNCTION
api.vessel_extended_fn	jsonb		FUNCTION
api.vessel_fn	jsonb	OUT vessel jsonb	FUNCTION
auth.check_role_exists	trigger		FUNCTION
auth.encrypt_pass	trigger		FUNCTION
auth.telegram_session_exists_fn	bool	user_id bigint	FUNCTION
auth.telegram_user_exists_fn	bool	email text, user_id bigint	FUNCTION
auth.user_role	name	email text, pass text	FUNCTION
auth.verify_otp_fn	text	token text	FUNCTION
public.autodiscovery_config_fn	jsonb	input_json jsonb	FUNCTION
public.badges_geom_fn	void	logbook_id integer, logbook_time text	FUNCTION
public.badges_logbook_fn	void	logbook_id integer, logbook_time text	FUNCTION
public.badges_moorages_fn	void		FUNCTION
public.cron_alerts_fn	void		FUNCTION
public.cron_process_autodiscovery_fn	void		FUNCTION
public.cron_process_grafana_fn	void		FUNCTION
public.cron_process_monitor_offline_fn	void		FUNCTION
public.cron_process_monitor_online_fn	void		FUNCTION
public.cron_process_new_logbook_fn	void		FUNCTION
public.cron_process_new_notification_fn	void		FUNCTION
public.cron_process_new_stay_fn	void		FUNCTION
public.cron_process_post_logbook_fn	void		FUNCTION
public.cron_process_pre_logbook_fn	void		FUNCTION
public.cron_prune_otp_fn	void		FUNCTION
public.cron_windy_fn	void		FUNCTION
public.debug_trigger_fn	trigger		FUNCTION
public.delete_vessel_fn	jsonb	_vessel_id text	FUNCTION
public.dump_account_fn	bool	_email text, _vessel_id text	FUNCTION
public.export_logbook_polar_fn	record	_id integer	FUNCTION
public.generate_uid_fn	text	size integer	FUNCTION
public.geojson_py_fn	jsonb	original jsonb, geometry_type text	FUNCTION
public.get_app_settings_fn	jsonb	OUT app_settings jsonb	FUNCTION
public.get_app_url_fn	jsonb	OUT app_settings jsonb	FUNCTION
public.get_user_settings_from_vesselid_fn	jsonb	vesselid text, OUT user_settings jsonb	FUNCTION
public.grafana_py_fn	void	_v_name text, _v_id text, _u_email text, app jsonb	FUNCTION
public.has_vessel_fn	bool		FUNCTION
public.has_vessel_metadata_fn	bool		FUNCTION
public.logbook_active_geojson_fn	jsonb	OUT _track_geojson jsonb	FUNCTION
public.logbook_completed_trigger_fn	trigger		FUNCTION
public.logbook_delete_trigger_fn	trigger		FUNCTION
public.logbook_get_extra_json_fn	json	search text, OUT output_json json	FUNCTION
public.logbook_metrics_dwithin_fn	numeric	_start text, _end text, lgn double precision, lat double precision, OUT count_metric numeric	FUNCTION
public.logbook_metrics_timebucket_fn	bool	bucket_interval text, _id integer, _start timestamp with time zone, _end timestamp with time zone, OUT timebucket boolean	FUNCTION
public.logbook_timelapse_geojson_fn	void	_id integer	FUNCTION
public.logbook_update_avg_fn	record	_id integer, _start text, _end text, OUT avg_speed double precision, OUT max_speed double precision, OUT max_wind_speed double precision, OUT avg_wind_speed double precision, OUT count_metric integer	FUNCTION
public.logbook_update_extra_json_fn	json	_id integer, _start text, _end text, OUT _extra_json json	FUNCTION
public.logbook_update_geojson_fn	json	_id integer, _start text, _end text, OUT _track_geojson json	FUNCTION
public.logbook_update_geom_distance_fn	record	_id integer, _start text, _end text, OUT _track_geom geometry, OUT _track_distance double precision	FUNCTION
public.logbook_update_gpx_fn	xml	_id integer, _start text, _end text, OUT _track_gpx xml	FUNCTION
public.logbook_update_metrics_short_fn	record	total_entry integer, start_date timestamp with time zone, end_date timestamp with time zone	FUNCTION
public.logbook_update_metrics_timebucket_fn	record	total_entry integer, start_date timestamp with time zone, end_date timestamp with time zone	FUNCTION
public.metadata_autodiscovery_trigger_fn	trigger		FUNCTION
public.metadata_grafana_trigger_fn	trigger		FUNCTION
public.metadata_upsert_trigger_fn	trigger		FUNCTION
public.metrics_trigger_fn	trigger		FUNCTION
public.moorage_delete_trigger_fn	trigger		FUNCTION
public.moorage_update_trigger_fn	trigger		FUNCTION
public.new_account_entry_fn	trigger		FUNCTION
public.new_account_otp_validation_entry_fn	trigger		FUNCTION
public.new_vessel_entry_fn	trigger		FUNCTION
public.new_vessel_public_fn	trigger		FUNCTION
public.new_vessel_trim_fn	trigger		FUNCTION
public.overpass_py_fn	jsonb	lon numeric, lat numeric, retry boolean DEFAULT false, OUT geo jsonb	FUNCTION
public.process_account_otp_validation_queue_fn	void	_email text	FUNCTION
public.process_account_queue_fn	void	_email text	FUNCTION
public.process_lat_lon_fn	record	lon numeric, lat numeric, OUT moorage_id integer, OUT moorage_type integer, OUT moorage_name text, OUT moorage_country text	FUNCTION
public.process_logbook_queue_fn	void	_id integer	FUNCTION
public.process_moorage_queue_fn	void	_id integer	FUNCTION
public.process_notification_queue_fn	void	_email text, message_type text	FUNCTION
public.process_post_logbook_fn	void	_id integer	FUNCTION
public.process_pre_logbook_fn	void	_id integer	FUNCTION
public.process_stay_queue_fn	void	_id integer	FUNCTION
public.process_vessel_queue_fn	void	_email text	FUNCTION
public.reverse_geocode_py_fn	jsonb	geocoder text, lon numeric, lat numeric, OUT geo jsonb	FUNCTION
public.reverse_geoip_py_fn	jsonb	_ip text	FUNCTION
public.send_email_py_fn	void	email_type text, _user jsonb, app jsonb	FUNCTION
public.send_notification_fn	void	email_type text, user_settings jsonb	FUNCTION
public.send_pushover_py_fn	void	message_type text, _user jsonb, app jsonb	FUNCTION
public.send_telegram_py_fn	void	message_type text, _user jsonb, app jsonb	FUNCTION
public.set_vessel_settings_from_vesselid_fn	jsonb	vesselid text, OUT vessel_settings jsonb	FUNCTION
public.split_logbook_by24h_fn	record	_id integer	FUNCTION
public.split_logbook_by24h_geojson_fn	jsonb	_id integer	FUNCTION
public.stay_active_geojson_fn	jsonb	_time timestamp with time zone DEFAULT now(), OUT _track_geojson jsonb	FUNCTION
public.stay_completed_trigger_fn	trigger		FUNCTION
public.stay_delete_trigger_fn	trigger		FUNCTION
public.stay_in_progress_fn	int4	_vessel_id text	FUNCTION
public.trip_in_progress_fn	int4	_vessel_id text	FUNCTION
public.update_logbook_with_geojson_trigger_fn	trigger		FUNCTION
public.update_metadata_configuration_trigger_fn	trigger		FUNCTION
public.update_metadata_userdata_added_at_trigger_fn	trigger		FUNCTION
public.update_tbl_userdata_added_at_trigger_fn	trigger		FUNCTION
public.urlencode_py_fn	text	uri text	FUNCTION
public.urlescape_py_fn	text	original text	FUNCTION
public.windy_pws_py_fn	jsonb	metric jsonb, _user jsonb, app jsonb	FUNCTION

Enums

Name	Values
public.public_type	public_logs, public_logs_list, public_monitoring, public_stats, public_timelapse
public.status_type	anchored, moored, motoring, sailing

Relations

erDiagram

"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.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.moorages" }o--|| "api.metadata" : "FOREIGN KEY (vessel_id) REFERENCES api.metadata(vessel_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.moorages" : "FOREIGN KEY (moorage_id) REFERENCES api.moorages(id) ON DELETE RESTRICT"
"api.stays" }o--o| "api.stays_at" : "FOREIGN KEY (stay_code) REFERENCES api.stays_at(stay_code) ON DELETE RESTRICT"
"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.eventlogs_view" {
  text channel ""
  integer id ""
  text payload ""
  timestamp_with_time_zone processed ""
  text ref_id ""
  timestamp_with_time_zone stored ""
}
"api.explore_view" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}
"api.log_view" {
  double_precision avg_speed ""
  numeric distance ""
  interval duration ""
  timestamp_with_time_zone ended ""
  jsonb extra ""
  text from ""
  integer from_moorage_id ""
  jsonb geojson ""
  boolean has_images ""
  integer id ""
  jsonb images ""
  double_precision max_speed ""
  double_precision max_wind_speed ""
  text name ""
  text notes ""
  jsonb observations ""
  jsonb polar ""
  timestamp_with_time_zone started ""
  jsonb tags ""
  text to ""
  integer to_moorage_id ""
}
"api.log_view3" {
  double_precision avg_speed ""
  numeric distance ""
  interval duration ""
  timestamp_with_time_zone ended ""
  jsonb extra ""
  text from ""
  integer from_moorage_id ""
  jsonb geojson ""
  boolean has_images ""
  integer id ""
  jsonb images ""
  double_precision max_speed ""
  double_precision max_wind_speed ""
  text name ""
  text notes ""
  jsonb observations ""
  jsonb polar ""
  timestamp_with_time_zone started ""
  jsonb tags ""
  text to ""
  integer to_moorage_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"
  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"
  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.logs_geojson_view" {
  jsonb geojson ""
  integer id ""
  text name ""
  timestamp_with_time_zone starttimestamp ""
}
"api.logs_view" {
  integer _from_moorage_id ""
  integer _to_moorage_id ""
  numeric distance ""
  interval duration ""
  timestamp_with_time_zone ended ""
  text from ""
  integer id ""
  text name ""
  timestamp_with_time_zone started ""
  jsonb tags ""
  text to ""
}
"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"
}
"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.monitoring_humidity" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}
"api.monitoring_live" {
  double_precision batterycharge ""
  double_precision batteryvoltage ""
  jsonb data ""
  double_precision depth ""
  jsonb geojson ""
  double_precision insidehumidity ""
  double_precision insidepressure ""
  double_precision insidetemperature ""
  jsonb live ""
  text name ""
  boolean offline ""
  double_precision outsidehumidity ""
  double_precision outsidepressure ""
  json outsidepressurehistory ""
  double_precision outsidetemperature ""
  double_precision solarpower ""
  double_precision solarvoltage ""
  text status ""
  double_precision tanklevel ""
  timestamp_with_time_zone time ""
  double_precision watertemperature ""
  double_precision winddirectiontrue ""
  double_precision windspeedoverground ""
}
"api.monitoring_temperatures" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}
"api.monitoring_view" {
  jsonb batterycharge ""
  jsonb batteryvoltage ""
  jsonb depth ""
  jsonb geojson ""
  jsonb insidehumidity ""
  jsonb insidepressure ""
  jsonb insidetemperature ""
  jsonb live ""
  text name ""
  boolean offline ""
  jsonb outsidehumidity ""
  jsonb outsidepressure ""
  jsonb outsidetemperature ""
  text status ""
  timestamp_with_time_zone time ""
  jsonb watertemperature ""
  jsonb winddirectiontrue ""
  jsonb windspeedoverground ""
}
"api.monitoring_view2" {
  text key ""
  jsonb value ""
}
"api.monitoring_view3" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}
"api.monitoring_voltage" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}
"api.moorage_view" {
  text default_stay ""
  integer default_stay_id ""
  geography_Point_4326_ geog ""
  boolean has_images ""
  boolean home ""
  integer id ""
  jsonb images ""
  double_precision latitude ""
  bigint logs_count ""
  double_precision longitude ""
  text name ""
  text notes ""
  timestamp_with_time_zone stay_first_seen ""
  integer stay_first_seen_id ""
  timestamp_with_time_zone stay_last_seen ""
  integer stay_last_seen_id ""
  bigint stays_count ""
  interval stays_sum_duration ""
}
"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"
  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.moorages_geojson_view" {
  jsonb geojson ""
  integer id ""
  text name ""
}
"api.moorages_stays_view" {
  integer _from_id ""
  text _from_name ""
  timestamp_with_time_zone _from_time ""
  integer _to_id ""
  text _to_name ""
  timestamp_with_time_zone _to_time ""
  interval duration ""
  integer id ""
  text name ""
  integer stay_code ""
}
"api.moorages_view" {
  bigint arrivals_departures ""
  text default_stay ""
  integer default_stay_id ""
  integer id ""
  text moorage ""
  interval total_duration ""
}
"api.stats_logs_view" {
  timestamp_with_time_zone first ""
  timestamp_with_time_zone last ""
  text longest_nonstop_sail ""
  double_precision max_speed ""
  double_precision max_wind_speed ""
  text name ""
  bigint number_of_log_entries ""
  numeric total_distance ""
  interval total_time_underway ""
}
"api.stats_moorages_away_view" {
  text description ""
  interval time_spent_away_by ""
}
"api.stats_moorages_view" {
  bigint home_ports ""
  interval time_spent_at_home_port(s) ""
  interval time_spent_away ""
  bigint unique_moorages ""
}
"api.stay_explore_view" {
  timestamp_with_time_zone arrived ""
  boolean has_images ""
  integer id ""
  jsonb images ""
  double_precision latitude ""
  double_precision longitude ""
  integer moorage_id ""
  text moorage_name ""
  text moorage_notes ""
  text name ""
  integer stay_code ""
  integer stay_id ""
  text stay_name ""
  text stay_notes ""
}
"api.stay_view" {
  timestamp_with_time_zone arrived ""
  integer arrived_from_moorage_id ""
  text arrived_from_moorage_name ""
  integer arrived_log_id ""
  timestamp_with_time_zone departed ""
  integer departed_log_id ""
  integer departed_to_moorage_id ""
  text departed_to_moorage_name ""
  interval duration ""
  boolean has_images ""
  integer id ""
  jsonb images ""
  text moorage ""
  integer moorage_id ""
  text name ""
  text notes ""
  text stayed_at ""
  integer stayed_at_id ""
}
"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"
  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.stays_geojson_view" {
  jsonb geojson ""
}
"api.stays_view" {
  timestamp_with_time_zone arrived ""
  integer arrived_from_moorage_id ""
  text arrived_from_moorage_name ""
  integer arrived_log_id ""
  timestamp_with_time_zone departed ""
  integer departed_log_id ""
  integer departed_to_moorage_id ""
  text departed_to_moorage_name ""
  interval duration ""
  integer id ""
  text moorage ""
  integer moorage_id ""
  text name ""
  text notes ""
  text stayed_at ""
  integer stayed_at_id ""
}
"api.total_info_view" {
  bigint logs ""
  bigint moorages ""
  bigint stays ""
}
"api.versions_view" {
  text api_version ""
  text postgis ""
  text postgrest ""
  text sys_version ""
  text timescaledb ""
}
"api.vessels_view" {
  timestamp_with_time_zone created_at ""
  interval duration ""
  interval duration_last_metrics ""
  text last_contact ""
  text last_metrics ""
  boolean metrics_offline ""
  numeric mmsi ""
  text name ""
  boolean offline ""
}
"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 ""
}
"public.aistypes" {
  text description ""
  numeric id ""
}
"public.app_settings" {
  text name "application settings name key"
  text value "application settings value"
}
"public.badges" {
  text description ""
  text name ""
}
"public.email_templates" {
  text email_content ""
  text email_subject ""
  text name ""
  text pushover_message ""
  text pushover_title ""
}
"public.geocoders" {
  text name ""
  text reverse_url ""
  text url ""
}
"public.iso3166" {
  text alpha_2 ""
  text alpha_3 ""
  text country ""
  integer id ""
}
"public.mid" {
  text country ""
  integer country_id ""
  numeric id ""
}
"public.process_queue" {
  text channel ""
  integer id ""
  text payload ""
  timestamp_with_time_zone processed ""
  text ref_id "either user_id or vessel_id"
  timestamp_with_time_zone stored ""
}

Generated by tbls

Keyboard shortcuts

Postgsail Documentation

PostgSail

Description

Tables

Stored procedures and functions

Enums

Relations