Sparks low-balance notification

Purpose

A tenant nearly out of Sparks gets one email telling them so, and never a second one for the same calendar period no matter how many times the signal that triggered it repeats. The invariant that makes it trustworthy is that the dedup is a row in Postgres, not a flag in a process: notify_low_balance (services/commerce/sparks_low_balance.py 34 to 112) inserts into sparks_low_balance_alerts before it does anything else, and a UniqueConstraint on (tenant_id, period_key) is what actually stops a resend, a retried delivery, a replay inside the signature's skew window, or two workers racing the same crossing all lose to the same constraint, not to an in-memory guard that a second process or a restart would forget.

When to use

Read it before changing what fires the "nearly out of Sparks" email, before touching sparks_low_balance_alerts, or when someone asks "why did this tenant get the email twice" or "why didn't this tenant get the email at all." Trigger phrases: "low balance email", "90% consumed", "Guthaben wird knapp", "Sparks gehen zur Neige", "low-balance-alert", "sparks_low_balance_alerts", "crossing_seq".

Not this recipe: the endpoint this function is called from, POST /internal/sparks/low-balance-alert (api/routers/prima_inbound_router.py), also records the crossing in prima_balance_events and charges an auto-top-up card via auto_topup_service.charge_if_due, that idempotency key, the charge outcomes (charged | declined | unknown | disabled | skipped | not_configured) and the dunning mail sequence belong to a separate auto-top-up recipe; this one covers only the email and its dedup. The email template itself (EmailService.send_sparks_low_balance_email, services/email_service.py 644 to 708) is documented here only as far as the two call shapes this function chooses between, its transport selection (resend vs smtp) and base template are _shared concerns this recipe does not restate. The HMAC verification that gates the endpoint calling this function (services/commerce/prima_inbound_sig.py) is _shared/hmac-seam-pattern, not repeated here except where its outcome changes what this function sees.

Stack assumptions

Labs at af7a352: fastapi==0.118.2, a synchronous SQLAlchemy Session passed in by the caller (no dependency injection inside this function itself, it is a plain service function, not a route handler), sqlalchemy.exc.IntegrityError as the dedup signal. core.sparks_config.LOW_BALANCE_CONSUMED_THRESHOLD = 0.90 (core/sparks_config.py line 110) and MICRO_PER_SPARK = 1_000 (line 50) are read by the caller (prima_inbound_router._sparks), not by this function, notify_low_balance itself takes already-converted Sparks integers (balance_sparks, threshold_sparks), never µCHF. services.email_service.email_service is imported lazily inside the function body (sparks_low_balance.py line 99), after the dedup row is already flushed, see Application logic for why that ordering matters. services.workspace.models.Workspace and (imported lazily, line 91) models.user_model.User are read directly by id; no workspace-scoping middleware or session is involved, because this function has no request context of its own, see Security & tenancy.

Environment: none read directly by this function. The caller's environment gate is PRIMA_LOW_BALANCE_ALERT_SECRET (core/config.py line 780, Optional[SecretStr]), unset means the HTTP endpoint refuses every call with 503 before this function is ever reached (api/routers/prima_inbound_router.py 126 to 131). Fail behaviour: this function never raises (module docstring, line 54; the email try/except Exception at lines 98 to 109 is explicit about this) and always returns a bool, so a caller cannot make notification failure block whatever triggered it.

Data model

The dedup table (models/sparks_billing.py lines 105 to 124; migration sparks_billing_001, migrations/versions/20260808_0001_sparks_billing.py lines 87 to 97):

sparks_low_balance_alerts (
  id                 uuid PRIMARY KEY,
  tenant_id          uuid NOT NULL,        -- == a Labs workspace id, no FK (mirrored tenant, same convention as sparks_grants.tenant_id)
  period_key         varchar(20) NOT NULL, -- 'YYYY-MM', see _period_key()
  percent_remaining  float NULL,
  created_at         timestamp NOT NULL
);
CREATE UNIQUE INDEX uq_sparks_low_balance_alerts_tenant_period ON sparks_low_balance_alerts(tenant_id, period_key);
CREATE INDEX ix_sparks_low_balance_alerts_tenant ON sparks_low_balance_alerts(tenant_id);

