SmartPot-DB (PostgreSQL 17 + TimescaleDB)

English

SmartPot database powered by PostgreSQL 17 + TimescaleDB. It replaces the legacy document database while preserving the data contract exposed to the rest of the platform: identifiers remain 24-character hexadecimal ObjectIds and field names seen through the API do not change. Sensor telemetry lives in a TimescaleDB hypertable built for continuous high-frequency ingestion.

The image includes the schema, the application roles, and optional demo seed data. Only the API connects directly to this database. For every other service, the storage layer is transparent.

Data Model

Eleven tables, all keyed by 24-character lowercase hexadecimal ObjectIds generated by the API. The engine enforces the ObjectId format with 25 dedicated CHECK constraints.

Table	Contents
`users`	Platform accounts (unique email, BCrypt hash, role).
`crops`	Real or virtual crops. `placement`, `device`, and `health` stored as `JSONB`.
`readings`	Hypertable: sensor telemetry with one typed column per metric.
`actuators`	Outputs for each crop (water pump, UV light, fan, etc.) and their state.
`commands`	Actuator commands and their execution lifecycle.
`notifications`	PWA alerts (account-level and crop-level).
`password_reset_tokens`	SHA-256 hash of password recovery tokens.
`channel_links`	Link between an account and an external notification channel (Telegram).
`crop_channels`	Per-crop channel notification settings; `recipients` stored as `JSONB`.
`virtual_devices`	Simulation configuration for virtual crops.
`access_tokens`	Personal connection tokens (SHA-256 hash, prefix, `READ` or `CONTROL` scope).

Integrity Guarantees

Twelve foreign keys enforce referential integrity. Eleven use ON DELETE CASCADE. notifications.crop_id uses ON DELETE SET NULL, so an account-level alert survives when its crop is deleted.

commands has a partial unique index named commands_one_inflight_per_actuator on actuator_id where status IN ('PENDING', 'SENT'). The engine admits at most one in-flight command per actuator.

Physical ranges are also checked in the engine:

readings.ph from 0 to 14.
readings.humidity and readings.soil_moisture from 0 to 100.
readings.brightness and readings.tds greater than or equal to 0.
readings.atmosphere from 300 to 1100.
readings.temperature from -20 to 60.
At least one reading metric must be non-null.
commands.duration_seconds > 0.
channel_links.failures >= 0.
virtual_devices.interval_seconds > 0.
crop_channels.digest_hours from 1 to 24.

The redundant index readings_crop_measured was removed. The covering index readings_crop_measured_covering remains and includes the seven metrics.

Why PostgreSQL + TimescaleDB

Typed relational model for business entities, with CHECK constraints replacing the previous document validators and unique indexes (email, crop + actuator type, etc.).
JSONB for rich, optional subdocuments (placement, device, health, recipients, events), preserving document-model flexibility.
TimescaleDB for readings: partitioned by time into 1-day chunks (create_hypertable) with columnar compression after 12 hours and a 365-day retention policy that drops old chunks automatically. Time-series queries use time_bucket and real-time continuous aggregates (readings_1m, readings_15m, readings_hourly).

The complete schema lives in schema/schema.sql. It is the single source of truth and is fully idempotent.

Architecture & Topology Diagram

