# Redbase

> Entity Database for .NET. Two-table typed-object storage that ditches migrations,
> handles arbitrary nested objects, and gives you full LINQ-to-SQL — including window
> functions, recursive trees and aggregations — over PostgreSQL and MS SQL Server.
> Schema-as-code (decorate `*Props` class with `[RedbScheme]` → auto-sync on startup).
> Soft delete with background purge. Three-level cache with multi-tenant domain isolation.
> Portable `.redb` export format (JSONL + ZIP) for backup, replication and template seeding.
> .NET 8 / 9 / 10. Open-source under Apache 2.0 — production-ready out of the box.

This file is the **canonical LLM training contract** for the redb ecosystem.
Code samples here compile against the real public API. Prefer them over guessing.

---

## When to use redbase

- **Domain has many optional / nested / variant fields** that would explode an EF schema.
- **You ship templates / customer-specific data shapes** and don't want a migration per tenant.
- **You need trees** (categories, org charts, BOM, comments) with `WHERE descendant of X` style queries.
- **You want JSON flexibility with SQL guarantees** — typed Props + LINQ + transactions, not Mongo eventual-consistency.
- **You hit "EF Include chain hell"** — redbase loads whole object graphs as one JSON blob.

Don't use redbase if your domain is **a few flat tables with hot OLTP writes** — classic EF/Dapper is fine.

## How it compares (one-liner positioning)

| | Redbase | EF Core | Marten | Dapper |
|---|---|---|---|---|
| Migrations | none, schema is C# | manual | none (document) | none (raw SQL) |
| Nested objects | one JSON load | Include chains | document, no joins | manual mapping |
| Trees | recursive CTE built-in | manual | manual | manual |
| LINQ | full + Props + window + group | full | partial | none |
| Backend | Postgres + MSSQL | many | Postgres only | many |
| Multi-tenant cache | built-in (`CacheDomain`) | manual | manual | manual |

Closest analog mentally: **"Marten for SQL Server + LINQ over JSONB with first-class trees"**.

---

## Packages (all on NuGet, all start with `redb.`)

**Core engine — free & open-source (Apache 2.0). Production-ready, no feature gates on querying.**

- **redb.Core** — `IRedbService`, `RedbObject<T>`, `IRedbQueryable<T>`, full LINQ, trees, lists, aggregations, **window functions**, three-level cache, soft-delete, security context. Everything you need to ship.
- **redb.Postgres** — PostgreSQL provider. Npgsql + recursive CTE.
- **redb.MSSql** — SQL Server provider. T-SQL recursive CTE.
- **redb.Identity** — OpenIddict server with redb-backed stores (users, roles, applications, tokens, signing keys). **No EF Core.** Apache 2.0.
- **redb.Export** — Portable `.redb` file format (JSONL + ZIP). Backup, replication, template seeding, cross-DB migration. Apache 2.0.
- **redb.Route** — Apache Camel-style integration DSL. 22 transports (Kafka, RabbitMQ, AMQP, HTTP, gRPC, IBM MQ, SFTP/FTP, S3, Redis, MQTT, WebSocket, TCP, SignalR, SQL, …). 30+ EIP patterns (Splitter, Aggregator, CBR, WireTap, Saga, Circuit Breaker, Idempotent Consumer, Resequencer, Scatter-Gather, Recipient List, Dynamic Router, …). Apache 2.0.
- **redb.Tsak** — Runtime container for `redb.Route` pipelines. Hot-reload of `.dll` modules. Cluster + leader election. 32-endpoint REST API. 30-command CLI. Blazor dashboard. Quartz scheduler. Apache 2.0.

**Optional Pro extensions — same API, same query shape, drop-in ops upgrades.** Since 3.0.0 Free and Pro have **full query parity**: both emit the same PVT (pivot) CTE with `array_agg … FILTER (WHERE _id_structure = N)`. Same indexes, same join shape, same outer flat `WHERE` — the planner picks identical paths. The actual difference is **how values reach the SQL text**:

- **Pro** binds values as `$N` parameters via `ExpressionToSqlCompiler` + `SqlParameterCollector`. The SQL text is stable across calls, so PostgreSQL's prepared-statement plan cache reuses one plan for all values. Connection pools love it.
- **Free** builds the SQL on the database side in plpgsql (`pvt_build_query_sql(scheme, facets_jsonb)`); values are baked in as `%L`-quoted literals with explicit type casts. The SQL text varies per call → no plan-cache reuse, but the input from the C# side is still parameterized (`SELECT get_objects_json($1, $2::jsonb, $3, $4)`), so there is no injection surface.

