Lyo.Job.Postgres 2.0.0

Lyo.Job.Postgres

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

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

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 Lyo.Api.Mapping.CompositeLyoMapper(job, mapsterFallback). Once an IMqService is available, replace the publisher with AddMqJobEventPublisher() (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")

The schema is fixed as job. EF migrations history lives in job.__EFMigrationsHistory.

JobMaintenanceOptions (JobMaintenanceOptions.SectionName = "JobMaintenance")

A per-definition RetentionDays value overrides the global default when > 0.

DI extension methods

Run state machine

Every lifecycle transition is a compare-and-swap update (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. Create, delete, CAS, log, dead-job timeout, and retention purge also bust entity:jobrun and entity:jobdefinition query-cache tags so run and last-run grid pages do not stay stale.

Dispatch suppression

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

Encryption flow

Values that match an encrypted definition parameter are stored encrypted (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.

JobService hardening

For scheduled runs, a second create for the same (JobScheduleId, ScheduledSlotUtc) returns the existing run. CreateService swallows unique-constraint exceptions, so CreateJobRun looks up the existing row from the failure text (and also pre-checks under the advisory lock).

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. Cancelling/Running runs past TimeoutMinutes → Timeout/Finished + 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]

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:

• ParameterClrTypeFullName. Widens parameter/result type to 1024, JSON-encodes stored values, folds allow_multiple into List<T> FullNames, then drops allow_multiple.
• 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).
• AddParameterDefaultExpression. Adds job_parameter.default_kind (Literal | Expression, defaulting to Literal) and job_parameter.default_template. Existing rows read as Literal, so the change is backward compatible.
• AddJobBlackoutHolidayAndCalendarDays. Adds holiday_slug, include_observed_date, month_flags, and days_of_month on job_blackout_window so holiday and calendar-day windows are stored as rules instead of one dated row per year.

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.Metadata (direct, lyo)
• Lyo.Configuration (direct, lyo)
• Lyo.Encryption (direct, lyo)
• Lyo.Exceptions (direct, lyo)
• Lyo.Job.Models (direct, lyo)
• Lyo.MessageQueue (direct, lyo)
• Lyo.Parameters (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.Common.Core (transitive, lyo)
• Lyo.Common.Json (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.Evaluation (transitive, lyo)
• Lyo.Query.Models (transitive, lyo)
• Lyo.Result (transitive, lyo)
• Lyo.Schedule.Models (transitive, lyo)
• Lyo.Streams (transitive, lyo)
• Lyo.Validation (transitive, lyo)
• Lyo.Validation.Models (transitive, lyo)
• BouncyCastle.Cryptography 2.6.2 (transitive, third-party, netstandard2.0)
• DynamicExpresso.Core 2.19.3 (transitive, third-party)
• EasyCompressor 2.1.0 (transitive, third-party)
• Konscious.Security.Cryptography.Argon2 1.3.1 (transitive, third-party)
• 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)