Pondhawk.Qbo.Sdk.DynamoDb 1.0.3

Pondhawk QBO

A mock QuickBooks Online API server backed by SQLite, plus a .NET SDK for the real (or mock) QBO API.

Pondhawk QBO lets you snapshot production QBO data into a local database and serve it through a faithful mock of the QBO REST API — complete with query parsing, schema validation, referential integrity checks, optimistic concurrency, batch operations, CDC, PDF generation, and file upload/download. Use it to build and test QBO integrations without burning API quota or touching live data.

Table of Contents

• Quick Start
• Mock Server
  • CLI Commands
  • API Endpoints
  • Query Language
  • Authentication
  • Schema Validation
  • Reference Validation
  • Idempotency
  • Audit Trail & Test Runs
  • Entity Lifecycle
  • PDF Generation
  • File Upload & Download
  • Data Directory Layout
• SDK
  • QboClient
  • Typed Models
  • Authentication Providers
  • Dependency Injection
  • DynamoDB Token Provider
• Build
• Release

---

Quick Start

# 1. Configure an environment with your QBO OAuth credentials
pondhawk-qbo --data-root ./data config set --name prod

# 2. Snapshot production data into SQLite
pondhawk-qbo --data-root ./data snapshot --name prod

# 3. Generate JSON Schemas from the snapshot (enables write validation)
pondhawk-qbo --data-root ./data schema generate --name prod

# 4. Start the mock server
pondhawk-qbo --data-root ./data serve --name prod --port 5059 --no-auth

Point your application at http://localhost:5059 instead of https://quickbooks.api.intuit.com and it will behave like the real QBO API.

---

Mock Server

CLI Commands

All commands require --data-root <path> to specify the root directory for settings, databases, and schemas.

config set --name <name>

Create or update a named environment. Prompts interactively for:

Field	Description
Realm ID	QBO company ID
Client ID	OAuth2 client ID
Client Secret	OAuth2 client secret
Refresh Token	OAuth2 refresh token
Sandbox?	Use QBO sandbox URLs (Y/n)

Environment names must be 1–50 characters, alphanumeric plus hyphens and underscores.

config list

List all configured environments with Realm ID, sandbox status, and data directory.

config show --name <name>

Display full details for an environment (credentials are masked).

snapshot --name <name> [--entities Type1 Type2 ...] [--cdc]

Pull data from the real QBO API into SQLite.

• Full snapshot (default): Pages through all entities (1000 per request) for 30 entity types.
• CDC mode (--cdc): Incremental update via Change Data Capture. Requires an existing full snapshot taken within the last 30 days.
• Selective (--entities): Snapshot only specific entity types.

Access tokens are automatically refreshed when expired (5-minute buffer).

Default entity types: Account, Bill, BillPayment, Budget, Class, CompanyInfo, CreditMemo, Customer, CustomerType, Department, Deposit, Employee, Estimate, Invoice, Item, JournalEntry, Payment, PaymentMethod, Preferences, Purchase, PurchaseOrder, RefundReceipt, SalesReceipt, TaxCode, TaxRate, Term, TimeActivity, Transfer, Vendor, VendorCredit.

schema generate --name <name> [--force]

Generate JSON Schema files (draft 2020-12) from snapshot data. One schema per entity type, saved to the environment's schemas directory. When schemas are present, the mock server validates all create and full-update requests against them.

Use --force to overwrite existing schema files.

serve --name <name> [--port 5059] [--no-auth] [--audit]

Start the mock API server.

Option	Default	Description
--port	5059	HTTP port
--no-auth	false	Disable Bearer token authentication
--audit	false	Enable audit trail and test-run endpoints

On startup the server prints loaded entity counts, schema count, and available download files.

purge --name <name>

Permanently remove soft-deleted entities from the database.

---

API Endpoints

The mock mirrors the QBO REST API URL structure. All endpoints are prefixed with /v3/company/{realmId}.

