Lyo.Job.Postgres 1.0.1

Lyo.Job.Postgres

PostgreSQL persistence and minimal-API host for the Lyo job-management subsystem. Wraps EF Core, the Lyo CRUD/QueryConcrete stack, hand-rolled JobLyoMapper (ILyoMapper), optional parameter encryption, audit recording, and IJobEventPublisher so a host can drop in a complete job service: definitions, parameters, schedules, triggers, calendars, workflows, worker registry, runs, batch children, run parameters, run results, run logs, and stats.

Examples

Register jobs (Postgres)

services.AddLyoQueryServices();
services.AddFusionCache(...); // or AddLocalCache(...)

services.AddPostgresJobManagement(o => {
    o.ConnectionString = connectionString;
    o.EnableAutoMigrations = true;
});

// Optional: encrypt sensitive parameter values at rest
services.AddJobParameterEncryption(keyName: "job-parameters");

// After IMqService is registered (e.g. AddRabbitMq):
services.AddMqJobEventPublisher();

// Dead-job watchdog, circuit-breaker reset, retention purge, SLA checks, worker pruning:
services.AddJobMaintenanceService(o => {
    o.DefaultRetentionDays = 90;
    o.PurgeBatchSize = 500;
    o.WorkerInstanceStaleMinutes = 5;
});

// Or bind maintenance from configuration (section JobMaintenance):
services.AddJobMaintenanceServiceFromConfiguration(configuration);

var app = builder.Build();
app.BuildJobGroup();

Drop-and-play registration

AddPostgresJobManagement registers the JobContext factory, optional auto-migrations, the Lyo CRUD services, JobService, JobLyoMapper as ILyoMapper, and a default no-op IJobEventPublisher (NullJobEventPublisher). Hosts that also use Mapster for non-job types should replace ILyoMapper with CompositeLyoMapper(job, mapsterFallback). Replace the publisher with AddMqJobEventPublisher() once you have an IMqService available (API hosts with a job database only). Scheduler and worker hosts must not use this package's publisher — use Lyo.Job.Client.AddMqJobEventPublisher* (IMqService + Job.Client) instead.

When IAuditRecorder is registered, CRUD hooks record JobDefinition.*, JobRun.*, and related entity events via JobAuditHelper.

PostgresJobOptions (PostgresJobOptions.SectionName = "PostgresJob")

Schema is fixed to job. EF migrations history lives in job.__EFMigrationsHistory.

JobMaintenanceOptions (JobMaintenanceOptions.SectionName = "JobMaintenance")

Per-definition RetentionDays overrides the global default when > 0.

DI extension methods

Run state machine

All lifecycle transitions are compare-and-swap updates (ExecuteUpdateAsync with the expected state in the WHERE clause), so duplicate MQ deliveries and races between workers, the API, and maintenance resolve deterministically. Future endpoints that mutate JobRun.State must keep this CAS discipline.

Run state machine — Dispatch suppression

CreateJobRun skips the immediate RunCreated publish when JobRunReq.SuppressDispatch is set or ScheduledSlotUtc is in the future (metric job.service.run.dispatch.deferred). The caller then owns dispatch: the scheduler's delayed-MQ envelope delivers backoff retries, and the workflow engine publishes step runs only after linking them to their run step. The maintenance service's stuck-queued recovery (below) is the safety net if the owner crashes before publishing. With suppression requested, creation succeeds even while MQ is disconnected.

Run state machine — Encryption flow

Parameter values matching an encrypted definition parameter are encrypted at rest (EncryptedValue) and masked (***) by JobLyoMapper on every API response. StartedJobRun is the single worker-trusted exception: it decrypts parameter values server-side in the response so the executing worker receives real values. Rerun and child-run creation build their requests from the stored entity (not the masked API response), so ciphertext survives cloning and masked *** strings are never persisted as real values.

Production hardening in JobService

Slot idempotency (JobScheduleId + ScheduledSlotUtc unique index) remains the multi-scheduler guard for scheduled runs.