flowchart TB
  subgraph ingest["Telemetry Ingest Layer"]
    api["SmartPot-API (Go 1.27.3)<br/>Role: smartpot / smartpot_ingest<br/>Batch pgx.CopyFrom every 10ms/100 readings"]
  end

  subgraph container["SmartPot-DB Container (TimescaleDB 2.17.2-pg17)"]
    direction TB
    subgraph hardening["Container Security & Health"]
      hc["TCP Healthcheck 127.0.0.1:5432<br/>--start-period=20s --interval=15s<br/>Multi-stage scratch / Non-root postgres"]
    end

    subgraph hypertable["readings Hypertable (Time-Series Partitioning)"]
      direction TB
      raw["Hot Chunks (Uncompressed)<br/>1-Day Time Partitions · Segmented by crop_id"]
      comp["Compressed Chunks (Columnar)<br/>Compression Policy: age > 12 Hours<br/>ORDER BY measured_at DESC"]
      ret["Data Retention Policy<br/>Drop chunks older than 365 Days"]
      raw -->|after 12h| comp
      comp -->|after 365d| ret
    end

    subgraph caggs["Real-Time Continuous Aggregates (CAGGs)"]
      direction TB
      cagg1m["readings_1m (1-Minute Buckets)<br/>reading_count + 7 sensor metrics"]
      cagg15m["readings_15m (15-Minute Buckets)<br/>Interpolated LTTB Time Series"]
      cagg1h["readings_hourly (Hourly Buckets)<br/>Historical Agronomic Trends"]
    end

    subgraph roles["Least-Privilege Database Roles"]
      direction LR
      r_app["smartpot<br/>DML on all public tables<br/>No DDL privileges"]
      r_ingest["smartpot_ingest<br/>SELECT & INSERT only<br/>on readings hypertable"]
      r_reader["smartpot_reader<br/>SELECT on readings & trainer_readings view<br/>Column-level crops(id, type, species)"]
    end
  end

  subgraph consumers["Analytics & ML Consumers"]
    direction LR
    pwa["SmartPot-Web (SolidJS)<br/>Queries CAGGs via API"]
    trainer["SmartPot-Trainer (Python)<br/>ADBC Driver via smartpot_reader"]
  end

  api -->|pgx pool| r_app
  api -->|optional minimal ingest| r_ingest
  r_app --> hypertable
  hypertable --> caggs
  caggs --> pwa
  hypertable -.->|security_barrier view trainer_readings| r_reader
  r_reader --> trainer

  classDef core fill:#067A52,stroke:#0B3D2B,color:#FFFFFF
  classDef leaf fill:#DDF5EA,stroke:#067A52,color:#17261F
  classDef water fill:#E3F2FB,stroke:#1F6FA0,color:#17261F
  classDef deep fill:#0B3D2B,stroke:#06281C,color:#FFFFFF

  class api,trainer,pwa water
  class hypertable,caggs leaf
  class r_app,r_ingest,r_reader,hc deep

Security

The administrator account (POSTGRES_USER) owns the schema and is never used by the API.
The application role (SMARTPOT_DB_USERNAME, default smartpot) has DML privileges (SELECT, INSERT, UPDATE, DELETE) on all tables. It has no DDL privileges and cannot manage roles.
The ingestion role (SMARTPOT_INGEST_USERNAME, default smartpot_ingest) has only SELECT and INSERT on readings.
The read-only role (SMARTPOT_READER_USERNAME, default smartpot_reader) has SELECT on readings, SELECT on trainer_readings, and column-level SELECT on crops(id, type, kind).
The trainer_readings view is declared security_barrier. It exposes readings joined only with crops.type and crops.kind. It never exposes owner_id, device credentials, users, or tokens.
PUBLIC has no CONNECT on the database and no USAGE on schema public.
The container runs hardened (read_only, cap_drop: ALL, no-new-privileges) like the rest of the stack.

Environment Variables

Copy .env.example to .env and fill in the secrets. SMARTPOT_DB_PASSWORD must be at least 16 characters long.

Variable	Purpose
`POSTGRES_USER` / `POSTGRES_PASSWORD`	Administrator account (schema owner).
`POSTGRES_DB`	Application database (default `smartpot`).
`SMARTPOT_DB_USERNAME` / `SMARTPOT_DB_PASSWORD`	Application role (DML only).
`SMARTPOT_SEED_DEMO`	`true` seeds `demo@smartpot.app` with two crops and 48 h of readings. Local/demo use only.
`SMARTPOT_INGEST_USERNAME` / `SMARTPOT_INGEST_PASSWORD`	Optional asynchronous ingestion role.
`SMARTPOT_READER_USERNAME` / `SMARTPOT_READER_PASSWORD`	Optional read-only role for the trainer.

Usage

One-Command Deployment

The image is public: ghcr.io/smartpottech/smartpot-db. You can launch the complete platform, including this service, with a single command and without cloning the repository:

curl -fsSL https://raw.githubusercontent.com/SmartPotTech/.github/main/docker/demo/compose.yaml | docker compose -f - up -d --wait

Open http://localhost:5173 and sign in with demo@smartpot.app / SmartPot2026. Stop it with docker compose -p smartpot-demo down (add -v to remove volumes). See the demo for details.

From Source with Docker Compose

cp .env.example .env     # fill in passwords
docker compose up -d --build

