# PostgreSQL Schema Management

LiteBus PostgreSQL inbox and outbox stores need a small, stable set of tables and indexes. v5 ships schema helpers and canonical `.sql` files that let you choose how those objects are created, upgraded, and validated without tying LiteBus to a specific migration framework.

## Two Different Version Concepts

LiteBus uses two independent version numbers. Mixing them up is a common source of confusion.

| Version | Stored where | What it versions | Who changes it |
| --- | --- | --- | --- |
| **Contract version** | Each inbox/outbox row (`contract_version`) | JSON payload shape for one message type | Your application when you register `Contracts.Register<T>(name, version: 2)` |
| **Table schema version** | `litebus_schema_versions` metadata table | Physical columns and indexes for the store table | LiteBus when you call `EnsureAsync` or run published upgrade scripts |

Contract version is per message type. Table schema version is per physical store table.

## Packages

| Package | Role |
| --- | --- |
| `LiteBus.PostgreSql` | Shared SQL templates, identifier quoting, schema version metadata, advisory locks, logging hook, drift exceptions |
| `LiteBus.Inbox.PostgreSql` | Inbox store, `PostgreSqlInboxSchema` helper, inbox SQL files |
| `LiteBus.Outbox.PostgreSql` | Outbox store, `PostgreSqlOutboxSchema` helper, outbox SQL files |
| `LiteBus.Inbox.PostgreSql.Extensions.Microsoft.Hosting` | Opt-in hosted schema creation for inbox |
| `LiteBus.Outbox.PostgreSql.Extensions.Microsoft.Hosting` | Opt-in hosted schema creation for outbox |

`LiteBus.PostgreSql` depends only on `Npgsql`.

## Canonical SQL Files (recommended for production)

LiteBus ships the DDL as plain `.sql` files. The runtime loads them as **embedded resources** inside the DLL (so `EnsureAsync` and `GetCreateScript` work from NuGet). The same files are also included as **loose files in the NuGet package** under `sql/` for copy-paste into migration tools.

### Copy from the installed NuGet package

After `dotnet add package LiteBus.Inbox.PostgreSql`, open the package folder in your NuGet cache:

```text
~/.nuget/packages/litebus.inbox.postgresql/{version}/sql/inbox/v1/create.sql
~/.nuget/packages/litebus.inbox.postgresql/{version}/sql/inbox/v1/ensure_indexes.sql
```

Shared metadata and upgrade scripts ship in `LiteBus.PostgreSql`:

```text
~/.nuget/packages/litebus.postgresql/{version}/sql/metadata/create.sql
~/.nuget/packages/litebus.postgresql/{version}/sql/shared/add_trace_context_column.sql
```

On Windows the root is typically `%USERPROFILE%\.nuget\packages\`.

### Copy from the GitHub repository

You can also copy from the LiteBus source tree (same file contents as the NuGet package):

| File | Purpose |
| --- | --- |
| `src/LiteBus.PostgreSql/Sql/metadata/create.sql` | Creates `litebus_schema_versions` |
| `src/LiteBus.Inbox.PostgreSql/Sql/inbox/v1/create.sql` | Creates the version 1 inbox table and indexes |
| `src/LiteBus.Inbox.PostgreSql/Sql/inbox/v1/ensure_indexes.sql` | Re-applies inbox indexes idempotently |
| `src/LiteBus.Outbox.PostgreSql/Sql/outbox/v1/create.sql` | Creates the version 1 outbox table and indexes |
| `src/LiteBus.Outbox.PostgreSql/Sql/outbox/v1/ensure_indexes.sql` | Re-applies outbox indexes idempotently |
| `src/LiteBus.PostgreSql/Sql/shared/add_trace_context_column.sql` | Version 2 upgrade shared by inbox and outbox |

Discover files from code as well:

```csharp
PostgreSqlSchemaSqlPaths.Files              // shared metadata and upgrade SQL
PostgreSqlInboxSchema.SqlFiles              // inbox SQL catalog
PostgreSqlOutboxSchema.SqlFiles             // outbox SQL catalog

