hannah-vernon/viegard-sentinel
hannah-vernon/viegard-sentinel
Watch
1
0
Fork
You've already forked viegard-sentinel
 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions

PostgreSQL persistence provider (D-0024) #5

Merged
hannah-vernon merged 1 commit from feature/postgres-persistence into dev 2026-08-19 23:51:33 -05:00
Conversation 0 Commits 1 Files changed 30 +3039 -30
hannah-vernon commented 2026-08-19 17:11:23 -05:00
Owner
Copy link

What does this PR do?

Implements the PostgreSQL persistence provider per D-0024, replacing the "in-memory until the DB decision" gap with real durable shared persistence.

  • Viegard.Persistence.Postgres:
    • ViegardDbContext + InitialSchema migration: raw observations (payload inline), events (jsonb entities/payload with polymorphic discriminators), incidents, classifications, decisions, actions, audit records, corrections, queue telemetry, source offsets, and the queue message/counter tables.
    • Store implementations for every persistence port, mapping the immutable domain records to EF row types.
    • PostgresWorkQueue: durable broker-semantics queue: FOR UPDATE SKIP LOCKED visibility-timeout leases, delivery counting with dead-lettering enforced at lease time (a crashed consumer's expired leases still count, so poison messages can never redeliver forever), LISTEN/NOTIFY wakeups with a fallback poll, per-queue counters for D-0012 telemetry. PostgresCommandQueue rides the same mechanics for admin commands.
    • DatabaseOptions + validator: the DB password is a named secret resolved via ISecretProvider (default viegard-db-password), never configuration; AutoMigrate applies migrations at host startup.
  • Domain: JSON polymorphism discriminators on EventPayload (BCL attributes only) for stable persisted payloads.
  • Application: IQueueStatsSource.GetStatsAsync (a sync stats call cannot be honestly implemented over a database); ChannelWorkQueue keeps a sync core.
  • PipelineHost: persistence provider switch: Viegard:Persistence:Provider = inmemory (default, dev) or postgres; startup migration when postgres.
  • Supply-chain review (2026-08-19): Npgsql 10.0.3 (945M downloads, PostgreSQL license, active), Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (470M), EF Core 10.0.11 (Microsoft, MIT). All low risk; recorded in THIRD-PARTY-NOTICES.md.

Fixes #

How was this tested?

  • dotnet build Viegard.slnx - 0 errors, 0 warnings
  • dotnet test Viegard.slnx - 140/140 pass; 7 PostgreSQL integration tests written (enqueue/lease/complete, redelivery counts, dead-lettering, lease-expiry crash recovery, 5-consumer no-double-lease, LISTEN/NOTIFY wakeup, polymorphic store round-trip) currently skipped: gated on VIEGARD_TEST_POSTGRES, pending local Docker (WSL reboot) or the Debian VM (tracked in TODO.md)
  • Manually tested: host boots with the default inmemory provider; migration generation via dotnet dotnet-ef succeeds
  • Live database verification outstanding (see above)

Checklist

  • I have read the Contributing Guide
  • Changes are focused - one logical change per PR
  • Documentation updated (AGENT-README.md, TODO.md, THIRD-PARTY-NOTICES.md)
  • New dependencies are MIT/PostgreSQL-license and security-vetted; recorded in THIRD-PARTY-NOTICES.md
  • No secrets, credentials, or real infrastructure identifiers in the diff
  • No commented-out code or debug leftovers
## What does this PR do? Implements the PostgreSQL persistence provider per D-0024, replacing the "in-memory until the DB decision" gap with real durable shared persistence. - **`Viegard.Persistence.Postgres`:** - `ViegardDbContext` + `InitialSchema` migration: raw observations (payload inline), events (jsonb entities/payload with polymorphic discriminators), incidents, classifications, decisions, actions, audit records, corrections, queue telemetry, source offsets, and the queue message/counter tables. - Store implementations for every persistence port, mapping the immutable domain records to EF row types. - **`PostgresWorkQueue`**: durable broker-semantics queue: `FOR UPDATE SKIP LOCKED` visibility-timeout leases, delivery counting with **dead-lettering enforced at lease time** (a crashed consumer's expired leases still count, so poison messages can never redeliver forever), `LISTEN/NOTIFY` wakeups with a fallback poll, per-queue counters for D-0012 telemetry. `PostgresCommandQueue` rides the same mechanics for admin commands. - `DatabaseOptions` + validator: the DB password is a named secret resolved via `ISecretProvider` (default `viegard-db-password`), never configuration; `AutoMigrate` applies migrations at host startup. - **Domain:** JSON polymorphism discriminators on `EventPayload` (BCL attributes only) for stable persisted payloads. - **Application:** `IQueueStatsSource.GetStatsAsync` (a sync stats call cannot be honestly implemented over a database); `ChannelWorkQueue` keeps a sync core. - **PipelineHost:** persistence provider switch: `Viegard:Persistence:Provider` = `inmemory` (default, dev) or `postgres`; startup migration when postgres. - **Supply-chain review (2026-08-19):** Npgsql 10.0.3 (945M downloads, PostgreSQL license, active), Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (470M), EF Core 10.0.11 (Microsoft, MIT). All low risk; recorded in THIRD-PARTY-NOTICES.md. Fixes # ## How was this tested? - [x] `dotnet build Viegard.slnx` - 0 errors, 0 warnings - [x] `dotnet test Viegard.slnx` - 140/140 pass; 7 PostgreSQL integration tests written (enqueue/lease/complete, redelivery counts, dead-lettering, lease-expiry crash recovery, 5-consumer no-double-lease, LISTEN/NOTIFY wakeup, polymorphic store round-trip) currently **skipped**: gated on `VIEGARD_TEST_POSTGRES`, pending local Docker (WSL reboot) or the Debian VM (tracked in TODO.md) - [x] Manually tested: host boots with the default inmemory provider; migration generation via `dotnet dotnet-ef` succeeds - [ ] Live database verification outstanding (see above) ## Checklist - [x] I have read the [Contributing Guide](../CONTRIBUTING.md) - [x] Changes are focused - one logical change per PR - [x] Documentation updated (AGENT-README.md, TODO.md, THIRD-PARTY-NOTICES.md) - [x] New dependencies are MIT/PostgreSQL-license and security-vetted; recorded in THIRD-PARTY-NOTICES.md - [x] No secrets, credentials, or real infrastructure identifiers in the diff - [x] No commented-out code or debug leftovers 
- Viegard.Persistence.Postgres:
  - ViegardDbContext + InitialSchema migration: raw observations, events
    (jsonb entities/payload), incidents, classifications, decisions,
    actions, audit records, corrections, queue telemetry, source
    offsets, queue messages + counters
  - Postgres store implementations for every persistence port, mapping
    immutable domain records to EF row types
  - PostgresWorkQueue: SKIP LOCKED visibility-timeout leases, delivery
    counting with dead-lettering enforced at lease time (crash-safe
    poison protection), LISTEN/NOTIFY wakeups with fallback poll,
    per-queue counters; PostgresCommandQueue for admin commands
  - DatabaseOptions (+ validator): password via ISecretProvider secret
    name, never configuration; AutoMigrate at host startup
  - Service registration extension + design-time factory for dotnet-ef
- Domain: JSON polymorphism discriminators on EventPayload
- Application: IQueueStatsSource.GetStatsAsync (durable queues compute
  stats in the database); ChannelWorkQueue keeps a sync core
- PipelineHost: persistence provider switch (inmemory default,
  postgres via Viegard:Persistence:Provider) with startup migration
- Tests: 140 passing + 7 PostgreSQL integration tests (queue
  round-trip, redelivery, dead-letter, lease expiry, concurrent
  consumers, LISTEN/NOTIFY wakeup, polymorphic store round-trip) gated
  on VIEGARD_TEST_POSTGRES pending local Docker (WSL reboot)
- Deps (supply-chain reviewed): Npgsql.EntityFrameworkCore.PostgreSQL
  10.0.3, EF Core 10.0.11, recorded in THIRD-PARTY-NOTICES.md

Verified: dotnet build (0 warnings), dotnet test (140/140 + 7 skips),
host boots with default inmemory provider.

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
Sign in to join this conversation.
Reviewers
No reviewers
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
hannah-vernon/viegard-sentinel!5
No description provided.