The API connects using a standard PostgreSQL URL, for example:

postgres://smartpot:<APP_PASSWORD>@db-smartpot:5432/smartpot?sslmode=disable

Migrating an Existing Database

The schema is idempotent. After updating the image, apply new tables, indexes, or policies without touching existing data:

docker exec smartpot-db-postgre /opt/smartpot/migrate.sh

scripts/migrate.sh runs a preflight before schema.sql. The preflight expires duplicate PENDING and SENT commands, keeps the newest command per actuator, and removes orphan rows before the foreign keys are created.

If the readings constraints are missing and compression is enabled, the script decompresses every chunk, disables compression, applies schema.sql, re-enables compression, and recompresses chunks older than 12 hours. The process is idempotent and was verified twice in a row.

Testing

sh tests/validate.sh <image> true|false runs 109 checks with the demo seed and 106 without it. The suite covers the schema, hypertable, compression and retention policies, constraints, the 12 foreign keys, the unique in-flight command index, continuous aggregates, role privileges and password authentication, write denials for the read-only and ingest roles, the trainer_readings view, idempotent migration, the destructive compression-recovery path, and a full pg_dump / pg_restore cycle with a checksum of the restored readings.

sh tests/validate.sh smartpot-db-postgre:ci true

The same suite runs in CI whenever the image is built, with and without demo seed data.

Publishing and Deployment

Every push to main builds and publishes the image to GHCR as ghcr.io/smartpottech/smartpot-db. When Docker Hub secrets are configured, it also publishes the mirror image.

The local compose stack tags the image as ghcr.io/smartpottech/smartpot-db-postgre:latest and names its container smartpot-db-postgre. The production stack pulls the published ghcr.io/smartpottech/smartpot-db image and names its container smartpot-db. Production deployment is coordinated by the central workflow in .github.

Time Series and Roles

readings hypertable with 1-day chunks, columnar compression after 12 hours segmented by crop_id and ordered by measured_at DESC, and 365-day retention.
Continuous aggregates readings_1m, readings_15m, and readings_hourly with real-time aggregation (timescaledb.materialized_only = false). Each one has reading_count plus count_temperature, count_humidity, count_brightness, count_ph, count_tds, count_atmosphere, and count_soil_moisture. Each one also has its own 365-day retention policy.
Roles: smartpot has DML on all tables and no DDL. smartpot_ingest has only SELECT and INSERT on readings. smartpot_reader has SELECT on readings, SELECT on trainer_readings, and column-level SELECT on crops(id, type, kind). PUBLIC has no CONNECT on the database and no USAGE on schema public. The administrator owns the schema and is never used by the API.
RLS on crops using app.current_tenant_id. Tenant isolation is enforced by the API on every query and reinforced by the RLS policy.

The interactive diagrams show the full relational schema and the TimescaleDB lifecycle of a reading, in English and Spanish, with links to the exact source lines.

License

MIT.

Español

Base de datos de SmartPot sobre PostgreSQL 17 + TimescaleDB. Reemplaza a la base documental anterior y conserva el contrato de datos que ve el resto de la plataforma: los identificadores siguen siendo ObjectId hexadecimales de 24 caracteres y los nombres de campo que expone la API no cambian. La telemetría vive en una hipertabla de TimescaleDB pensada para ingesta continua de alta frecuencia.

La imagen incluye el esquema, los roles de la aplicación y datos de demostración opcionales. Solo la API se conecta directamente a esta base. Para los demás servicios, la capa de almacenamiento es transparente.

Modelo de datos

Once tablas, todas con identificadores ObjectId hexadecimales de 24 caracteres en minúscula, generados por la API. El motor impone el formato ObjectId con 25 restricciones CHECK dedicadas.

Tabla	Contenido
`users`	Cuentas de la plataforma (correo único, hash BCrypt, rol).
`crops`	Cultivos reales o virtuales. `placement`, `device` y `health` como `JSONB`.
`readings`	Hipertabla: telemetría de sensores. Una columna tipada por variable.
`actuators`	Salidas de cada cultivo (bomba, luz UV, ventilador, etc.) y su estado.
`commands`	Órdenes a los actuadores y su ciclo de vida.
`notifications`	Alertas de la PWA (cuenta y cultivo).
`password_reset_tokens`	Solo el SHA-256 del token de recuperación.
`channel_links`	Vínculo de una cuenta con un canal externo (Telegram).
`crop_channels`	Avisos de un cultivo por un canal; `recipients` como `JSONB`.
`virtual_devices`	Configuración de simulación de un cultivo virtual.
`access_tokens`	Tokens de conexión personal (hash SHA-256, prefijo, alcance `READ`/`CONTROL`).