CRUD

Operation	Method	Path	Notes
Read	GET	/{entityType}/{entityId}	Returns single entity
Create	POST	/{entityType}	Body: { "EntityType": { ... } } — no Id
Update	POST	/{entityType}	Body includes Id + SyncToken
Sparse Update	POST	/{entityType}	Body includes Id + SyncToken + "sparse": true
Delete	POST	/{entityType}?operation=delete	Body: { "EntityType": { "Id": "...", "SyncToken": "..." } }
Void	POST	/{entityType}?operation=void	Supported: Invoice, Payment, SalesReceipt, BillPayment

Query

Operation	Method	Path
Query	GET	/query?query={sql}

See Query Language for syntax.

Batch

Operation	Method	Path	Notes
Batch	POST	/batch	Up to 30 operations per request

Request body:

{
  "BatchItemRequest": [
    { "bId": "1", "Invoice": { "CustomerRef": { "value": "1" }, "Line": [] } },
    { "bId": "2", "Query": "SELECT * FROM Customer MAXRESULTS 5" },
    { "bId": "3", "operation": "delete", "Invoice": { "Id": "10", "SyncToken": "2" } }
  ]
}

Each item can be a create, update, delete, or query. Responses echo the bId for correlation.

Change Data Capture

Operation	Method	Path
CDC	GET	/cdc?entities={types}&changedSince={timestamp}

Returns all entities (including soft-deleted) changed since the given timestamp. Entity types are comma-separated.

Files

Operation	Method	Path	Notes
PDF	GET	/{entityType}/{entityId}/pdf	Invoice, SalesReceipt, Estimate, CreditMemo, PurchaseOrder, RefundReceipt
Upload	POST	/upload	Multipart form with file
Download	GET	/download/{attachableId}	Returns file content

Diagnostics

Operation	Method	Path	Notes
List entities	GET	/entities	Returns { "EntityType": count, ... }
OpenAPI spec	GET	/openapi/v1.json	Auto-generated

---

Query Language

The mock implements a SQL-like query parser compatible with QBO's query syntax.

SELECT [*|COUNT(*)|Field1,Field2,...] FROM EntityType
  [WHERE condition [AND condition ...]]
  [ORDER BY Field [ASC|DESC]]
  [STARTPOSITION N]
  [MAXRESULTS N]

SELECT modes:

• SELECT * — full entities
• SELECT COUNT(*) — count only (returned in totalCount)
• SELECT Field1, Field2 — project specific fields (dotted paths supported, e.g. MetaData.CreateTime)

WHERE operators:

Operator	Example
=	WHERE DisplayName = 'Acme Corp'
!=	WHERE Status != 'Closed'
< <= > >=	WHERE TotalAmt > 100
LIKE	WHERE DisplayName LIKE '%acme%'
IN	WHERE Status IN ('Open', 'Overdue')

Multiple conditions are joined with AND. String comparisons are case-insensitive. Values are single-quoted.

Pagination:

• STARTPOSITION N — 1-based offset (default: 1)
• MAXRESULTS N — page size (default: 100)

Examples:

SELECT * FROM Invoice WHERE CustomerRef = '42' ORDER BY TxnDate DESC MAXRESULTS 50
SELECT COUNT(*) FROM Customer WHERE Active = true
SELECT Id, DocNumber, TotalAmt FROM Invoice WHERE TxnDate >= '2024-01-01'
SELECT * FROM Item WHERE Name LIKE '%widget%' STARTPOSITION 1 MAXRESULTS 25
SELECT * FROM Payment WHERE TotalAmt IN ('100', '200', '500')

---

Authentication

When the server runs without --no-auth, every request must include a Authorization: Bearer <token> header.

The mock validates JWT structure:

1. Token must have 3 dot-separated segments
2. Payload must be valid base64url-encoded JSON
3. Payload must contain an exp claim (Unix timestamp)
4. Token must not be expired

