Introduction

View Demo . Report Bug . Request Feature . Website . Sponsors . Discord . DeepWiki

signalk-postgsail:

postgsail-backend:

postgsail-frontend:

postgsail-telegram-bot:

postgsail-mcp-server:

PostgSail is an open-source maritime vessel tracking and data management system that automatically collects, processes, and analyzes vessel telemetry data from SignalK-compatible sources.
PostgSail is an open-source platform designed for effortless logging and historical tracking of marine vessel activity. It automatically logs voyages covering trips, anchorages, dockings, and moorages without requiring manual intervention to start or stop logging.
It's essentially a complete "digital logbook" that automatically tracks everything about your sailing adventures, from navigation to weather to vessel systems, all while providing a modern web interface for access and management.

PostgSail offers a rich set of capabilities tailored to maritime use:

• Automatic logging of voyages with details like boat speed, heading, wind speed, etc.
• Create timelapse videos of trips, with or without time control.
• Add custom notes to logs.
• Export logs in various formats: CSV, GPX, GeoJSON, KML, PNG, MP4.
• Trip statistics and aggregations: longest voyage, time spent anchored, home ports, etc.
• Visualize moorages on a global map, showing incoming/outgoing voyages.
• Remote monitoring of metrics—position, depth, wind, temperature, battery, etc.
• Trend history and alert notifications (via email, PushOver, or Telegram) for conditions like low voltage or low fuel.
• Support for offline and low-bandwidth operation.
• Integration with monitoring dashboards using Grafana and related tools.

Deployment

PostgSail supports both cloud-hosted and self-hosted deployment options:

Cloud-based SaaS iot.openplotter.cloud

• Managed PostgreSQL
• Managed infrastructure
• Automatic scaling and backup
• Free for single vessel use
• Multi-vessel commercial plans available
• Generate video(s) of trip(s)
• Generate image(s) of trip(s)
• AI assistant
• Image gallery support

Self-Hosted (Infrastructure you manage)

• Docker Compose orchestration
• Full control over data and configuration
• Requires Docker and basic Linux administration
• Requires PostgreSQL administration
• Custom integration capabilities

Cloud-hosted PostgSail

Remove the hassle of running PostgSail yourself. Here you can skip the technical setup, the maintenance work and server costs by getting PostgSail on our reliable and secure PostgSail Cloud. Register and try for free at iot.openplotter.cloud.

PostgSail Cloud is Open Source and free for personal use with a single vessel. If wish to manage multiple boats contact us.

PostgSail is free to use, but is not free to make or host. The stability and accuracy of PostgSail depends on its volunteers and donations from its users. Please consider sponsoring PostgSail.

Infrastructure you manage

Self host postgSail where you want and how you want. There are no restrictions, you’re in full control. Install Guide

PostgSail is free to use, but is not free to make or host. The stability and accuracy of PostgSail depends on its volunteers and donations from its users. Please consider sponsoring PostgSail.

Self-Hosting the PostgSail Control Plane

Self-hosting PostgSail means running your own instance of the PostgSail Control Plane.

When you self-host PostgSail, you’re deploying:

• Database (backend) - PostgreSQL for storing state and metadata
• API Server - REST APIs using PostgREST
• Dashboard (frontend) - Web UI for monitoring and visualize logs,stays,moorages

Setup & Deployment

Architecture

Efficient, simple and scalable architecture.

flowchart TD
    %% User
    user([User])

    %% Frontend and Auth
    user -->|HTTPS| frontend[Frontend VueJS]
    user -->|HTTPS| grafana[Grafana]

    %% Web Server Layer
    frontend -->|Apache / nginx| postgrest[PostgREST]

    %% Database
    postgrest --> |SQL|postgres[(PostgreSQL / TimescaleDB / PostGIS / MobilityDB)]
    grafana --> |SQL|postgres

    %% External System
    postgres -->|HTTPS|telegram[Telegram API]
    postgres -->|HTTPS|pushover[Pushover API]
    postgres -->|SMTP|email[Email]
    postgres -->|HTTPS|overpass[Overpass API]
    postgres -->|HTTPS|nominatim[Nominatim Geocoding]

    %% Boat (External Client)
    vessel([Boat])
    vessel -->|HTTPS| postgrest

    %% MCP (External Client)
    mcp([AI-Agent])
    mcp -->|HTTPS| postgrest

    %% Telegram Bot (External Client)
    telegram_bot([Telegram Bot])
    telegram_bot -->|HTTPS| postgrest

    %% Groupings
    subgraph Clients
        user
        vessel
        mcp
        telegram_bot
    end

    subgraph PostgSail
        frontend
        grafana
        postgrest
        postgres
    end

    subgraph "Notification endpoint"
        telegram
        pushover
        email
    end

    subgraph "Data endpoint"
        overpass
        nominatim
    end

For more clarity and visibility the complete Entity-Relationship Diagram (ERD) is export as Mermaid file.

Using PostgSail

On-premise (self-hosted)

This deployment needs the docker application to be installed and running. Check this tutorial.

Docker run pre packaged application (aka images) which can be retrieved as sources (Dockerfile and resources) to build or already built from registries (private or public).

PostgSail depends heavily on PostgreSQL. Check this tutorial.

pre-deploy configuration

To get these running, copy .env.example and rename to .env then set the value accordingly.

# cp .env.example .env

# nano .env

Notice, that PGRST_JWT_SECRET must be at least 32 characters long.

$ cat /dev/urandom | LC_ALL=C tr -dc 'a-zA-Z0-9' | fold -w 42 | head -n 1

PGSAIL_APP_URL is the URL you connect to from your browser.

PGSAIL_API_URL is the URL where PGSAIL_APP_URL connect to.

PGRST_DB_URI is the URI where the PGSAIL_API_URL connect to.

For more details check the Environment Variables.

To summarize:

flowchart LR
    subgraph frontend
        direction TB
        A(PGSAIL_APP_URL)
        B(PGSAIL_API_URL)
    end
    subgraph backend
        direction TB
        B(PGSAIL_API_URL) -- SQL --> C(PGRST_DB_URI)
    end
    %% ^ These subgraphs are identical, except for the links to them:

    %% Link *to* subgraph1: subgraph1 direction is maintained

    User -- HTTP --> A
    User -- HTTP --> B
    %% Link *within* subgraph2:
    %% subgraph2 inherits the direction of the top-level graph (LR)

    Boat -- HTTP --> B

Deploy

There is two compose files used. You can update the default settings by editing docker-compose.yml and docker-compose.dev.yml to your need.

Now let’s initialize the database.

Step 1. Initialize database

First let’s import the SQL schema, execute:

$ docker compose up db

Step 2. Start backend (db, api)

Then launch the full backend stack (db, api), execute:

$ docker compose up db api

The API should be accessible via port HTTP/3000. The database should be accessible via port TCP/5432.

You can connect to the database via a web gui like pgadmin or you can use a client dbeaver.

$ docker compose -f docker-compose.yml -f docker-compose.dev.yml pgadmin

Then connect to the web UI on port HTTP/5050.

Step 3. Start frontend (web)

Last build and launch the web frontend, execute:

docker compose build web
docker compose up web

The first step can take some time as it will first run a build to generate the static website based on your settings.

The frontend is a SPA (Single-Page Application). With SPA, the server provides the user with an empty HTML page and Javascript. The latter is where the magic happens. When the browser receives the HTML + Javascript, it loads the Javascript. Once loaded, the JS takes place and, through a set of operations in the DOM, renders the necessary components to the page. The routing is then handled by the browser itself, not hitting the server.

The frontend should be accessible via port HTTP/8080.

Users are collaborating on two installation guide:

• Self-hosted-installation-guide
• Self-hosted-installation-guide on AWS EC2
• Self-hosted-installation-guide

SQL Configuration

Check and update your postgsail settings via SQL in the table app_settings:

SELECT * FROM app_settings;

UPDATE app_settings
    SET
        value = 'new_value'
    WHERE name = 'app.email_server';

As it is all about SQL, Read more about the database to configure your instance and explore your data.

Ingest data

Next, to ingest data from signalk, you need to install signalk-postgsail plugin on your signalk server instance.

Also, if you like, you can import saillogger data using the postgsail helpers, postgsail-helpers.

You might want to import your influxdb1 data as well, outflux. For InfluxDB 2.x and 3.x. You will need to enable the 1.x APIs to use them. Consult the InfluxDB documentation for more details.

Last, if you like, you can import the sample data from Signalk NMEA Plaka by running the tests. If everything goes well all tests pass successfully and you should receive a few notifications by email or PushOver or Telegram. End-to-End (E2E) Testing.

$ docker-compose up tests

API Documentation

The OpenAPI description output depends on the permissions of the role that is contained in the JWT role claim.

Other applications can also use the PostgSAIL API.

API anonymous:

$ curl http://localhost:3000/

API user_role:

$ curl http://localhost:3000/ -H 'Authorization: Bearer my_token_from_login_or_signup_fn'

API vessel_role:

$ curl http://localhost:3000/ -H 'Authorization: Bearer my_token_from_register_vessel_fn'

API main workflow

Check the End-to-End (E2E) test sample.

Docker dependencies

docker compose is used to start environment dependencies. Dependencies consist of 3 containers:

• timescaledb-postgis alias db, PostgreSQL with TimescaleDB extension along with the PostGIS extension.
• postgrest alias api, Standalone web server that turns your PostgreSQL database directly into a RESTful API.
• grafana alias app, visualize and monitor your data

Optional docker images

• pgAdmin, web UI to monitor and manage multiple PostgreSQL
• Swagger, web UI to visualize documentation from PostgREST

docker-compose -f docker-compose-optional.yml up

Environment Variables

Environment Variables

You need to configure PostgSail using environment variables. Copy the example file.

cp .env.example .env

Environment Variables Documentation

This document describes all environment variables used in the PostgSail system, organized by category and usage context.

Database Configuration

API Configuration

Frontend Configuration

Grafana Configuration

External Integrations

Push Notifications

Development Tools

Additional Services

Additional parameters are directly set in the docker-compose.yml

SignalK PostgSail plugin

SignalK server plugin to send all self SignalK numeric data and navigation entry to a PostgSail server.

SignalK PostgSail plugin

