Database & State Management

The Planovi Backend couples Supabase Edge Functions with a multi-tenant PostgreSQL schema, real-time channels, and S3-compatible object storage.

Multi-Tenancy Architecture

Planovi operates across two distinct organizational hierarchy levels:

Commercial Companies (company_id): Standard business CRM, appointment scheduling, and customer deal flow.
Energy Cooperatives (coop_id): Community microgrids, collective generation (PV), energy sharing ledgers, and governance.

erDiagram
    COOPERATIVE ||--o{ COOP_MEMBERS : contains
    COOPERATIVE ||--o{ IOT_DEVICES : owns
    COOPERATIVE ||--o{ ENERGY_LEDGER : balances
    COOPERATIVE ||--o{ DECLARATIONS : governs
    
    IOT_DEVICES ||--o{ TELEMETRY_RAW : produces
    IOT_DEVICES ||--o{ TELEMETRY_HOURLY : aggregates
    
    COOP_MEMBERS ||--o{ INVOICES : billed
    COOP_MEMBERS ||--o{ DECLARATION_SUBMISSIONS : files

Global vs Per-Cooperative System Settings Strategy

Functions such as ingest-telemetry and billing-calculator employ a hierarchical settings resolution strategy:

Global Defaults (coop_id IS NULL): Fallback configurations, system-wide polling intervals, and default network timeouts used by crons and edge functions.
Per-Cooperative Overrides (coop_id = '<uuid>'): Specific pricing plans, battery discharge thresholds, and custom billing tariffs configured via the Flutter Management Portal.

// Resolution logic executed in Edge Functions:
const { data: coopSettings } = await supabaseAdmin
  .from('system_settings')
  .select('*')
  .eq('coop_id', targetCoopId)
  .maybeSingle();

const effectiveSettings = coopSettings ?? globalDefaults;

Telemetry Time-Series & Live State Engine

IoT smart meters and inverters transmit high-frequency telemetry every 15–60 seconds. To maintain extreme responsiveness in the mobile app without querying millions of raw rows:

flowchart LR
    Hardware[IoT Smart Meters] -->|Push Telemetry| Ingest[ingest-telemetry]
    Ingest -->|Insert Fast| RawTable[(telemetry_raw)]
    Ingest -->|Trigger| LiveCache[refresh-live-state]
    LiveCache -->|Update Snapshot| LiveTable[(coop_live_state)]
    LiveTable -->|Realtime WebSocket| FlutterClient[Planovi Flutter App]
    
    RawTable -->|Hourly Cron| HourlyAgg[aggregate-hourly]
    HourlyAgg -->|Rollup| HourlyTable[(telemetry_hourly)]
    HourlyTable -->|Daily Cron| DailyAgg[aggregate-daily]
    DailyAgg -->|Rollup| DailyTable[(telemetry_daily)]
    DailyAgg -->|Purge >30d| CleanupFn[cleanup-telemetry]

1. `coop_live_state`

High-speed single-row snapshot per cooperative containing current grid load, instantaneous solar generation (kW), battery state-of-charge (SoC %), and member net consumption.
The Flutter application listens to Supabase Realtime broadcast updates on this table, achieving instant screen refreshes with zero polling overhead.

2. Time-Series Aggregation Pipeline

telemetry_raw: High-resolution timeseries table. Retained for 30 days before automated pruning by cleanup-telemetry.
telemetry_hourly: Computed by aggregate-hourly. Stored indefinitely for 12-month trend comparisons.
telemetry_daily: Computed by aggregate-daily. Powers monthly billing calculations and annual energy balance certificates.

Supabase Storage Buckets

Unstructured binary files generated or ingested by Edge Functions are organized into designated storage buckets:

Bucket Name	Access Policy	Edge Function Producers / Consumers	Contents
`invoices`	Private (Signed URLs)	`generate-invoice-pdf`, `generate-monthly-invoices`	Rendered PDF member invoices and monthly statements.
`declarations`	Restricted	`upload-declaration`, `process-declaration-ocr`, `docx-to-html`	Scanned member declarations (PDF, JPG, PNG) and Word templates.
`resolutions`	Private	`generate-resolution-document`, `generate-prefilled-docx`	Statutory cooperative voting outcome documents.
`reports`	Private	`export-csv`, `generate-report`	Accounting audit dumps, CSV ledgers, and DSO tariff balance sheets.