Database-as-Code: Attribute-Driven Schema Management with Tuxedo ORM and Bowtie Migrations

using System.ComponentModel.DataAnnotations;
using Noundry.Tuxedo.Contrib;
using Noundry.Tuxedo.Bowtie.Attributes;

namespace MyApp.Models;

/// <summary>
/// Abstract base for all domain entities. Provides a GUID primary key,
/// audit timestamps, and a soft-delete flag. Every table in our schema
/// inherits these four columns automatically.
/// </summary>
public abstract class BaseEntity
{
    [ExplicitKey]
    [Column("id")]
    public Guid Id { get; set; } = Guid.NewGuid();

    [Column("created_at")]
    [DefaultValue("NOW()", IsRawSql = true)]
    public DateTime CreatedAt { get; set; }

    [Column("updated_at")]
    public DateTime? UpdatedAt { get; set; }

    [Column("is_deleted")]
    [DefaultValue("false")]
    public bool IsDeleted { get; set; }
}

using System.ComponentModel.DataAnnotations;
using Noundry.Tuxedo.Contrib;
using Noundry.Tuxedo.Bowtie.Attributes;

namespace MyApp.Models;

/// <summary>
/// Customer entity — TableOrder 1 (base table, no foreign key dependencies).
/// Demonstrates unique constraints, B-Tree indexes, column length limits,
/// TEXT columns, check constraints, and default values.
/// </summary>
[Table("customers")]
[TableOrder(1)]
public class Customer : BaseEntity
{
    [Column("email", MaxLength = 320)]
    [Unique("UQ_customers_email")]
    public string Email { get; set; } = string.Empty;

    [Column("first_name", MaxLength = 100)]
    public string FirstName { get; set; } = string.Empty;

    [Column("last_name", MaxLength = 100)]
    public string LastName { get; set; } = string.Empty;

    [Column("phone", MaxLength = 20)]
    [Index("IX_customers_phone", IsUnique = false, IndexType = IndexType.BTree)]
    public string? Phone { get; set; }

    [Column("shipping_address", TypeName = "text")]
    public string? ShippingAddress { get; set; }

    [Column("billing_address", TypeName = "text")]
    public string? BillingAddress { get; set; }

    [Column("loyalty_points")]
    [DefaultValue("0")]
    [CheckConstraint("loyalty_points >= 0", Name = "CK_customers_loyalty_points")]
    public int LoyaltyPoints { get; set; }

    [Column("tier", MaxLength = 20)]
    [DefaultValue("Bronze")]
    [CheckConstraint("tier IN ('Bronze','Silver','Gold','Platinum')", Name = "CK_customers_tier")]
    [Index("IX_customers_tier")]
    public string Tier { get; set; } = "Bronze";

    [Column("is_verified")]
    [DefaultValue("false")]
    public bool IsVerified { get; set; }

    [Write(false)]
    public string FullName => $"{FirstName} {LastName}";
}

using System.ComponentModel.DataAnnotations;
using Noundry.Tuxedo.Contrib;
using Noundry.Tuxedo.Bowtie.Attributes;

namespace MyApp.Models;

/// <summary>
/// Order entity — TableOrder 2 (references Customer).
/// Demonstrates foreign keys with cascading deletes, check constraints
/// on monetary values and status enums, default values with raw SQL,
/// and B-Tree indexes for query performance.
/// </summary>
[Table("orders")]
[TableOrder(2)]
public class Order : BaseEntity
{
    [Column("customer_id")]
    [ForeignKey("customers", ReferencedColumn = "id", OnDelete = ReferentialAction.Cascade)]
    [Index("IX_orders_customer_id")]
    public Guid CustomerId { get; set; }

    [Column("order_number", MaxLength = 50)]
    [Unique("UQ_orders_order_number")]
    public string OrderNumber { get; set; } = string.Empty;

    [Column("status", MaxLength = 30)]
    [DefaultValue("Pending")]
    [CheckConstraint("status IN ('Pending','Confirmed','Shipped','Delivered','Cancelled','Refunded')", Name = "CK_orders_status")]
    [Index("IX_orders_status")]
    public string Status { get; set; } = "Pending";

    [Column("subtotal", Precision = 18, Scale = 2)]
    [CheckConstraint("subtotal >= 0", Name = "CK_orders_subtotal")]
    public decimal Subtotal { get; set; }

    [Column("tax_amount", Precision = 18, Scale = 2)]
    [DefaultValue("0")]
    [CheckConstraint("tax_amount >= 0", Name = "CK_orders_tax_amount")]
    public decimal TaxAmount { get; set; }