Send, monitor, alert, observe all numeric values & positions & status to a self-hosted or cloud instances of PostgSail (PostgreSQL, Grafana).

Dependencies

signalk-autostate by @meri-imperiumi. Used to determine the vessel’s state based on sensor values, and updates the navigation.state value accordingly.

The signalk-derived-data and signalk-path-mapper plugins are both useful to remap available data to the required canonical paths.

Source data

The signalk-derived-data and signalk-path-mapper plugins are both useful to remap available data to the required canonical paths.

Hosting Environment–specific Guides

Docker

Connect to the server

% ssh root@my.server.com

Clone the git repo

% git clone https://github.com/xbgmsharp/postgsail
Cloning into 'postgsail'...
...

Edit the configuration

% cd postgsail
% cp .env.example .env
% cat /dev/urandom | LC_ALL=C tr -dc 'a-zA-Z0-9' | fold -w 42 | head -n 1
..
% nano .env

Install Docker

From https://docs.docker.com/engine/install/ubuntu/

% apt-get update
...
% apt-get install -y ca-certificates curl
...
% install -m 0755 -d /etc/apt/keyrings
% curl -fsSL https://download.docker.com/linux/ubuntu/gpg -o /etc/apt/keyrings/docker.asc
% chmod a+r /etc/apt/keyrings/docker.asc
% echo \
  "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.asc] https://download.docker.com/linux/ubuntu \
  $(. /etc/os-release && echo "$VERSION_CODENAME") stable" | \
  sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
% apt-get update
...
% apt-get install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin
...

Init the database

% docker compose up db
...
Gracefully stopping... (press Ctrl+C again to force)
[+] Stopping 1/1
 ✔ Container db  Stopped

Start the db with the api

% docker compose pull api
...
% docker compose up -d db api

Checks

Making sure it works.

% telnet localhost 5432
...
telnet> quit
Connection closed.
% curl localhost:3000
...
% docker ps
...
% docker logs api
...

Run the web instance

% docker compose -f docker-compose.yml -f docker-compose.dev.yml build web (be patient)
...

% docker compose -f docker-compose.yml -f docker-compose.dev.yml up web (be patient)
...
web | 
web  |   ➜  Local:   http://localhost:8080/
web  |   ➜  Network: http://172.18.0.4:8080/

Docker Compose

Running with Docker Compose

You can use example docker-compose.yml file as a reference

services:
  martin:
    image: ghcr.io/maplibre/martin:v0.16.0
    restart: unless-stopped
    ports:
      - "3000:3000"
    environment:
      - DATABASE_URL=postgresql://postgres:password@db/db
    depends_on:
      - db

  db:
    image: postgis/postgis:17-3.5-alpine
    restart: unless-stopped
    environment:
      - POSTGRES_DB=db
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
    volumes:
      # persist PostgreSQL data in a local directory outside of the docker container
      - ./pg_data:/var/lib/postgresql/data

First, you need to start db service

docker compose up -d db

Then, after db service is ready to accept connections, you can start martin

docker compose up -d martin

By default, Martin will be available at localhost:3000

Official Docker image includes a HEALTHCHECK instruction which will be used by Docker Compose. Note that Compose won’t restart unhealthy containers. To monitor and restart unhealthy containers you can use Docker Autoheal.

AWS

Self AWS cloud hosted setup example

In this guide we install, setup and run a postgsail project on an AWS instance in the cloud.

On AWS Console

Launch an instance on AWS EC2 With the following settings:

• Ubuntu

• Instance type: t2.small

• Create a new key pair:

  • key pair type: RSA
  • Private key file format: .pem

• The key file is stored for later use

• Allow SSH traffic from: Anywhere

• Allow HTTPS traffic from the internet

• Allow HTTP traffic from the internet

Configure storage: The standard storage of 8GiB is too small so change this to 16GiB.

Create a new security group

• Go to: EC2>Security groups>Create security group Add inbound rules for the following ports:443, 8080, 80, 3000, 5432, 22, 5050
• Go to your instance>select your instance>Actions>security>change security group
• And add the correct security group to the instance.

Connect to instance with SSH

• Copy the key file in your default SSH configuration file location (the one VSCode will use)
• In terminal, go to the folder and run this command to ensure your key is not publicly viewable:

chmod 600 "privatekey.pem"

We are using VSCode to connect to the instance:

• Install the Remote - SSH Extension for VSCode
• Open the Command Palette (Ctrl+Shift+P) and type Remote-SSH: Add New SSH Host:

ssh -i "privatekey.pem" ubuntu@ec2-111-22-33-44.eu-west-1.compute.amazonaws.com

When prompted, select the default SSH configuration file location. Open the config file and add the location:

xIdentityFile ~/.ssh/privatekey.pem

Install Docker on your instance

To install Docker on your new EC2 Ubuntu instance via SSH, follow these steps:

Update your package list:

sudo apt-get update

Install required dependencies:

sudo apt-get install apt-transport-https ca-certificates curl software-properties-common

Add Docker’s official GPG key:

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg

Add Docker’s official repository:

echo "deb [arch=amd64 signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

Update the package list again:

sudo apt-get update

Install Docker:

sudo apt-get install docker-ce docker-ce-cli containerd.io

Verify Docker installation:

sudo docker --version

Add your user to the docker group to run Docker without sudo:

sudo usermod -aG docker ubuntu

Then, log out and back in or use the following to apply the changes:

newgrp docker

Now you can follow the Postgsail process.

Reverse Proxies

PostgSail can run without a reverse proxy. Hosting PostgSail behind a reverse proxy can help with security, scalability, and flexibility.

Doing so has a few downsides:

• PostgSail does not support HTTPS connections (TLS termination).
• We do not check HOST-headers - we just serve on a port. This means anybody can point their dns record to your server and serve to all requests going to the port Martin is running on. Using a reverse proxy makes this abuse obvious.
• Martin only supports a simple in-memory caching. If you need more advanced caching options, you can use a reverse proxy like Nginx, Varnish, or Apache with custom rules. For example, you may choose to only cache zoom 0..10.
• You may need to host more than just tiles at a single domain name.

NGINX

Using with NGINX

You should run PostgSail behind NGINX proxy, so you can cache frequently accessed tiles and reduce unnecessary pressure on the database. Here is an example docker-compose.yml file that runs PostgSail with NGINX and PostgreSQL.

version: '3'

services:
  nginx:
    image: nginx:alpine
    restart: unless-stopped
    ports:
      - "80:80"
    volumes:
      - ./cache:/var/cache/nginx
      - ./nginx.conf:/etc/nginx/nginx.conf:ro
    depends_on:
      - martin

  martin:
    image: maplibre/martin:v0.7.0
    restart: unless-stopped
    environment:
      - DATABASE_URL=postgresql://postgres:password@db/db
    depends_on:
      - db

  db:
    image: postgis/postgis:14-3.3-alpine
    restart: unless-stopped
    environment:
      - POSTGRES_DB=db
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
    volumes:
      - ./pg_data:/var/lib/postgresql/data

You can find an example NGINX configuration file here.

Rewriting URLs

If you are running Martin behind NGINX proxy, you may want to rewrite the request URL to properly handle tile URLs in TileJSON.

location ~ /tiles/(?<fwd_path>.*) {
    proxy_set_header  X-Rewrite-URL $uri;
    proxy_set_header  X-Forwarded-Host $host:$server_port;
    proxy_set_header  X-Forwarded-Proto $scheme;
    proxy_redirect    off;

    proxy_pass        http://martin:3000/$fwd_path$is_args$args;
}

Caching tiles

You can also use NGINX to cache tiles. In the example, the maximum cache size is set to 10GB, and caching time is set to 1 hour for responses with codes 200, 204, and 302 and 1 minute for responses with code 404.

http {
  ...
  proxy_cache_path  /var/cache/nginx/
                    levels=1:2
                    max_size=10g
                    use_temp_path=off
                    keys_zone=tiles_cache:10m;

  server {
    ...
    location ~ /tiles/(?<fwd_path>.*) {
        proxy_set_header        X-Rewrite-URL $uri;
        proxy_set_header        X-Forwarded-Host $host:$server_port;
        proxy_set_header        X-Forwarded-Proto $scheme;
        proxy_redirect          off;

        proxy_cache             tiles_cache;
        proxy_cache_lock        on;
        proxy_cache_revalidate  on;

        # Set caching time for responses
        proxy_cache_valid       200 204 302 1h;
        proxy_cache_valid       404 1m;

        proxy_cache_use_stale   error timeout http_500 http_502 http_503 http_504;
        add_header              X-Cache-Status $upstream_cache_status;

        proxy_pass              http://martin:3000/$fwd_path$is_args$args;
    }
  }
}

You can find an example NGINX configuration file here.

Apache

Using with Apache

You should run PostgSail behind Apache “kind of” proxy, so you can use HTTPs with it. Here is an example of the configuration file that runs PostgSail with Apache.

First you have to setup a virtual host that is working on the port 443.

Enable necessary modules

Ensure the required modules are enabled:

sudo a2enmod proxy
sudo a2enmod proxy_http
sudo a2enmod headers
sudo a2enmod rewrite

Modify your VHOST configuration

Open your VHOST configuration file for the domaine you’re using, mydomain.tld :

sudo nano /etc/apache2/sites-available/mydomain.tld.conf

Update the configuration

<VirtualHost *:443>
    ServerName mydomain.tld
    ServerAdmin webmaster@localhost
    DocumentRoot /var/www/mydomain
    ProxyPreserveHost On

    RewriteEngine on
    RewriteCond %{REQUEST_URI} ^/tiles/(.*)$
    RewriteRule ^/tiles/(.*)$ http://localhost:3000/tiles/$1 [P,L]

    <IfModule mod_headers.c>
        RequestHeader set X-Forwarded-Proto "https"
    </IfModule>

    ProxyPass / http://localhost:3000/
    ProxyPassReverse / http://localhost:3000/
</VirtualHost>

Check Configuration: Verify the Apache configuration for syntax errors

sudo apache2ctl configtest

Restart Apache: If the configuration is correct, restart Apache to apply the changes

sudo systemctl restart apache2

Upgrade

Containers

docker compose pull

Self hosted upgrade guide