Failures return a 401 with an AuthenticationFault describing the issue.

Use --no-auth to disable authentication entirely for local development.

---

Schema Validation

When JSON Schema files are present in the environment's schemas directory, the mock validates create and full update requests:

• Required fields — entity-type-specific (e.g., Invoice requires CustomerRef and Line)
• Field types — string, number, boolean, object, array
• Unknown fields — rejected when additionalProperties: false

Sparse updates skip required-field validation (only the provided fields are checked).

Generate schemas from your snapshot data:

pondhawk-qbo --data-root ./data schema generate --name prod

Required fields per entity type:

Entity Type	Required Fields
Account	Name, AccountType
Bill	VendorRef, Line
BillPayment	VendorRef, TotalAmt, PayType
Budget	Name, BudgetType, StartDate, EndDate
Class	Name
CreditMemo	CustomerRef, Line
Customer	DisplayName
Department	Name
Deposit	DepositToAccountRef, Line
Employee	GivenName, FamilyName
Estimate	CustomerRef, Line
Invoice	CustomerRef, Line
Item	Name, Type
JournalEntry	Line
Payment	CustomerRef, TotalAmt
PaymentMethod	Name
Purchase	PaymentType, AccountRef, Line
PurchaseOrder	VendorRef, Line, APAccountRef
RefundReceipt	Line
SalesReceipt	CustomerRef, Line
TaxCode	Name
TaxRate	Name, RateValue
Term	Name
TimeActivity	NameOf
Transfer	FromAccountRef, ToAccountRef, Amount
Vendor	DisplayName
VendorCredit	VendorRef, Line

---

Reference Validation

The mock validates that all *Ref fields in a request body reference entities that exist in the database.

Ref field mappings:

Ref Field	Target Entity Type
CustomerRef	Customer
VendorRef	Vendor
AccountRef, DepositToAccountRef, APAccountRef, FromAccountRef, ToAccountRef, BankAccountRef, ExpenseAccountRef, IncomeAccountRef, AssetAccountRef	Account
ItemRef	Item
ClassRef	Class
DepartmentRef	Department
TermRef, SalesTermRef	Term
PaymentMethodRef	PaymentMethod
TaxCodeRef, TxnTaxCodeRef	TaxCode
TaxRateRef	TaxRate
ParentRef	Same entity type (self-reference)

Skipped refs (not validated): CurrencyRef, LastModifiedByRef, CreatedByRef, TaxExemptionRef, EntityRef.

Validation walks the entire JSON tree including nested objects and arrays, and reports the full path to any missing reference.

---

Idempotency

Send a Request-Id header on any POST/write operation. If the same Request-Id is sent again within 24 hours, the mock returns the cached response without re-processing the request.

The cache auto-purges when it exceeds 100 entries.

---

Audit Trail & Test Runs

Start the server with --audit to enable the audit system. This adds test-run management endpoints and logs every API operation during an active run.

Endpoint	Method	Description
/test-runs	POST	Start a new test run ({ "name": "my test" })
/test-runs/stop	POST	Stop the active run and return the full audit log
/test-runs	GET	List all test runs with entry counts
/test-runs/{id}	GET	Get a single run with full audit entries

Only one test run can be active at a time (returns 409 if one is already running).

Each audit entry captures:

Field	Description
timestamp	UTC RFC 3339
method	HTTP method
path	Request path + query string
operation_type	Read, Create, Update, Delete, Void, Query, Batch, CDC, PDF, Upload, Download, etc.
entity_type	Target entity type (if applicable)
entity_id	Target entity ID (if applicable)
request_body	POST body (for writes)
response_status	HTTP status code
response_summary	Human-readable summary (e.g., "Invoice 42 created")
duration_ms	Request duration in milliseconds

---

Entity Lifecycle

Optimistic Concurrency

