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.
• AI assistant integration via Model Context Protocol (MCP) for querying vessel data with LLMs.

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
• Kubernetes 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 — PostgreSQL with TimescaleDB (time-series metrics), PostGIS (spatial data), MobilityDB (vessel trajectories), and pg_cron (background jobs)
• Migrations — Goose migration runner that applies the schema, roles, grants, and seed data
• API Server — PostgREST, which auto-generates a REST API directly from the api schema
• Frontend — Vue 3 SPA for monitoring and visualizing logbook entries, stays, and moorages
• Grafana — Monitoring dashboards connected directly to the database
• Telegram Bot — Optional notification service for vessel alerts

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 -->|HTTPS| 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 API]

    %% 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 are two compose files. You can update the default settings by editing docker-compose.yml and docker-compose.dev.yml to your need.

Note

Most PostgSail images are not available in a public registry — they must be built from source. Only api (PostgREST) and app (Grafana) use official upstream images.

Step 0. Build the images

docker compose build

This builds db, migrate, web, and telegram from their upstream git repositories. The web build takes a few minutes as it compiles the Vue 3 frontend.

Step 1. Initialize database

First start the database and wait for it to be ready:

docker compose up db

Step 2. Run migrations

Run the database migrations (applies the schema, roles, grants, and seed data):

docker compose up migrate

The migrate service will run once and exit when complete.

Step 3. Start backend (db, api)

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

docker compose up -d db api

The API is accessible on port HTTP/3000. The database is accessible on port TCP/5432.

You can connect to the database via a web GUI like pgadmin or a client like dbeaver.

docker compose -f docker-compose.yml -f docker-compose.dev.yml up -d pgadmin

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

Step 4. Start frontend (web)

Build and launch the web frontend:

docker compose build web
docker compose up -d 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 services

docker compose is used to start all services. The core services are:

Development services (docker-compose.dev.yml)

Additional services available with the dev compose file:

docker compose -f docker-compose.yml -f docker-compose.dev.yml up -d pgadmin swagger

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

Deploying on a Linux Server

This guide covers provisioning a fresh Linux server and installing Docker before following the Docker Compose deployment guide.

Connect to the server

ssh root@my.server.com

Clone the repository

git clone https://github.com/xbgmsharp/postgsail
cd postgsail

Install Docker

From 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" | \
  tee /etc/apt/sources.list.d/docker.list > /dev/null
apt-get update
apt-get install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Once Docker is installed, follow the Docker Compose guide for configuration, building images, and starting the stack.

Running with Docker Compose

Docker Compose is the recommended way to run PostgSail locally or on a single server.

Prerequisites

• Docker and the Compose plugin installed
• The repository cloned: git clone https://github.com/xbgmsharp/postgsail

Note

Most PostgSail images are not available in a public registry and must be built locally. The only exceptions are api (PostgREST) and app (Grafana), which use official upstream images.

Configuration

Copy the example environment file and edit it with your settings:

cd postgsail
cp .env.example .env

At minimum, set a strong PGRST_JWT_SECRET (at least 32 characters):

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

Paste the output into .env as the value for PGRST_JWT_SECRET.

Building the Images

Before starting the stack, build all custom images:

docker compose build

This builds the following services from source:

The web build in particular takes time as it compiles the Vue 3 frontend with your environment variables baked in.

Starting the Stack

The migrate service waits for db to be healthy automatically, so a single command starts everything in the correct order:

docker compose up -d

Or start services step by step:

# 1. Start the database (waits until healthy)
docker compose up -d db

# 2. Run migrations (exits when complete)
docker compose up migrate

# 3. Start the rest
docker compose up -d api app web telegram

The services will be available at:

• Web UI: http://localhost:8080
• API: http://localhost:3000
• Grafana: http://localhost:3001
• Telegram bot: http://localhost:3005

Verifying the Installation

# Check running containers
docker compose ps

# Check API is responding
curl http://localhost:3000

# Check API logs
docker compose logs api

# Check database logs
docker compose logs db

Development Stack

The development stack adds pgAdmin, Swagger UI, and hot-reload frontend support:

docker compose -f docker-compose.yml -f docker-compose.dev.yml up -d

Additional services:

• pgAdmin: http://localhost:5050
• Swagger UI: http://localhost:8181

Updating

Rebuild images from the latest upstream sources and restart:

docker compose build --pull --no-cache
docker compose up -d

The --pull flag fetches the latest base images; --no-cache forces a full rebuild of each layer.

Stopping

docker compose down

To also remove volumes (destructive — deletes all data):

docker compose down -v

Running with Kubernetes

A manifest for deploying PostgSail on Kubernetes is available in the kubernetes/ directory of the repository. In addition to the application deployments, the manifest includes Services for connecting to each component and Persistent Volume Claims for retaining data between restarts.

Prerequisites

• kubectl CLI installed and configured for a running cluster
• The repository cloned locally: git clone https://github.com/xbgmsharp/postgsail
• A configured .env file (copy .env.example and edit)

Note

Most PostgSail images are not available in a public registry and must be built locally then imported into your cluster. Only api (PostgREST) and app (Grafana) use official upstream images.