In this guide we are updating a self hosted installation version 0.7.2 to version 0.9.3. When updating from or to other versions principle remain the same.

The installation we are upgrading was installed in April 2024 using the installation instructions found on the pgsail github site. Platform is an Ubuntu 22.04 Virtual Machine. Before the upgrade, around 120 trips were logged. Needless to say we don’t want to loose our data.

Unfortunately, there is no automatic update path available, this may change but for now we had to follow the general update instructions.

General update instructions

• Stop the containers.
• Make a backup
• Update the containers.
• Update possible database extensions.
• Run database migrations.
• Additional data migration.
• Update SignalK client.

Let’s go

Tools used

In addition to the tools that are already installed as part of Ubuntu and PostgSail, I used DBeaver to examine the database from my Windows desktop.

https://dbeaver.io/download/

Make a backup

Start by making a backup of the database, the docker-compose.yml and .env files. Note that in my case the database was stored in a host folder, later versions are using a docker volume. To copy the database it necessary the containers are stopped.

cd postgsail
mkdir backup
docker compose stop
cp .env docker-compose.yml backup/
docker compose cp -a db:/var/lib/postgresql/data backup/db-data

Update the containers

Make a note of the last migration in the initdb folder, in my case this was 99_migrations_202404.sql. Because I used git clone, the migration file was a bit in between 0.7.1 and 0.7.2, therefore I decided 99_migrations_202404.sql was the first migration to run.

Remove the containers:

docker compose down

Get the latest PostgSail from github, we checkout a specific tag to ensure we have a stable release version. If you installed it from a binary release, just update from the latest binary release.

git pull remote main
git fetch --all --tags
git checkout tags/v0.9.3

Note: switching to 'tags/v0.9.3'.

You are in 'detached HEAD' state. You can look around, make experimental
changes and commit them, and you can discard any commits you make in this
state without impacting any branches by switching back to a branch.

If you want to create a new branch to retain commits you create, you may
do so (now or later) by using -c with the switch command. Example:

  git switch -c <new-branch-name>

Or undo this operation with:

  git switch -

Turn off this advice by setting config variable advice.detachedHead to false

HEAD is now at 12e4baf Release PostgSail 0.9.3

Ensure new docker-compose.yml file matches your database folder or volume setting, adjust as needed.

Get the latest containers.

docker compose pull

Update possible extensions

Start database container.

docker compose up -d db

Execute psql shell in database container.

docker compose exec db sh
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"
\c signalk;

Check extensions which can be updated, be sure to run from the signalk database:

SELECT name, default_version, installed_version FROM pg_available_extensions where default_version <> installed_version;

The postgis extension can be upgraded with this SQL query:

SELECT postgis_extensions_upgrade();

Updating the timescaledb requires running from a new session, use following commands (note the -X options, that is necessary):

docker compose exec db sh
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -X

Then run following SQL commands from the psql shell:

ALTER EXTENSION timescaledb UPDATE;
CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit;
ALTER EXTENSION timescaledb_toolkit UPDATE;

For others, to be checked. In my case, the postgis extension was essential.

Vacuum tables

It’s necessary to do VACUUM periodically, especially on frequently-updated tables. garbage-collect and optionally analyze a database

docker compose exec db sh
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"
\c signalk;

This operations may take some time and does require exclusive lock.

VACUUM (FULL, VERBOSE, ANALYZE, INDEX_CLEANUP) public.process_queue,public.app_settings,public.email_templates;
VACUUM (FULL, VERBOSE, ANALYZE, INDEX_CLEANUP) auth.accounts,auth.vessels,auth.otp;
VACUUM (FULL, VERBOSE, ANALYZE, INDEX_CLEANUP) api.logbook,api.stays,api.moorages,api.metadata,api.metrics;

Run database migrations

Then run the migrations, adjust start and end for first and last migration file to execute.

start=202404; end=202507; for f in $(ls ./docker-entrypoint-initdb.d/99_migrations_*.sql | sort); do s=$(basename "$f" | sed -E 's/^99_migrations_([0-9]{6})\.sql$/\1/'); if [[ "$s" < "$start" || "$s" > "$end" ]]; then continue; fi; echo "Running $f"; psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < "$f"; done

Or line by line

psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202404.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202405.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202406.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202407.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202408.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202409.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202410.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202411.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202412.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202501.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202504.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202505.sql
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" < ./docker-entrypoint-initdb.d/99_migrations_202507.sql

Now rebuild the web app.

docker compose build web

Maybe need to run 99env.sh - check.

Then we can start the other containers.

docker compose up -d

After everything is started, the web site should be accessible.

Additional data migration

Depending on the starting version, additional data migration may be needed. If the old trips are visible, but the routes are not, we need to run an SQL Script to re-calculate the trip metadata.

DO $$
declare
	-- Re calculate the trip metadata
    logbook_rec record;
    avg_rec record;
	t_rec record;
begin
    FOR logbook_rec IN
        SELECT *
        FROM api.logbook
        WHERE _from IS NOT NULL
            AND _to IS NOT NULL
            AND active IS FALSE
            AND trip IS NULL
            --AND trip_heading IS NULL
            --AND vessel_id = '06b6d311ccfe'
        ORDER BY id DESC
    LOOP
        -- Update logbook entry with the latest metric data and calculate data
        PERFORM set_config('vessel.id', logbook_rec.vessel_id, false);

        -- Calculate trip metadata
        avg_rec := logbook_update_avg_fn(logbook_rec.id, logbook_rec._from_time::TEXT, logbook_rec._to_time::TEXT);
        --UPDATE api.logbook
        --	SET extra = jsonb_recursive_merge(extra, jsonb_build_object('avg_wind_speed', avg_rec.avg_wind_speed))
        -- WHERE id = logbook_rec.id;
        if avg_rec.count_metric IS NULL OR avg_rec.count_metric = 0 then
            -- We don't have the orignal metrics, we should read the geojson
            continue; -- return current row of SELECT
        end if;

        -- mobilitydb, add spaciotemporal sequence
        -- reduce the numbers of metrics by skipping row or aggregate time-series
        -- By default the signalk plugin report one entry every minute.
        IF avg_rec.count_metric < 30 THEN -- if less ~20min trip we keep it all data
            t_rec := logbook_update_metrics_short_fn(avg_rec.count_metric, logbook_rec._from_time, logbook_rec._to_time);
        ELSIF avg_rec.count_metric < 2000 THEN -- if less ~33h trip we skip data
            t_rec := logbook_update_metrics_fn(avg_rec.count_metric, logbook_rec._from_time, logbook_rec._to_time);
        ELSE -- As we have too many data, we time-series aggregate data
            t_rec := logbook_update_metrics_timebucket_fn(avg_rec.count_metric, logbook_rec._from_time, logbook_rec._to_time);
        END IF;
        --RAISE NOTICE 'mobilitydb [%]', t_rec;
        IF t_rec.trajectory IS NULL THEN
            RAISE WARNING '-> process_logbook_queue_fn, vessel_id [%], invalid mobilitydb data [%] [%]', logbook_rec.vessel_id, _id, t_rec;
            RETURN;
        END IF;

        RAISE NOTICE '-> process_logbook_queue_fn, vessel_id [%], update entry logbook id:[%] start:[%] end:[%]', logbook_rec.vessel_id, logbook_rec.id, logbook_rec._from_time, logbook_rec._to_time;
        UPDATE api.logbook
            SET
                trip = t_rec.trajectory,
                trip_cog = t_rec.courseovergroundtrue,
                trip_sog = t_rec.speedoverground,
                trip_twa = t_rec.windspeedapparent,
                trip_tws = t_rec.truewindspeed,
                trip_twd = t_rec.truewinddirection,
                trip_notes = t_rec.notes, -- don't overwrite existing user notes. **** Must set trip_notes otherwise replay is not working.
                trip_status = t_rec.status,
                trip_depth = t_rec.depth,
                trip_batt_charge = t_rec.stateofcharge,
                trip_batt_voltage = t_rec.voltage,
                trip_temp_water = t_rec.watertemperature,
                trip_temp_out = t_rec.outsidetemperature,
                trip_pres_out = t_rec.outsidepressure,
                trip_hum_out = t_rec.outsidehumidity,
                trip_heading = t_rec.heading, -- heading True
                trip_tank_level = t_rec.tankLevel, -- Tank currentLevel
                trip_solar_voltage = t_rec.solarVoltage, -- solar voltage
                trip_solar_power = t_rec.solarPower -- solar powerPanel
            WHERE id = logbook_rec.id;

    END LOOP;
END $$;

Update SignalK client

The SignalK client can be updated from the SignalK Web UI. After the migration we updated this to version v0.5.0

Trouble shooting

During this migration, several issues came up, they eventually boiled down to an extension not updated and permissions issues.

Postgres

List postgres extensions:

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" 
    FROM pg_catalog.pg_extension e 
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace 
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass 
    ORDER BY 1;

List installed extensions available for upgrade

SELECT name, default_version, installed_version FROM pg_available_extensions where default_version <> installed_version;

Troubleshooting

Model context protocol (MCP)

Connect your AI tools to PostgSail using MCP

The Model Context Protocol (MCP) is a standard for connecting Large Language Models (LLMs) to platforms like PostgSail.

Once connected, your AI assistants can interact with and query your PostgSail API on your behalf.

PostgSail MCP server provides AI agents with read-only access to PostgSail marine data systems. This server enables LeChat, Claude and other AI assistants to search and navigate logs, moorages, and stays, monitor and analyze your boat all in one place.

Key Features

• Daily vessel summaries (current status, weather, systems)
• Voyage summary (reviewing past trips, moorages, stays information)
• System monitoring (battery, solar, sensors, connectivity)
• Historical analysis (tracking patterns, favorite destinations)
• Data export (for external navigation tools)
• Maintenance tracking (through stay notes and logs)

Authentication

Tool execution (API call) require a valid PostgSail JWT token. Get your token from the profile settings page in the web portal.

Available Methods

MCP implementation:

• initialize - Initialize MCP connection
• tools/list - List available tools
• tools/call - Execute a tool (requires JWT authentication)
• prompts/list - List available prompts
• prompts/get - Get a specific prompt
• resources/list - List available resources
• resources/read - Read a specific resource