    [Column("discount_amount", Precision = 18, Scale = 2)]
    [DefaultValue("0")]
    [CheckConstraint("discount_amount >= 0", Name = "CK_orders_discount")]
    public decimal DiscountAmount { get; set; }

    [Column("total", Precision = 18, Scale = 2)]
    [CheckConstraint("total >= 0", Name = "CK_orders_total")]
    public decimal Total { get; set; }

    [Column("currency", MaxLength = 3)]
    [DefaultValue("USD")]
    public string Currency { get; set; } = "USD";

    [Column("notes", TypeName = "text")]
    public string? Notes { get; set; }

    [Column("shipped_at")]
    public DateTime? ShippedAt { get; set; }

    [Column("delivered_at")]
    public DateTime? DeliveredAt { get; set; }

    [Write(false)]
    public decimal GrandTotal => Subtotal + TaxAmount - DiscountAmount;
}

using System.ComponentModel.DataAnnotations;
using Noundry.Tuxedo.Contrib;
using Noundry.Tuxedo.Bowtie.Attributes;

namespace MyApp.Models;

/// <summary>
/// OrderItem entity — TableOrder 3 (references Order).
/// Demonstrates composite indexes for preventing duplicate line items,
/// check constraints on quantities and prices, and foreign key
/// relationships with cascade delete.
/// </summary>
[Table("order_items")]
[TableOrder(3)]
public class OrderItem : BaseEntity
{
    [Column("order_id")]
    [ForeignKey("orders", ReferencedColumn = "id", OnDelete = ReferentialAction.Cascade)]
    [Index("IX_order_items_order_id")]
    public Guid OrderId { get; set; }

    [Column("product_sku", MaxLength = 50)]
    [Index(Group = "IX_order_items_order_product", Order = 2)]
    public string ProductSku { get; set; } = string.Empty;

    [Column("product_name", MaxLength = 200)]
    public string ProductName { get; set; } = string.Empty;

    [Column("quantity")]
    [CheckConstraint("quantity > 0", Name = "CK_order_items_quantity")]
    public int Quantity { get; set; }

    [Column("unit_price", Precision = 18, Scale = 2)]
    [CheckConstraint("unit_price >= 0", Name = "CK_order_items_unit_price")]
    public decimal UnitPrice { get; set; }

    [Column("discount_percent", Precision = 5, Scale = 2)]
    [DefaultValue("0")]
    [CheckConstraint("discount_percent >= 0 AND discount_percent <= 100", Name = "CK_order_items_discount_pct")]
    public decimal DiscountPercent { get; set; }

    [Column("line_total", Precision = 18, Scale = 2)]
    [CheckConstraint("line_total >= 0", Name = "CK_order_items_line_total")]
    public decimal LineTotal { get; set; }

    [Write(false)]
    public decimal CalculatedTotal => Quantity * UnitPrice * (1 - DiscountPercent / 100m);
}

using Noundry.Tuxedo.DependencyInjection;
using Noundry.Tuxedo.Bowtie;
using MyApp.Services;

var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection")
    ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");

// -------------------------------------------------------
// 1. Register Tuxedo ORM (provides IDbConnection via DI)
// -------------------------------------------------------
builder.Services.AddTuxedoPostgres(connectionString);

// For SQL Server: builder.Services.AddTuxedoSqlServer(connectionString);
// For SQLite:     builder.Services.AddTuxedoSqlite("app.db");

// -------------------------------------------------------
// 2. Register Bowtie Migrations
//    AutoMigrate = true syncs schema on startup.
//    In production, set to false and use the CLI instead.
// -------------------------------------------------------
builder.Services.AddBowtie(options =>
{
    options.ConnectionString = connectionString;
    options.Provider = DatabaseProvider.Postgres;
    options.AutoMigrate = builder.Environment.IsDevelopment();
});

// -------------------------------------------------------
// 3. Register application services
// -------------------------------------------------------
builder.Services.AddScoped<CustomerService>();
builder.Services.AddScoped<OrderService>();

builder.Services.AddRazorPages();

var app = builder.Build();

app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.MapRazorPages();

app.Run();

using System.Data;
using Noundry.Tuxedo;
using Noundry.Tuxedo.Contrib;
using Noundry.Tuxedo.Exceptions;
using MyApp.Models;

namespace MyApp.Services;

/// <summary>
/// Production-grade service demonstrating the full Tuxedo query API:
/// typed CRUD, raw SQL queries, parameterized commands, pagination,
/// transactions, and strongly-typed exception handling.
/// </summary>
public class CustomerService
{
    private readonly IDbConnection _db;