Garantías de integridad

Doce llaves foráneas garantizan la integridad referencial. Once usan ON DELETE CASCADE. notifications.crop_id usa ON DELETE SET NULL para conservar el aviso de cuenta cuando se borra el cultivo.

commands tiene un índice único parcial llamado commands_one_inflight_per_actuator sobre actuator_id cuando status está en PENDING o SENT. El motor admite como máximo un comando en vuelo por actuador.

Los rangos físicos también se validan en el motor:

readings.ph de 0 a 14.
readings.humidity y readings.soil_moisture de 0 a 100.
readings.brightness y readings.tds mayores o iguales que 0.
readings.atmosphere de 300 a 1100.
readings.temperature de -20 a 60.
Al menos una métrica de la lectura debe ser distinta de nulo.
commands.duration_seconds > 0.
channel_links.failures >= 0.
virtual_devices.interval_seconds > 0.
crop_channels.digest_hours de 1 a 24.

El índice redundante readings_crop_measured se eliminó. Se conserva el índice cubriente readings_crop_measured_covering, que incluye las siete métricas.

Por qué PostgreSQL + TimescaleDB

Relacional tipado para las entidades de negocio, con restricciones CHECK que reemplazan a los validadores anteriores e índices únicos (correo, cultivo + tipo de actuador, etc.).
JSONB para los subdocumentos ricos y opcionales (lugar, dispositivo, salud, destinatarios, eventos), sin perder la flexibilidad del modelo documental.
TimescaleDB para readings: la tabla se particiona por tiempo en fragmentos de 1 día (create_hypertable) con compresión columnar a las 12 horas y una política de retención de 365 días que descarta los fragmentos antiguos. Las consultas de series temporales usan time_bucket y los agregados continuos en tiempo real (readings_1m, readings_15m, readings_hourly).

El esquema completo está en schema/schema.sql. Es la única definición del modelo y es idempotente.

Diagrama de arquitectura y topología

flowchart TB
  subgraph ingest["Telemetry Ingest Layer"]
    api["SmartPot-API (Go 1.27.3)<br/>Role: smartpot / smartpot_ingest<br/>Batch pgx.CopyFrom every 10ms/100 readings"]
  end

  subgraph container["SmartPot-DB Container (TimescaleDB 2.17.2-pg17)"]
    direction TB
    subgraph hardening["Container Security & Health"]
      hc["TCP Healthcheck 127.0.0.1:5432<br/>--start-period=20s --interval=15s<br/>Multi-stage scratch / Non-root postgres"]
    end

    subgraph hypertable["readings Hypertable (Time-Series Partitioning)"]
      direction TB
      raw["Hot Chunks (Uncompressed)<br/>1-Day Time Partitions · Segmented by crop_id"]
      comp["Compressed Chunks (Columnar)<br/>Compression Policy: age > 12 Hours<br/>ORDER BY measured_at DESC"]
      ret["Data Retention Policy<br/>Drop chunks older than 365 Days"]
      raw -->|after 12h| comp
      comp -->|after 365d| ret
    end

    subgraph caggs["Real-Time Continuous Aggregates (CAGGs)"]
      direction TB
      cagg1m["readings_1m (1-Minute Buckets)<br/>reading_count + 7 sensor metrics"]
      cagg15m["readings_15m (15-Minute Buckets)<br/>Interpolated LTTB Time Series"]
      cagg1h["readings_hourly (Hourly Buckets)<br/>Historical Agronomic Trends"]
    end

    subgraph roles["Least-Privilege Database Roles"]
      direction LR
      r_app["smartpot<br/>DML on all public tables<br/>No DDL privileges"]
      r_ingest["smartpot_ingest<br/>SELECT & INSERT only<br/>on readings hypertable"]
      r_reader["smartpot_reader<br/>SELECT on readings & trainer_readings view<br/>Column-level crops(id, type, species)"]
    end
  end

  subgraph consumers["Analytics & ML Consumers"]
    direction LR
    pwa["SmartPot-Web (SolidJS)<br/>Queries CAGGs via API"]
    trainer["SmartPot-Trainer (Python)<br/>ADBC Driver via smartpot_reader"]
  end

  api -->|pgx pool| r_app
  api -->|optional minimal ingest| r_ingest
  r_app --> hypertable
  hypertable --> caggs
  caggs --> pwa
  hypertable -.->|security_barrier view trainer_readings| r_reader
  r_reader --> trainer

  classDef core fill:#067A52,stroke:#0B3D2B,color:#FFFFFF
  classDef leaf fill:#DDF5EA,stroke:#067A52,color:#17261F
  classDef water fill:#E3F2FB,stroke:#1F6FA0,color:#17261F
  classDef deep fill:#0B3D2B,stroke:#06281C,color:#FFFFFF

  class api,trainer,pwa water
  class hypertable,caggs leaf
  class r_app,r_ingest,r_reader,hc deep