Remote MCP Server

Accessible at https://mcp.openplotter.cloud/

LeChat allow remote connectors even with a free account.

Others AI ClaudeAI and OpenAI requires an upgrade plan to use remote connectors.

However you can use the MCP locally with Claude Desktop app.

Endpoints

POST /mcp - Main MCP endpoint (JSON-RPC 2.0)
GET /health - Health check endpoint
GET / - Overview

Authentication

The authentication is done via authorization Bearer header, https://iot.openplotter.cloud/profile profile page -> MCP.

Configuration

{
    "mcpServers": {
        "postgsail": {
            "type": "streamable-http",
            "url": "https://mcp.openplotter.cloud/mcp",
              "note": "A Model Context Protocol (MCP) server that provides AI agents with read-only access to PostgSail marine data systems.",
              "headers": {
                "Authorization": "Bearer ${POSTGSAIL_MCP_ACCESS_TOKEN}"
              }
        }
    }
}

Local STDIO MCP Server

Accessible locally, works with Claude Desktop app.

Installation

• Install Claude App
• Settings -> Extensions
• Drag .MCPB or .DXT files here to install

OR

• Install Claude App.
• Settings -> Extensions -> Advanced settings -> Extension Developer
• Install Extension…

API Documentation

Overview

PostgSail exposes its PostgreSQL database through a RESTful API using PostgREST, which serves as the primary interface for all client interactions. The API supports three main authentication roles that determine access permissions and available endpoints.

Authentication

The API uses JWT (JSON Web Token) authentication with role-based switching. The JWT token contains claims that set session variables used by Row Level Security policies:

• vessel.id - Current vessel identifier
• user.email - Current user email
• user.id - Current user identifier

API Roles

api_anonymous

Unauthenticated access with read-only permissions on public data for public access when enable

user_role

Authenticated web users with full access to their vessel data.

vessel_role

SignalK plugin data ingestion with insert/update permissions.

mcp_role

Authenticated web users with read-only access to their vessel data via an Large language model.

API Endpoints

Base URL

http://localhost:3000

Authentication Examples

Anonymous access:

curl http://localhost:3000/

User role access:

curl http://localhost:3000/ -H 'Authorization: Bearer my_token_from_login_or_signup_fn'

Vessel role access:

curl http://localhost:3000/ -H 'Authorization: Bearer my_token_from_register_vessel_fn'

Key Endpoints by Role

Authentication Functions

• POST /rpc/login - User authentication
• POST /rpc/signup - User registration
• POST /rpc/register_vessel - Vessel registration

Data Tables (user_role and vessel_role)

• GET /metrics - Time-series telemetry data
• GET /logbook - Trip records with trajectories
• GET /stays - Mooring/anchoring periods
• GET /moorages - Named locations
• GET /metadata - Vessel metadata including configuration

Views (user_role)

• GET /logs_view - Enriched logbook data
• GET /log_view - Details logbook data
• GET /moorages_view - Enriched moorage data
• GET /moorage_view - Details moorage data
• GET /stays_view - Enriched Stays data
• GET /stay_view - Details Stay data
• GET /vessels_view - Vessel data
• GET /monitoring_view - System monitoring data

RPC Functions

• POST /rpc/settings_fn - User preferences
• POST /rpc/update_user_preferences_fn - Update preferences
• POST /rpc/versions_fn - System version information
• POST /rpc/vessel_fn - Vessel details

OpenAPI Documentation

The OpenAPI specification is dynamically generated based on the authenticated role’s permissions. Access the interactive documentation at:

https://petstore.swagger.io/?url=https://raw.githubusercontent.com/xbgmsharp/postgsail/main/openapi.json

Other applications can also use the PostgSAIL API.

The available endpoints and operations in the OpenAPI spec will vary depending on the JWT role used to access /.

Public Access

Anonymous users can access specific public data when vessel owners have enabled public sharing.

Configuration

The PostgREST service is configured via environment variables:

• PGRST_DB_SCHEMA: api - Default schema
• PGRST_DB_ANON_ROLE: api_anonymous - Anonymous role
• PGRST_DB_PRE_REQUEST: public.check_jwt - JWT validation function
• PGRST_JWT_SECRET - JWT signing secret

Notes

• The API runs on port 3000 by default
• All data access is controlled by Row Level Security policies based on vessel ownership
• The SignalK plugin uses vessel_role for continuous data ingestion
• Rate limiting and connection limits are enforced per role

ERD Schema

erDiagram
    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 "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
        timestamp_with_time_zone _from_time "{NOT_NULL}"
        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 "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 "{NOT_NULL}"
        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 "Unique identifier for the vessel associated with the api.metadata entry {NOT_NULL}"
    }

    api_metadata {
        boolean active "trigger monitor online/offline"
        boolean active 
        jsonb available_keys "Signalk paths with unit for custom mapping"
        jsonb available_keys 
        double_precision beam 
        jsonb configuration "User-defined Signalk path mapping for metrics"
        jsonb configuration 
        timestamp_with_time_zone created_at "{NOT_NULL}"
        double_precision height 
        text ip "Store vessel ip address"
        text ip 
        double_precision length 
        text mmsi "Maritime Mobile Service Identity (MMSI) number associated with the vessel, link to public.mid"
        text mmsi 
        text name 
        text platform 
        text plugin_version "{NOT_NULL}"
        numeric ship_type "Type of ship associated with the vessel, link to public.aistypes"
        numeric ship_type 
        text signalk_version "{NOT_NULL}"
        timestamp_with_time_zone time "{NOT_NULL}"
        timestamp_with_time_zone updated_at "{NOT_NULL}"
        jsonb user_data "User-defined data including vessel polar (theoretical performance), make/model, and preferences"
        jsonb user_data 
        text vessel_id "Link auth.vessels with api.metadata via FOREIGN KEY and REFERENCES {NOT_NULL}"
        text vessel_id "{NOT_NULL}"
    }

    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 "{NOT_NULL}"
        text vessel_id "Unique identifier for the vessel associated with the api.metadata entry {NOT_NULL}"
        double_precision windspeedapparent 
    }

    api_moorages {
        text country 
        geography geog "postgis geography type default SRID 4326 Unit: degres"
        boolean home_flag 
        integer id "{NOT_NULL}"
        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 "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 "Unique identifier for the vessel associated with the api.metadata entry {NOT_NULL}"
    }

    api_stays {
        boolean active 
        timestamp_with_time_zone arrived "{NOT_NULL}"
        timestamp_with_time_zone departed 
        interval duration "Best to use standard ISO 8601"
        geography geog "postgis geography type default SRID 4326 Unit: degres"
        integer id "{NOT_NULL}"
        double_precision latitude 
        double_precision longitude 
        integer moorage_id "Link api.moorages with api.stays via FOREIGN KEY and REFERENCES"
        text name 
        text notes 
        integer stay_code "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 "Unique identifier for the vessel associated with the api.metadata entry {NOT_NULL}"
    }

    api_stays_at {
        text description "{NOT_NULL}"
        integer stay_code "{NOT_NULL}"
    }

    auth_accounts {
        timestamp_with_time_zone connected_at "{NOT_NULL}"
        timestamp_with_time_zone created_at "{NOT_NULL}"
        citext email "{NOT_NULL}"
        text first "User first name with CONSTRAINT CHECK {NOT_NULL}"
        integer id "{NOT_NULL}"
        text last "User last name with CONSTRAINT CHECK {NOT_NULL}"
        text pass "{NOT_NULL}"
        jsonb preferences 
        name role "{NOT_NULL}"
        timestamp_with_time_zone updated_at "{NOT_NULL}"
        text user_id "{NOT_NULL}"
    }

    auth_otp {
        text otp_pass "{NOT_NULL}"
        timestamp_with_time_zone otp_timestamp 
        smallint otp_tries "{NOT_NULL}"
        citext user_email "{NOT_NULL}"
    }

    auth_vessels {
        timestamp_with_time_zone created_at "{NOT_NULL}"
        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 "{NOT_NULL}"
        citext owner_email "{NOT_NULL}"
        name role "{NOT_NULL}"
        timestamp_with_time_zone updated_at "{NOT_NULL}"
        text vessel_id "{NOT_NULL}"
    }

    public_aistypes {
        text description 
        numeric id 
    }

    public_app_settings {
        text name "application settings name key {NOT_NULL}"
        text value "application settings value {NOT_NULL}"
    }

    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_mobilitydb_opcache {
        integer ltypnum 
        oid opid 
        integer opnum 
        integer rtypnum 
    }

    public_ne_10m_geography_marine_polys {
        text changed 
        text featurecla 
        geometry geom 
        integer gid "{NOT_NULL}"
        text label 
        double_precision max_label 
        double_precision min_label 
        text name 
        text name_ar 
        text name_bn 
        text name_de 
        text name_el 
        text name_en 
        text name_es 
        text name_fa 
        text name_fr 
        text name_he 
        text name_hi 
        text name_hu 
        text name_id 
        text name_it 
        text name_ja 
        text name_ko 
        text name_nl 
        text name_pl 
        text name_pt 
        text name_ru 
        text name_sv 
        text name_tr 
        text name_uk 
        text name_ur 
        text name_vi 
        text name_zh 
        text name_zht 
        text namealt 
        bigint ne_id 
        text note 
        smallint scalerank 
        text wikidataid 
    }

    public_process_queue {
        text channel "{NOT_NULL}"
        integer id "{NOT_NULL}"
        text payload "{NOT_NULL}"
        timestamp_with_time_zone processed 
        text ref_id "either user_id or vessel_id {NOT_NULL}"
        timestamp_with_time_zone stored "{NOT_NULL}"
    }

    public_spatial_ref_sys {
        character_varying auth_name 
        integer auth_srid 
        character_varying proj4text 
        integer srid "{NOT_NULL}"
        character_varying srtext 
    }

    api_logbook }o--|| api_metadata : ""
    api_logbook }o--|| api_moorages : ""
    api_logbook }o--|| api_moorages : ""
    api_metrics }o--|| api_metadata : ""
    api_moorages }o--|| api_metadata : ""
    api_stays }o--|| api_metadata : ""
    api_moorages }o--|| api_stays_at : ""
    api_stays }o--|| api_moorages : ""
    api_stays }o--|| api_stays_at : ""
    auth_otp |o--|| auth_accounts : ""
    auth_vessels }o--|| auth_accounts : ""