// Repository path constants:
PostgreSqlInboxSchemaSqlPaths.V1Create      // src/LiteBus.Inbox.PostgreSql/Sql/inbox/v1/create.sql
PostgreSqlOutboxSchemaSqlPaths.V1Create     // src/LiteBus.Outbox.PostgreSql/Sql/outbox/v1/create.sql
```

### Render scripts in code

When object names differ from LiteBus defaults, call `GetCreateScript` or `GetUpgradeScript` to render the embedded SQL templates with your options:

```csharp
var options = new PostgreSqlInboxStoreOptions
{
    SchemaName = "app",
    TableName = "litebus_inbox_commands"
};

var ddl = PostgreSqlInboxSchema.GetCreateScript(options);
File.WriteAllText("V001__litebus_inbox.sql", ddl);
```

Both approaches are valid. Copying the `.sql` files gives DBAs full control. Rendering with `GetCreateScript` avoids manual token replacement.

## Best Practices for External SQL Resources

LiteBus ships SQL as plain files that are compiled into the assembly and also copied into the NuGet package. Follow these practices when you own migrations or call schema helpers in production.

### Single source of truth

Edit the `.sql` files under each package's `Sql/` folder in the LiteBus repository. Do not maintain a forked copy with different DDL unless your DBAs require it. When you need custom object names, keep the canonical scripts and render them with `GetCreateScript` / `GetUpgradeScript` rather than hand-editing placeholders.

### Embedded vs loose files

| Location | When it is used | Best practice |
| --- | --- | --- |
| Embedded resource in the DLL | `EnsureAsync`, `GetCreateScript`, integration tests | Trust this at runtime; it always matches the package version you referenced |
| Loose `sql/` folder in the NuGet package | DBA review, Flyway/Liquibase copy-paste | Copy verbatim into your migration repo; pin the LiteBus package version in release notes |
| GitHub `src/.../Sql/` tree | Documentation and PR review | Same content as the NuGet loose files for a given release tag |

Runtime code never reads loose files from disk. Deployments that omit the `sql/` folder from the published app still work because the embedded copy is always present.

### Token replacement

Placeholders use the form `{{TokenName}}`. Prefer `GetCreateScript(options)` over manual search-and-replace so schema names, table names, and index names stay correctly quoted for PostgreSQL.

### Connection configuration

| Approach | When to use |
| --- | --- |
| `UseDataSource(NpgsqlDataSource)` | Production apps that already build one shared data source for pooling, health checks, and tracing |
| `UseConnectionString(string)` | Samples, tests, and small services where the module should create and register the data source |

When inbox and outbox share one database, build a single `NpgsqlDataSource` and pass it to both stores with `UseDataSource`. Calling `UseConnectionString` on both stores creates two pools against the same server.

```csharp
// Preferred when both stores use the same database
var dataSource = NpgsqlDataSource.Create(configuration.GetConnectionString("OrdersDb")!);

liteBus.AddPostgreSqlCommandInboxStore(p => p.UseDataSource(dataSource));
liteBus.AddPostgreSqlOutboxStore(p => p.UseDataSource(dataSource));

// Simpler when only one store is registered
liteBus.AddPostgreSqlCommandInboxStore(p =>
    p.UseConnectionString(configuration.GetConnectionString("OrdersDb")!));