Metrics (job.service.*, job.maintenance.*, job.sla.*) — job.service.*

Metrics (job.service.*, job.maintenance.*, job.sla.*) — job.maintenance.*

Metrics (job.service.*, job.maintenance.*, job.sla.*) — job.sla.*

JobMaintenanceService

BackgroundService (IHealth: job-maintenance) ticking every CheckIntervalSeconds:

1. Dead jobs — Running/Cancelling runs past TimeoutMinutes → Finished/Timeout + optional DeadJob alert. After commit, publishes RunFinished for each timed-out run so the scheduler's retry/trigger/circuit-breaker accounting fires (a late worker finish for the same run is rejected by the state check and dropped by the worker as terminal).
2. Circuit breaker reset — re-enables definitions after CircuitBreakerResetMinutes; publishes DefinitionUpdated so scheduler caches refresh promptly.
3. Stuck queued redispatch — re-publishes RunCreated for due Queued runs untouched for QueuedRunRedispatchMinutes (lost publishes, delayed retries whose slot came due, crashed suppressed-dispatch owners). Bumps UpdatedTimestamp so a stuck run retries once per threshold window; duplicate deliveries are harmless because StartedJobRun only transitions Queued -> Running once.
4. Retention purge — deletes finished runs older than effective retention in PurgeBatchSize batches. Detaches FK references from surviving rows first: ReRanFromJobRunId/ TriggeredByJobRunId/ParentJobRunId on related runs and JobWorkflowRunStep.JobRunId (workflow history is preserved with the run reference nulled), so purging workflow-created runs or parents with surviving children cannot violate FKs and wedge the purge.
5. Worker pruning — removes stale JobWorkerInstance rows.
6. SLA breach scan — marks SlaBreached and increments job.sla.breach for overdue queued/running jobs. Alerts for SLA breaches are published by JobService on start/finish transitions, not by this background scan.

flowchart LR
    tick[Maintenance tick] --> dead[Fail dead jobs]
    tick --> cb[Reset circuit breakers]
    tick --> redisp[Redispatch stuck queued runs]
    tick --> ret[Purge by retention]
    tick --> wrk[Prune worker instances]
    tick --> sla[Detect SLA breaches]
    dead --> alert[PublishAlertAsync]
    dead --> fin[PublishRunFinishedAsync]
    cb --> defupd[PublishDefinitionUpdatedAsync]

Endpoint matrix (BuildJobGroup)

Tag "Job". CRUD + export on definitions; runs expose Query/Get/Delete/DeleteBulk + export.

Lifecycle routes (RunStarted, RunFinished, RunRequeue, RunHeartbeat, RunLog, …) are mapped alongside CRUD for scheduler/worker hosts.

Event publishers (Events/)

• NullJobEventPublisher — default; IsConnected() == false.
• MqJobEventPublisher — creates queues/exchange bindings; publishes run events with optional priority; routes alerts to job.notifications.alert. Resolves worker types from EF when JobContext is registered. For scheduler/worker hosts use Lyo.Job.Client.MqJobEventPublisher instead.

Design-time migrations

Set JOB_CONNECTION_STRING and run:

export JOB_CONNECTION_STRING="Host=localhost;Database=postgres;Username=postgres;Password=password"
dotnet ef migrations add YourMigrationName --project Lyo.Job.Postgres

Recent migrations of note:

• WidenJobDefinitionWorkerType — widens job_definition.worker_type from 7 to 50 characters, matching job_worker_instance.worker_type (worker type names longer than 7 characters were previously truncated/rejected on the definition side only).

Dependencies

Generated from ProjectReference / PackageReference (same model as docs/Lyo.ProjectGraph.html).