PostgSail

Description

PostgSail database documentation

Tables

Stored procedures and functions

Enums

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

api.eventlogs_view

Description

Event logs view

CREATE VIEW eventlogs_view AS (
 SELECT id,
    channel,
    payload,
    ref_id,
    stored,
    processed
   FROM process_queue pq
  WHERE ((processed IS NOT NULL) AND (channel <> 'new_stay'::text) AND (channel <> 'pre_logbook'::text) AND (channel <> 'post_logbook'::text) AND ((ref_id = current_setting('user.id'::text, false)) OR (ref_id = current_setting('vessel.id'::text, true))))
  ORDER BY id DESC
)

Columns

Referenced Tables

Relations

erDiagram


"api.eventlogs_view" {
  text channel ""
  integer id ""
  text payload ""
  timestamp_with_time_zone processed ""
  text ref_id ""
  timestamp_with_time_zone stored ""
}

Generated by tbls

api.explore_view

Description

explore_view web view

CREATE VIEW explore_view AS (
 WITH raw_metrics AS (
         SELECT m."time",
            m.metrics
           FROM api.metrics m
          ORDER BY m."time" DESC
         LIMIT 1
        )
 SELECT raw_metrics."time",
    jsonb_each_text.key,
    jsonb_each_text.value
   FROM raw_metrics,
    LATERAL jsonb_each_text(raw_metrics.metrics) jsonb_each_text(key, value)
  ORDER BY jsonb_each_text.key
)

Columns

Referenced Tables

Relations

erDiagram


"api.explore_view" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}

Generated by tbls

api.log_view

Description

Log web view

CREATE VIEW log_view AS (
 SELECT id,
    name,
    _from AS "from",
    _from_time AS started,
    _to AS "to",
    _to_time AS ended,
    distance,
    duration,
    notes,
    api.export_logbook_geojson_trip_fn(id) AS geojson,
    avg_speed,
    max_speed,
    max_wind_speed,
    extra,
    _from_moorage_id AS from_moorage_id,
    _to_moorage_id AS to_moorage_id,
    (extra -> 'polar'::text) AS polar,
    (user_data -> 'images'::text) AS images,
    (user_data -> 'tags'::text) AS tags,
    (user_data -> 'observations'::text) AS observations,
        CASE
            WHEN (jsonb_array_length((user_data -> 'images'::text)) > 0) THEN true
            ELSE false
        END AS has_images
   FROM api.logbook l
  WHERE ((_to_time IS NOT NULL) AND (trip IS NOT NULL))
  ORDER BY _from_time DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.logbook

Description

Stores generated logbook

Columns

Constraints

Indexes

Triggers

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

api.logs_geojson_view

Description

List logs as geojson

CREATE VIEW logs_geojson_view AS (
 SELECT id,
    name,
    starttimestamp,
    (st_asgeojson(tbl.*))::jsonb AS geojson
   FROM ( SELECT l.id,
            l.name,
            starttimestamp(l.trip) AS starttimestamp,
            endtimestamp(l.trip) AS endtimestamp,
            duration(l.trip) AS duration,
            ((length(l.trip) * (0.0005399568)::double precision))::numeric AS distance,
            maxvalue(l.trip_sog) AS max_sog,
            maxvalue(l.trip_tws) AS max_tws,
            maxvalue(l.trip_twd) AS max_twd,
            maxvalue(l.trip_depth) AS max_depth,
            maxvalue(l.trip_temp_water) AS max_temp_water,
            maxvalue(l.trip_temp_out) AS max_temp_out,
            maxvalue(l.trip_pres_out) AS max_pres_out,
            maxvalue(l.trip_hum_out) AS max_hum_out,
            maxvalue(l.trip_batt_charge) AS max_stateofcharge,
            maxvalue(l.trip_batt_voltage) AS max_voltage,
            maxvalue(l.trip_solar_voltage) AS max_solar_voltage,
            maxvalue(l.trip_solar_power) AS max_solar_power,
            maxvalue(l.trip_tank_level) AS max_tank_level,
            twavg(l.trip_sog) AS avg_sog,
            twavg(l.trip_tws) AS avg_tws,
            twavg(l.trip_twd) AS avg_twd,
            twavg(l.trip_depth) AS avg_depth,
            twavg(l.trip_temp_water) AS avg_temp_water,
            twavg(l.trip_temp_out) AS avg_temp_out,
            twavg(l.trip_pres_out) AS avg_pres_out,
            twavg(l.trip_hum_out) AS avg_hum_out,
            twavg(l.trip_batt_charge) AS avg_stateofcharge,
            twavg(l.trip_batt_voltage) AS avg_voltage,
            twavg(l.trip_solar_voltage) AS avg_solar_voltage,
            twavg(l.trip_solar_power) AS avg_solar_power,
            twavg(l.trip_tank_level) AS avg_tank_level,
            (trajectory(l.trip))::geometry AS track_geog,
            l.extra,
            l._to_moorage_id,
            l._from_moorage_id,
            (l.extra -> 'polar'::text) AS polar,
            (l.user_data -> 'images'::text) AS images,
            (l.user_data -> 'tags'::text) AS tags,
            (l.user_data -> 'observations'::text) AS observations,
                CASE
                    WHEN (jsonb_array_length((l.user_data -> 'images'::text)) > 0) THEN true
                    ELSE false
                END AS has_images
           FROM api.logbook l
          WHERE ((l._to_time IS NOT NULL) AND (l.trip IS NOT NULL))
          ORDER BY l._from_time DESC) tbl
)

Columns

Referenced Tables

Relations

erDiagram


"api.logs_geojson_view" {
  jsonb geojson ""
  integer id ""
  text name ""
  timestamp_with_time_zone starttimestamp ""
}

Generated by tbls

api.logs_view

Description

Logs web view

CREATE VIEW logs_view AS (
 SELECT id,
    name,
    _from AS "from",
    _from_time AS started,
    _to AS "to",
    _to_time AS ended,
    distance,
    duration,
    _from_moorage_id,
    _to_moorage_id,
    (user_data -> 'tags'::text) AS tags
   FROM api.logbook l
  WHERE ((name IS NOT NULL) AND (_to_time IS NOT NULL))
  ORDER BY _from_time DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.metadata

Description

Stores metadata received from vessel, aka signalk plugin

Columns

Constraints

Indexes

Triggers

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

Generated by tbls

api.metrics

Description

Stores metrics from vessel

Columns

Constraints

Indexes

Triggers

Relations

erDiagram

"api.metrics" }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.moorages" }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.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.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.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.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.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"
}

Generated by tbls

api.monitoring_humidity

Description

Monitoring environment.%.humidity web view

CREATE VIEW monitoring_humidity AS (
 SELECT m."time",
    jsonb_each_text.key,
    jsonb_each_text.value
   FROM api.metrics m,
    LATERAL jsonb_each_text(m.metrics) jsonb_each_text(key, value)
  WHERE ((jsonb_each_text.key ~~* 'environment.%.humidity'::text) OR (jsonb_each_text.key ~~* 'environment.%.relativeHumidity'::text))
  ORDER BY m."time" DESC
)

Columns

Referenced Tables

Relations

erDiagram


"api.monitoring_humidity" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}

Generated by tbls

api.monitoring_live

Description

Dynamic Monitoring web view