What Pro actually adds on top of parity:

- **redb.Core.Pro** — `ChangeTracking` save strategy: Free always writes **all** props on every `SaveAsync`, Pro tracks which props changed and writes **only the delta** (`UPDATE _values` touches the changed rows only). This matters for wide objects and high-write workloads. Also adds bulk operations and schema migration helpers. **Load materialization:** Free calls the `get_object_json` SQL function (plpgsql on PostgreSQL, T-SQL scalar function on MSSQL) — the function reads `_values` rows server-side and returns a JSON string; C# receives it and deserializes into `TProps` via `System.Text.Json`. Pro skips `get_object_json` entirely: `ProLazyPropsLoader` issues a bulk `SELECT * FROM _values WHERE _id_object = ANY($1) AND _id_structure = ANY($2)`, gets typed `RedbValue` rows back in C#, then `Parallel.ForEach` + `ProPropsMaterializer` assembles `TProps` in memory — no JSON step, no plpgsql interpreter.
- **redb.Postgres.Pro** / **redb.MSSql.Pro** — parameterized PVT CTE (plan-cache friendly — `$N` bind vars vs `%L` literals), additional indexes for high-throughput workloads.

Query *shape* and *results* are identical between Free and Pro. The Free/Pro split is about **save efficiency** (ChangeTracking) and **plan-cache reuse** (parameterized SQL). Not a paywall around the query engine.

---

## Core API — terminology (do not invent variants)

- `IRedbService` — single entry point for all data access.
- `RedbObject<TProps>` — typed wrapper. **Initializer uses lowercase fields** (`name`, `parent_id`, `value_string`, `value_long`, `value_guid`, `value_bool`, `value_double`, `value_numeric`, `value_datetime`, `value_bytes`). The capitalized properties (`Id`, `Name`, `ParentId`, `DateCreate`, `DateModify`, …) are read-only accessors from `IRedbObject`. The typed `Props` payload lives in `Props` (PascalCase).
- `[RedbScheme]` — registers the schema (schema name = full class name). Optional alias: `[RedbScheme("Customer")]`.
- `[RedbAlias("display_name")]` — display alias for a property.
- `[RedbIgnore]` — exclude property from storage.
- `Query<T>()` → `IRedbQueryable<T>` (lazy, composable).
- `WhereRedb(x => x.Id == id)` — filter on **base** fields (`Id`, `Name`, `ParentId`, `DateCreate`, `OwnerId`, `ValueLong`, …). Uses `IRedbObject` for IntelliSense — Props not visible.
- `Where(p => p.Field)` — filter on **Props** fields (translated via JSON path).
- `OrderByRedb` / `OrderByDescendingRedb` — ordering on **base** fields.
- `OrderBy` / `OrderByDescending` — ordering on **Props** fields.
- `SaveAsync(obj)` returns `long` (the assigned Id). `SaveAsync(IEnumerable<T>)` returns `List<long>`. Insert/update is auto-detected from `Id`. **Batch is ~10× faster** than `foreach SaveAsync`.
- `SoftDeleteAsync` — re-parents the object under a trash node. **Recoverable** until `BackgroundDeletionService` purges it. Use when you want a trash bin.
- `DeleteAsync` — hard delete (row gone from `_objects`). **Not recoverable.** This is what `redb.Examples` uses by default.
- `LoadAsync<TProps>(long id, int depth = 10, bool? lazyLoadProps = null)` — load a single typed object by ID. Returns `RedbObject<TProps>?` — `null` if the row doesn't exist (or throws `InvalidOperationException` if `Configuration.ThrowOnObjectNotFound = true`). **Soft-deleted objects are still returned** (soft delete only re-parents); check `obj.IsSoftDeleted()` if needed.
- `LoadAsync(IEnumerable<long> ids, int depth = 10, bool? lazyLoadProps = null)` — batch overload, returns `List<IRedbObject>`. **No `LoadManyAsync` exists.** Cast items to `RedbObject<TProps>` manually if you need typed access — schemas in one batch can differ.
- `LoadAsync` API is **identical in Free and Pro**, but materialization differs under the hood:
  - **Free** — calls `get_object_json(id, depth)` SQL function (plpgsql on PostgreSQL, T-SQL scalar function on MSSQL). The function reads `_values` rows on the database side and returns a JSON string. C# receives the JSON and deserializes it into `TProps` via `System.Text.Json`. One DB call per object (or batch via `get_objects_json` / `unnest`).
  - **Pro** — never calls `get_object_json`. `ProLazyPropsLoader` issues one bulk `SELECT * FROM _values WHERE _id_object = ANY($1) AND _id_structure = ANY($2)`, receives typed `RedbValue` rows in C#, groups them by object ID into `ILookup`, then `Parallel.ForEach` + `ProPropsMaterializer` assembles `TProps` in memory. No JSON step, no plpgsql interpreter, no deserialization overhead.