• Lyo.Api — (direct, lyo)
• Lyo.Api.Export — (direct, lyo)
• Lyo.Audit — (direct, lyo)
• Lyo.Common — (direct, lyo)
• Lyo.Encryption — (direct, lyo)
• Lyo.Exceptions — (direct, lyo)
• Lyo.Job.Models — (direct, lyo)
• Lyo.MessageQueue — (direct, lyo)
• Lyo.Postgres — (direct, lyo)
• Lyo.Scheduler — (direct, lyo)
• Microsoft.EntityFrameworkCore 10.0.5 — (direct, microsoft)
• Microsoft.EntityFrameworkCore.Design 10.0.5 — (direct, microsoft)
• Microsoft.Extensions.Configuration.Binder 10.0.5 — (direct, microsoft)
• Lyo.Api.Models — (transitive, lyo)
• Lyo.Cache — (transitive, lyo)
• Lyo.Compression — (transitive, lyo)
• Lyo.DateAndTime — (transitive, lyo)
• Lyo.Diagnostic — (transitive, lyo)
• Lyo.Diagnostic.AspNetCore — (transitive, lyo)
• Lyo.Diff — (transitive, lyo)
• Lyo.EntityReference.Models — (transitive, lyo)
• Lyo.Formatter — (transitive, lyo)
• Lyo.Hashing — (transitive, lyo)
• Lyo.Health — (transitive, lyo)
• Lyo.KeyStore — (transitive, lyo)
• Lyo.Metrics — (transitive, lyo)
• Lyo.PackageMetadata — (transitive, lyo)
• Lyo.Query — (transitive, lyo)
• Lyo.Query.Models — (transitive, lyo)
• Lyo.Result — (transitive, lyo)
• Lyo.Schedule.Models — (transitive, lyo)
• Lyo.Streams — (transitive, lyo)
• Lyo.Validation — (transitive, lyo)
• BouncyCastle.Cryptography 2.6.2 — (transitive, third-party, netstandard2.0)
• EasyCompressor 2.1.0 — (transitive, third-party)
• Konscious.Security.Cryptography.Argon2 1.3.1 — (transitive, third-party)
• Microsoft.AspNetCore.Authorization 10.0.5 — (transitive, microsoft)
• Microsoft.AspNetCore.Http.Abstractions 2.* — (transitive, microsoft)
• Microsoft.AspNetCore.OpenApi 10.0.5 — (transitive, microsoft)
• Microsoft.Bcl.AsyncInterfaces 10.0.5 — (transitive, microsoft, netstandard2.0)
• Microsoft.EntityFrameworkCore.Analyzers 10.0.5 — (transitive, microsoft)
• Microsoft.EntityFrameworkCore.Relational 10.0.5 — (transitive, microsoft)
• Microsoft.Extensions.Caching.Memory 10.0.5 — (transitive, microsoft)
• Microsoft.Extensions.DependencyInjection 10.0.5 — (transitive, microsoft)
• Microsoft.Extensions.DependencyInjection.Abstractions 10.0.5 — (transitive, microsoft, net10.0, netstandard2.0)
• Microsoft.Extensions.Hosting.Abstractions 10.0.5 — (transitive, microsoft)
• Microsoft.Extensions.Logging.Abstractions 10.0.5 — (transitive, microsoft)
• Microsoft.Extensions.Options 10.0.5 — (transitive, microsoft)
• Microsoft.Extensions.Options.ConfigurationExtensions 10.0.5 — (transitive, microsoft)
• Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 — (transitive, third-party)
• SmartFormat.NET 3.6.1 — (transitive, third-party)
• System.Buffers 4.6.1 — (transitive, microsoft, netstandard2.0)
• System.ComponentModel.Annotations 5.0.0 — (transitive, microsoft)
• System.Diagnostics.DiagnosticSource 10.0.5 — (transitive, microsoft, netstandard2.0)
• System.IO.Hashing 10.0.5 — (transitive, microsoft, net10.0)
• System.Memory 4.6.3 — (transitive, microsoft, netstandard2.0)
• System.Text.Json 10.0.5 — (transitive, microsoft, netstandard2.0)
• System.Threading.Tasks.Extensions 4.6.3 — (transitive, microsoft, netstandard2.0)