    public CustomerService(IDbConnection db)
    {
        _db = db;
    }

    // -------------------------------------------------------
    // TYPED CRUD — zero SQL, maximum convenience
    // -------------------------------------------------------

    public async Task<Customer?> GetByIdAsync(Guid id)
    {
        return await _db.GetAsync<Customer>(id);
    }

    public async Task<Guid> CreateAsync(Customer customer)
    {
        var conn = _db.WithExceptionTranslation();

        try
        {
            customer.Id = Guid.NewGuid();
            customer.CreatedAt = DateTime.UtcNow;
            await conn.InsertAsync(customer);
            return customer.Id;
        }
        catch (UniqueConstraintException ex)
            when (ex.ConstraintName == "UQ_customers_email")
        {
            throw new InvalidOperationException(
                $"A customer with email '{customer.Email}' already exists.", ex);
        }
        catch (CheckConstraintException ex)
            when (ex.ConstraintName == "CK_customers_tier")
        {
            throw new ArgumentException(
                $"Invalid tier '{customer.Tier}'. Must be Bronze, Silver, Gold, or Platinum.", ex);
        }
        catch (MaxLengthExceededException ex)
        {
            throw new ArgumentException(
                $"Value for '{ex.ColumnName}' exceeds maximum length of {ex.MaxLength}.", ex);
        }
    }

    public async Task UpdateAsync(Customer customer)
    {
        customer.UpdatedAt = DateTime.UtcNow;
        await _db.UpdateAsync(customer);
    }

    public async Task SoftDeleteAsync(Guid id)
    {
        await _db.ExecuteAsync(
            "UPDATE customers SET is_deleted = true, updated_at = @Now WHERE id = @Id",
            new { Id = id, Now = DateTime.UtcNow });
    }

    // -------------------------------------------------------
    // RAW SQL — full control when you need it
    // -------------------------------------------------------

    public async Task<IEnumerable<Customer>> SearchAsync(
        string? term, string? tier, int page = 1, int pageSize = 25)
    {
        var sql = @"
            SELECT * FROM customers
            WHERE is_deleted = false
              AND (@Term IS NULL OR first_name ILIKE @Pattern
                   OR last_name ILIKE @Pattern OR email ILIKE @Pattern)
              AND (@Tier IS NULL OR tier = @Tier)
            ORDER BY created_at DESC
            LIMIT @Limit OFFSET @Offset";

        return await _db.QueryAsync<Customer>(sql, new
        {
            Term = term,
            Pattern = $"%{term}%",
            Tier = tier,
            Limit = pageSize,
            Offset = (page - 1) * pageSize
        });
    }

    public async Task<int> GetTotalCountAsync(string? tier = null)
    {
        var sql = @"
            SELECT COUNT(*) FROM customers
            WHERE is_deleted = false
              AND (@Tier IS NULL OR tier = @Tier)";

        return await _db.ExecuteScalarAsync<int>(sql, new { Tier = tier });
    }

    public async Task<IEnumerable<CustomerOrderSummary>> GetTopCustomersAsync(int top = 10)
    {
        var sql = @"
            SELECT
                c.id          AS CustomerId,
                c.email       AS Email,
                c.first_name  AS FirstName,
                c.last_name   AS LastName,
                c.tier        AS Tier,
                COUNT(o.id)   AS TotalOrders,
                COALESCE(SUM(o.total), 0) AS TotalSpent
            FROM customers c
            LEFT JOIN orders o ON o.customer_id = c.id
                AND o.is_deleted = false
            WHERE c.is_deleted = false
            GROUP BY c.id, c.email, c.first_name, c.last_name, c.tier
            ORDER BY TotalSpent DESC
            LIMIT @Top";

        return await _db.QueryAsync<CustomerOrderSummary>(sql, new { Top = top });
    }

    // -------------------------------------------------------
    // TRANSACTIONS — atomic multi-table operations
    // -------------------------------------------------------