CREATE VIEW monitoring_live AS (
 WITH pressure AS (
         SELECT json_agg(json_build_object('time', sub.time_bucket, 'outsidePressure', sub.outsidepressure) ORDER BY sub.time_bucket) AS outsidepressurehistory
           FROM ( SELECT time_bucket('PT10M'::interval, mt_1."time") AS time_bucket,
                    avg((COALESCE(((mt_1.metrics -> 'pressure'::text) ->> 'outside'::text), (mt_1.metrics ->> (md_1.configuration ->> 'outsidePressureKey'::text)), (mt_1.metrics ->> 'environment.outside.pressure'::text)))::double precision) AS outsidepressure
                   FROM (api.metrics mt_1
                     JOIN api.metadata md_1 ON ((md_1.vessel_id = mt_1.vessel_id)))
                  WHERE ((mt_1.vessel_id = current_setting('vessel.id'::text, false)) AND (mt_1."time" > ((now() AT TIME ZONE 'UTC'::text) - 'PT6H'::interval)))
                  GROUP BY (time_bucket('PT10M'::interval, mt_1."time"))
                 HAVING (avg((COALESCE(((mt_1.metrics -> 'pressure'::text) ->> 'outside'::text), (mt_1.metrics ->> (md_1.configuration ->> 'outsidePressureKey'::text)), (mt_1.metrics ->> 'environment.outside.pressure'::text)))::double precision) IS NOT NULL)) sub
        )
 SELECT mt."time",
    ((((now() AT TIME ZONE 'UTC'::text))::timestamp with time zone - mt."time") > 'PT1H10M'::interval) AS offline,
    mt.metrics AS data,
    jsonb_build_object('type', 'Feature', 'geometry', (st_asgeojson(st_makepoint(mt.longitude, mt.latitude)))::jsonb, 'properties', jsonb_build_object('name', current_setting('vessel.name'::text, false), 'latitude', mt.latitude, 'longitude', mt.longitude, 'time', mt."time", 'speedoverground', mt.speedoverground, 'windspeedapparent', mt.windspeedapparent, 'truewindspeed', (COALESCE(((mt.metrics -> 'wind'::text) ->> 'speed'::text), (mt.metrics ->> (md.configuration ->> 'windSpeedKey'::text)), (mt.metrics ->> 'environment.wind.speedTrue'::text)))::double precision, 'truewinddirection', (COALESCE(((mt.metrics -> 'wind'::text) ->> 'direction'::text), (mt.metrics ->> (md.configuration ->> 'windDirectionKey'::text)), (mt.metrics ->> 'environment.wind.directionTrue'::text)))::double precision, 'status', COALESCE(mt.status, NULL::text))) AS geojson,
    current_setting('vessel.name'::text, false) AS name,
    mt.status,
    (COALESCE(((mt.metrics -> 'water'::text) ->> 'temperature'::text), (mt.metrics ->> (md.configuration ->> 'waterTemperatureKey'::text)), (mt.metrics ->> 'environment.water.temperature'::text)))::double precision AS watertemperature,
    (COALESCE(((mt.metrics -> 'temperature'::text) ->> 'inside'::text), (mt.metrics ->> (md.configuration ->> 'insideTemperatureKey'::text)), (mt.metrics ->> 'environment.inside.temperature'::text)))::double precision AS insidetemperature,
    (COALESCE(((mt.metrics -> 'temperature'::text) ->> 'outside'::text), (mt.metrics ->> (md.configuration ->> 'outsideTemperatureKey'::text)), (mt.metrics ->> 'environment.outside.temperature'::text)))::double precision AS outsidetemperature,
    (COALESCE(((mt.metrics -> 'wind'::text) ->> 'speed'::text), (mt.metrics ->> (md.configuration ->> 'windSpeedKey'::text)), (mt.metrics ->> 'environment.wind.speedTrue'::text)))::double precision AS windspeedoverground,
    (COALESCE(((mt.metrics -> 'wind'::text) ->> 'direction'::text), (mt.metrics ->> (md.configuration ->> 'windDirectionKey'::text)), (mt.metrics ->> 'environment.wind.directionTrue'::text)))::double precision AS winddirectiontrue,
    (COALESCE(((mt.metrics -> 'humidity'::text) ->> 'inside'::text), (mt.metrics ->> (md.configuration ->> 'insideHumidityKey'::text)), (mt.metrics ->> 'environment.inside.relativeHumidity'::text), (mt.metrics ->> 'environment.inside.humidity'::text)))::double precision AS insidehumidity,
    (COALESCE(((mt.metrics -> 'humidity'::text) ->> 'outside'::text), (mt.metrics ->> (md.configuration ->> 'outsideHumidityKey'::text)), (mt.metrics ->> 'environment.outside.relativeHumidity'::text), (mt.metrics ->> 'environment.outside.humidity'::text)))::double precision AS outsidehumidity,
    (COALESCE(((mt.metrics -> 'pressure'::text) ->> 'outside'::text), (mt.metrics ->> (md.configuration ->> 'outsidePressureKey'::text)), (mt.metrics ->> 'environment.outside.pressure'::text)))::double precision AS outsidepressure,
    (COALESCE(((mt.metrics -> 'pressure'::text) ->> 'inside'::text), (mt.metrics ->> (md.configuration ->> 'insidePressureKey'::text)), (mt.metrics ->> 'environment.inside.pressure'::text)))::double precision AS insidepressure,
    (COALESCE(((mt.metrics -> 'battery'::text) ->> 'charge'::text), (mt.metrics ->> (md.configuration ->> 'stateOfChargeKey'::text)), (mt.metrics ->> 'electrical.batteries.House.capacity.stateOfCharge'::text)))::double precision AS batterycharge,
    (COALESCE(((mt.metrics -> 'battery'::text) ->> 'voltage'::text), (mt.metrics ->> (md.configuration ->> 'voltageKey'::text)), (mt.metrics ->> 'electrical.batteries.House.voltage'::text)))::double precision AS batteryvoltage,
    (COALESCE(((mt.metrics -> 'water'::text) ->> 'depth'::text), (mt.metrics ->> (md.configuration ->> 'depthKey'::text)), (mt.metrics ->> 'environment.depth.belowTransducer'::text)))::double precision AS depth,
    (COALESCE(((mt.metrics -> 'solar'::text) ->> 'power'::text), (mt.metrics ->> (md.configuration ->> 'solarPowerKey'::text)), (mt.metrics ->> 'electrical.solar.Main.panelPower'::text)))::double precision AS solarpower,
    (COALESCE(((mt.metrics -> 'solar'::text) ->> 'voltage'::text), (mt.metrics ->> (md.configuration ->> 'solarVoltageKey'::text)), (mt.metrics ->> 'electrical.solar.Main.panelVoltage'::text)))::double precision AS solarvoltage,
    (COALESCE(((mt.metrics -> 'tank'::text) ->> 'level'::text), (mt.metrics ->> (md.configuration ->> 'tankLevelKey'::text)), (mt.metrics ->> 'tanks.fuel.0.currentLevel'::text)))::double precision AS tanklevel,
        CASE
            WHEN ((mt.status <> 'moored'::text) AND (mt.status <> 'anchored'::text)) THEN ( SELECT logbook_active_geojson_fn() AS logbook_active_geojson_fn)
            WHEN ((mt.status = 'moored'::text) OR (mt.status = 'anchored'::text)) THEN ( SELECT stay_active_geojson_fn(mt."time") AS stay_active_geojson_fn)
            ELSE NULL::jsonb
        END AS live,
    pressure.outsidepressurehistory
   FROM ((api.metrics mt
     JOIN api.metadata md ON ((md.vessel_id = mt.vessel_id)))
     CROSS JOIN pressure)
  ORDER BY mt."time" DESC
 LIMIT 1
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.monitoring_temperatures

Description

Monitoring environment.%.temperature web view

CREATE VIEW monitoring_temperatures AS (
 SELECT m."time",
    jsonb_each_text.key,
    jsonb_each_text.value
   FROM api.metrics m,
    LATERAL jsonb_each_text(m.metrics) jsonb_each_text(key, value)
  WHERE (jsonb_each_text.key ~~* 'environment.%.temperature'::text)
  ORDER BY m."time" DESC
)

Columns

Referenced Tables

Relations

erDiagram


"api.monitoring_temperatures" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}

Generated by tbls

api.monitoring_view

Description

Monitoring static web view

CREATE VIEW monitoring_view AS (
 SELECT "time",
    ((((now() AT TIME ZONE 'UTC'::text))::timestamp with time zone - "time") > 'PT1H10M'::interval) AS offline,
    (metrics -> 'environment.water.temperature'::text) AS watertemperature,
    (metrics -> 'environment.inside.temperature'::text) AS insidetemperature,
    (metrics -> 'environment.outside.temperature'::text) AS outsidetemperature,
    (metrics -> 'environment.wind.speedTrue'::text) AS windspeedoverground,
    (metrics -> 'environment.wind.directionTrue'::text) AS winddirectiontrue,
    (metrics -> 'environment.inside.relativeHumidity'::text) AS insidehumidity,
    (metrics -> 'environment.outside.relativeHumidity'::text) AS outsidehumidity,
    (metrics -> 'environment.outside.pressure'::text) AS outsidepressure,
    (metrics -> 'environment.inside.pressure'::text) AS insidepressure,
    (metrics -> 'electrical.batteries.House.capacity.stateOfCharge'::text) AS batterycharge,
    (metrics -> 'electrical.batteries.House.voltage'::text) AS batteryvoltage,
    (metrics -> 'environment.depth.belowTransducer'::text) AS depth,
    jsonb_build_object('type', 'Feature', 'geometry', (st_asgeojson(st_makepoint(longitude, latitude)))::jsonb, 'properties', jsonb_build_object('name', current_setting('vessel.name'::text, false), 'latitude', latitude, 'longitude', longitude, 'time', "time", 'speedoverground', speedoverground, 'windspeedapparent', windspeedapparent, 'truewindspeed', COALESCE((metrics -> 'environment.wind.speedTrue'::text), NULL::jsonb), 'truewinddirection', COALESCE((metrics -> 'environment.wind.directionTrue'::text), NULL::jsonb), 'status', COALESCE(status, NULL::text))) AS geojson,
    current_setting('vessel.name'::text, false) AS name,
    status,
        CASE
            WHEN ((status <> 'moored'::text) AND (status <> 'anchored'::text)) THEN ( SELECT logbook_active_geojson_fn() AS logbook_active_geojson_fn)
            WHEN ((status = 'moored'::text) OR (status = 'anchored'::text)) THEN ( SELECT stay_active_geojson_fn(m."time") AS stay_active_geojson_fn)
            ELSE NULL::jsonb
        END AS live
   FROM api.metrics m
  ORDER BY "time" DESC
 LIMIT 1
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.monitoring_view2

Description

Monitoring Last whatever data from json web view

CREATE VIEW monitoring_view2 AS (
 SELECT key,
    value
   FROM jsonb_each(( SELECT m.metrics
           FROM api.metrics m
          ORDER BY m."time" DESC
         LIMIT 1)) jsonb_each(key, value)
)

Columns

Referenced Tables

Relations

erDiagram


"api.monitoring_view2" {
  text key ""
  jsonb value ""
}

Generated by tbls

api.monitoring_view3

Description

Monitoring Timeseries whatever data from json web view

CREATE VIEW monitoring_view3 AS (
 SELECT m."time",
    jsonb_each_text.key,
    jsonb_each_text.value
   FROM api.metrics m,
    LATERAL jsonb_each_text(m.metrics) jsonb_each_text(key, value)
  ORDER BY m."time" DESC
)

Columns

Referenced Tables

Relations

erDiagram


"api.monitoring_view3" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}

Generated by tbls

api.monitoring_voltage

Description

Monitoring electrical.%.voltage web view

CREATE VIEW monitoring_voltage AS (
 SELECT m."time",
    jsonb_each_text.key,
    jsonb_each_text.value
   FROM api.metrics m,
    LATERAL jsonb_each_text(m.metrics) jsonb_each_text(key, value)
  WHERE (jsonb_each_text.key ~~* 'electrical.%.voltage'::text)
  ORDER BY m."time" DESC
)

Columns

Referenced Tables

Relations

erDiagram


"api.monitoring_voltage" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}

Generated by tbls

api.moorage_view

Description

Moorage details web view