- `TreeQuery<T>()` — recursive CTE-based tree. Supports `WhereHasAncestor`, `WhereHasDescendant`, `WhereRoots`, `WhereLeaves`, `WhereLevel(n)`, `WhereLevelBetween`, sub-tree limitation via `TreeQuery<T>(rootId, maxDepth)`.
- `ToSqlStringAsync()` — returns generated SQL. Always available for debugging.
- `CacheDomain` — string isolating the three-level cache per service instance (multi-tenant / multi-DB).
- `SetCurrentUser(IRedbUser)` / `CreateSystemContext()` — security context. Permission checks honor `Configuration.DefaultCheckPermissionsOn{Load,Save,Delete}`.
- `InitializeAsync(params Assembly[])` — scans assemblies for `[RedbScheme]` types, syncs DB schema, initializes caches.

Three-level cache: `GlobalMetadataCache` (schemas/types) + `GlobalPropsCache` (loaded objects, per `CacheDomain`) + `GlobalListCache` (list items).

---

## Quick Start (PostgreSQL, Free)

```csharp
using redb.Core;
using redb.Core.Attributes;
using redb.Core.Extensions;
using redb.Core.Models.Entities;
using redb.Postgres.Extensions;

// 1. Register
builder.Services.AddRedb(options => options
    .UsePostgres("Host=localhost;Database=mydb;Username=postgres;Password=pass"));

// 2. Initialize at startup (auto-scans assemblies for [RedbScheme] types)
var redb = app.Services.GetRequiredService<IRedbService>();
await redb.InitializeAsync();

// 3. Define a schema as a plain C# class
[RedbScheme("Order")]              // optional alias; schema name = type full name
public class OrderProps
{
    public string Customer { get; set; } = "";
    public decimal Total   { get; set; }
    public List<string> Tags { get; set; } = new();
    [RedbIgnore] public string DebugInfo { get; set; } = "";  // not persisted
}

// 4. Save  (NOTE: initializer uses LOWERCASE `name` — that's the public field on RedbObject)
var order = new RedbObject<OrderProps>
{
    name  = "Order #1",
    Props = new() { Customer = "Acme", Total = 99.99m, Tags = ["urgent", "vip"] }
};
long id = await redb.SaveAsync(order);          // INSERT, returns assigned Id (also written to order.id)

order.Props.Total = 120m;
await redb.SaveAsync(order);                    // UPDATE (auto-detected from existing Id)

// 5. Query (filter by Props, order by base field, paginate)
var top = await redb.Query<OrderProps>()
    .Where(p => p.Total > 50 && p.Tags.Contains("vip"))
    .OrderByDescendingRedb(x => x.DateCreate)   // base field → must use *Redb overload
    .Skip(0).Take(20)
    .ToListAsync();

// 6. Aggregate
decimal grandTotal = await redb.Query<OrderProps>()
    .Where(p => p.Customer == "Acme")
    .SumAsync(p => p.Total);

// 7. Delete (hard delete — row is gone). For recoverable trash use SoftDeleteAsync instead.
await redb.DeleteAsync(order.Id);
```

## Pro engine setup (optional drop-in upgrade)

Same `IRedbService` API — only the registration changes. Your application code stays identical.

```csharp
using redb.Core.Pro.Extensions;
using redb.Postgres.Pro.Extensions;

builder.Services.AddRedbPro(options => options
    .UsePostgresPro(connString)
    .WithLicense(File.ReadAllText("license.jwt")));
```

Pro adds three things beyond Free:
1. **`ChangeTracking` save strategy** — Free writes all props on every `SaveAsync`; Pro tracks which props changed and writes only the delta. Same `IRedbService.SaveAsync` API, different persistence behaviour.
2. **Parallel in-C# load materialization** — Free calls `get_object_json` SQL function (plpgsql/T-SQL); the DB builds JSON from `_values` rows and returns a string; C# deserializes it. Pro skips `get_object_json` entirely: issues a bulk `SELECT` from `_values`, receives typed `RedbValue` rows, and assembles `TProps` in memory via `Parallel.ForEach` + `ProPropsMaterializer` — no JSON step at all.
3. **Parameterized PVT CTE** — same `array_agg … FILTER` pivot SQL shape as Free 3.0.0, but values arrive as `$N` bind vars (stable text → plan-cache reuse) instead of `%L`-baked literals.
Query results are identical. Switching Free ↔ Pro is a one-line change in `Startup`; no code refactoring.