```

### Version alignment

Record the LiteBus package version and `CurrentSchemaVersion` in your deployment runbook. After upgrading LiteBus, apply any new upgrade script before rolling out pods that call `ValidateAsync` with `ValidateSchemaCreationOnStartup = true`.

### Do not edit embedded resource names casually

If you contribute to LiteBus, keep SQL paths stable. The loader resolves `{AssemblyName}.Sql.{path.with.dots}.sql`. Renaming folders without updating `EmbeddedResource` items breaks runtime schema creation.

### Placeholder tokens

| Token | Example rendered value | Used in |
| --- | --- | --- |
| `{{QuotedSchemaName}}` | `"app"` | Store table create scripts |
| `{{QualifiedTableName}}` | `"app"."litebus_inbox_commands"` | Store DDL and v2 upgrade |
| `{{QuotedMetadataSchemaName}}` | `"app"` | Metadata table create |
| `{{QualifiedMetadataTableName}}` | `"app"."litebus_schema_versions"` | Metadata DDL |
| `{{IdempotencyIndexName}}` | quoted index name | Inbox only |
| `{{LeaseIndexName}}` | quoted index name | Inbox and outbox |
| `{{TopicIndexName}}` | quoted index name | Outbox only |

## Three Ownership Models

Pick one model per environment. You can use different models in development and production.

### 1. Migration-owned (recommended for production)

Your team owns schema timing. Copy the canonical `.sql` files or rendered output from `GetCreateScript` / `GetUpgradeScript`.

Leave `EnsureSchemaCreationOnStartup = false` (the default). Run validation in deploy checks if you want a fail-fast guard:

```csharp
await PostgreSqlInboxSchema.ValidateAsync(dataSource, options, cancellationToken);
```

### 2. Explicit bootstrap

Call `EnsureAsync` from application startup, a one-shot deploy job, or integration test setup. The call is idempotent.

```csharp
await PostgreSqlInboxSchema.EnsureAsync(dataSource, options, cancellationToken);
await PostgreSqlOutboxSchema.EnsureAsync(dataSource, options, cancellationToken);
```

Use this for internal services, prototypes, and test environments where a migration pipeline is overhead.

### 3. Opt-in host schema creation

Enable automatic schema creation when the generic host starts. Both the store option and the hosting module must be registered.

```csharp
builder.Services.AddLiteBus(liteBus =>
{
    liteBus.AddPostgreSqlCommandInboxStore(postgres =>
    {
        postgres.UseDataSource(dataSource);
        postgres.EnsureSchemaCreationOnStartup();
    });

    // Register schema hosting BEFORE processor hosting.
    liteBus.AddPostgreSqlCommandInboxSchemaHosting();

    liteBus.AddCommandInboxModule(inbox => { /* contracts */ });
    liteBus.AddCommandInboxProcessorHosting(host => host.PollInterval = TimeSpan.FromSeconds(1));
});
```

The schema hosted service no-ops when `EnsureSchemaCreationOnStartup` is `false`.

## Configuration Options

Both inbox and outbox share the same option shape through `PostgreSqlSchemaStoreOptions`.

| Option | Default | Purpose |
| --- | --- | --- |
| `SchemaName` | `public` | PostgreSQL schema for the store table |
| `TableName` | `litebus_inbox_commands` / `litebus_outbox_messages` | Store table name |
| `MetadataSchemaName` | `public` | Schema for the version metadata table |
| `MetadataTableName` | `litebus_schema_versions` | Version metadata table name |
| `EnsureSchemaCreationOnStartup` | `false` | Run `EnsureAsync` when the host starts |
| `ValidateSchemaCreationOnStartup` | `true` | Run `ValidateAsync` after schema creation on startup |
| `Logger` | `null` | Optional `IPostgreSqlSchemaLogger` for schema operations |

Example with custom object names and logging:

```csharp
postgres.UseOptions(new PostgreSqlInboxStoreOptions
{
    SchemaName = "messaging",
    TableName = "command_inbox",
    MetadataSchemaName = "messaging",
    MetadataTableName = "litebus_schema_versions",
    Logger = new ConsolePostgreSqlSchemaLogger()
});
```

Use the same option instance for the store registration and schema helpers so table names stay aligned.

## Logging

`LiteBus.PostgreSql` exposes `IPostgreSqlSchemaLogger` without taking a dependency on Microsoft logging packages. Assign an implementation through `PostgreSqlSchemaStoreOptions.Logger`.

Schema operations log at these points:

- Starting and completing `EnsureAsync`
- Advisory lock acquired or waiting on another session
- Creating version 1 objects
- Applying version upgrades
- Recording metadata version rows
- Validation success or `PostgreSqlSchemaDriftException` details

Example adapter for local development:

```csharp
public sealed class ConsolePostgreSqlSchemaLogger : IPostgreSqlSchemaLogger
{
    public void Log(PostgreSqlSchemaLogLevel level, string message, Exception? exception = null)
    {
        Console.WriteLine($"[{level}] {message}");
        if (exception is not null)
        {
            Console.WriteLine(exception);
        }
    }
}
```

Hosting applications can bridge `IPostgreSqlSchemaLogger` to `ILogger<T>` in the hosting package without changing `LiteBus.PostgreSql` dependencies.

## Schema Version Metadata

LiteBus records applied table schema versions in `litebus_schema_versions`:

| Column | Purpose |
| --- | --- |
| `component` | `inbox` or `outbox` |
| `schema_name` | Store table schema |
| `table_name` | Store table name |
| `version` | Applied table schema version |
| `applied_at` | UTC timestamp of the last recorded upgrade |

Primary key: `(component, schema_name, table_name)`.

One metadata table serves all LiteBus store tables in the database. Each inbox or outbox table you configure gets its own row.

## Current Schema Versions (v5.0)

| Component | Version | Notes |
| --- | --- | --- |
| Inbox | **2** | v1 initial table; v2 adds nullable `trace_context jsonb` reserved for future tracing metadata |
| Outbox | **2** | v1 initial table; v2 adds nullable `trace_context jsonb` reserved for future tracing metadata |

Constants: `PostgreSqlInboxSchema.CurrentSchemaVersion` and `PostgreSqlOutboxSchema.CurrentSchemaVersion`.

The store implementations do not read `trace_context` yet. The column exists so future LiteBus releases can add tracing metadata without another immediate breaking DDL change.

## API Reference

### Create and upgrade

| Method | Purpose |
| --- | --- |
| `GetCreateScript(options)` | Full rendered DDL for the current schema version, metadata table, and indexes |
| `GetUpgradeScript(from, to, options)` | Incremental rendered DDL between two table schema versions |
| `EnsureAsync(dataSource, options, ct)` | Create, upgrade, and record the current version idempotently |
| `CreateIfNotExistsAsync(...)` | Alias for `EnsureAsync` kept for readability in tests |
| `ValidateAsync(dataSource, options, ct)` | Fail fast when the physical table or metadata does not match the library |
| `SqlFiles` | Catalog of repository SQL file paths and descriptions |

### Exceptions

`PostgreSqlSchemaDriftException` is thrown by `ValidateAsync` and by startup validation. It includes:

- `Component` (`inbox` / `outbox`)
- `SchemaName`, `TableName`
- `ExpectedVersion`, `ActualVersion`
- `Details` (missing columns, missing table, version mismatch)

Treat this as a deployment blocker. Do not catch and ignore it in production startup.

## Multi-Instance and Microservice Safety

`EnsureAsync` is safe when many pods or services start at the same time.

1. **Initial create** uses `CREATE ... IF NOT EXISTS` for schemas, tables, and indexes.
2. **Upgrades** use idempotent DDL such as `ADD COLUMN IF NOT EXISTS`.
3. **Advisory locks** ensure one connection applies upgrades at a time per store table. Other instances wait until the table reaches the expected version or the wait timeout elapses.

Lock key format: `litebus:{component}:{schema}:{table}`.

Waiting instances poll the metadata table and physical column shape. They do not fail merely because another instance holds the lock.

### What to run from every pod vs one deploy job

| Operation | Safe from all pods | Recommended pattern |
| --- | --- | --- |
| First-time `EnsureAsync` | Yes | Opt-in host schema creation or startup call |
| Version upgrade (`EnsureAsync`) | Yes (with advisory lock) | Prefer a deploy hook in large fleets; host schema creation is still safe |
| Destructive DDL (drop/rename/type change) | No | Manual migration only; never auto-run from LiteBus |
| `ValidateAsync` | Yes | Deploy check or startup when `ValidateSchemaCreationOnStartup = true` |

## Upgrade Path for Existing v1 Tables

If you created tables with early v5 previews using `CreateIfNotExistsAsync` before metadata existed, `EnsureAsync` handles the transition:

1. Detects the existing table.
2. Infers version **1** from column shape when metadata is missing.
3. Applies the **1 → 2** upgrade script.
4. Writes metadata at version **2**.

If metadata says version **2** but a column was removed manually, `EnsureAsync` trusts the physical column shape and re-applies missing upgrades.

## Registration Order With Processor Hosting

Schema creation must finish before inbox or outbox processors lease rows. Register modules in this order:

```csharp
liteBus.AddPostgreSqlCommandInboxStore(/* ... */);
liteBus.AddPostgreSqlCommandInboxSchemaHosting();   // 1. schema creation
liteBus.AddCommandInboxModule(/* ... */);
liteBus.AddCommandInboxProcessorHosting(/* ... */);  // 2. processor