Building and importing images

Build all custom images:

docker compose build

Then import them into your cluster. For MicroK8s:

docker save xbgmsharp/postgsail-db:latest     | microk8s ctr images import -
docker save xbgmsharp/postgsail-migrate:latest | microk8s ctr images import -
docker save xbgmsharp/postgsail-web:latest     | microk8s ctr images import -
docker save xbgmsharp/postgsail-telegram-bot:latest | microk8s ctr images import -

For other clusters (e.g. kind):

kind load docker-image xbgmsharp/postgsail-db:latest
kind load docker-image xbgmsharp/postgsail-migrate:latest
kind load docker-image xbgmsharp/postgsail-web:latest
kind load docker-image xbgmsharp/postgsail-telegram-bot:latest

Verify the images are available:

microk8s ctr images ls | grep postgs

Quick Start

Create the namespace:

kubectl create namespace postgsail --dry-run=client -o yaml | kubectl apply -f -

Create secrets from your .env file:

kubectl -n postgsail create secret generic postgsail-secrets \
  --from-env-file=.env \
  --dry-run=client -o yaml | kubectl apply -f -

Apply the manifest:

kubectl apply -f kubernetes/postgsail-deployment.yaml

Note

The cluster must have a Storage Class enabled, which many Kubernetes implementations do by default.

If you find a pod stuck in a pending state, it may be due to no available storage class. See the Settings and Data Storage section below.

Reaching the Server

Load Balancer

The deployment manifest includes a Service resource for attachment to an external load balancer. Once the cluster has a load balancer with an available IP address, your PostgSail instance becomes externally reachable. Find the assigned IP with:

kubectl describe service postgsail -n postgsail

It may take some time for your load balancer to assign addresses.

MicroK8s

An easy-to-configure load balancer is available in MicroK8s:

microk8s enable metallb

Specify a range of local IPs to dedicate to it (default ranges will often work).

Ingress

For local or single-node clusters, an Ingress resource is the simplest way to access the server without setting up a load balancer.

minikube

This command sets up a tunnel into the postgsail service and attempts to open it in a browser:

minikube service postgsail

Depending on how minikube is deployed, you may need to leave the resulting CLI process running to keep the tunnel available.

MicroK8s

Enable the nginx ingress controller (binds to host port 80):

microk8s enable ingress

Then deploy the ingress resource:

kubectl apply -f kubernetes/postgsail-ingress.yaml

The PostgSail web UI is then available at http://localhost.

Resource Architecture

graph TD
    A[Web Browsers] -- HTTPS :443 --> C
    B[API Clients] -- HTTPS :443 --> C
    C(["Ingress\n(nginx / cloud LB)"])

    C -- "/* HTTP :80" --> D
    C -- "/api/* HTTP :3000" --> E

    subgraph services [Kubernetes Services ClusterIP]
        D["Service: postgsail-web :80"]
        E["Service: postgsail-api :3000"]
        K["Service: postgsail-db :5432"]
    end

    subgraph pods [Kubernetes Pods]
        G["Pod: postgsail-web\nnginx · Vue 3 Frontend"]
        H["Pod: postgsail-api\nPostgREST"]
        F["Pod: postgsail-db\nPostgreSQL + TimescaleDB"]
    end

    D --> G
    E --> H
    H -- "SQL :5432" --> K
    K --> F
    F -- mounts --> J

    subgraph storage [Persistent Storage]
        J["PersistentVolumeClaim: postgres-data (10Gi)"]
        I[("PersistentVolumeClaim: grafana-data (5Gi)")]
    end

Settings and Data Storage

The deployment manifest uses Persistent Volumes to retain data across restarts:

• postgres-data (10Gi) — PostgreSQL database
• grafana-data (5Gi) — Grafana dashboards and configuration

Many Kubernetes implementations include a default storage class. If not, pods will remain in a pending state.

MicroK8s

Enable the hostpath storage addon:

microk8s enable hostpath-storage

Then re-apply the deployment manifest.

Checking Deployment Status

# List all pods
kubectl -n postgsail get pods

# Check migration job logs
kubectl -n postgsail logs job/migrate

# Check API logs
kubectl -n postgsail logs deployment/api

# List images (MicroK8s)
microk8s ctr images ls | grep postgs

Uninstall

Remove all resources added to the cluster:

kubectl delete -f kubernetes/postgsail-deployment.yaml

MicroK8s

Disable ingress and storage addons if needed:

microk8s disable ingress
microk8s disable storage

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 natively (TLS termination must be handled by the proxy).
• PostgREST does not check HOST headers — it serves on a port regardless of the hostname used. A reverse proxy lets you enforce correct hostname routing.
• PostgREST only supports simple in-memory caching. If you need more advanced caching, use a reverse proxy like Nginx, Varnish, or Apache with custom rules.
• You may need to host multiple services (API, frontend, Grafana) under a single domain or set of subdomains.

NGINX

Using with NGINX

You should run PostgSail behind an NGINX reverse proxy to enable HTTPS and serve multiple services from a single domain. Here is an example configuration that proxies the PostgSail API, web frontend, and Grafana dashboard.