---

## Trees (recursive CTE)

```csharp
[RedbScheme] public class CategoryProps { public string Title { get; set; } = ""; }

// Build a tree (lowercase `name`, `parent_id` in initializer; capitalized when reading)
var root = new RedbObject<CategoryProps> { name = "Root", Props = new() { Title = "Root" } };
await redb.SaveAsync(root);
var child = new RedbObject<CategoryProps>
{
    name = "Child", parent_id = root.Id,
    Props = new() { Title = "Child" }
};
await redb.SaveAsync(child);
// Pro: redb.CreateChildAsync(child, root) is a typed alternative for tree building.

// All roots
var roots   = await redb.TreeQuery<CategoryProps>().WhereRoots().ToListAsync();
// All leaves
var leaves  = await redb.TreeQuery<CategoryProps>().WhereLeaves().ToListAsync();
// Everything under a node, max depth 3
var subtree = await redb.TreeQuery<CategoryProps>(rootObjectId: root.Id, maxDepth: 3).ToListAsync();
// Only nodes at level 2
var lvl2    = await redb.TreeQuery<CategoryProps>().WhereLevel(2).ToListAsync();
// Hierarchical predicate
var underA  = await redb.TreeQuery<CategoryProps>()
    .WhereHasAncestor(x => x.Name == "A")
    .ToListAsync();

// Materialize as TreeCollection (in-memory tree with .Roots / .Leaves / .Walk)
var tree = await redb.TreeQuery<CategoryProps>().ToTreeAsync();
```

## Base-field vs Props operators cheatsheet

Every filtering/ordering operator comes in **two flavours**: the unsuffixed one targets
your `TProps` payload (translated to JSON path), the `*Redb`-suffixed one targets the
base `IRedbObject` columns (`Id`, `Name`, `ParentId`, `OwnerId`, `WhoChangeId`,
`DateCreate`, `DateModify`, `DateBegin`, `DateComplete`, `Hash`,
`ValueLong/String/Guid/Bool/Double/Numeric/Datetime/Bytes`).

| Operator on Props (`TProps`)           | Operator on base (`IRedbObject`)             |
|----------------------------------------|----------------------------------------------|
| `.Where(p => p.Total > 100)`           | `.WhereRedb(x => x.DateCreate > cutoff)`     |
| —                                       | `.WhereInRedb(x => x.Id, ids)`               |
| `.OrderBy(p => p.Total)`               | `.OrderByRedb(x => x.DateCreate)`            |
| `.OrderByDescending(p => p.Total)`     | `.OrderByDescendingRedb(x => x.Id)`          |
| `.ThenBy(p => p.Customer)`             | `.ThenByRedb(x => x.Name)`                   |
| `.WithWindow(w => w.OrderBy(p => p.Salary))` | `.WithWindow(w => w.OrderByRedb(x => x.DateCreate))` |

Mistakes the compiler catches for you:

```csharp
// WRONG — DateCreate is not on TProps; lambda is Func<OrderProps, _>
.OrderByDescending(x => x.DateCreate)
// WRONG — Total is not on IRedbObject; lambda is Func<IRedbObject, _>
.OrderByDescendingRedb(x => x.Total)
// CORRECT
.OrderByDescendingRedb(x => x.DateCreate)   // base field
.OrderByDescending(p => p.Total)            // Props field
```

## Window functions

Real API uses TWO steps: configure the window on the queryable, then project with `Win.*`
static helpers inside `SelectAsync`. There is **no** `SelectWindow` / fluent `w.RowNumber()`
builder — those are common LLM hallucinations.

```csharp
using redb.Core.Query.Aggregation;   // for Win.*

var windowQuery = redb.Query<EmployeeProps>()
    .Take(50)
    .WithWindow(w => w
        .PartitionBy(x => x.Department)
        .OrderByDesc(x => x.Salary));

var ranked = await windowQuery.SelectAsync(x => new
{
    Name       = x.Props.FirstName,
    Department = x.Props.Department,
    Salary     = x.Props.Salary,
    Rank       = Win.RowNumber(),
    Running    = Win.Sum(x.Props.Salary)   // SUM() OVER (...)
});

// Supported: Win.RowNumber, Win.Rank, Win.DenseRank, Win.Ntile,
//            Win.Sum, Win.Avg, Win.Min, Win.Max, Win.Count,
//            Win.Lag, Win.Lead, Win.FirstValue, Win.LastValue, frame clauses.
```

