Database.md

Database

This document covers Entity Framework Core configuration, migrations, seeding, and database schema.

Overview

OpenCashFlow uses PostgreSQL 16 with Entity Framework Core 9.0 as the ORM.

Database provider: Npgsql.EntityFrameworkCore.PostgreSQL

Connection String

Host=localhost;Database=opencashflow_db;Username=opencashflow;Password=your_password;Port=5432

Production additions:

SSL Mode=Require;Trust Server Certificate=true;Pooling=true;Minimum Pool Size=5;Maximum Pool Size=100

ApplicationDbContext

Location: src/OpenCashFlow.Shared/Data/ApplicationDbContext.cs

DbSet Definitions

public class ApplicationDbContext : DbContext
{
    // Identity
    public DbSet<AspNetUser> AspNetUser_DS { get; set; }
    public DbSet<AspNetRole> AspNetRole_DS { get; set; }
    public DbSet<AspNetUserRole> AspNetUserRole_DS { get; set; }
    public DbSet<AspNetUserClaim> AspNetUserClaim_DS { get; set; }
    public DbSet<AspNetUserLogin> AspNetUserLogin_DS { get; set; }
    public DbSet<AspNetUserToken> AspNetUserToken_DS { get; set; }

    // Companies
    public DbSet<Company> Company_DS { get; set; }
    public DbSet<Company_Address> Company_Address_DS { get; set; }
    public DbSet<Company_Invoice> Company_Invoice_DS { get; set; }
    public DbSet<Company_Staff> Company_Staff_DS { get; set; }

    // Payments
    public DbSet<Payment> Payment_DS { get; set; }
    public DbSet<Payment_Method_LookUps> Payment_Method_LookUps_DS { get; set; }
    public DbSet<Payment_DocumentType_LookUp> Payment_DocumentType_LookUp_DS { get; set; }
    public DbSet<Payment_DailyPayments> Payment_DailyPayments_DS { get; set; }

    // Billing
    public DbSet<Plan> Plan_DS { get; set; }
    public DbSet<Company_Subscription> Company_Subscription_DS { get; set; }
    public DbSet<Company_Renewal> Company_Renewal_DS { get; set; }
    public DbSet<Stripe_Webhook_Event> Stripe_Webhook_Event_DS { get; set; }

    // Cash Management
    public DbSet<CashBalance> CashBalance_DS { get; set; }
    public DbSet<CashLedger> CashLedger_DS { get; set; }

    // Admin
    public DbSet<Admin_AuditLog> Admin_AuditLog_DS { get; set; }
}

Entity Definitions

Core Entities

AspNetUser

public class AspNetUser
{
    public Guid UserID { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public string? PhoneNumber { get; set; }
    public string? PhoneNumberPrefix { get; set; }

    // Personal information
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? Gender { get; set; }
    public DateTime? DoB { get; set; }
    public string? Nationality { get; set; }

    // Authentication
    public string PasswordHash { get; set; }
    public string PasswordSalt { get; set; }
    public string? SecurityStamp { get; set; }

    // MFA
    public bool TwoFactorEnabled { get; set; }
    public bool LockoutEnabled { get; set; }
    public DateTime? LockoutEnd { get; set; }
    public int AccessFailedCount { get; set; }

    // Preferences
    public string? UserAvatar { get; set; }
    public string? Language { get; set; }
    public string? Country { get; set; }
    public string? Timezone { get; set; }

    // Permissions
    public string? AssignedPermissions { get; set; }
    public string? DeniedPermissions { get; set; }

    // Audit
    public DateTime DateIns { get; set; }
    public DateTime? DateEdit { get; set; }
    public bool IsDeleted { get; set; }
}

Company

public class Company
{
    public Guid TenantID { get; set; }
    public string CompanyName { get; set; }
    public int MaxUsers { get; set; }
    public string? Avatar { get; set; }
    public string? BusinessCategory { get; set; }

    // Stripe integration
    public string? StripeCustomerID { get; set; }
    public string? StripeDefaultPaymentMethodID { get; set; }
    public string? BillingEmail { get; set; }

    // Billing information
    public string? VAT { get; set; }
    public string? IBAN { get; set; }
    public string? BIC { get; set; }
    public DateTime? StartingContract { get; set; }
    public DateTime? EndingContract { get; set; }

    // Audit fields
    public string CreatedBy { get; set; }
    public DateTime DateIns { get; set; }
    public string? EditedBy { get; set; }
    public DateTime? DateEdit { get; set; }
    public bool IsDeleted { get; set; }
    public string? IsDeletedBy { get; set; }
    public string? IsDeletedWhy { get; set; }
    public DateTime? DateDeleted { get; set; }
}

Payment

public class Payment
{
    public Guid PaymentID { get; set; }
    public Guid TenantID { get; set; }
    public Guid? RequestId { get; set; }  // Idempotency key