Example: Subdomain-based routing

The following nginx.conf example routes traffic for three subdomains:

• api.example.com → PostgREST API (port 3000)
• web.example.com → Vue 3 frontend (port 8080)
• app.example.com → Grafana (port 3001)

server {
    listen 443 ssl;
    server_name api.example.com;

    ssl_certificate     /etc/ssl/certs/api.example.com.crt;
    ssl_certificate_key /etc/ssl/private/api.example.com.key;

    location / {
        proxy_pass         http://localhost:3000/;
        proxy_set_header   Host $host;
        proxy_set_header   X-Real-IP $remote_addr;
        proxy_set_header   X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header   X-Forwarded-Proto $scheme;
    }
}

server {
    listen 443 ssl;
    server_name web.example.com;

    ssl_certificate     /etc/ssl/certs/web.example.com.crt;
    ssl_certificate_key /etc/ssl/private/web.example.com.key;

    location / {
        proxy_pass         http://localhost:8080/;
        proxy_set_header   Host $host;
        proxy_set_header   X-Real-IP $remote_addr;
        proxy_set_header   X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header   X-Forwarded-Proto $scheme;
    }
}

server {
    listen 443 ssl;
    server_name app.example.com;

    ssl_certificate     /etc/ssl/certs/app.example.com.crt;
    ssl_certificate_key /etc/ssl/private/app.example.com.key;

    location / {
        proxy_pass         http://localhost:3001/;
        proxy_set_header   Host $host;
        proxy_set_header   X-Real-IP $remote_addr;
        proxy_set_header   X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header   X-Forwarded-Proto $scheme;
    }
}

HTTP to HTTPS redirect

Add this block for each domain to redirect HTTP traffic to HTTPS:

server {
    listen 80;
    server_name api.example.com web.example.com app.example.com;
    return 301 https://$host$request_uri;
}

Apply and reload

sudo nginx -t          # Test configuration
sudo systemctl reload nginx

With Docker Compose

Add NGINX as a service in your docker-compose.yml:

services:
  nginx:
    image: nginx:alpine
    restart: unless-stopped
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf:ro
      - ./certs:/etc/ssl:ro
    depends_on:
      - api
      - web
      - app

For automated TLS certificate management, see Certbot or use the Kubernetes deployment with cert-manager.

Apache

Using with Apache

You should run PostgSail behind an Apache reverse proxy to enable HTTPS, apply rate-limiting per IP, and serve multiple services from a single domain.

First you have to set up a virtual host working on port 443.

Enable necessary modules

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

Example: Subdomain-based routing

The following example routes three subdomains to PostgSail services. Create one VirtualHost block per subdomain:

API (api.example.com → PostgREST port 3000):

<VirtualHost *:443>
    ServerName api.example.com
    ServerAdmin webmaster@localhost
    ProxyPreserveHost On

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

    # Rate-limit to 100 requests/second per IP
    <Location "/">
        SetOutputFilter RATE_LIMIT
        SetEnv rate-limit 100
    </Location>

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

Web frontend (web.example.com → Vue 3 port 8080):

<VirtualHost *:443>
    ServerName web.example.com
    ServerAdmin webmaster@localhost
    ProxyPreserveHost On

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

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

Grafana (app.example.com → Grafana port 3001):

<VirtualHost *:443>
    ServerName app.example.com
    ServerAdmin webmaster@localhost
    ProxyPreserveHost On

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

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

HTTP to HTTPS redirect

Add this for each domain to redirect plain HTTP:

<VirtualHost *:80>
    ServerName api.example.com
    Redirect permanent / https://api.example.com/
</VirtualHost>

Check and restart

sudo apache2ctl configtest
sudo systemctl restart apache2

Upgrade

Updating Containers

Most PostgSail images are built from source and are not available in a public registry. To update to the latest version, rebuild the images from their upstream sources:

docker compose build --pull --no-cache
docker compose up -d

• --pull — fetches the latest base images before building
• --no-cache — forces a full rebuild (avoids stale cached layers)

The two services that use official upstream images (api and app) can be updated with:

docker compose pull api app
docker compose up -d api app

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 and the containers. 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
docker tags postgsail-db postgsail-db:backup

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.

Rebuild the images from the latest sources. Most PostgSail images are not available in a public registry and must be built locally.

docker compose build --pull --no-cache

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

Data Units and Conventions

PostgSail follows SignalK SI unit conventions. All numeric values stored in the database use SI base units:

Data Export

Trip data can be exported in multiple formats via RPC functions:

• GET /rpc/export_logbook_geojson_fn — GeoJSON (route geometry + metadata)
• GET /rpc/export_logbook_gpx_fn — GPX (compatible with Navionics, OpenCPN, etc.)
• GET /rpc/export_logbook_kml_fn — KML (Google Earth)
• GET /rpc/export_logbook_csv_fn — CSV (spreadsheet-compatible)

Notes