liteBus.AddPostgreSqlOutboxStore(/* ... */);
liteBus.AddPostgreSqlOutboxSchemaHosting();           // 1. schema creation
liteBus.AddOutboxModule(/* ... */);
liteBus.AddOutboxProcessorHosting(/* ... */);         // 2. processor
```

Schema hosting registers an `IHostedService` that completes `StartAsync` before background processor loops begin.

## Manual Deploy Job Example

For teams that want automatic DDL without running it from every pod:

```csharp
var dataSource = NpgsqlDataSource.Create(connectionString);

await PostgreSqlInboxSchema.EnsureAsync(dataSource, inboxOptions);
await PostgreSqlOutboxSchema.EnsureAsync(dataSource, outboxOptions);

await PostgreSqlInboxSchema.ValidateAsync(dataSource, inboxOptions);
await PostgreSqlOutboxSchema.ValidateAsync(dataSource, outboxOptions);

Console.WriteLine("LiteBus PostgreSQL schema is ready.");
```

Run this once per deployment before rolling out application pods with `EnsureSchemaCreationOnStartup = false`.

## Flyway / Liquibase Workflow

1. Copy the `.sql` files from `PostgreSqlInboxSchema.SqlFiles` / `PostgreSqlOutboxSchema.SqlFiles` into your migration repository, or generate rendered scripts with `GetCreateScript`.
2. On LiteBus upgrades that bump `CurrentSchemaVersion`, add the new upgrade `.sql` file or call `GetUpgradeScript(previous, current)`.
3. Record the LiteBus release and schema version in your internal runbook.
4. Call `ValidateAsync` from a smoke test after migration.

Prefer copying the shipped `.sql` files verbatim. Edit only when DBAs require renames, tablespaces, or ownership clauses.

## Custom Stores

If you implement `ICommandInboxWriter` / `IOutboxMessageWriter` and your own storage, you own the schema entirely. LiteBus does not require the metadata table or `trace_context` column for custom stores. Match the envelope fields your implementation reads and writes.

## Future LiteBus Releases

When LiteBus bumps `CurrentSchemaVersion`:

1. Read the release notes and migration guide entry for the target version.
2. Add the new `.sql` upgrade file to your migration pipeline, or apply `GetUpgradeScript(oldVersion, newVersion)`.
3. Deploy application code that expects the new library version after DDL succeeds.
4. Prefer additive, backward-compatible DDL (nullable columns, new indexes). LiteBus auto-upgrade only runs safe scripts.

LiteBus will not auto-run destructive changes (drops, renames, narrowing type changes) from application pods.

## Related Docs

- [Command Inbox](Command-Inbox.md)
- [Outbox](Outbox.md)
- [Processor Hosting](Processor-Hosting.md)
- [Dependency Graph](Dependency-Graph.md)
- [Migration Guide v5](Migration-Guide-v5.md)