DAY 2 — Database & Entities Design (PostgreSQL + EF Core)

So, we’ll set up Entity Framework Core + PostgreSQL in your AccountingSuite clean architecture.
This will be your 📅 Day 2 Plan — Database & Entities Design (with PostgreSQL).

We’ll go step-by-step, very beginner-friendly — just like company-level setup but explained clearly.

🎯 Goal

✅ Connect your API to PostgreSQL
✅ Create your AppDbContext
✅ Add Entities (Company, Customer, Vendor, Item, Invoice, Payment, Role, User, Audit)
✅ Run your first EF Core Migration and verify the database

---

⚙️ Step 1 — Install EF Core Packages

Go to your Accounting.Infrastructure project folder (important 👇):

cd src/Accounting.Infrastructure

Then run these commands one by one 👇

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

✅ Why:
These are needed to connect C# → PostgreSQL using EF Core ORM.

---

⚙️ Step 2 — Create “Data” Folder and AppDbContext.cs

Inside
📁 src/Accounting.Infrastructure/Data/

create file: AppDbContext.cs

Paste this code 👇

using Accounting.Domain.Entities;
using Microsoft.EntityFrameworkCore;

using Accounting.Core.Entities;
using Microsoft.EntityFrameworkCore;

namespace Accounting.Infrastructure.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

        public DbSet<Company> Companies => Set<Company>();
        public DbSet<User> Users => Set<User>();
        public DbSet<Role> Roles => Set<Role>();
        public DbSet<Customer> Customers => Set<Customer>();
        public DbSet<Vendor> Vendors => Set<Vendor>();
        public DbSet<Item> Items => Set<Item>();
        public DbSet<Invoice> Invoices => Set<Invoice>();
        public DbSet<InvoiceItem> InvoiceItems => Set<InvoiceItem>();
        public DbSet<Ledger> Ledgers => Set<Ledger>();
        public DbSet<Expense> Expenses => Set<Expense>();
        public DbSet<Payment> Payments => Set<Payment>();
        public DbSet<Audit> Audits => Set<Audit>();

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            // optional: add constraints / indexes later
        }
    }
}

🪜 STEP 4 – Register DbContext in Program.cs

In Program.cs (inside Accounting.API):

using Accounting.Infrastructure.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();
app.UseSwagger();
app.UseSwaggerUI();
app.MapControllers();
app.Run();

🪜 STEP 5 – Create Entities

Create a new folder in Accounting.Core/Entities →
add these entity files:

Company.cs

Role.cs

User.cs

Customer.cs

Vendor.cs

Item.cs

Invoice.cs

InvoiceItem.cs

Ledger.cs

Expense.cs

Payment.cs

Audit.cs

To keep it easy, I’ll give you Company.cs, Invoice.cs, and InvoiceItem.cs first.

✅ Meaning:
This class tells EF Core that each DbSet = table in PostgreSQL.

---

⚙️ Step 3 — Add Entities (One by One)

Go to src/Accounting.Domain/Entities/
and create these simple model files 👇

---

🧾 Company.cs

namespace Accounting.Core.Entities
{
    public class Company
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Address { get; set; } = string.Empty;
        public string GSTNumber { get; set; } = string.Empty;
        public DateTime CreatedAtUtc { get; set; } = DateTime.UtcNow;
    }
}

---

👤 Customer.cs

namespace Accounting.Core.Entities
{
    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;
        public string Mobile { get; set; } = string.Empty;
        public string Address { get; set; } = string.Empty;
        public DateTime CreatedAtUtc { get; set; } = DateTime.UtcNow;
    }
}

---

🧾 Vendor.cs

namespace Accounting.Core.Entities
{
    public class Vendor
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;
        public string Mobile { get; set; } = string.Empty;
        public string Address { get; set; } = string.Empty;
    }
}

---

📦 Item.cs

namespace Accounting.Core.Entities
{
    public class Item
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Type { get; set; } = string.Empty;
        public decimal Price { get; set; }
        public int Stock { get; set; }
    }
}

---

🧾 Invoice.cs

namespace Accounting.Core.Entities
{
    public class Invoice
    {
        public int Id { get; set; }
        public int CompanyId { get; set; }
        public int CustomerId { get; set; }
        public DateTime InvoiceDate { get; set; } = DateTime.UtcNow;
        public decimal TotalAmount { get; set; }