• The API runs on port 3000 by default
• All data access is controlled by Row Level Security (RLS) policies scoped per vessel/user via JWT claims
• The SignalK plugin uses vessel_role for continuous data ingestion
• Rate limiting per IP is enforced at the reverse proxy layer (Apache or NGINX) — see reverse proxy guides
• Connection pool limits are configured via PGRST_DB_POOL and related settings

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 "{NOT_NULL}"
        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"
        timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync. {NOT_NULL}"
        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 {NOT_NULL}"
        boolean active "{NOT_NULL}"
        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"
        timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync. {NOT_NULL}"
        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 "{NOT_NULL}"
        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"
        timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync. {NOT_NULL}"
        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 "{NOT_NULL}"
    }

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

    public_badges {
        text description 
        text name "{NOT_NULL}"
    }

    public_email_templates {
        text email_content 
        text email_subject 
        text name "{NOT_NULL}"
        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 "{NOT_NULL}"
    }

    public_metrics_rejected {
        double_precision anglespeedapparent 
        text client_id 
        double_precision courseovergroundtrue 
        double_precision latitude 
        double_precision longitude 
        jsonb metrics 
        timestamp_with_time_zone rejected_at 
        text rejection_reason 
        double_precision speedoverground 
        text status 
        timestamp_with_time_zone time "{NOT_NULL}"
        text vessel_id "{NOT_NULL}"
        double_precision windspeedapparent 
    }

    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}"
        bigint id "bigint primary key (upgraded from integer to avoid sequence exhaustion at sustained load). {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_metadata |o--|| auth_vessels : ""
    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 ""
  bigint id ""
  text payload ""
  timestamp_with_time_zone processed ""
  text ref_id ""
  timestamp_with_time_zone stored ""
}
"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 ""
  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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.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 ""
  timestamp_with_time_zone updated_at ""
}
"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.metrics_explore_view" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}
"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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.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" {
  numeric arrivals_departures ""
  text default_stay ""
  integer default_stay_id ""
  integer id ""
  text moorage ""
  interval total_duration ""
}
"api.stats_logs_view" {
  numeric best_24h_distance_nm ""
  integer best_24h_log_id ""
  text best_24h_route ""
  timestamp_with_time_zone best_24h_window_start ""
  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_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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays_at" {
  text description ""
  integer stay_code ""
}
"api.stays_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.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 ""
  bigint id "bigint primary key (upgraded from integer to avoid sequence exhaustion at sustained load)."
  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 <> ALL (ARRAY['new_stay'::text, 'pre_logbook'::text, 'post_logbook'::text])) AND ((ref_id = current_setting('user.id'::text, true)) OR (ref_id = current_setting('vessel.id'::text, true))))
  ORDER BY id DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.log_view

Description

Log web view

CREATE VIEW log_view AS (
 SELECT id,
    vessel_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 ""
  text vessel_id ""
}

Generated by tbls

api.logbook

Description

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

Columns

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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.moorages" {
  text country ""
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  boolean home_flag ""
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  text name ""
  jsonb nominatim "Output of the nominatim reverse geocoding service, see https://nominatim.org/release-docs/develop/api/Reverse/"
  text notes ""
  jsonb overpass "Output of the overpass API, see https://wiki.openstreetmap.org/wiki/Overpass_API"
  integer stay_code FK "Link api.stays_at with api.moorages via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays" {
  boolean active ""
  timestamp_with_time_zone arrived ""
  timestamp_with_time_zone departed ""
  interval duration "Best to use standard ISO 8601"
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  integer moorage_id FK "Link api.moorages with api.stays via FOREIGN KEY and REFERENCES"
  text name ""
  text notes ""
  integer stay_code FK "Link api.stays_at with api.stays via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays_at" {
  text description ""
  integer stay_code ""
}
"api.metadata" {
  boolean active "trigger monitor online/offline"
  jsonb available_keys "Signalk paths with unit for custom mapping"
  double_precision beam ""
  jsonb configuration "User-defined Signalk path mapping for metrics"
  timestamp_with_time_zone created_at ""
  double_precision height ""
  text ip "Store vessel ip address"
  double_precision length ""
  text mmsi "Maritime Mobile Service Identity (MMSI) number associated with the vessel, link to public.mid"
  text name ""
  text platform ""
  text plugin_version ""
  numeric ship_type "Type of ship associated with the vessel, link to public.aistypes"
  text signalk_version ""
  timestamp_with_time_zone time ""
  timestamp_with_time_zone updated_at ""
  jsonb user_data "User-defined data including vessel polar (theoretical performance), make/model, and preferences"
  text vessel_id FK "Link auth.vessels with api.metadata via FOREIGN KEY and REFERENCES"
}
"auth.vessels" {
  timestamp_with_time_zone created_at ""
  numeric mmsi "MMSI can be optional but if present must be a valid one and unique but must be in numeric range between 100000000 and 800000000"
  text name ""
  citext owner_email FK ""
  name role ""
  timestamp_with_time_zone updated_at ""
  text vessel_id ""
}
"api.metrics" {
  double_precision anglespeedapparent ""
  text client_id "Deprecated client_id to be removed"
  double_precision courseovergroundtrue ""
  double_precision latitude "With CONSTRAINT but allow NULL value to be ignored silently by trigger"
  double_precision longitude "With CONSTRAINT but allow NULL value to be ignored silently by trigger"
  jsonb metrics ""
  double_precision speedoverground ""
  text status ""
  timestamp_with_time_zone time ""
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
  double_precision windspeedapparent ""
}

Generated by tbls

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,
    updated_at
   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 ""
  timestamp_with_time_zone updated_at ""
}