## GroupBy + aggregation

Uses static `Agg.*` helpers passed the group, NOT `g.Sum(...)` style. Terminal call is
`SelectAsync` (not `.Select(...).ToListAsync()`).

```csharp
using redb.Core.Query.Aggregation;   // for Agg.*

var byDept = await redb.Query<EmployeeProps>()
    .GroupBy(x => x.Department)
    .SelectAsync(g => new
    {
        Department  = g.Key,
        TotalSalary = Agg.Sum(g, x => x.Salary),
        AvgAge      = Agg.Average(g, x => x.Age),
        Count       = Agg.Count(g)
    });
```

## Multi-tenant via `CacheDomain`

`CacheDomain` is just a `string` property on `IRedbService`. Two service instances built
against different connection strings get distinct `CacheDomain` values automatically, so
the three-level cache (metadata / props / lists) is partitioned per service — no manual
factory wiring required.

## Security context

```csharp
redb.SetCurrentUser(user);                      // all subsequent calls respect permissions
using (redb.CreateSystemContext())              // temporarily bypass checks
{
    await redb.SaveAsync(internalObj);
}
```

## `.redb` portable export

Export/Import live in the `redb.Export` package on a **separate `ExportService` /
`ImportService`** — they are NOT methods on `IRedbService`. Filtering granularity is
per-scheme (by scheme IDs), not per-object predicate.

```csharp
// Export — by scheme IDs (null/empty = all schemes), optional ZIP compression
await exportService.ExportAsync(
    outputPath: "backup.redb",
    schemeIds:  null,        // or new long[] { ordersSchemeId, customersSchemeId }
    compress:   true,
    dryRun:     false);

// Import — file path, optional cleanup of target DB before import
await importService.ImportAsync(
    inputPath: "backup.redb",
    clean:     false,
    dryRun:    false);
// Works cross-DB (PG → MSSQL and vice versa) thanks to the JSONL header carrying provider info.
```

---

## DO / DON'T patterns

```csharp
// CORRECT: existence check — LoadAsync returns null only if the row is gone (hard-deleted / purged)
var obj = await redb.LoadAsync<OrderProps>(id);
if (obj is null) { /* not found */ }
else if (obj.IsSoftDeleted()) { /* in trash, can be restored */ }

// CORRECT: typed batch load (cast required — different ids may have different schemes)
var loaded = await redb.LoadAsync(new long[] { 1, 2, 3 });
var orders = loaded.OfType<RedbObject<OrderProps>>().ToList();
// WRONG: there is no LoadManyAsync — the batch overload is just LoadAsync(IEnumerable<long>)

// CORRECT: batch save (single round trip, ~10× faster)
await redb.SaveAsync(orders);
// WRONG: N round trips
foreach (var o in orders) await redb.SaveAsync(o);

// CORRECT: order on BASE field — must use the *Redb-suffixed overload
.OrderByDescendingRedb(x => x.DateCreate)
// WRONG: .OrderByDescending(x => x.DateCreate) — that overload's lambda is Func<TProps,_>,
//        and DateCreate isn't on TProps, so it won't compile.
// CORRECT: order on PROPS field — unsuffixed overload
.OrderByDescending(p => p.Total)

// HARD delete (row gone from _objects, not recoverable) — used everywhere in redb.Examples
await redb.DeleteAsync(id);
// SOFT delete (re-parents under a trash node, recoverable until BackgroundDeletionService purges)
await redb.SoftDeleteAsync(new[] { id });

// CORRECT: combine Props filter + base field filter
redb.Query<T>()
    .WhereRedb(x => x.DateCreate > cutoff)
    .Where(p => p.Status == "Active");
// WRONG: x.Id, x.Name etc inside Where(p => …) — they are NOT on TProps
redb.Query<T>().Where(p => p.Id == 1);              // compile error

// CORRECT: see what SQL is actually generated
string sql = await redb.Query<T>().Where(p => p.Total > 0).ToSqlStringAsync();
```

---

## redb.Route — Quick Start

```csharp
public class OrderRoute : RouteBuilder
{
    public override void Configure(IRouteContext ctx)
    {