        // navigation
        public Company? Company { get; set; }
        public Customer? Customer { get; set; }
    }
}

---

💰 Payment.cs

namespace Accounting.Core.Entities
{
    public class Payment
    {
        public int Id { get; set; }
        public string Type { get; set; } = string.Empty; // "Incoming" or "Outgoing"
        public string Mode { get; set; } = string.Empty; // "Cash", "Bank", "UPI"
        public decimal Amount { get; set; }
        public DateTime PaymentDate { get; set; } = DateTime.UtcNow;
        public string? ReferenceNo { get; set; }

        // optional links
        public int? CustomerId { get; set; }
        public Customer? Customer { get; set; }

        public int? VendorId { get; set; }
        public Vendor? Vendor { get; set; }

        public int? InvoiceId { get; set; }
        public Invoice? Invoice { get; set; }

        // ledger entry
        public int? LedgerId { get; set; }
        public Ledger? Ledger { get; set; }
    }
}

🧾 InvoiceItem.cs

namespace Accounting.Core.Entities
{
    public class InvoiceItem
    {
        public int Id { get; set; }
        public int InvoiceId { get; set; }
        public int ItemId { get; set; }
        public int Quantity { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal Total => Quantity * UnitPrice;

        // Relationships
        public Invoice? Invoice { get; set; }
        public Item? Item { get; set; }
    }
}

---

🧑‍💼 Role.cs

namespace Accounting.Core.Entities
{
    public class Role
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;

        // navigation
        public ICollection<User>? Users { get; set; }
    }
}

---

👨‍💼 User.cs

namespace Accounting.Core.Entities
{
    public class User
    {
        public int Id { get; set; }
        public string FullName { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;
        public string PasswordHash { get; set; } = string.Empty;

        // foreign key + navigation
        public int RoleId { get; set; }
        public Role? Role { get; set; }

        public DateTime CreatedAtUtc { get; set; } = DateTime.UtcNow;
    }
}

---

🧾 AuditLog.cs

namespace Accounting.Core.Entities
{
    public class Audit
    {
        public int Id { get; set; }
        public string TableName { get; set; } = string.Empty;
        public string ActionType { get; set; } = string.Empty; // e.g., "CREATE", "UPDATE", "DELETE"
        public string OldValue { get; set; } = string.Empty;
        public string NewValue { get; set; } = string.Empty;
        public string PerformedBy { get; set; } = "System";
        public DateTime PerformedAtUtc { get; set; } = DateTime.UtcNow;
    }
}

✅ Now you have 9 entity classes in your Domain layer.

---

⚙️ Step 4 — Configure EF Core in Program.cs

Open:
src/Accounting.API/Program.cs

Add top:

using Accounting.Infrastructure.Data;
using Microsoft.EntityFrameworkCore;

Then before builder.Build(), add this:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

---

⚙️ Step 5 — Add Connection String

Open src/Accounting.API/appsettings.json and add:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=accountingdb;Username=postgres;Password=yourpassword"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

✅ Replace yourpassword with your actual PostgreSQL password.

---

⚙️ Step 6 — Add EF Tools (for migrations)

Install EF Core CLI Tools (only once):

dotnet tool install --global dotnet-ef

Then go to Accounting.API folder:

cd src/Accounting.API
dotnet ef migrations add InitialCreate --startup-project ../Accounting.API --project ../Accounting.Infrastructure
dotnet ef database update --startup-project ../Accounting.API --project ../Accounting.Infrastructure

✅ Expected Result:

• “Migration created successfully”
• “Database update successful”
• Database accountingdb visible in pgAdmin with 9 tables

---

⚙️ Step 7 — Verify Database in pgAdmin

Open pgAdmin → accountingdb → Tables.
You should see:

Companies
Customers
Vendors
Items
Invoices
Payments
Roles
Users
AuditLogs

✅ Done! Your database and entities are ready. 🎉

---

✅ End of Day 2 Checklist

Task	Done
EF Core packages installed	✅
AppDbContext created	✅
Entities added	✅
Connection string configured	✅
Migration + Database created	✅
Tables visible in pgAdmin	✅

---

🎯 End of Day 2 Result:

You now have a fully connected PostgreSQL + EF Core database for your Accounting Suite.
From Day 3, we’ll start building Repository Pattern + Role/User seeding + Authentication.

---

Would you like me to prepare Day 3 (Repository + Role/User Seeding + Auth Setup) next in the same beginner-friendly style?