RevenueFlow.Stores.Postgres 0.1.0-alpha.7.2

This is a prerelease version of RevenueFlow.Stores.Postgres.
dotnet add package RevenueFlow.Stores.Postgres --version 0.1.0-alpha.7.2
                    
NuGet\Install-Package RevenueFlow.Stores.Postgres -Version 0.1.0-alpha.7.2
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="RevenueFlow.Stores.Postgres" Version="0.1.0-alpha.7.2" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="RevenueFlow.Stores.Postgres" Version="0.1.0-alpha.7.2" />
                    
Directory.Packages.props
<PackageReference Include="RevenueFlow.Stores.Postgres" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add RevenueFlow.Stores.Postgres --version 0.1.0-alpha.7.2
                    
#r "nuget: RevenueFlow.Stores.Postgres, 0.1.0-alpha.7.2"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package RevenueFlow.Stores.Postgres@0.1.0-alpha.7.2
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=RevenueFlow.Stores.Postgres&version=0.1.0-alpha.7.2&prerelease
                    
Install as a Cake Addin
#tool nuget:?package=RevenueFlow.Stores.Postgres&version=0.1.0-alpha.7.2&prerelease
                    
Install as a Cake Tool

RevenueFlow.Stores.Postgres

IRevenueReservationExpiryQueryStore adds native scoped pending-reservation discovery in exact expiry-tick/UUID order. Generated status and existing exact expiry columns are maintained atomically from payloads; the explicit schema initializer adds partial pending/invalid-state indexes for status 1. Queries exclude captured/released history before returning a bounded page. A separate indexed invalid-state/deadline check fails closed even after a cursor. Returned account-owned work must retain the exact explicit account owner; identity UUIDs never bypass full scope.

Tests retain actual PostgreSQL EXPLAIN (ANALYZE) index selection over 1,050 terminal rows and exact 100ns resume/restart boundaries. These verify the local query shape and bounded selected prefix, not arbitrary database throughput. Other numeric reserved-state codes preserve query semantics but are outside the status-1 partial-index performance evidence. Initialize this additive schema explicitly before enabling the new role; the application controls deployment and quiesced migration.

User metadata/external-key replacement uses IRevenueUserMutationStore with an expected revision and atomic uniqueness. Explicit InitializeAsync adds the backward-compatible users.revision column to schema v1; existing users start at revision1. Run initialization deliberately during deployment before using the revisioned user API.

Native Npgsql stores for atomic commerce documents, durable command receipts and commerce users. Tested with PostgreSQL 17. This package has no EF dependency.

The application owns an NpgsqlDataSource, its credentials and its lifetime. Call PostgresRevenueSchema.InitializeAsync(dataSource, cancellationToken) explicitly during a controlled schema deployment, then construct PostgresRevenueDocumentStore and PostgresRevenueUserStore. Constructors and background work never create or migrate database objects. Version one uses the fixed revenueflow schema; schema DDL is transactional, serialized and additive. Outbox projection backfill follows in separate bounded, revision-checked statements; cancellation can leave that derived backfill incomplete without hiding records. An existing version table must contain exactly version one; unsupported or ambiguous versions are rejected before schema changes. Future schema upgrades require an explicit versioned migration. Database roles, backups, retention and disaster recovery remain application operations.

The native deployment/restart qualification exercises cancellation at an observed DDL lock wait and after a committed backfill page, then restarts PostgreSQL and resumes with original authority and receipts intact. Committed DDL resumes forward under maintenance; this establishes no automatic downgrade, production lock-duration bound or old-binary rolling support.

Documents have global (Kind, Id) identity, revision checks and exact owning-scope authorization. Users have global UUID identity; external IDs are ordinal and unique within the four-part scope. Scope components are interned separately so valid Unicode scope values fit PostgreSQL index limits. All text comparisons and unique indexes use C collation. Query pages use PostgreSQL UUID ordering, equivalent to Guid.CompareTo, and live cursors. Metadata discovery uses a repeatable-read snapshot within one page to keep projection-readiness checks consistent with native selection; separate pages remain live reads.

Each document commit writes its state, unique-key changes and original command receipt in one database transaction. Stable ordered advisory locks coordinate receipt replay, global document identity and incoming unique claims. These use 4,096 fixed hash stripes to bound PostgreSQL shared lock memory; collisions can serialize unrelated operations while exact database comparisons determine identity and scope. Lock and mutation statements are batched to avoid a network round trip per unique key. Server-confirmed deadlock/serialization aborts retry up to three attempts; exhaustion returns a definite concurrency conflict. CAS and unique violations roll back every effect. Transactions never include provider requests or callbacks. No adapter can extend this transaction to another database or a remote payment provider.