Generated by tbls

api.metadata

Description

Stores metadata received from vessel, aka signalk plugin. single-row-per-vessel store

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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.moorages" {
  text country ""
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  boolean home_flag ""
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  text name ""
  jsonb nominatim "Output of the nominatim reverse geocoding service, see https://nominatim.org/release-docs/develop/api/Reverse/"
  text notes ""
  jsonb overpass "Output of the overpass API, see https://wiki.openstreetmap.org/wiki/Overpass_API"
  integer stay_code FK "Link api.stays_at with api.moorages via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays_at" {
  text description ""
  integer stay_code ""
}

Generated by tbls

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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.moorages" {
  text country ""
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  boolean home_flag ""
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  text name ""
  jsonb nominatim "Output of the nominatim reverse geocoding service, see https://nominatim.org/release-docs/develop/api/Reverse/"
  text notes ""
  jsonb overpass "Output of the overpass API, see https://wiki.openstreetmap.org/wiki/Overpass_API"
  integer stay_code FK "Link api.stays_at with api.moorages via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays" {
  boolean active ""
  timestamp_with_time_zone arrived ""
  timestamp_with_time_zone departed ""
  interval duration "Best to use standard ISO 8601"
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  integer moorage_id FK "Link api.moorages with api.stays via FOREIGN KEY and REFERENCES"
  text name ""
  text notes ""
  integer stay_code FK "Link api.stays_at with api.stays via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}

Generated by tbls

api.metrics_explore_view

Description

metrics explore view web view

CREATE VIEW metrics_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.metrics_explore_view" {
  text key ""
  timestamp_with_time_zone time ""
  text value ""
}

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_summary AS (
         SELECT s.moorage_id,
            min(s.arrived) AS first_seen,
            max(s.departed) AS last_seen,
            sum(s.duration) AS total_duration,
            count(*) AS stay_count,
            min(s.id) FILTER (WHERE (s.arrived = ( SELECT min(stays.arrived) AS min
                   FROM api.stays
                  WHERE ((stays.moorage_id = s.moorage_id) AND (stays.vessel_id = current_setting('vessel.id'::text, true)) AND (stays.active = false))))) AS first_seen_id,
            max(s.id) FILTER (WHERE (s.departed = ( SELECT max(stays.departed) AS max
                   FROM api.stays
                  WHERE ((stays.moorage_id = s.moorage_id) AND (stays.vessel_id = current_setting('vessel.id'::text, true)) AND (stays.active = false))))) AS last_seen_id
           FROM api.stays s
          WHERE ((s.vessel_id = current_setting('vessel.id'::text, true)) AND (s.active = false))
          GROUP BY s.moorage_id, s.id, s.arrived, s.departed
        ), log_summary AS (
         SELECT logs.moorage_id,
            count(DISTINCT logs.id) AS log_count
           FROM ( SELECT logbook._from_moorage_id AS moorage_id,
                    logbook.id
                   FROM api.logbook
                  WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook.active = false) AND (logbook._from_moorage_id IS NOT NULL))
                UNION ALL
                 SELECT logbook._to_moorage_id AS moorage_id,
                    logbook.id
                   FROM api.logbook
                  WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook.active = false) AND (logbook._to_moorage_id IS NOT NULL))) 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,
    (jsonb_array_length((m.user_data -> 'images'::text)) > 0) 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.vessel_id = current_setting('vessel.id'::text, true)) AND (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

The moorages table stores locations where vessels can stay (marinas, anchorages, etc.). Each moorage has geographic coordinates and metadata.

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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.logbook" {
  text _from "Name of the location where the log started, usually a moorage name"
  double_precision _from_lat ""
  double_precision _from_lng ""
  integer _from_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone _from_time ""
  text _to "Name of the location where the log ended, usually a moorage name"
  double_precision _to_lat ""
  double_precision _to_lng ""
  integer _to_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone _to_time ""
  boolean active ""
  double_precision avg_speed "avg speed in knots"
  numeric distance "Distance in Nautical Miles converted mobilitydb meters to NM"
  interval duration "Duration in ISO 8601 format"
  jsonb extra "Computed SignalK metrics such as runtime, current level, etc."
  integer id ""
  double_precision max_speed "max speed in knots"
  double_precision max_wind_speed "true wind speed converted in knots, m/s from signalk plugin"
  text name ""
  text notes ""
  tgeogpoint trip "MobilityDB trajectory, speed in m/s, distance in meters"
  tfloat trip_awa "AWA (Apparent Wind Angle) in degrees converted from radians by signalk plugin"
  tfloat trip_aws "AWS (Apparent Wind Speed), windSpeedApparent in knots converted by signalk plugin"
  tfloat trip_batt_charge "Battery Charge"
  tfloat trip_batt_voltage "Battery Voltage"
  tfloat trip_cog "COG - Course Over Ground True in degrees converted from radians by signalk plugin"
  tfloat trip_depth "Depth in meters, raw from signalk plugin"
  tfloat trip_heading "Heading True in degrees converted from radians, raw from signalk plugin"
  tfloat trip_hum_out "Humidity outside"
  ttext trip_notes ""
  tfloat trip_pres_out "Pressure outside"
  tfloat trip_sog "SOG - Speed Over Ground in knots converted by signalk plugin"
  tfloat trip_solar_power "solar powerPanel"
  tfloat trip_solar_voltage "solar voltage"
  ttext trip_status ""
  tfloat trip_tank_level "Tank currentLevel"
  tfloat trip_temp_out "Temperature outside in Kelvin, raw from signalk plugin"
  tfloat trip_temp_water "Temperature water in Kelvin, raw from signalk plugin"
  tfloat trip_twd "TWD - True Wind Direction in degrees converted from radians, raw from signalk plugin"
  tfloat trip_tws "TWS - True Wind Speed in knots converted from m/s, raw from signalk plugin"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays_at" {
  text description ""
  integer stay_code ""
}
"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 (
 WITH logbook_counts AS (
         SELECT logbook._from_moorage_id AS moorage_id,
            count(*) AS entries
           FROM api.logbook
          WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook._from_moorage_id IS NOT NULL) AND (logbook.active = false))
          GROUP BY logbook._from_moorage_id
        UNION ALL
         SELECT logbook._to_moorage_id AS moorage_id,
            count(*) AS entries
           FROM api.logbook
          WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook._to_moorage_id IS NOT NULL) AND (logbook.active = false))
          GROUP BY logbook._to_moorage_id
        ), logbook_total AS (
         SELECT logbook_counts.moorage_id,
            sum(logbook_counts.entries) AS total_arrivals_departures
           FROM logbook_counts
          GROUP BY logbook_counts.moorage_id
        ), stays_agg AS (
         SELECT stays.moorage_id,
            sum(stays.duration) AS total_duration
           FROM api.stays
          WHERE ((stays.vessel_id = current_setting('vessel.id'::text, true)) AND (stays.active = false))
          GROUP BY stays.moorage_id
        )
 SELECT m.id,
    m.name AS moorage,
    sa.description AS default_stay,
    sa.stay_code AS default_stay_id,
    COALESCE(lt.total_arrivals_departures, (0)::numeric) AS arrivals_departures,
    COALESCE(st.total_duration, 'PT0S'::interval) AS total_duration
   FROM (((api.moorages m
     JOIN api.stays_at sa ON ((m.stay_code = sa.stay_code)))
     LEFT JOIN logbook_total lt ON ((lt.moorage_id = m.id)))
     LEFT JOIN stays_agg st ON ((st.moorage_id = m.id)))
  WHERE ((m.vessel_id = current_setting('vessel.id'::text, true)) AND (m.geog IS NOT NULL))
  ORDER BY COALESCE(st.total_duration, 'PT0S'::interval) DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.stays_explore_view