Seguridad

La cuenta administradora (POSTGRES_USER) es la dueña del esquema y no la usa la API.
El rol de la aplicación (SMARTPOT_DB_USERNAME, por defecto smartpot) tiene permisos de DML (SELECT, INSERT, UPDATE, DELETE) sobre todas las tablas. No tiene permisos de DDL ni puede administrar roles.
El rol de ingesta (SMARTPOT_INGEST_USERNAME, por defecto smartpot_ingest) solo tiene SELECT e INSERT sobre readings.
El rol de solo lectura (SMARTPOT_READER_USERNAME, por defecto smartpot_reader) tiene SELECT sobre readings, SELECT sobre trainer_readings y SELECT a nivel de columna sobre crops(id, type, kind).
La vista trainer_readings está declarada como security_barrier. Expone las lecturas unidas solo con crops.type y crops.kind. Nunca expone owner_id, credenciales de dispositivo, usuarios ni tokens.
PUBLIC no tiene CONNECT sobre la base ni USAGE sobre el esquema public.
El contenedor corre endurecido (read_only, cap_drop: ALL, no-new-privileges) igual que el resto del stack.

Variables de entorno

Copia .env.example a .env y complétalo. SMARTPOT_DB_PASSWORD debe tener al menos 16 caracteres.

Variable	Uso
`POSTGRES_USER` / `POSTGRES_PASSWORD`	Cuenta administradora (dueña del esquema).
`POSTGRES_DB`	Base de la aplicación (por defecto `smartpot`).
`SMARTPOT_DB_USERNAME` / `SMARTPOT_DB_PASSWORD`	Rol de la aplicación (solo DML).
`SMARTPOT_SEED_DEMO`	`true` carga `demo@smartpot.app` con dos cultivos y 48 h de lecturas. Solo en local o demo.
`SMARTPOT_INGEST_USERNAME` / `SMARTPOT_INGEST_PASSWORD`	Rol opcional de ingesta asíncrona.
`SMARTPOT_READER_USERNAME` / `SMARTPOT_READER_PASSWORD`	Rol opcional de solo lectura para el entrenador.

Uso

Despliegue en un comando

La imagen es pública: ghcr.io/smartpottech/smartpot-db. La plataforma completa, con este servicio incluido, se levanta con un solo comando y sin descargar el código:

curl -fsSL https://raw.githubusercontent.com/SmartPotTech/.github/main/docker/demo/compose.yaml | docker compose -f - up -d --wait

Abre http://localhost:5173 e ingresa con demo@smartpot.app / SmartPot2026. Se detiene con docker compose -p smartpot-demo down (con -v borra también los datos). Más detalle en la demo.

Desde el código con Docker Compose

cp .env.example .env     # completa las contraseñas
docker compose up -d --build

La API se conecta con una URL de PostgreSQL, por ejemplo:

postgres://smartpot:<APP_PASSWORD>@db-smartpot:5432/smartpot?sslmode=disable

Migración de una base existente

El esquema es idempotente. Después de actualizar la imagen, aplica las tablas, índices o políticas nuevas sin tocar los datos:

docker exec smartpot-db-postgre /opt/smartpot/migrate.sh