    public decimal Amount { get; set; }
    public string EntryType { get; set; }  // "Income" or "Outcome"
    public string? Description { get; set; }
    public DateTime PaymentDate { get; set; }

    // Foreign keys
    public int PaymentMethodID { get; set; }
    public int DocumentTypeID { get; set; }
    public Guid UserID { get; set; }

    // Navigation properties
    public Payment_Method_LookUps PaymentMethod { get; set; }
    public Payment_DocumentType_LookUp DocumentType { get; set; }

    // Audit fields
    public string CreatedBy { get; set; }
    public DateTime DateIns { get; set; }
    public string? EditedBy { get; set; }
    public DateTime? DateEdit { get; set; }
    public bool IsDeleted { get; set; }
}

Lookup Tables

Payment_Method_LookUps

public class Payment_Method_LookUps
{
    public int PaymentMethodID { get; set; }
    public string PaymentMethodName { get; set; }
    public string? Description { get; set; }
    public bool IsActive { get; set; }
}

Payment_DocumentType_LookUp

public class Payment_DocumentType_LookUp
{
    public int DocumentTypeID { get; set; }
    public string DocumentTypeName { get; set; }
    public string? Description { get; set; }
    public bool IsActive { get; set; }
}

Cash Management

CashBalance

public class CashBalance
{
    public Guid CompanyId { get; set; }
    public decimal Balance { get; set; }
    public DateTime LastUpdated { get; set; }