Description

List moorages notes order by stays

CREATE VIEW stays_explore_view AS (
 SELECT s.id AS stay_id,
    m.id AS moorage_id,
    m.name AS moorage_name,
    s.name AS stay_name,
    s.arrived,
    s.stay_code,
    s.latitude,
    s.longitude,
    s.notes AS stay_notes,
    m.notes AS moorage_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,
    s.id,
    s.name
   FROM (api.stays s
     LEFT JOIN api.moorages m ON ((s.moorage_id = m.id)))
  ORDER BY s.arrived DESC
)

Columns

Referenced Tables

Relations

erDiagram


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

Generated by tbls

api.stats_logs_view

Description

Statistics Logs web view. Includes best_24h_distance_nm, best_24h_window_start,
best_24h_log_id (for deep-link to the logbook entry), and best_24h_route.

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
          WHERE ((l_1.vessel_id = current_setting('vessel.id'::text, true)) AND (l_1.active = false))
        ), best24h AS (
         SELECT b_1.best_distance_nm,
            b_1.window_start,
            b_1.anchor_log_id,
            b_1.route_summary
           FROM best_24h_distance_fn(current_setting('vessel.id'::text, true)) b_1(best_distance_nm, window_start, window_end, anchor_log_id, anchor_log_name, contributing_log_ids, route_summary)
        )
 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,
    b.best_distance_nm AS best_24h_distance_nm,
    b.window_start AS best_24h_window_start,
    b.anchor_log_id AS best_24h_log_id,
    b.route_summary AS best_24h_route
   FROM first_metric fm,
    last_metric lm,
    logbook l,
    meta m,
    best24h b
)

Columns

Referenced Tables

Relations

erDiagram