CREATE VIEW moorage_view AS (
 WITH stay_details AS (
         SELECT s.moorage_id,
            s.arrived,
            s.departed,
            s.duration,
            s.id AS stay_id,
            first_value(s.id) OVER (PARTITION BY s.moorage_id ORDER BY s.arrived) AS first_seen_id,
            first_value(s.id) OVER (PARTITION BY s.moorage_id ORDER BY s.departed DESC) AS last_seen_id
           FROM api.stays s
          WHERE (s.active = false)
        ), stay_summary AS (
         SELECT stay_details.moorage_id,
            min(stay_details.arrived) AS first_seen,
            max(stay_details.departed) AS last_seen,
            sum(stay_details.duration) AS total_duration,
            count(*) AS stay_count,
            max(stay_details.first_seen_id) AS first_seen_id,
            max(stay_details.last_seen_id) AS last_seen_id
           FROM stay_details
          GROUP BY stay_details.moorage_id
        ), log_summary AS (
         SELECT logs.moorage_id,
            count(DISTINCT logs.id) AS log_count
           FROM ( SELECT l_1._from_moorage_id AS moorage_id,
                    l_1.id
                   FROM api.logbook l_1
                  WHERE (l_1.active = false)
                UNION ALL
                 SELECT l_1._to_moorage_id AS moorage_id,
                    l_1.id
                   FROM api.logbook l_1
                  WHERE (l_1.active = false)) logs
          GROUP BY logs.moorage_id
        )
 SELECT m.id,
    m.name,
    sa.description AS default_stay,
    sa.stay_code AS default_stay_id,
    m.notes,
    m.home_flag AS home,
    m.geog,
    m.latitude,
    m.longitude,
    COALESCE(l.log_count, (0)::bigint) AS logs_count,
    COALESCE(ss.stay_count, (0)::bigint) AS stays_count,
    COALESCE(ss.total_duration, 'PT0S'::interval) AS stays_sum_duration,
    ss.first_seen AS stay_first_seen,
    ss.last_seen AS stay_last_seen,
    ss.first_seen_id AS stay_first_seen_id,
    ss.last_seen_id AS stay_last_seen_id,
        CASE
            WHEN (jsonb_array_length((m.user_data -> 'images'::text)) > 0) THEN true
            ELSE false
        END AS has_images,
    (m.user_data -> 'images'::text) AS images
   FROM (((api.moorages m
     JOIN api.stays_at sa ON ((m.stay_code = sa.stay_code)))
     LEFT JOIN stay_summary ss ON ((m.id = ss.moorage_id)))
     LEFT JOIN log_summary l ON ((m.id = l.moorage_id)))
  WHERE (m.geog IS NOT NULL)
  ORDER BY ss.total_duration DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.moorages

Description

Stores generated moorages

Columns

Constraints

Indexes

Triggers

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.logbook" }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"
"api.stays" }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.moorages" }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.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.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.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.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 ""
}
"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

api.moorages_geojson_view

Description

List moorages as geojson

CREATE VIEW moorages_geojson_view AS (
 SELECT id,
    name,
    (st_asgeojson(m.*))::jsonb AS geojson
   FROM ( SELECT m_1.id,
            m_1.name,
            m_1.default_stay,
            m_1.default_stay_id,
            m_1.notes,
            m_1.home,
            m_1.geog,
            m_1.latitude,
            m_1.longitude,
            m_1.logs_count,
            m_1.stays_count,
            m_1.stays_sum_duration,
            m_1.stay_first_seen,
            m_1.stay_last_seen,
            m_1.stay_first_seen_id,
            m_1.stay_last_seen_id,
            m_1.has_images,
            m_1.images
           FROM api.moorage_view m_1
          WHERE (m_1.geog IS NOT NULL)) m
)

Columns

Referenced Tables

Relations

erDiagram


"api.moorages_geojson_view" {
  jsonb geojson ""
  integer id ""
  text name ""
}

Generated by tbls

api.moorages_stays_view

Description

Moorages stay listing web view

CREATE VIEW moorages_stays_view AS (
 SELECT _to.name AS _to_name,
    _to.id AS _to_id,
    _to._to_time,
    _from.id AS _from_id,
    _from.name AS _from_name,
    _from._from_time,
    s.stay_code,
    s.duration,
    m.id,
    m.name
   FROM api.stays_at sa,
    api.moorages m,
    ((api.stays s
     LEFT JOIN api.logbook _from ON ((_from._from_time = s.departed)))
     LEFT JOIN api.logbook _to ON ((_to._to_time = s.arrived)))
  WHERE ((s.departed IS NOT NULL) AND (s.name IS NOT NULL) AND (s.stay_code = sa.stay_code) AND (s.moorage_id = m.id))
  ORDER BY _to._to_time DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.moorages_view

Description

Moorages listing web view

CREATE VIEW moorages_view AS (
 SELECT m.id,
    m.name AS moorage,
    sa.description AS default_stay,
    sa.stay_code AS default_stay_id,
    COALESCE(count(DISTINCT l.id), (0)::bigint) AS arrivals_departures,
    COALESCE(sum(DISTINCT s.duration), 'PT0S'::interval) AS total_duration
   FROM (((api.moorages m
     JOIN api.stays_at sa ON ((m.stay_code = sa.stay_code)))
     LEFT JOIN api.stays s ON (((m.id = s.moorage_id) AND (s.active = false))))
     LEFT JOIN api.logbook l ON (((m.id = l._from_moorage_id) OR ((m.id = l._to_moorage_id) AND (l.active = false)))))
  WHERE ((m.geog IS NOT NULL) AND (m.stay_code = sa.stay_code))
  GROUP BY m.id, m.name, sa.description, sa.stay_code
  ORDER BY COALESCE(sum(DISTINCT s.duration), 'PT0S'::interval) DESC
)

Columns

Referenced Tables

Relations

erDiagram


"api.moorages_view" {
  bigint arrivals_departures ""
  text default_stay ""
  integer default_stay_id ""
  integer id ""
  text moorage ""
  interval total_duration ""
}

Generated by tbls

api.stays_explore_view

api.stats_logs_view

Description

Statistics Logs web view

CREATE VIEW stats_logs_view AS (
 WITH meta AS (
         SELECT m_1.name
           FROM api.metadata m_1
        ), last_metric AS (
         SELECT m_1."time"
           FROM api.metrics m_1
          ORDER BY m_1."time" DESC
         LIMIT 1
        ), first_metric AS (
         SELECT m_1."time"
           FROM api.metrics m_1
          ORDER BY m_1."time"
         LIMIT 1
        ), logbook AS (
         SELECT count(*) AS number_of_log_entries,
            max(l_1.max_speed) AS max_speed,
            max(l_1.max_wind_speed) AS max_wind_speed,
            sum(l_1.distance) AS total_distance,
            sum(l_1.duration) AS total_time_underway,
            concat(max(l_1.distance), ' NM, ', max(l_1.duration), ' hours') AS longest_nonstop_sail
           FROM api.logbook l_1
        )
 SELECT m.name,
    fm."time" AS first,
    lm."time" AS last,
    l.number_of_log_entries,
    l.max_speed,
    l.max_wind_speed,
    l.total_distance,
    l.total_time_underway,
    l.longest_nonstop_sail
   FROM first_metric fm,
    last_metric lm,
    logbook l,
    meta m
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.stats_moorages_away_view

Description

Statistics Moorages Time Spent Away web view

CREATE VIEW stats_moorages_away_view AS (
 SELECT sa.description,
    sum(m.stays_sum_duration) AS time_spent_away_by
   FROM api.moorage_view m,
    api.stays_at sa
  WHERE ((m.home IS FALSE) AND (m.default_stay_id = sa.stay_code))
  GROUP BY m.default_stay_id, sa.description
  ORDER BY m.default_stay_id
)

Columns

Referenced Tables

Relations

erDiagram


"api.stats_moorages_away_view" {
  text description ""
  interval time_spent_away_by ""
}

Generated by tbls

api.stats_moorages_view

Description

Statistics Moorages web view

CREATE VIEW stats_moorages_view AS (
 WITH home_ports AS (
         SELECT count(*) AS home_ports
           FROM api.moorage_view m
          WHERE (m.home IS TRUE)
        ), unique_moorage AS (
         SELECT count(*) AS unique_moorage
           FROM api.moorage_view m
        ), time_at_home_ports AS (
         SELECT sum(m.stays_sum_duration) AS time_at_home_ports
           FROM api.moorage_view m
          WHERE (m.home IS TRUE)
        ), time_spent_away AS (
         SELECT sum(m.stays_sum_duration) AS time_spent_away
           FROM api.moorage_view m
          WHERE (m.home IS FALSE)
        )
 SELECT home_ports.home_ports,
    unique_moorage.unique_moorage AS unique_moorages,
    time_at_home_ports.time_at_home_ports AS "time_spent_at_home_port(s)",
    time_spent_away.time_spent_away
   FROM home_ports,
    unique_moorage,
    time_at_home_ports,
    time_spent_away
)

Columns

Referenced Tables

Relations

erDiagram


"api.stats_moorages_view" {
  bigint home_ports ""
  interval time_spent_at_home_port(s) ""
  interval time_spent_away ""
  bigint unique_moorages ""
}

Generated by tbls

api.stay_view

Description

Stay web view

CREATE VIEW stay_view AS (
 SELECT s.id,
    s.name,
    m.name AS moorage,
    m.id AS moorage_id,
    (s.departed - s.arrived) AS duration,
    sa.description AS stayed_at,
    sa.stay_code AS stayed_at_id,
    s.arrived,
    _from.id AS arrived_log_id,
    _from._to_moorage_id AS arrived_from_moorage_id,
    _from._to AS arrived_from_moorage_name,
    s.departed,
    _to.id AS departed_log_id,
    _to._from_moorage_id AS departed_to_moorage_id,
    _to._from AS departed_to_moorage_name,
    s.notes,
        CASE
            WHEN (jsonb_array_length((s.user_data -> 'images'::text)) > 0) THEN true
            ELSE false
        END AS has_images,
    (s.user_data -> 'images'::text) AS images
   FROM ((((api.stays s
     JOIN api.stays_at sa ON ((s.stay_code = sa.stay_code)))
     JOIN api.moorages m ON ((s.moorage_id = m.id)))
     LEFT JOIN api.logbook _from ON ((_from._from_time = s.departed)))
     LEFT JOIN api.logbook _to ON ((_to._to_time = s.arrived)))
  WHERE ((s.departed IS NOT NULL) AND (_from._to_moorage_id IS NOT NULL) AND (s.name IS NOT NULL))
  ORDER BY s.arrived DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.stays

Description

Stores generated stays

Columns

Constraints

Indexes

Triggers

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

api.stays_at

Description

Stay Type

Columns

Constraints

Indexes

Relations

erDiagram

"api.moorages" }o--o| "api.stays_at" : "FOREIGN KEY (stay_code) REFERENCES api.stays_at(stay_code) 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.stays" }o--o| "api.moorages" : "FOREIGN KEY (moorage_id) REFERENCES api.moorages(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.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_at" {
  text description ""
  integer stay_code ""
}
"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.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.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.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"
}

