SmartPot-DB (PostgreSQL 17 + TimescaleDB)

English | Español

---

English

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

The image includes the schema, the application role, and optional demo seed data. Only the API (SmartPot-API) connects directly to this database; for all other services, the storage layer is transparent.

Data Model

Eleven tables, all using 24-character CHAR(24) identifiers generated by the API:

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

Why PostgreSQL + TimescaleDB

• Typed relational model for business entities, with CHECK constraints replacing the previous $jsonSchema 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 1-year retention policy that drops old chunks automatically. Time-series queries use time_bucket and real-time continuous aggregates (readings_1m, readings_15m, readings_1h).

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

Security

• The administrator account (POSTGRES_USER) owns the schema and is never used by the API.
• The application user (SMARTPOT_DB_USERNAME, default smartpot) only has DML privileges (SELECT, INSERT, UPDATE, DELETE) on its database; it cannot alter the schema or manage roles.
• An optional read-only role (SMARTPOT_READER_USERNAME, default smartpot_reader) grants SELECT only on readings and crops(id, type, kind) for SmartPot-Trainer.
• The container runs hardened (read_only, cap_drop: ALL, no-new-privileges) just 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 user (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 async ingestion role (synchronous_commit = off).
SMARTPOT_READER_USERNAME / SMARTPOT_READER_PASSWORD	Optional read-only role for SmartPot-Trainer over ADBC.

Usage

One-Command Deployment

The image is public: ghcr.io/smartpottech/smartpot-db-postgre. 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-beta/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-beta down (add -v to remove volumes). See the beta 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 any new tables, indexes, or policies without touching existing data:

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

Testing

tests/validate.sh starts the hardened image and validates the schema, hypertable, retention policy, application user permissions, CHECK constraints, continuous aggregates, read-only role, idempotent migration, and demo seed data:

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

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

Publishing and Deployment

Every push to main builds and publishes the image to GHCR (ghcr.io/smartpottech/smartpot-db-postgre) and, when Docker Hub secrets are configured, mirrors it to Docker Hub. 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 configurable retention.
• Continuous aggregates readings_1m, readings_15m, and readings_1h with real-time aggregation (materialized_only = false): used by the API for time-series charts with sample-count weighted averages.
• Roles: smartpot for the application and optional smartpot_reader read-only role (used by the trainer via ADBC). The smartpot_ingest role is provisioned for asynchronous batch ingestion.
• RLS on crops using app.current_tenant_id; tenant isolation is enforced by the API on every query and reinforced by the RLS policy.

License

MIT.

---

Español

Base de datos de SmartPot sobre PostgreSQL 17 + TimescaleDB. Sustituye a la base documental anterior (SmartPot-DB-Mongo) manteniendo el mismo contrato de datos de cara al resto de la plataforma: los identificadores siguen siendo ObjectId hexadecimales de 24 caracteres y los nombres que ven los demás componentes (a través de la API) no cambian. La telemetría vive en una hipertabla de TimescaleDB, pensada para la ingesta continua de lecturas de sensores.

La imagen trae el esquema, el usuario de la aplicación y, opcionalmente, datos de demostración. Solo la API (SmartPot-API) se conecta a esta base; para los demás servicios la migración es transparente.

Modelo de datos

Once tablas, todas con identificadores CHAR(24) generados por la API:

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

Por qué PostgreSQL + TimescaleDB

• Relacional tipado para las entidades de negocio, con restricciones CHECK que reemplazan a los validadores $jsonSchema de la versión anterior, 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 1 año que descarta los fragmentos antiguos (equivale al índice TTL anterior). Las consultas de series temporales usan time_bucket y agregados continuos en tiempo real (readings_1m, readings_15m, readings_1h).

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

Seguridad

• La cuenta administradora (POSTGRES_USER) es la dueña del esquema y no la usa la API.
• El usuario de la aplicación (SMARTPOT_DB_USERNAME, por defecto smartpot) solo tiene permisos de DML (SELECT, INSERT, UPDATE, DELETE) sobre su base; no puede modificar el esquema ni administrar usuarios.
• El rol opcional de solo lectura (SMARTPOT_READER_USERNAME, por defecto smartpot_reader) solo permite SELECT sobre readings y crops(id, type, kind) para SmartPot-Trainer.
• La imagen corre endurecida (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	Usuario 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 (synchronous_commit = off).
SMARTPOT_READER_USERNAME / SMARTPOT_READER_PASSWORD	Rol opcional de solo lectura para SmartPot-Trainer por ADBC.

Uso

Despliegue en un comando

La imagen es pública: ghcr.io/smartpottech/smartpot-db-postgre. 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-beta/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-beta down (con -v borra también los datos). Más detalle en la demo beta.

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. Tras actualizar la imagen, aplica las tablas, índices o políticas nuevas sin tocar los datos:

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

Pruebas

tests/validate.sh arranca la imagen endurecida y valida el esquema, la hipertabla, la retención, los permisos del usuario de la aplicación, las restricciones CHECK, los agregados continuos, el rol lector, la migración idempotente y los datos demo:

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

Lo mismo corre en CI al construir la imagen (con y sin datos demo).

Publicación y despliegue

Cada push a main construye y publica la imagen en GHCR (ghcr.io/smartpottech/smartpot-db-postgre) y, con los secretos de Docker Hub, su réplica. El despliegue a producción lo coordina el workflow central de .github.

Series temporales y roles

• Hipertabla readings con chunks de 1 día, compresión columnar a las 12 horas (segmentada por cultivo y ordenada por fecha) y retención configurable.
• Agregados continuos readings_1m, readings_15m y readings_1h con datos en tiempo real (materialized_only = false): la API los usa para las gráficas y el promedio se pondera por la cantidad de lecturas.
• Roles: smartpot para la aplicación y, opcional, smartpot_reader de solo lectura (lo usa el entrenador por ADBC). El rol smartpot_ingest se crea pero la API todavía no lo usa: la ingesta por lotes con COPY corre con el rol de la aplicación.
• RLS en crops por app.current_tenant_id; hoy la API no fija esa variable, así que el aislamiento entre cuentas lo garantiza la API en cada consulta.

Licencia

MIT.