"api.stats_logs_view" {
  numeric best_24h_distance_nm ""
  integer best_24h_log_id ""
  text best_24h_route ""
  timestamp_with_time_zone best_24h_window_start ""
  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 listing 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.moorages m ON ((s.moorage_id = m.id)))
     JOIN api.stays_at sa ON ((s.stay_code = sa.stay_code)))
     LEFT JOIN api.logbook "from" ON ((("from"._from_time = s.departed) AND ("from"._to_moorage_id IS NOT NULL))))
     LEFT JOIN api.logbook "to" ON (("to"._to_time = s.arrived)))
  WHERE ((s.departed 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

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

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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.moorages" {
  text country ""
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  boolean home_flag ""
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  text name ""
  jsonb nominatim "Output of the nominatim reverse geocoding service, see https://nominatim.org/release-docs/develop/api/Reverse/"
  text notes ""
  jsonb overpass "Output of the overpass API, see https://wiki.openstreetmap.org/wiki/Overpass_API"
  integer stay_code FK "Link api.stays_at with api.moorages via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.logbook" {
  text _from "Name of the location where the log started, usually a moorage name"
  double_precision _from_lat ""
  double_precision _from_lng ""
  integer _from_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone _from_time ""
  text _to "Name of the location where the log ended, usually a moorage name"
  double_precision _to_lat ""
  double_precision _to_lng ""
  integer _to_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone _to_time ""
  boolean active ""
  double_precision avg_speed "avg speed in knots"
  numeric distance "Distance in Nautical Miles converted mobilitydb meters to NM"
  interval duration "Duration in ISO 8601 format"
  jsonb extra "Computed SignalK metrics such as runtime, current level, etc."
  integer id ""
  double_precision max_speed "max speed in knots"
  double_precision max_wind_speed "true wind speed converted in knots, m/s from signalk plugin"
  text name ""
  text notes ""
  tgeogpoint trip "MobilityDB trajectory, speed in m/s, distance in meters"
  tfloat trip_awa "AWA (Apparent Wind Angle) in degrees converted from radians by signalk plugin"
  tfloat trip_aws "AWS (Apparent Wind Speed), windSpeedApparent in knots converted by signalk plugin"
  tfloat trip_batt_charge "Battery Charge"
  tfloat trip_batt_voltage "Battery Voltage"
  tfloat trip_cog "COG - Course Over Ground True in degrees converted from radians by signalk plugin"
  tfloat trip_depth "Depth in meters, raw from signalk plugin"
  tfloat trip_heading "Heading True in degrees converted from radians, raw from signalk plugin"
  tfloat trip_hum_out "Humidity outside"
  ttext trip_notes ""
  tfloat trip_pres_out "Pressure outside"
  tfloat trip_sog "SOG - Speed Over Ground in knots converted by signalk plugin"
  tfloat trip_solar_power "solar powerPanel"
  tfloat trip_solar_voltage "solar voltage"
  ttext trip_status ""
  tfloat trip_tank_level "Tank currentLevel"
  tfloat trip_temp_out "Temperature outside in Kelvin, raw from signalk plugin"
  tfloat trip_temp_water "Temperature water in Kelvin, raw from signalk plugin"
  tfloat trip_twd "TWD - True Wind Direction in degrees converted from radians, raw from signalk plugin"
  tfloat trip_tws "TWS - True Wind Speed in knots converted from m/s, raw from signalk plugin"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays_at" {
  text description ""
  integer stay_code ""
}
"api.metadata" {
  boolean active "trigger monitor online/offline"
  jsonb available_keys "Signalk paths with unit for custom mapping"
  double_precision beam ""
  jsonb configuration "User-defined Signalk path mapping for metrics"
  timestamp_with_time_zone created_at ""
  double_precision height ""
  text ip "Store vessel ip address"
  double_precision length ""
  text mmsi "Maritime Mobile Service Identity (MMSI) number associated with the vessel, link to public.mid"
  text name ""
  text platform ""
  text plugin_version ""
  numeric ship_type "Type of ship associated with the vessel, link to public.aistypes"
  text signalk_version ""
  timestamp_with_time_zone time ""
  timestamp_with_time_zone updated_at ""
  jsonb user_data "User-defined data including vessel polar (theoretical performance), make/model, and preferences"
  text vessel_id FK "Link auth.vessels with api.metadata via FOREIGN KEY and REFERENCES"
}
"auth.vessels" {
  timestamp_with_time_zone created_at ""
  numeric mmsi "MMSI can be optional but if present must be a valid one and unique but must be in numeric range between 100000000 and 800000000"
  text name ""
  citext owner_email FK ""
  name role ""
  timestamp_with_time_zone updated_at ""
  text vessel_id ""
}
"api.metrics" {
  double_precision anglespeedapparent ""
  text client_id "Deprecated client_id to be removed"
  double_precision courseovergroundtrue ""
  double_precision latitude "With CONSTRAINT but allow NULL value to be ignored silently by trigger"
  double_precision longitude "With CONSTRAINT but allow NULL value to be ignored silently by trigger"
  jsonb metrics ""
  double_precision speedoverground ""
  text status ""
  timestamp_with_time_zone time ""
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
  double_precision windspeedapparent ""
}

Generated by tbls

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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.logbook" {
  text _from "Name of the location where the log started, usually a moorage name"
  double_precision _from_lat ""
  double_precision _from_lng ""
  integer _from_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone _from_time ""
  text _to "Name of the location where the log ended, usually a moorage name"
  double_precision _to_lat ""
  double_precision _to_lng ""
  integer _to_moorage_id FK "Link api.moorages with api.logbook via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone _to_time ""
  boolean active ""
  double_precision avg_speed "avg speed in knots"
  numeric distance "Distance in Nautical Miles converted mobilitydb meters to NM"
  interval duration "Duration in ISO 8601 format"
  jsonb extra "Computed SignalK metrics such as runtime, current level, etc."
  integer id ""
  double_precision max_speed "max speed in knots"
  double_precision max_wind_speed "true wind speed converted in knots, m/s from signalk plugin"
  text name ""
  text notes ""
  tgeogpoint trip "MobilityDB trajectory, speed in m/s, distance in meters"
  tfloat trip_awa "AWA (Apparent Wind Angle) in degrees converted from radians by signalk plugin"
  tfloat trip_aws "AWS (Apparent Wind Speed), windSpeedApparent in knots converted by signalk plugin"
  tfloat trip_batt_charge "Battery Charge"
  tfloat trip_batt_voltage "Battery Voltage"
  tfloat trip_cog "COG - Course Over Ground True in degrees converted from radians by signalk plugin"
  tfloat trip_depth "Depth in meters, raw from signalk plugin"
  tfloat trip_heading "Heading True in degrees converted from radians, raw from signalk plugin"
  tfloat trip_hum_out "Humidity outside"
  ttext trip_notes ""
  tfloat trip_pres_out "Pressure outside"
  tfloat trip_sog "SOG - Speed Over Ground in knots converted by signalk plugin"
  tfloat trip_solar_power "solar powerPanel"
  tfloat trip_solar_voltage "solar voltage"
  ttext trip_status ""
  tfloat trip_tank_level "Tank currentLevel"
  tfloat trip_temp_out "Temperature outside in Kelvin, raw from signalk plugin"
  tfloat trip_temp_water "Temperature water in Kelvin, raw from signalk plugin"
  tfloat trip_twd "TWD - True Wind Direction in degrees converted from radians, raw from signalk plugin"
  tfloat trip_tws "TWS - True Wind Speed in knots converted from m/s, raw from signalk plugin"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays" {
  boolean active ""
  timestamp_with_time_zone arrived ""
  timestamp_with_time_zone departed ""
  interval duration "Best to use standard ISO 8601"
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  integer moorage_id FK "Link api.moorages with api.stays via FOREIGN KEY and REFERENCES"
  text name ""
  text notes ""
  integer stay_code FK "Link api.stays_at with api.stays via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.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 stays_explore_view.stay_id,
            stays_explore_view.moorage_id,
            stays_explore_view.moorage_name,
            stays_explore_view.stay_name,
            stays_explore_view.arrived,
            stays_explore_view.stay_code,
            stays_explore_view.latitude,
            stays_explore_view.longitude,
            stays_explore_view.stay_notes,
            stays_explore_view.moorage_notes,
            stays_explore_view.has_images,
            stays_explore_view.images,
            st_makepoint(stays_explore_view.longitude, stays_explore_view.latitude) AS st_makepoint
           FROM api.stays_explore_view) tbl
)