period_key comes from _period_key(now) (sparks_low_balance.py lines 30 to 31): now.strftime("%Y-%m"), UTC unless the caller passes a now. One row per tenant per calendar month, ever, the table is never pruned or updated, only inserted into; a second INSERT for the same (tenant_id, period_key) is the mechanism, not an exception to it.

What is NOT stored: which crossing (crossing_seq, if any) triggered the row, the table predates ADR-0036's crossing_seq and was never widened to carry it, so a row proves an email was sent this period but not which of possibly several crossings caused it; the Sparks figures shown in the email (balance_sparks, threshold_sparks), only percent_remaining (nullable, display-only, from the legacy wire shape) is kept; whether the email actually sent successfully, the row is written and committed regardless of SMTP/Resend outcome (see Application logic), so "a row exists" means "we attempted at most one send this period," not "delivery succeeded."

API surface

notify_low_balance is a service function, not a route, so there is no method/path of its own. It is reached through exactly one production entry point:

Route	Body	Codes	Notes
POST /internal/sparks/low-balance-alert (api/routers/prima_inbound_router.py 112 to 156)	LowBalanceAlertRequest (75 to 109): tenant_id: UUID, percent_remaining: float [0.0, 1.0], plus ADR-0036-additive optionals crossing_seq: int | None (>=1), balance_micro: int | None (no ge=0, deliberately, a post-paid tenant's overdraft is legally negative), allowance_micro: int | None (>=0), threshold_micro: int | None (>0), occurred_at: datetime | None	200 {"ok": true, "duplicate": bool, "email_sent": bool, "topup": "..."}; 401 "Invalid signature"; 503 "Low-balance alert seam not configured"	mounted surface_exempt("public", ...) (app_fastapi.py line 892), no Labs session, HMAC only (X-Prima-Signature, X-Prima-Timestamp, X-Prima-Nonce headers, verified fail-closed at lines 135 to 145 before any handler code runs)

Both non-200 codes are raised before notify_low_balance is ever called. Once inside the handler, notify_low_balance itself never raises and never produces an HTTP status of its own, its bool return becomes the email_sent field. Errors from the handler go through Labs' global envelope (app_fastapi.py http_exception_handler, line 725): for a plain string detail the body is {"error": <message>, "code": <ErrorCode value>, "request_id": <str>, "timestamp": <iso8601>, "details": null} (schemas/error_schemas.py create_error_response, lines 112 to 136); 401 maps to ErrorCode.INVALID_CREDENTIALS, 503 is absent from error_code_map (line 731) so it falls through to ErrorCode.INTERNAL_ERROR.

Application logic

notify_low_balance(db, *, tenant_id, percent_remaining, now=None, balance_sparks=None, threshold_sparks=None) (sparks_low_balance.py 34 to 112), in order:

1. Resolve now (UTC if not given) and period_key = now.strftime("%Y-%m") (60 to 61).
2. Build a SparksLowBalanceAlert row and db.add it, then db.flush(), not commit() yet (63 to 71). A flush is enough to hit the unique constraint without committing anything else on the session.
3. On IntegrityError (the unique constraint firing): db.rollback(), log at info level ("already alerted tenant=%s period=%s, skipping"), return False (72 to 80). This is the entire dedup: no prior SELECT, no application-level lock, the database is the single source of truth for "already sent this period," which is what makes the guarantee hold across two racing requests, not only two sequential ones.
4. Look up the Workspace by tenant_id (82). If none exists: log a warning, db.commit() (this commits the dedup row from step 2, the alert is recorded as having been attempted even though no target was found), return False (83 to 89). A crossing for an unknown tenant therefore still consumes that tenant's one email-per-period slot.
5. Lazily import models.user_model.User (91, inside the function body, avoids a module-level import cycle) and look up the workspace's owner_id. If no owner or no owner.email: log a warning, db.commit(), return False (92 to 96). Same consequence as step 4: the dedup row is kept.
6. Lazily import services.email_service.email_service (99) and call send_sparks_low_balance_email(owner.email, name=owner.first_name or owner.username or "there", percent_remaining=..., db=db, balance_sparks=..., threshold_sparks=...). Wrapped in a bare try/except Exception that only logs (logger.exception, 108 to 109), an SMTP or template failure never propagates.
7. db.commit() unconditionally (111), this is the commit that persists the dedup row from step 2 for the success path; steps 4 and 5 already committed on their own exit. Return True (112).

Invariants

• The dedup is a database constraint, not an in-process check: uq_sparks_low_balance_alerts_tenant_period is what a concurrent or retried call actually loses to (step 3), so the guarantee survives process restarts, multiple workers, and retried HTTP deliveries alike, a design that kept the check as a prior SELECT ... WHERE tenant_id = ... AND period_key = ... would have a race between the read and a competing insert that this one does not.
• The row is written even when no email is sent. An unknown tenant (step 4) or an ownerless/emailless workspace (step 5) still consumes that tenant's slot for the period, and a subsequent legitimate crossing in the same month gets no email either. This recipe states it as measured fact, not endorsement, see Gotchas for whether that is the intended behaviour.
• The function never raises. Every exit path (steps 3, 4, 5, 6-into-7) either returns cleanly or swallows and logs. A caller that depends on this (the inbound router, sparks_low_balance.py's own module docstring lines 10 to 12) is depending on that contract holding, not testing it itself each time.
• percent_remaining alone never determines the email's wording; balance_sparks being non-None is the switch between the prepaid wording ("Noch N ✦ Guthaben") and the legacy percentage wording, see Frontend/email shape below and test_step_0_the_prepaid_low_balance_mail_prints_sparks_never_a_percentage.
• A negative balance_sparks is legal input (a post-paid tenant's overdraft) and is floored to 0 for display only inside the email method, never inside this function or the dedup row (email_service.py line 676; test_an_overdraft_balance_is_shown_as_zero_rather_than_a_negative).

Frontend

The frontend module that reads this domain's subscription/balance state:

billing-credits/ui/subscription-manager   authored by a parallel agent; this recipe does not measure its contents

No admin console or debug page in this codebase reads sparks_low_balance_alerts directly at af7a352 (grep -rl sparks_low_balance_alerts frontend/src finds nothing). Minimum debug surface, all direct SQL: SELECT tenant_id, period_key, percent_remaining, created_at FROM sparks_low_balance_alerts ORDER BY created_at DESC LIMIT 50 (recent alerts, newest first); SELECT count(*) FROM sparks_low_balance_alerts WHERE tenant_id = '<id>' (how many periods this tenant has ever been alerted in); the log lines sparks low-balance: already alerted tenant=%s period=%s, skipping (dedup hit), sparks low-balance: unknown tenant %s, dedup row kept, no email sent, and sparks low-balance: tenant %s has no reachable owner email (the two silent-consumption paths in Invariants) and sparks low-balance: email send failed for tenant %s (logger.exception, includes traceback).

Security & tenancy

No auth dependency of its own, this is a plain function, not a route. Its only production caller sits behind POST /internal/sparks/low-balance-alert, mounted surface_exempt("public", ...) (app_fastapi.py line 892) with no Labs session; the standing-in credential is the HMAC signature verified by prima_inbound_sig.verify (_shared/hmac-seam-pattern), keyed to PRIMA_LOW_BALANCE_ALERT_SECRET specifically (core/config.py line 780), never PRIMA_CREDIT_GRANT_SECRET or PRIMA_KEY_SYNC_SECRET, on purpose: this credential can only trigger one deduped email (and, through the sibling code path this recipe does not cover, one deduped capped charge), never mint balance or a working API key (core/config.py lines 792 to 798). A model_validator at boot (validate_prima_seam_secrets_are_distinct, lines 782 to 843) refuses to start Labs if any two of the three Prima seam secrets are equal, raising RuntimeError (not ValueError, deliberately, so the offending values never appear in a wrapped Pydantic error message, lines 808 to 817).

Tenancy: tenant_id arrives as a field in the signed request body, not derived from a session or from _shared/workspace-scoping's middleware, there is no Labs session on this seam at all. notify_low_balance trusts it as a Labs workspace id and looks up Workspace/User by it directly (Application logic steps 4 to 5); the signature check is the only thing standing between an arbitrary caller and triggering an email addressed to any tenant's owner. Fail direction: the signature check fails closed (401/503 before this function runs); inside this function, an unresolvable tenant or owner fails "closed" on the email (no send) but "open" on the dedup commit (the row is still written, Invariants). Nothing here touches a credential at rest beyond the HMAC key itself, which is _shared/hmac-seam-pattern's concern. No PII is newly stored by this function: owner.email is read and used for the send but never written into sparks_low_balance_alerts.

Tests

All run 2026-09-12 against a fresh scratch Postgres at af7a35246dd4a009485bc67b3a13fea6e5a688cc (alembic upgrade head to sparks_billing_001 and beyond; verified with a second, independent scratch database per suite):

• tests/sparks/test_sparks_grants_db.py -k low_balance (DB-backed, SPARKS_TEST_DATABASE_URL gate, tests/sparks/conftest.py): 3 passed. test_low_balance_alert_sends_and_records_a_dedup_row (232): a fresh tenant gets notify_low_balance(...) is True and the email mock is called with percent_remaining echoed through. test_low_balance_alert_is_not_resent_within_the_same_period (249): two calls in the same period, second one returns False and the email mock's call count stays 1, the direct known-answer vector for the dedup. test_low_balance_alert_for_an_unknown_tenant_does_not_raise (267): an unresolvable tenant_id returns cleanly rather than raising.
• tests/prima_credits/test_auto_topup.py, test_auto_topup_outcomes.py, test_auto_topup_mails.py together (DB-backed, PRIMA_CREDITS_TEST_DATABASE_URL gate, tests/prima_credits/conftest.py): 124 passed. The two that exercise this function through the real HTTP seam: test_one_signed_crossing_emails_once_charges_once_and_replays_free (test_auto_topup.py 1024): one signed POST gets email_sent: true; an identical replay gets email_sent: false, duplicate: true (the replay is caught by prima_balance_events' own crossing_seq key before notify_low_balance is even called, that table's dedup, not this recipe's, though the same principle); emails[0]["balance_sparks"] == 4600 and ["threshold_sparks"] == 5000 confirm the prepaid wording's inputs reach the email. test_a_legacy_alert_with_no_crossing_seq_emails_but_never_charges (1106): a body with no crossing_seq still gets an email (email_sent implied true by the mocked send being called), this function's own dedup does not require crossing_seq to exist, it is purely calendar-period-and-tenant. Also relevant: test_the_blocking_half_of_the_seam_never_runs_on_the_event_loop (1134) proves the call into this function happens inside run_in_threadpool, never on the event loop.
• tests/prima_credits/test_auto_topup_outcomes.py::test_the_prepaid_wording_prints_the_balance_it_was_given (338) and test_an_overdraft_balance_is_shown_as_zero_rather_than_a_negative (352), tests/prima_credits/test_auto_topup_mails.py::test_step_0_the_prepaid_low_balance_mail_prints_sparks_never_a_percentage (407): known-answer vectors on EmailService.send_sparks_low_balance_email directly (not this function, but the shape this function's step 6 depends on), "Noch 4'600 ✦ Guthaben", "5'000 ✦", no % character in the prepaid path; -3 shown as "Noch 0 ✦".
• tests/sparks/test_prima_inbound_sig.py (11 cases, no DB): the HMAC scheme this function's only production caller sits behind, 11 passed. tests/prima_credits/test_auto_topup_client.py -k seam_secret (no DB): test_two_identical_seam_secrets_refuse_to_boot, test_distinct_seam_secrets_boot_fine, 2 passed.
• What a new app adds and this codebase lacks: a hermetic test that asserts the dedup row's columns (tenant_id, period_key, percent_remaining) directly rather than only the boolean return and the email mock's call count; a test that the unknown-tenant and no-owner-email paths (Application logic 4 and 5) still write the dedup row, the current suite tests "does not raise" but not "and the slot is consumed," which this recipe's own reading of the code establishes (Invariants) but the test suite does not pin.

Deploy & ops

Config keys: PRIMA_LOW_BALANCE_ALERT_SECRET (Optional[SecretStr], core/config.py line 780), unset means the endpoint 503s and this function is never called in production. EMAIL_TRANSPORT (resend or smtp) governs step 6's actual delivery mechanism but is _shared, not owned here. Migrations order: sparks_billing_001 (revises prima_credit_grants_001) creates the table; nothing downstream in the chain measured (prima_auto_topup_snapshot_001 at HEAD) alters it. Scheduler jobs: none, this is called synchronously (off the event loop, in a threadpool) from the inbound HTTP handler, never from a periodic sweep; there is no code path in this repository that calls notify_low_balance on a timer.

Argus-shaped checks: SELECT count(*) FROM sparks_low_balance_alerts WHERE created_at > now() - interval '1 day' (alerts sent in the last day; a sudden zero on a day Prima is known to be sending crossings is the signal to check the seam, not this function); the boot-time validate_prima_seam_secrets_are_distinct check doubles as a deploy-time Argus check in spirit, a boot failure with RuntimeError naming two credential variable names (never values) means a distribute-env.sh-style credential collision. Rollback: sparks_billing_001's downgrade() drops sparks_low_balance_alerts outright (migrations/versions/20260808_0001_sparks_billing.py lines 100 to 102), that is a data-losing rollback, not a reversible one in the sense of "safe to run against a database with real rows in it."

Gotchas

• 2026-09-12 (measured at af7a352): the Prima-side sender for this seam does not exist yet. services/commerce/prima_inbound_sig.py's module docstring says so explicitly (lines 15 to 26): nothing in the alchemy_labs repository calls out to Prima to ask "is tenant X below 90%," because that read was ruled out by the founder on 2026-08-03, Prima is meant to notice the crossing on its own side, post-settle, and POST here. As of this measurement nobody has built that POST. notify_low_balance and the endpoint that calls it are real, tested code with zero production callers.
• 2026-09-12 (measured at af7a352): an unresolvable tenant or an ownerless/emailless workspace still consumes that tenant's one-email-per-period slot (Application logic steps 4 and 5, db.commit() before returning False). If Prima ever sends a crossing for a tenant Labs has not yet provisioned (a race during account creation, say), that tenant gets no email for the rest of the calendar period even once the workspace exists, with nothing surfaced beyond a warning log line. A rebuild that wants "notify once the tenant is resolvable" needs to not commit the dedup row on these two paths, which changes the guarantee this recipe documents.
• 2026-09-12 (measured at af7a352): the dedup table has no column for which crossing_seq triggered the email (Data model). Two different crossings in the same calendar month, say a tenant dips under 90%, tops up, and crosses again, produce only one email between them, and there is no way to read back from the table which crossing it was. percent_remaining is stored but is the legacy, not-very-useful figure for a prepaid tenant (see the field's own docstring at prima_inbound_router.py 77 to 83); balance_sparks/threshold_sparks, the figures that actually appear in the prepaid email, are not persisted anywhere.
• 2026-09-12 (measured at af7a352): notify_low_balance's docstring return-value contract ("True if an email was sent... False if already sent... or the tenant/owner could not be resolved") collapses three different outcomes into one boolean. A caller cannot distinguish "already alerted this period" from "unknown tenant" from "no owner email" without reading the logs, the inbound router's email_sent response field inherits the same collapse.

Reference implementation

Paths relative to the repo root; branch w1-auto-topup, commit af7a35246dd4a009485bc67b3a13fea6e5a688cc, dated 2026-09-12. git merge-base --is-ancestor af7a352 main on this worktree answers false: this commit is not on alchemy_labs main as of this measurement.

• alchemy_labs:
  • backend/services/commerce/sparks_low_balance.py (112 lines, whole file this recipe covers): _period_key (30 to 31), notify_low_balance (34 to 112).
  • backend/api/routers/prima_inbound_router.py (254 lines; the only production caller): LowBalanceAlertRequest (75 to 109), low_balance_alert (112 to 156), _alert_side_effects (159 to 199, the notify_low_balance call at 182 to 188), _record_crossing (202 to 234, a sibling dedup this recipe does not cover), _sparks (237 to 254).
  • backend/services/commerce/prima_inbound_sig.py (102 lines): verify (67 to end), DEFAULT_SKEW_SECONDS = 300 (line 44).
  • backend/models/sparks_billing.py: SparksLowBalanceAlert (105 to 124).
  • backend/migrations/versions/20260808_0001_sparks_billing.py: table creation (87 to 97), downgrade (100 to 102).
  • backend/services/email_service.py: send_sparks_low_balance_email (644 to 708).
  • backend/core/sparks_config.py: MICRO_PER_SPARK = 1_000 (50), LOW_BALANCE_CONSUMED_THRESHOLD = 0.90 (110).
  • backend/core/config.py: PRIMA_LOW_BALANCE_ALERT_SECRET (780), validate_prima_seam_secrets_are_distinct (782 to 843).
  • backend/app_fastapi.py: mount at line 892.
  • backend/schemas/error_schemas.py: create_error_response (112 to 136).
  • Tests as listed under Tests.

Deviations of the reference from an ideal rebuild, all dated 2026-09-12: no Prima-side sender exists, so this path is untested in production; the dedup row is written even when no email can be sent, silently consuming a tenant's slot; no crossing_seq on the dedup row, so a rebuild wanting per-crossing traceability needs a schema change; a three-way boolean-and-logs return contract where a typed result (sent | already_alerted | unknown_tenant | no_owner_email) would let a caller react differently to each.

Acceptance checks

Run from alchemy_labs/backend at af7a352 against a scratch Postgres migrated to at least sparks_billing_001 (sparks_billing_001 is transitively required; running alembic upgrade head is simplest and is what was used to measure this recipe), or from a new app built to this recipe.

1. SPARKS_TEST_DATABASE_URL=<scratch-url> python -m pytest tests/sparks/test_sparks_grants_db.py -k low_balance -q --no-cov -o addopts="" --confcutdir=tests/sparks prints 3 passed.
2. PRIMA_CREDITS_TEST_DATABASE_URL=<scratch-url> python -m pytest tests/prima_credits/test_auto_topup.py tests/prima_credits/test_auto_topup_outcomes.py tests/prima_credits/test_auto_topup_mails.py -q --no-cov -o addopts="" --confcutdir=tests/prima_credits prints 124 passed.
3. python -m pytest tests/sparks/test_prima_inbound_sig.py -q --no-cov -o addopts="" prints 11 passed (no database needed).
4. PRIMA_CREDITS_TEST_DATABASE_URL=<scratch-url> python -m pytest tests/prima_credits/test_auto_topup_client.py -k seam_secret -q --no-cov -o addopts="" --confcutdir=tests/prima_credits prints 2 passed.
5. grep -n "db.flush()" backend/services/commerce/sparks_low_balance.py | wc -l prints 1, and grep -n "except IntegrityError" backend/services/commerce/sparks_low_balance.py | wc -l prints 1 (the dedup is exactly one flush-and-catch, not a prior read).
6. Against the scratch database: calling notify_low_balance(db, tenant_id=<a real tenant>, percent_remaining=0.09) twice in the same process, same calendar period, returns True then False; SELECT count(*) FROM sparks_low_balance_alerts WHERE tenant_id = '<that tenant>' prints 1, not 2.
7. git -C alchemy_labs merge-base --is-ancestor af7a35246dd4a009485bc67b3a13fea6e5a688cc main; echo $? prints 1 (not an ancestor, confirms maturity: unmerged).