    // Optimistic concurrency
    public uint xmin { get; set; }
}

CashLedger

public class CashLedger
{
    public Guid Id { get; set; }
    public Guid CompanyId { get; set; }
    public decimal Delta { get; set; }
    public string RefType { get; set; }
    public Guid RefId { get; set; }
    public DateTime CreatedAt { get; set; }
}

Migrations

Location

Migrations are stored in: src/OpenCashFlow.Shared/Data/Migrations/

Creating a Migration

Using the helper script (recommended):

./scripts/create-migration.sh AddPaymentIndex

Using dotnet CLI directly:

dotnet ef migrations add AddPaymentIndex \
  --project src/OpenCashFlow.Shared/OpenCashFlow.Shared.csproj \
  --startup-project src/OpenCashFlow.API/OpenCashFlow.API.csproj \
  --context ApplicationDbContext \
  --output-dir Data/Migrations

Applying Migrations

Using the helper script:

./scripts/create-migration.sh AddPaymentIndex --apply

Using dotnet CLI:

dotnet ef database update \
  --project src/OpenCashFlow.Shared/OpenCashFlow.Shared.csproj \
  --startup-project src/OpenCashFlow.API/OpenCashFlow.API.csproj \
  --context ApplicationDbContext

Removing the Last Migration

./scripts/create-migration.sh --remove

Or:

dotnet ef migrations remove \
  --project src/OpenCashFlow.Shared/OpenCashFlow.Shared.csproj \
  --startup-project src/OpenCashFlow.API/OpenCashFlow.API.csproj \
  --context ApplicationDbContext

Migration Naming Convention

Use descriptive names that indicate the change:

• InitialCreate - Initial schema
• AddPaymentIndex - Adding an index
• AddCompanyAvatar - Adding a column
• RenameUserEmailToContactEmail - Renaming a column
• CreateCashLedgerTable - Creating a new table

Seeding

Initial Seed Data

The InitialCreate migration includes seed data for:

Roles

migrationBuilder.InsertData(
    table: "AspNetRoles",
    columns: new[] { "RoleID", "RoleName", "NormalizedName" },
    values: new object[,]
    {
        { Guid.Parse("00000000-0000-0000-0000-000000000001"), "Administrator", "ADMINISTRATOR" },
        { Guid.Parse("00000000-0000-0000-0000-000000000002"), "Employee", "EMPLOYEE" },
        { Guid.Parse("00000000-9999-9999-9999-000000000009"), "GIManagers", "GIMANAGERS" }
    });

Payment Methods

migrationBuilder.InsertData(
    table: "Payment_Method_LookUps",
    columns: new[] { "PaymentMethodID", "PaymentMethodName", "IsActive" },
    values: new object[,]
    {
        { 1, "Cash", true },
        { 2, "Bank Transfer", true },
        { 3, "Credit Card", true },
        { 4, "PayPal", true }
    });

Document Types

migrationBuilder.InsertData(
    table: "Payment_DocumentType_LookUp",
    columns: new[] { "DocumentTypeID", "DocumentTypeName", "IsActive" },
    values: new object[,]
    {
        { 1, "Invoice", true },
        { 2, "Receipt", true },
        { 3, "Credit Note", true },
        { 4, "Other", true }
    });

Demo Data

For development, a demo user and company are seeded:

// Demo Company
migrationBuilder.InsertData(
    table: "Companies",
    columns: new[] { "TenantID", "CompanyName", "MaxUsers", "DateIns", "CreatedBy" },
    values: new object[]
    {
        Guid.Parse("11111111-1111-1111-1111-111111111111"),
        "Demo Company",
        10,
        DateTime.UtcNow,
        "SYSTEM"
    });

// Demo User (password: DemoPassword123!)
migrationBuilder.InsertData(
    table: "AspNetUsers",
    columns: new[] { "UserID", "Email", "UserName", "PasswordHash", "PasswordSalt", ... },
    values: new object[] { ... });

Indexes

Performance Indexes

// Payment queries by tenant and date
modelBuilder.Entity<Payment>()
    .HasIndex(p => new { p.TenantID, p.PaymentDate })
    .HasDatabaseName("IX_Payment_TenantID_PaymentDate");

// Soft delete filtering
modelBuilder.Entity<Payment>()
    .HasIndex(p => p.IsDeleted)
    .HasDatabaseName("IX_Payment_IsDeleted");

// User lookup by email
modelBuilder.Entity<AspNetUser>()
    .HasIndex(u => u.Email)
    .IsUnique()
    .HasDatabaseName("IX_AspNetUser_Email");

Unique Constraints

// Stripe customer ID must be unique
modelBuilder.Entity<Company>()
    .HasIndex(c => c.StripeCustomerID)
    .IsUnique()
    .HasFilter("\"StripeCustomerID\" IS NOT NULL")
    .HasDatabaseName("IX_Company_StripeCustomerID");

// Cash ledger entry uniqueness
modelBuilder.Entity<CashLedger>()
    .HasIndex(l => new { l.CompanyId, l.RefType, l.RefId })
    .IsUnique()
    .HasDatabaseName("IX_CashLedger_Company_Ref");

Concurrency Control

Optimistic Concurrency

The CashBalance table uses PostgreSQL's xmin system column for optimistic concurrency:

modelBuilder.Entity<CashBalance>()
    .Property(e => e.xmin)
    .IsRowVersion();

Usage:

var balance = await context.CashBalance_DS.FindAsync(companyId);
balance.Balance += amount;

try
{
    await context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
    // Handle concurrent update conflict
}

Query Patterns

Filtered Queries (Multi-Tenant)

public async Task<IEnumerable<Payment>> GetPaymentsAsync(Guid tenantId)
{
    return await _context.Payment_DS
        .Where(p => p.TenantID == tenantId && !p.IsDeleted)
        .Include(p => p.PaymentMethod)
        .OrderByDescending(p => p.PaymentDate)
        .ToListAsync();
}

Global Query Filters

Consider adding global query filters for soft deletes:

modelBuilder.Entity<Payment>()
    .HasQueryFilter(p => !p.IsDeleted);

modelBuilder.Entity<Company>()
    .HasQueryFilter(c => !c.IsDeleted);

Pagination

public async Task<PagedResult<Payment>> GetPaymentsPagedAsync(
    Guid tenantId, int page, int pageSize)
{
    var query = _context.Payment_DS
        .Where(p => p.TenantID == tenantId && !p.IsDeleted);

    var total = await query.CountAsync();

    var items = await query
        .OrderByDescending(p => p.PaymentDate)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<Payment>
    {
        Items = items,
        TotalCount = total,
        Page = page,
        PageSize = pageSize
    };
}

Database Schema Diagram

┌──────────────────┐       ┌──────────────────┐
│   AspNetUsers    │       │   AspNetRoles    │
├──────────────────┤       ├──────────────────┤
│ UserID (PK)      │       │ RoleID (PK)      │
│ Email            │       │ RoleName         │
│ PasswordHash     │       └────────┬─────────┘
│ ...              │                │
└────────┬─────────┘                │
         │                          │
         │    ┌─────────────────────┘
         │    │
         ▼    ▼
┌──────────────────┐
│  AspNetUserRoles │
├──────────────────┤
│ UserID (FK)      │
│ RoleID (FK)      │
└──────────────────┘

┌──────────────────┐       ┌──────────────────┐
│    Companies     │       │  Company_Staff   │
├──────────────────┤       ├──────────────────┤
│ TenantID (PK)    │◄──────│ TenantID (FK)    │
│ CompanyName      │       │ UserID (FK)      │
│ StripeCustomerID │       └──────────────────┘
│ ...              │
└────────┬─────────┘
         │
         │ TenantID
         ▼
┌──────────────────┐
│     Payments     │
├──────────────────┤
│ PaymentID (PK)   │
│ TenantID (FK)    │───────┐
│ Amount           │       │
│ EntryType        │       │
│ PaymentMethodID  │───────┼──► Payment_Method_LookUps
│ DocumentTypeID   │───────┼──► Payment_DocumentType_LookUp
│ UserID (FK)      │───────┘
└──────────────────┘

Backup and Recovery

See Docs/Backup.md and Docs/database-recovery-and-connection-guide.md for backup procedures and recovery guidance.