IRevenueCommandPreparationStore.ReadCommandPreparationAsync returns the scoped command receipt and AdaptiveAdmission scheduling advice through the existing single receipt query. Commerce may use that advice to admit fresh commands to a contended local retry cohort. It changes no transaction, replay or scope guarantee, and does not coordinate separate processes. A custom store that omits the optional role retains retry-only scheduling.

Document/claim inserts use bounded typed bulk arrays. Exact unchanged ordered claims retain their original rows; all changed old ownership is removed before any replacement claim, preserving atomic key transfers/swaps. Claim comparisons use a fresh statement snapshot after document CAS locks. The advisory-key encoding remains compatible with earlier writers, while a reusable JSON writer and stack hash buffer avoid per-key serialization allocations.

Retained outbox work discovery

PostgresRevenueDocumentStore implements optional IRevenueOutboxQueryStore. A partial index excludes canonical terminal outboxes; its due-time range predicate excludes future work before payload retrieval and the existing live UUID cursor. Business outboxes, history and receipts remain retained. Every returned candidate still receives normal dispatcher validation and CAS fencing. Many due records can require a native sort; page limits bound returned payloads, not every row the planner scans.

Controlled initialization adds nullable projection values and a generated effective timestamp guarded by the document revision. A late old writer advancing revision automatically makes stale metadata unclassified and visible. Normal writes and operational imports project atomically. Initialization backfills pre-existing records in pages up to 256 using revision CAS and a captured high-water cursor. PostgresRevenueSchema.BackfillOutboxIndexAsync(dataSource, pageSize, cancellationToken) can explicitly refresh later legacy changes; no cached readiness flag hides newly unclassified data. Backfill changes neither business revisions, raw JSON, unique keys nor receipts.

The evaluated work-table prototype was rejected after paired write-pressure and retained-worker comparisons. Explicit initialization atomically retires its known version-one triggers, function and derived tables while recreating the selected document index. Unknown/ambiguous markers and external object dependencies reject the transaction. Cancellation rolls retirement back; business records and receipts remain unchanged. Current operation requires no readiness state or work-table DML grants.

A transport error or cancellation during commit can leave an unknown result. Recover by reading the scoped receipt or retrying exactly the same command and fingerprint. The store propagates uncertain failures and never retries them automatically. Receipt retention must cover the supported replay window; economic unique claims must outlive possible provider event replay. Destructive retention and maintenance are intentionally absent.

Payloads, receipt results and metadata use PostgreSQL json columns, preserving lexical JSON (including whitespace and exponents outside PostgreSQL numeric range) while providing database JSON validation and native JSON operators. They intentionally use json rather than jsonb, which normalizes numeric values and has narrower numeric limits. Metadata filtering reads bounded chunks of scoped users in UUID order and applies the shared exact evaluator, retaining only one page plus one result. Filters are not server-indexed, so selective queries may scan a full scoped user set. Server-side JSON indexing and schema upgrades are planned separately.

Document metadata has an additional optional IRevenueDocumentMetadataQueryStore role, used by billing accounts. Atomic commits/imports maintain a separate JSONB map of encoded typed tokens and equality hashes with source revision/projection version; original metadata stays lexical JSON. Native filters run before UUID pagination and never coerce JSON numbers through PostgreSQL numeric. Explicit schema initialization adds these derived columns and a partial readiness index. After upgrading an existing database, call InitializeDocumentMetadataAsync(100) explicitly; stale/legacy projections use the exact scoped fallback until backfilled. EnsureDocumentMetadataEqualityIndexAsync(["plan"]) creates an optional selected-path equality digest index; exact tokens verify matches, including ordinal strings. Ordered/negative/existence predicates can still inspect many scoped rows. No request performs schema changes or backfill. This document role does not replace the personal user metadata API described above.

Integration tests require an explicitly configured disposable PostgreSQL database through REVENUEFLOW_TEST_POSTGRES. They create and drop uniquely named test databases and need database-creation privileges. Never point this variable at a production cluster.

Indexed operational document filters

PostgresRevenueDocumentStore also implements optional IRevenueDocumentFilterStore for exact root-level UserId, ResourceKey, OrderId and positive RemainingUnits predicates. Filtering occurs in SQL before the existing UUID cursor and page limit. These typed operational filters are separate from arbitrary user-metadata filters. Full scope remains mandatory; a globally unique user or document UUID does not authorize another app.