scripts/migrate.sh ejecuta una revisión previa antes de schema.sql. La revisión expira los comandos PENDING y SENT duplicados, conserva el más reciente por actuador y elimina las filas huérfanas antes de crear las llaves foráneas.

Si faltan las restricciones de readings y la compresión está activa, el script descomprime todos los fragmentos, desactiva la compresión, aplica schema.sql, vuelve a activar la compresión y recomprime los fragmentos con más de 12 horas. El proceso es idempotente y se verificó dos veces seguidas.

Pruebas

sh tests/validate.sh <imagen> true|false ejecuta 109 comprobaciones con los datos demo y 106 sin ellos. La suite cubre el esquema, la hipertabla, las políticas de compresión y retención, las restricciones, las 12 llaves foráneas, el índice único de comandos en vuelo, los agregados continuos, los privilegios de los roles y la autenticación por contraseña, la denegación de escritura para los roles de solo lectura e ingesta, la vista trainer_readings, la migración idempotente, la ruta destructiva de recuperación de compresión y un ciclo completo de pg_dump / pg_restore con checksum de las lecturas restauradas.

sh tests/validate.sh smartpot-db-postgre:ci true

La misma suite corre en CI cada vez que se construye la imagen, con y sin datos demo.

Publicación y despliegue

Cada push a main construye y publica la imagen en GHCR como ghcr.io/smartpottech/smartpot-db. Con los secretos de Docker Hub, también publica la imagen réplica.

El compose local etiqueta la imagen como ghcr.io/smartpottech/smartpot-db-postgre:latest y usa el contenedor smartpot-db-postgre. El stack de producción descarga la imagen publicada ghcr.io/smartpottech/smartpot-db y usa el contenedor smartpot-db. El despliegue a producción lo coordina el workflow central de .github.

Series temporales y roles

Hipertabla readings con fragmentos de 1 día, compresión columnar a las 12 horas segmentada por crop_id y ordenada por measured_at DESC, y retención de 365 días.
Agregados continuos readings_1m, readings_15m y readings_hourly con datos en tiempo real (timescaledb.materialized_only = false). Cada uno tiene reading_count y las columnas count_temperature, count_humidity, count_brightness, count_ph, count_tds, count_atmosphere y count_soil_moisture. Cada uno tiene además su propia política de retención de 365 días.
Roles: smartpot tiene DML sobre todas las tablas y no tiene DDL. smartpot_ingest solo tiene SELECT e INSERT sobre readings. smartpot_reader tiene SELECT sobre readings, SELECT sobre trainer_readings y SELECT a nivel de columna sobre crops(id, type, kind). PUBLIC no tiene CONNECT sobre la base ni USAGE sobre el esquema public. La cuenta administradora es la dueña del esquema y nunca la usa la API.
RLS en crops por app.current_tenant_id. La API aplica el aislamiento entre cuentas en cada consulta y la política de RLS lo refuerza.

Los diagramas interactivos muestran el esquema relacional completo y el ciclo de vida de una lectura en TimescaleDB, en español e inglés, con enlaces a las líneas exactas del código.

Licencia

MIT.

Name		Name	Last commit message	Last commit date
.github		.github
docs/diagrams		docs/diagrams
init		init
schema		schema
scripts		scripts
tests		tests
.dockerignore		.dockerignore
.env.example		.env.example
.gitattributes		.gitattributes
.gitignore		.gitignore
.snyk		.snyk
Dockerfile		Dockerfile
LICENSE		LICENSE
README.md		README.md
compose.yaml		compose.yaml

Latest commit

History

Folders and files

Repository files navigation

SmartPot-DB (PostgreSQL 17 + TimescaleDB)

English

Data Model

Integrity Guarantees

Why PostgreSQL + TimescaleDB

Architecture & Topology Diagram

Security

Environment Variables

Usage

One-Command Deployment

From Source with Docker Compose

Migrating an Existing Database

Testing

Publishing and Deployment

Time Series and Roles

License

Español

Modelo de datos

Garantías de integridad

Por qué PostgreSQL + TimescaleDB

Diagrama de arquitectura y topología

Seguridad

Variables de entorno

Uso

Despliegue en un comando

Desde el código con Docker Compose

Migración de una base existente

Pruebas

Publicación y despliegue

Series temporales y roles

Licencia

About

Topics

Resources

Code of conduct

Contributing

Security policy

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Languages