Generated by tbls

api.stays_geojson_view

Description

List stays as geojson

CREATE VIEW stays_geojson_view AS (
 SELECT (st_asgeojson(tbl.*))::jsonb AS geojson
   FROM ( SELECT stay_explore_view.stay_id,
            stay_explore_view.moorage_id,
            stay_explore_view.moorage_name,
            stay_explore_view.stay_name,
            stay_explore_view.arrived,
            stay_explore_view.stay_code,
            stay_explore_view.latitude,
            stay_explore_view.longitude,
            stay_explore_view.stay_notes,
            stay_explore_view.moorage_notes,
            stay_explore_view.has_images,
            stay_explore_view.images,
            st_makepoint(stay_explore_view.longitude, stay_explore_view.latitude) AS st_makepoint
           FROM api.stay_explore_view) tbl
)

Columns

Referenced Tables

Relations

erDiagram


"api.stays_geojson_view" {
  jsonb geojson ""
}

Generated by tbls

api.stays_view

Description

Stays web view

CREATE VIEW stays_view AS (
 SELECT s.id,
    s.name,
    m.name AS moorage,
    m.id AS moorage_id,
    (s.departed - s.arrived) AS duration,
    sa.description AS stayed_at,
    sa.stay_code AS stayed_at_id,
    s.arrived,
    _from.id AS arrived_log_id,
    _from._to_moorage_id AS arrived_from_moorage_id,
    _from._to AS arrived_from_moorage_name,
    s.departed,
    _to.id AS departed_log_id,
    _to._from_moorage_id AS departed_to_moorage_id,
    _to._from AS departed_to_moorage_name,
    s.notes
   FROM api.stays_at sa,
    api.moorages m,
    ((api.stays s
     LEFT JOIN api.logbook _from ON ((_from._from_time = s.departed)))
     LEFT JOIN api.logbook _to ON ((_to._to_time = s.arrived)))
  WHERE ((s.departed IS NOT NULL) AND (_from._to_moorage_id IS NOT NULL) AND (s.name IS NOT NULL) AND (s.stay_code = sa.stay_code) AND (s.moorage_id = m.id))
  ORDER BY s.arrived DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.total_info_view

Description

total_info_view web view

CREATE VIEW total_info_view AS (
 WITH l AS (
         SELECT count(*) AS logs
           FROM api.logbook
        ), s AS (
         SELECT count(*) AS stays
           FROM api.stays
        ), m AS (
         SELECT count(*) AS moorages
           FROM api.moorages
        )
 SELECT l.logs,
    s.stays,
    m.moorages
   FROM l,
    s,
    m
)

Columns

Referenced Tables

Relations

erDiagram


"api.total_info_view" {
  bigint logs ""
  bigint moorages ""
  bigint stays ""
}

Generated by tbls

api.versions_view

Description

Expose as a table view app and system version to API

CREATE VIEW versions_view AS (
 SELECT value AS api_version,
    rtrim("substring"(version(), 0, 17)) AS sys_version,
    ( SELECT pg_extension.extversion AS timescaledb
           FROM pg_extension
          WHERE (pg_extension.extname = 'timescaledb'::name)) AS timescaledb,
    ( SELECT pg_extension.extversion AS postgis
           FROM pg_extension
          WHERE (pg_extension.extname = 'postgis'::name)) AS postgis,
    ( SELECT rtrim("substring"(pg_stat_activity.application_name, 'PostgREST [0-9.]+'::text)) AS postgrest
           FROM pg_stat_activity
          WHERE (pg_stat_activity.application_name ~~* '%postgrest%'::text)
         LIMIT 1) AS postgrest
   FROM app_settings
  WHERE (name = 'app.version'::text)
)

Columns

Referenced Tables

Relations

erDiagram


"api.versions_view" {
  text api_version ""
  text postgis ""
  text postgrest ""
  text sys_version ""
  text timescaledb ""
}

Generated by tbls

api.vessels_view

Description

Expose vessels listing to web api

CREATE VIEW vessels_view AS (
 WITH metrics AS (
         SELECT COALESCE((( SELECT m."time"
                   FROM api.metrics m
                  WHERE (m.vessel_id = current_setting('vessel.id'::text))
                  ORDER BY m."time" DESC
                 LIMIT 1))::text, NULL::text) AS last_metrics
        ), metadata AS (
         SELECT COALESCE((( SELECT m."time"
                   FROM api.metadata m
                  WHERE (m.vessel_id = current_setting('vessel.id'::text))))::text, NULL::text) AS last_contact
        )
 SELECT v.name,
    v.mmsi,
    v.created_at,
    metadata.last_contact,
    ((((now() AT TIME ZONE 'UTC'::text))::timestamp with time zone - (metadata.last_contact)::timestamp with time zone) > 'PT1H10M'::interval) AS offline,
    (((now() AT TIME ZONE 'UTC'::text))::timestamp with time zone - (metadata.last_contact)::timestamp with time zone) AS duration,
    metrics.last_metrics,
    ((((now() AT TIME ZONE 'UTC'::text))::timestamp with time zone - (metrics.last_metrics)::timestamp with time zone) > 'PT1H10M'::interval) AS metrics_offline,
    (((now() AT TIME ZONE 'UTC'::text))::timestamp with time zone - (metrics.last_metrics)::timestamp with time zone) AS duration_last_metrics
   FROM auth.vessels v,
    metadata,
    metrics
  WHERE ((v.owner_email)::text = current_setting('user.email'::text))
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

auth.accounts

Description

users account table

Columns

Constraints

Indexes

Triggers

Relations

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

Generated by tbls

auth.otp

Description

Stores temporal otp code for up to 15 minutes

Columns

Constraints

Indexes

Relations

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"

"auth.otp" {
  text otp_pass ""
  timestamp_with_time_zone otp_timestamp ""
  smallint otp_tries ""
  citext user_email FK ""
}
"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.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 ""
}

Generated by tbls

auth.vessels

Description

vessels table link to accounts email user_id column

Columns

Constraints

Indexes

Triggers

Relations

erDiagram

"auth.vessels" }o--|| "auth.accounts" : "FOREIGN KEY (owner_email) REFERENCES auth.accounts(email) ON DELETE RESTRICT"
"auth.otp" |o--|| "auth.accounts" : "FOREIGN KEY (user_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"
"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.moorages" }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"

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

Generated by tbls

public.aistypes

Description

aistypes AIS Ship Types, https://api.vesselfinder.com/docs/ref-aistypes.html

Columns

Constraints

Indexes

Relations

erDiagram


"public.aistypes" {
  text description ""
  numeric id ""
}

Generated by tbls

public.app_settings

Description

application settings

Columns

Constraints

Indexes

Relations

erDiagram


"public.app_settings" {
  text name "application settings name key"
  text value "application settings value"
}

Generated by tbls

public.badges

Description

Badges descriptions

Columns

Constraints

Indexes

Relations

erDiagram


"public.badges" {
  text description ""
  text name ""
}

Generated by tbls

public.email_templates

Description

email/message templates for notifications

Columns

Constraints

Indexes

Relations

erDiagram


"public.email_templates" {
  text email_content ""
  text email_subject ""
  text name ""
  text pushover_message ""
  text pushover_title ""
}

Generated by tbls

public.geocoders

Description

geo service nominatim url

Columns

Constraints

Indexes

Relations

erDiagram


"public.geocoders" {
  text name ""
  text reverse_url ""
  text url ""
}

Generated by tbls

public.iso3166

Description

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

Columns

Relations

erDiagram


"public.iso3166" {
  text alpha_2 ""
  text alpha_3 ""
  text country ""
  integer id ""
}

Generated by tbls

public.mid

Description

MMSI MID Codes (Maritime Mobile Service Identity) Filtered by Flag of Registration, https://www.marinevesseltraffic.com/2013/11/mmsi-mid-codes-by-flag.html

Columns

Constraints

Indexes

Relations

erDiagram


"public.mid" {
  text country ""
  integer country_id ""
  numeric id ""
}

Generated by tbls

public.process_queue

Description

process queue for async job

Columns

Constraints

Indexes

Relations

erDiagram


"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

Development

docker

Install docker and docker-compose

A full-featured development environment.

With CodeSandbox

• Develop on
  • or via direct link

With DevPod

• 
  • or via direct link

With Docker Dev Environments

• Open in Docker dev-envs!

Git setup

The recommended setup for both core and casual contributors is to always create a fork of the primary repo under their own account. The local repo should have two remotes: upstream pointing to the primary xbgmsharp/postgsail repo, and origin pointing to the user’s own fork. The main branch should track upstream/main, but all new work will be pushed to origin and PRs will be created from there.

# clone the primary fork to your local machine, naming the remote "upstream"
# make sure to replace the URL with the correct one
git clone -o upstream https://github.com/xbgmsharp/postgsail.git
cd martin

# add your own fork as a remote, naming it "origin"
git remote add origin https://github.com/username/postgsail.git

For further setup instructions for IDEs, please see the Getting Involved step after you have installed the necessary tools below.

# Getting a quick glance about your remotes: git remote -v
git remote -v
# Rename the existing remote to "upstream". Your "main" branch will now track "upstream/main"
git remote rename origin upstream

# Add your own fork as a remote, naming it "origin" (adjust the URL)
git remote add origin https://github.com/username/postgsail.git

Contributing New Code

# switch to main branch (tracking upstream/main), and pull the latest changes
git switch main
git fetch upstream

# create a new branch for your work
git switch -c my-new-feature

# edit files, and commit changes
# '-a' will add all modified files
# `-m` allows you to add a short commit message
git commit -a -m "My new feature"

# push the changes to your own fork
# '-u' will track your local branch with the remote
git push -u origin my-new-feature

# Click the link shown by `git push` in the terminal to create a pull request
# from your fork using the GitHub web interface

Getting involved