# טיוטה: נתוני אנרגיה בניהול

> טיוטת PR #69 — לא מוזגה. מקור: docs/admin-energy-read.md · 34bdd4e · 2026-11-21.

Status: source implementation and isolated PostgreSQL verification, 21 November 2026. No hosted database migration or paid Supabase resource was created by this change.

## Purpose and separation

The Admin transactions screen has separate datasets for company billing obligations and energy usage. Billing obligations remain subscription charges and charging commissions. Energy usage combines completed platform charging sessions with customer-imported electricity, petrol, diesel and LPG records. These usage amounts are not company revenue, verified supplier statements or settled payments.

The existing canonical tenant report is `public.lne_energy_report` in `infra/energy/003_unified_energy_report.sql`. It requires a tenant-member JWT and cannot be reused by impersonating a customer. The new `public.lne_admin_energy_page` is an additive company read projection; the existing tenant function, private table grants and customer policies are unchanged.

## Access boundary

The application must authorize the current company staff identity for `transactions.read` before obtaining the server-only database client. The SQL function is `SECURITY INVOKER`, `STABLE`, uses an empty search path and qualifies every relation. Execution is revoked from `PUBLIC`, `anon` and `authenticated`, and granted only to the existing `service_role`. It uses that role's existing privileges, creates no table, changes no RLS policy and grants no direct access to the private energy ledger.

The public API returns only customer name and ID, source-prefixed event ID, energy kind, provider, event time, quantity/unit, amount/currency, provenance and reconciliation state. Vehicle IDs, registration numbers, station names, coordinates, customer contact details, imported-by identities, external references and raw provider payloads are excluded. Service credentials remain server-side.

## Canonical behavior

The new query preserves the canonical report's provider/reference reconciliation:

- Every matching key includes the customer tenant, so identical references from different customers cannot collapse into one event.
- A completed platform event remains canonical when its imported counterpart differs in date or reporting period. Counterparts outside the requested period are considered before period filtering.
- Matching electric imports do not add a second event. Differences in amount, quantity, millisecond event time or an explicitly assigned vehicle produce `reconciliation_required`.
- Fuel records remain independent of electric sessions. Unmatched imports remain visible.
- Future ingestion timestamps are excluded consistently with the canonical report.
- Existing provenance remains `customer_import_unverified` or `platform_session_unverified`. This change creates no `provider_verified` state or live supplier connection.

There was no reusable private canonical helper in the existing schema. Consequently this narrow read projection follows the same CTE reconciliation rules, with regression tests comparing its output directly against the existing per-tenant function. Changes to that canonical algorithm must update and rerun the comparison tests.

## Request and response

`GET /api/admin/energy` uses the same staff permission as the billing transactions screen. Supported parameters are a literal customer-name search, current plan, `kind`, `status`, inclusive `from`/`to` dates, page and page size. `kind` is electricity, petrol, diesel or LPG; status is unverified or reconciliation required. Empty/all filter values mean no filter. Date filtering is on usage occurrence, with both Israeli local midnights converted independently for DST.

The default period is 30 days. Maximum range is 366 inclusive calendar dates, maximum page size is 50 and the maximum page number is 1,000. The SQL function repeats enum/date/paging validation, limits offset to 49,950 and rejects unaligned offsets. Search percent/underscore characters are literal text.

The result is an `AdminResult<AdminPage<AdminEnergyTransaction>>`. A page includes exact matching row count and next/previous indicators. Count and items come from one SQL statement snapshot. Offset pages are independent requests, so changes between requests can alter page contents; this is an operational list, not an immutable accounting export.

Amounts use integer ILS minor units. Quantities use milli-units: one Wh equals one milli-kWh for electricity; fuel quantities use milli-litres. The server validates safe integers, unit/source consistency, allowed provenance, page metadata and unique row IDs before returning the projection.

## Activation and failure behavior

Apply `supabase/migrations/20261121143921_company_admin_energy_read.sql` to the existing selected database through the reviewed release workflow. The database must already have the independent unified energy extension (`infra/energy/001` through `004`). The function can be created before that extension, but invocation explicitly reports `LNE_ADMIN_ENERGY_NOT_CONFIGURED` until the canonical table and report exist.

If the RPC, extension or authorized connection is missing, the Admin energy screen receives an explicit unavailable result. It never substitutes old charging-only tables or preview fixtures. The synthetic UI preview is selected separately and is always labeled `source: preview`.

## Verification

`scripts/test-company-admin-energy.mjs` runs only against a loopback PostgreSQL database named `ev1_test_final_ci`. All fixtures roll back. The isolated run passed 28 checks covering service-only ACLs, invoker/stable configuration, customer/anonymous/forged-claim denial, canonical report equivalence, cross-tenant and cross-period reconciliation, privacy minimization, provenance, DST-aware boundaries, literal search, bounded paging and invalid parameters. A temporarily absent canonical table yields the expected unavailable condition.

The TypeScript data tests additionally verify RPC parameter bounds, safe response projection, missing-RPC handling and rejection of invented provider verification. No real customer data or hosted credentials are used in these checks.