Every entity has a SyncToken (starts at "0", increments on each update). Updates and deletes require the current SyncToken — a stale token returns a 400 StaleObjectError.

Create

• ID auto-generated (sequential integers per entity type)
• SyncToken set to "0"
• MetaData.CreateTime and MetaData.LastUpdatedTime set to current UTC

Update

• Full update: Replaces the entity. Schema validation runs on all required fields.
• Sparse update ("sparse": true): Merges provided fields onto the existing entity. Skips required-field validation.

Both increment SyncToken and update MetaData.LastUpdatedTime (preserving CreateTime).

Delete

Behavior depends on entity type:

• Named list entities (Account, Class, Customer, Department, Employee, Item, PaymentMethod, Term, Vendor): Set Active = false. Entity remains queryable.
• Transaction entities (Invoice, Bill, Payment, etc.): Soft-deleted — status set to "Deleted", hidden from queries but visible in CDC responses. Use purge to permanently remove.

Void

Supported for Invoice, Payment, SalesReceipt, and BillPayment. Sets Balance = 0 and appends "Voided" to PrivateNote.

---

PDF Generation

The mock generates placeholder PDFs for Invoice, SalesReceipt, Estimate, CreditMemo, PurchaseOrder, and RefundReceipt.

Each PDF includes:

• Header section with entity-specific fields (doc number, customer/vendor, dates, totals, status)
• Line items table (description, quantity, rate, amount)
• Total amount
• Multi-page support

Request: GET /v3/company/{realmId}/Invoice/42/pdf Response: application/pdf

---

File Upload & Download

Upload

POST /v3/company/{realmId}/upload with multipart form data.

• File field: file_content_01
• Optional metadata field: file_metadata_01 (JSON with AttachableRef, Note, etc.)

Creates an Attachable entity and returns it in an AttachableResponse array.

Download

GET /v3/company/{realmId}/download/{attachableId}

The mock looks for a sample file in the environment's downloads directory matching the original file extension. If found, it returns that file. Otherwise it returns a text placeholder.

Drop sample files (e.g., sample.pdf, sample.xlsx) into the downloads directory:

{dataRoot}/{name}/{name}.downloads/

---

Data Directory Layout

{dataRoot}/
  settings.db                          # Environment credentials (SQLite)
  {name}/
    {name}.db                          # Entity data + audit logs (SQLite)
    {name}.schemas/                    # JSON Schema files (*.schema.json)
      Invoice.schema.json
      Customer.schema.json
      ...
    {name}.downloads/                  # Sample files for download endpoint
      sample.pdf
      sample.xlsx

---

SDK

The Pondhawk.Qbo.Sdk package is a .NET client for the QBO REST API (real or mock). It has no external dependencies beyond Microsoft.Extensions.Http.

QboClient

The core client operates on System.Text.Json.Nodes.JsonObject for maximum flexibility.

using Pondhawk.Qbo.Sdk;
using Pondhawk.Qbo.Sdk.Auth;

var token = new StaticTokenProvider("your-access-token");
var options = new QboClientOptions("your-realm-id");
using var client = new QboClient(token, options);

// Read
var response = await client.ReadAsync("Invoice", "42");
var invoice = response.Entity; // JsonObject

// Create
var newCustomer = new JsonObject { ["DisplayName"] = "Acme Corp" };
var created = await client.CreateAsync("Customer", newCustomer);

// Query
var result = await client.QueryAsync("SELECT * FROM Invoice WHERE TotalAmt > 100");
foreach (var entity in result.Entities) { /* ... */ }

// Auto-paginating query (fetches all pages)
var allInvoices = await client.QueryAllAsync("Invoice");

// Count
var count = await client.CountAsync("Invoice", "TotalAmt > 1000");

// Update
invoice["CustomerMemo"] = new JsonObject { ["value"] = "Updated memo" };
var updated = await client.UpdateAsync("Invoice", invoice);