    public async Task UpgradeTierAsync(
        Guid customerId, string newTier, int bonusPoints)
    {
        _db.Open();
        using var transaction = _db.BeginTransaction();

        try
        {
            var conn = _db.WithExceptionTranslation();

            await conn.ExecuteAsync(
                @"UPDATE customers
                  SET tier = @Tier,
                      loyalty_points = loyalty_points + @Bonus,
                      updated_at = @Now
                  WHERE id = @Id AND is_deleted = false",
                new
                {
                    Id = customerId,
                    Tier = newTier,
                    Bonus = bonusPoints,
                    Now = DateTime.UtcNow
                },
                transaction);

            await conn.ExecuteAsync(
                @"INSERT INTO audit_log
                      (id, entity_type, entity_id, action, details, created_at)
                  VALUES
                      (@Id, 'Customer', @EntityId, 'TierUpgrade', @Details, @Now)",
                new
                {
                    Id = Guid.NewGuid(),
                    EntityId = customerId,
                    Details = $"Upgraded to {newTier}, +{bonusPoints} points",
                    Now = DateTime.UtcNow
                },
                transaction);

            transaction.Commit();
        }
        catch (CheckConstraintException ex)
            when (ex.ConstraintName == "CK_customers_tier")
        {
            transaction.Rollback();
            throw new ArgumentException($"Invalid tier: {newTier}", ex);
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }

    // -------------------------------------------------------
    // BULK OPERATIONS — efficient batch processing
    // -------------------------------------------------------

    public async Task<int> DeactivateInactiveCustomersAsync(
        int inactiveDays = 365)
    {
        var sql = @"
            UPDATE customers
            SET is_deleted = true, updated_at = @Now
            WHERE is_deleted = false
              AND id NOT IN (
                  SELECT DISTINCT customer_id FROM orders
                  WHERE created_at > @CutoffDate
                    AND is_deleted = false
              )";

        return await _db.ExecuteAsync(sql, new
        {
            Now = DateTime.UtcNow,
            CutoffDate = DateTime.UtcNow.AddDays(-inactiveDays)
        });
    }
}

/// <summary>
/// Projection DTO for the top-customers report query.
/// </summary>
public class CustomerOrderSummary
{
    public Guid CustomerId { get; set; }
    public string Email { get; set; } = string.Empty;
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Tier { get; set; } = string.Empty;
    public int TotalOrders { get; set; }
    public decimal TotalSpent { get; set; }
}

# -------------------------------------------------------
# Install the Bowtie CLI globally
# -------------------------------------------------------
dotnet tool install -g Noundry.Tuxedo.Bowtie.CLI

# -------------------------------------------------------
# DRY RUN — Preview what Bowtie will do without touching the DB.
# Reads your entity attributes and compares to the live schema.
# -------------------------------------------------------
bowtie sync \
  --assembly ./bin/Release/net10.0/MyApp.dll \
  --connection-string "Host=localhost;Database=myapp;Username=postgres;Password=secret" \
  --provider PostgreSQL \
  --dry-run

# Output:
# [Bowtie] Detected 3 entities: Customer, Order, OrderItem
# [Bowtie] CREATE TABLE customers (id uuid, email varchar(320), ...)
# [Bowtie] CREATE TABLE orders (id uuid, customer_id uuid, ...)
# [Bowtie] CREATE TABLE order_items (id uuid, order_id uuid, ...)
# [Bowtie] CREATE INDEX IX_customers_phone ON customers (phone)
# [Bowtie] CREATE UNIQUE INDEX UQ_customers_email ON customers (email)
# [Bowtie] ADD CONSTRAINT CK_customers_tier CHECK (...)
# [Bowtie] Dry run complete. No changes applied.

# -------------------------------------------------------
# SYNC — Apply migrations to the database.
# Bowtie respects [TableOrder] to handle FK dependency order.
# -------------------------------------------------------
bowtie sync \
  --assembly ./bin/Release/net10.0/MyApp.dll \
  --connection-string "Host=localhost;Database=myapp;Username=postgres;Password=secret" \
  --provider PostgreSQL

# -------------------------------------------------------
# GENERATE — Export the full schema as a SQL file.
# Perfect for code reviews, docs, or seeding CI databases.
# -------------------------------------------------------
bowtie generate \
  --assembly ./bin/Release/net10.0/MyApp.dll \
  --provider PostgreSQL \
  --output schema.sql

# -------------------------------------------------------
# VALIDATE — Check entity attributes for errors
# without connecting to any database. Great for CI pipelines.
# -------------------------------------------------------
bowtie validate \
  --assembly ./bin/Release/net10.0/MyApp.dll \
  --provider PostgreSQL

# Output:
# [Bowtie] Scanning assembly: MyApp.dll
# [Bowtie] Found 3 entities with [Table] attribute
# [Bowtie] Validated [TableOrder] chain: customers -> orders -> order_items
# [Bowtie] All foreign key references resolved
# [Bowtie] All check constraints syntactically valid
# [Bowtie] Validation passed with 0 errors, 0 warnings

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret;Include Error Detail=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Noundry.Tuxedo": "Debug"
    }
  }
}