Explicit schema initialization adds generated typed columns and partial indexes for these document fields. PostgreSQL computes them from the original JSON in the same row write, including existing rows when the columns are installed. Invalid GUIDs, wrong JSON types, non-integer/out-of-range numbers and missing/null fields remain nonmatches. GUID strings use GUID equality, resource keys use exact C equality, and a resource-key hash only narrows the index search; the full string is always verified. Projection helpers safely handle unrelated JSON NUL escapes without changing the stored lexical payload. No application-side projection backfill or eventually consistent index is involved.

Adding stored generated columns rewrites the shared document table once and index installation scans it. Run initialization in a controlled database deployment with appropriate DDL-lock and disk capacity planning; it is not an online per-app migration. Constructors do not install missing columns. Repeat initialization is additive/idempotent. Resource operations may still process many lots belonging to the selected account/source, and the shared 256-write/64 KiB document limits remain.

Point, unique-key, receipt and paged document reads resolve full scope within their single SQL statement, avoiding a separate scope round trip and avoiding a process-local scope-ID cache. The commit lock strategy remains unchanged. Native tests verify shared filter conformance, upgrade/backfill, atomic rollback of projected values, ordinal Unicode, malformed JSON fields, exact GUID cursors and actual API query plans over 20,000 historical rows. See docs/performance/postgres-resource-queries.md for the query-plan diagnostic.

Analytical and current-query capabilities

The same package supplies PostgresRevenueAnalyticsStore for immutable typed facts, native SQL metrics, bounded financial/resource/customer pages, growth measurements and reconciled generation activation. Use the operational data source or an independent analytical one. Explicitly initialize PostgresRevenueAnalyticsSchema; constructors never migrate. Exact signed minor-unit/decimal money remains currency-separated, and UTC tick columns preserve 100 ns time boundaries. Reports aggregate inside PostgreSQL under repeatable-read and bound result groups rather than underlying fact counts.

WriteManyAsync snapshots and validates 1–100 same-scope/generation events, with at most 256 resource facts per event and 1,000 per microbatch. Source/fact identity conflicts roll back the complete batch. The short transaction locks its scope/generation row and sends batched native commands; unrelated apps proceed independently. First financial recorded time is stable on replay. No provider callback or analytical HTTP request runs inside the transaction.

PostgresRevenueAnalyticsAdmissionStore(dataSource, destinationId) supplies metadata coordination for another analytical engine. Initialize PostgresRevenueAnalyticsAdmissionSchema explicitly. It reserves source/fact hashes and first recorded time before external I/O, and completes only after native acceptance. Full scope, destination and generation isolate metadata. Pending reservations prevent reconciliation/activation; uncertain external writes recover by exact retry. This is separate from native PostgreSQL fact tables and does not claim a distributed transaction.

PostgresRevenueSubscriptionQueryStore implements the optional current subscription count contract directly over transactional subscription documents. It does not consult the analytics projection. Counts preserve domain active/renewing semantics, full scope and exact timestamp offsets/ticks. The query aggregates the selected scoped subscription set in SQL; this is not a historical report.

Native tests include 10,001 financial plus 10,001 resource facts, atomic batch conflicts, concurrent replay, generation/destination independence, immediate current counts and exact temporal boundaries. See the repository's docs/scalable-stores.md for role composition and completeness obligations.

For large audiences, select the optional IRevenueAnalyticsTotalsQueryStore/ReportTotalsAsync capability. It computes currency and subscription counters without customer, product, origin or cohort lists, and prunes stored JSON payloads from SQL. Detailed reports retain their explicit dimension bounds; customer drilldowns page independently. Native tests prove totals and complete paging with 10,001 distinct customers, including narrow windows after the audience already exceeds the detailed-report limit.

Quiesced operational transfer

Call PostgresRevenueStoreTransferSchema.InitializeAsync(dataSource) explicitly, then construct PostgresRevenueStoreTransfer. It transfers the selected app's complete operational role: every user, every document kind and its ordered unique keys, and every original command receipt. Source revisions, user creation ticks, JSON numeric forms and receipt results remain unchanged. Native import is one insert-only transaction, accepts exact existing state, rejects global UUID or scoped unique conflicts, and records migration/batch fingerprints in separate adapter metadata. A lost import response is recovered by retrying the identical batch. Import metadata is excluded from business exports.