Columns

Referenced Tables

Relations

erDiagram


"api.stays_geojson_view" {
  jsonb geojson ""
}

Generated by tbls

api.stays_view

Description

Stays listing 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 s
     JOIN api.moorages m ON ((s.moorage_id = m.id)))
     JOIN api.stays_at sa ON ((s.stay_code = sa.stay_code)))
     LEFT JOIN api.logbook "from" ON ((("from"._from_time = s.departed) AND ("from"._to_moorage_id IS NOT NULL))))
     LEFT JOIN api.logbook "to" ON (("to"._to_time = s.arrived)))
  WHERE ((s.departed IS NOT NULL) AND (s.name IS NOT NULL))
  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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by logbook_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Log-specific data including actual tags, observations, images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.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"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by moorages_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Mooring-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}
"api.stays" {
  boolean active ""
  timestamp_with_time_zone arrived ""
  timestamp_with_time_zone departed ""
  interval duration "Best to use standard ISO 8601"
  geography_Point_4326_ geog "postgis geography type default SRID 4326 Unit: degres"
  integer id ""
  double_precision latitude ""
  double_precision longitude ""
  integer moorage_id FK "Link api.moorages with api.stays via FOREIGN KEY and REFERENCES"
  text name ""
  text notes ""
  integer stay_code FK "Link api.stays_at with api.stays via FOREIGN KEY and REFERENCES"
  timestamp_with_time_zone updated_at "Timestamp of last row modification, maintained automatically by stays_moddatetime trigger. Use for incremental sync."
  jsonb user_data "User-defined data Stay-specific data including images and custom fields"
  text vessel_id FK "Unique identifier for the vessel associated with the api.metadata entry"
}

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

Constraints

Indexes

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 ""
  bigint id "bigint primary key (upgraded from integer to avoid sequence exhaustion at sustained load)."
  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 postgsail

# 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