// Sparse update
var sparse = new JsonObject { ["Id"] = "42", ["SyncToken"] = "1", ["CustomerMemo"] = new JsonObject { ["value"] = "New memo" } };
var sparseResult = await client.SparseUpdateAsync("Invoice", sparse);

// Delete / Void
await client.DeleteAsync("Invoice", "42", syncToken: "2");
await client.VoidAsync("Invoice", "42", syncToken: "2");

// Batch (up to 30 operations)
var batch = new JsonArray
{
    new JsonObject { ["bId"] = "1", ["Query"] = "SELECT * FROM Customer MAXRESULTS 5" },
    new JsonObject { ["bId"] = "2", ["Invoice"] = new JsonObject { ["CustomerRef"] = new JsonObject { ["value"] = "1" }, ["Line"] = new JsonArray() } }
};
var batchResult = await client.BatchAsync(batch);

// CDC
var changes = await client.CdcAsync(["Invoice", "Customer"], DateTimeOffset.UtcNow.AddDays(-7));

// PDF
byte[] pdf = await client.GetPdfAsync("Invoice", "42");

// Upload / Download
var uploadResult = await client.UploadAsync(fileStream, "receipt.pdf", "application/pdf");
byte[] fileBytes = await client.DownloadAsync(attachableId);

QboClientOptions

new QboClientOptions(
    RealmId: "your-realm-id",
    BaseUrl: QboEnvironment.Production,  // or QboEnvironment.Sandbox, or "http://localhost:5059"
    MinorVersion: 75);

---

Typed Models

Add using Pondhawk.Qbo.Sdk.Models; to access strongly-typed extension methods and 30 entity model classes.

using Pondhawk.Qbo.Sdk.Models;

// Typed create
var invoice = new Invoice
{
    CustomerRef = new Ref("42", "Acme Corp"),
    Line =
    [
        new Line
        {
            Amount = 100.00m,
            DetailType = "SalesItemLineDetail",
            SalesItemLineDetail = new JsonObject
            {
                ["ItemRef"] = new JsonObject { ["value"] = "1" },
                ["UnitPrice"] = 100,
                ["Qty"] = 1
            }
        }
    ]
};
var created = await client.CreateAsync(invoice);
Console.WriteLine(created.Id); // "123"

// Typed read
var fetched = await client.ReadAsync<Invoice>("123");

// Typed update / sparse update
fetched.PrivateNote = "Updated";
var updated = await client.UpdateAsync(fetched);
var sparse = await client.SparseUpdateAsync(fetched);

// Typed delete / void
await client.DeleteAsync<Invoice>("123", syncToken: "1");
await client.VoidAsync<Invoice>("123", syncToken: "1");

// Typed query all (auto-paginating)
var allCustomers = await client.QueryAllAsync<Customer>();

// Typed count
int count = await client.CountAsync<Invoice>("TotalAmt > 1000");

Available entity models: Account, Bill, BillPayment, Budget, Class, CompanyInfo, CreditMemo, Customer, CustomerType, Department, Deposit, Employee, Estimate, Invoice, Item, JournalEntry, Payment, PaymentMethod, Preferences, Purchase, PurchaseOrder, RefundReceipt, SalesReceipt, TaxCode, TaxRate, Term, TimeActivity, Transfer, Vendor, VendorCredit.

Supporting types: Ref, Address, EmailAddress, PhoneNumber, MetaData, Line, LinkedTxn.

All entity models implement IQboEntity with ToJsonObject() and FromJsonObject() for seamless conversion between typed and dynamic representations.

---

Authentication Providers

StaticTokenProvider

For testing or pre-obtained tokens:

var provider = new StaticTokenProvider("your-access-token");

OAuthTokenProvider

Full OAuth2 with thread-safe caching and automatic pre-expiry refresh:

var creds = new OAuthCredentials("client-id", "client-secret", "refresh-token");
var provider = new OAuthTokenProvider(creds);