The application must keep the source scope quiesced throughout export and verification, fence direct destination access until cutover, and use stable independent coordination and ingress buffering. Pages contain at most 100 records and 4 MiB, and records at most 128 KiB. Cursors are opaque and scope-bound. An individual record larger than the requested page byte limit fails explicitly. This adapter does not transfer native analytics or analytics admission tables: select the separate native adapters below for moving roles, or explicitly retain an independent role unchanged in the migration plan.

Explicit native analytical transfer roles

PostgresRevenueAnalyticsStoreTransfer covers all seven scoped native tables: generations, source receipts, fact identity claims, financial entries, subscriptions, resources and growth facts. PostgresRevenueAnalyticsAdmissionStoreTransfer(dataSource, destinationId) separately covers the selected destination's generations, accepted and pending sources, fact claims and source-to-fact links. Every generation's sealed/active/reconciled state, all hashes, normalized facts, original observation/recorded UTC ticks, pending flags, exact numeric scale and JSON lexemes are preserved. Schema version markers and transfer receipts are adapter metadata and are excluded from business data.

Explicitly call PostgresRevenueAnalyticsStoreTransferSchema.InitializeAsync or PostgresRevenueAnalyticsAdmissionStoreTransferSchema.InitializeAsync for each selected capability. These initialize the corresponding native role and shared, role-keyed import receipts. Constructors and requests never migrate schema. An all-in-one app migration must select operational data and each moving native analytical/admission role, or explicitly name roles retained on independent live stores. Each admission destination is a distinct binding. Matching implemented record kinds are required; these native adapters support PostgreSQL-to-PostgreSQL transfer. They do not convert native analytical tables to another engine or silently substitute operational JSON documents for unsupported roles.

The portable envelope contains logical scope and an opaque native row; physical scope intern IDs are translated at import. Static table/column/primary-key whitelists govern SQL, and all data is parameterized. Export follows dependency order with bounded ordinal primary-key cursors. Import is one insert-only transaction, preserves exact existing rows, enforces native keys/foreign keys/active-generation claims, and verifies each inserted row's re-exported fingerprint before commit. A repeated identical import recovers its original role-specific receipt; an uncertain commit remains retryable with the same batch. Opaque JSON binds directly as json, bypassing scalar record conversion and all jsonb normalization. The common 100-record / 4 MiB page and 128 KiB record bounds apply. Source quiescence and destination fencing remain mandatory throughout copy and verification.

Optional protected inbound queue

Claim and renewal move indexed availability to the exact lease deadline. Receive derives that deadline once from a fresh database clock after the selected row is locked; renewal qualifies ownership and expiry after locking. The existing availability index can skip active leases, while legacy rows retain the residual lease predicate and adopt the indexed deadline on their next claim/renewal. Retry/redrive keep their requested availability. No queue schema migration or eager backfill is required. Moving an indexed value adds native write work, which must be evaluated alongside backlog read savings.

PostgresInboundQueue implements the Core publisher, consumer and redriver contracts in this existing store package. The application explicitly initializes PostgresInboundQueueSchema, provides an NpgsqlDataSource and PostgresInboundQueueOptions.QueueId, and registers publisher/consumer/worker roles as required. Queue-only initialization creates no commerce tables. The queue may share the operational database or use a separate one; a migration buffer must remain available while both operational endpoints are frozen or unavailable.

Messages contain only application-protected opaque bytes. Queue binding and message IDs are ordinal; an exact repeated publication is durable replay, while differing bytes under one ID are rejected. Only DurableAccepted permits an ingress acknowledgment. Unknown outcomes never authorize inline execution. Native database-clock leases use random fenced receipts, SKIP LOCKED concurrent claims, renewal, bounded delayed retry, and atomic completion or dead-letter disposition. Attempts count deliveries and saturate at the Core limit of 1,000,000, so an exhausted counter never hides a message.

Dead-letter evidence retains protected bytes and a bounded non-sensitive reason code. Authorized inspection is bounded by 100 records and 4 MiB with opaque queue-bound cursors. Redrive resets the attempt count and atomically marks the retained evidence, making retry of the same dead-letter ID idempotent. Completed-message identity and payload remain retained for publication deduplication; dead-letter evidence also remains retained. Applications own retention, authorization, encryption-key availability, database durability settings and worker hosting. No constructor migrates schema and no queue API decrypts or logs payloads. This transport transaction is independent of commerce inbox and economic deduplication; consumers must still use the ordinary durable commerce path.

Product Compatible and additional computed target framework versions.
.NET net10.0 is compatible.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
0.1.0-alpha.7.2 50 11/10/2026