// Optional: listen for token refresh events
provider.TokenRefreshed += args =>
{
    // Persist new refresh token
    SaveRefreshToken(args.RefreshToken);
};

• Tokens are cached and refreshed automatically 5 minutes before expiry
• Thread-safe via SemaphoreSlim with fast-path optimization
• Posts to https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer

---

Dependency Injection

Register the SDK with Microsoft.Extensions.DependencyInjection:

// With OAuth
services.AddQboClient(
    client => {
        client.RealmId = "your-realm-id";
        client.BaseUrl = QboEnvironment.Production;
    },
    oauth => {
        oauth.ClientId = "client-id";
        oauth.ClientSecret = "client-secret";
        oauth.RefreshToken = "refresh-token";
    });

// With static token
services.AddQboStaticTokenProvider("your-token");
services.AddQboClient(client => {
    client.RealmId = "your-realm-id";
});

// Returns IHttpClientBuilder for further config (Polly, logging, etc.)

---

DynamoDB Token Provider

The Pondhawk.Qbo.Sdk.DynamoDb package provides a multi-server-safe token provider backed by DynamoDB with distributed lease coordination.

services.AddDefaultAWSOptions(config.GetAWSOptions());
services.AddAWSService<IAmazonDynamoDB>();

services.AddQboDynamoDbTokenProvider(opts => {
    opts.RealmId = "your-realm-id";
    opts.ClientId = "client-id";
    opts.ClientSecret = "client-secret";
    opts.InitialRefreshToken = "refresh-token";
    opts.TableName = "QboTokens";         // default
    opts.AutoCreateTable = true;           // default
    opts.PreExpiryBuffer = TimeSpan.FromMinutes(5);  // default
    opts.LeaseDuration = TimeSpan.FromSeconds(30);   // default
});

services.AddQboClient(client => {
    client.RealmId = "your-realm-id";
});

How it works:

• Tokens are stored in a DynamoDB table keyed by RealmId
• Only one server refreshes at a time via conditional-update lease acquisition
• Other servers wait for the version to increment (exponential backoff, up to 30 seconds)
• Background refresh starts when the token enters the pre-expiry buffer (5 minutes before expiry by default)
• Cold-start race conditions handled via conditional puts

DynamoDB table schema:

Attribute	Type	Description
RealmId	String (PK)	Partition key
AccessToken	String	Current access token
RefreshToken	String	Current refresh token
ExpiresAt	Number	Unix timestamp (seconds)
LockOwner	String	Server ID holding lease
LockExpiry	Number	Lease expiration timestamp
Version	Number	Incremented on each refresh

---

Build

The project uses Cake Frosting for build orchestration.

dotnet run --project build                              # Default: Clean > Restore > Build > Test
dotnet run --project build -- --target Build             # Just Clean > Restore > Build
dotnet run --project build -- --target Publish           # Full pipeline + single-file publish
dotnet run --project build -- --target Publish --runtime linux-x64  # Publish for specific RID
dotnet run --project build -- --target Pack              # Pack SDK NuGet packages
dotnet run --project build -- --target Push              # Pack + push to GitHub Packages

Cake task pipeline:

Clean > Restore > Build > Test > Pack > Push
                               > Publish (self-contained single-file)

Argument	Default	Description
--configuration	Release	Build configuration
--runtime	win-x64	Target RID for Publish
--nuget-key	$NUGET_API_KEY	NuGet API key for Push
--nuget-source	$NUGET_SOURCE or GitHub Packages	NuGet feed URL

---

Release

Pushing a v* tag triggers the GitHub Actions release workflow which:

1. Builds self-contained single-file executables for win-x64, osx-arm64, linux-arm64, and linux-x64
2. Packs and pushes the SDK NuGet packages to GitHub Packages
3. Creates a GitHub Release with all platform binaries attached

git tag v0.1.0
git push origin v0.1.0