Database¶

{
  "personalInfo": {
    "firstName": "", "lastName": "", "email": "",
    "phone": "", "location": "", "linkedIn": "", "website": ""
  },
  "summary": "",
  "experience": [
    { "id": "", "company": "", "position": "", "startDate": "",
      "endDate": "", "current": false, "description": "" }
  ],
  "projects": [
    { "id": "", "name": "", "description": "",
      "technologies": [], "link": "" }
  ],
  "skills": [{ "id": "", "name": "", "level": "" }],
  "education": [
    { "id": "", "institution": "", "degree": "",
      "field": "", "startDate": "", "endDate": "" }
  ],
  "certifications": [
    { "id": "", "name": "", "issuer": "", "date": "", "link": "" }
  ],
  "languages": [{ "id": "", "name": "", "level": "" }]
}

# Create tools manifest file
dotnet new tool-manifest

# Install tools
dotnet tool restore

# Questions API
dotnet ef migrations add <MigrationName> --project Questions.Api --context ApplicationDbContext --output-dir Infrastructure/Migrations

# Organizer API
dotnet ef migrations add <MigrationName> --project Organizer.Api --context ApplicationDbContext --output-dir Infrastructure/Migrations

# Portfolio API
dotnet ef migrations add <MigrationName> --project Portfolio.Api --context ApplicationDbContext --output-dir Infrastructure/Migrations

# Users API
dotnet ef migrations add <MigrationName> --project Users.Api --context ApplicationDbContext --output-dir Migrations

cd Questions.Api && dotnet ef database update
cd Organizer.Api && dotnet ef database update
cd Portfolio.Api && dotnet ef database update
cd Users.Api && dotnet ef database update

dotnet ef migrations remove

// Category with hierarchy
public class CategoryEntity : BaseEntity
{
    public required string Name { get; init; }
    public string? Description { get; init; }
    public string? IconName { get; init; }
    public int Order { get; init; }
    public Guid? ParentCategoryId { get; init; }
    public ICollection<CategoryEntity> ChildCategories { get; init; } = [];
    public ICollection<QuestionEntity> Questions { get; init; } = [];
}

// Question with voting and learning
public class QuestionEntity : AuditEntity
{
    public required string Name { get; set; }
    public string? Description { get; set; }
    public required Guid CategoryId { get; init; }
    public int Order { get; set; }
    public QuestionStatus Status { get; set; } = QuestionStatus.Approved;
    public int PromoteCount { get; set; }
    public ICollection<QuestionVoteEntity> Votes { get; init; } = [];
}

// Vote tracking (one per user per question)
public class QuestionVoteEntity : BaseEntity
{
    public required Guid QuestionId { get; init; }
    public required Guid UserId { get; init; }
    public DateTimeOffset VotedOn { get; init; }
}

// Learning list
public class LearningQuestionEntity : BaseEntity
{
    public required Guid QuestionId { get; init; }
    public required Guid UserId { get; init; }
    public DateTimeOffset AddedOn { get; init; }
}

// Job offer
public class OfferEntity : AuditEntity
{
    public required string Name { get; set; }
    public string? Link { get; set; }
    public required string Company { get; set; }
    public required string City { get; set; }
    public required OfferStatus Status { get; set; } = OfferStatus.New;
    public IList<string> Reasons { get; set; } = [];
    public string? Description { get; set; }
    public OfferSource? Source { get; set; }
    public WorkMode? WorkMode { get; set; }
    public string? Intermediary { get; set; }
    public decimal? SalaryMin { get; set; }
    public decimal? SalaryMax { get; set; }
    public SalaryCurrency? SalaryCurrency { get; set; }
    public SalaryType? SalaryType { get; set; }
    public int Order { get; set; }
    public bool IsAiGenerated { get; set; }
    public string? RawContent { get; set; }
    public string? SourceUrl { get; set; }
    public ICollection<InterviewEntity> Interviews { get; init; } = [];
}

// Interview
public class InterviewEntity : AuditEntity
{
    public required Guid OfferId { get; init; }
    public OfferEntity Offer { get; init; } = null!;
    public required InterviewStage Stage { get; set; }
    public required InterviewStatus Status { get; set; } = InterviewStatus.Scheduled;
    public DateTimeOffset? ScheduledAt { get; set; }
    public int? Duration { get; set; }
    public string? Notes { get; set; }
    public string? Location { get; set; }
}

// Reference data
public class KeyValueListEntity
{
    public required DictionaryKey Key { get; init; }
    public IList<string> Values { get; init; } = [];
}

// CV portfolio (one or more variants per user)
public class PortfolioEntity : AuditEntity
{
    public required string Name { get; set; }
    public required PortfolioData Data { get; set; }
}

// Stored as JSONB — not a separate table
public class PortfolioData
{
    public PortfolioPersonalInfo PersonalInfo { get; set; } = new();
    public string Summary { get; set; } = "";
    public List<PortfolioExperience> Experience { get; set; } = [];
    public List<PortfolioProject> Projects { get; set; } = [];
    public List<PortfolioSkill> Skills { get; set; } = [];
    public List<PortfolioEducation> Education { get; set; } = [];
    public List<PortfolioCertification> Certifications { get; set; } = [];
    public List<PortfolioLanguage> Languages { get; set; } = [];
}

// User profile (one per user)
public class UserProfileEntity : AuditEntity
{
    public required string DisplayName { get; set; }
    public string? Bio { get; set; }
    public string? PreferredRole { get; set; }
    public int? ExperienceYears { get; set; }
    public string? Location { get; set; }
    public string? Skills { get; set; }
    public string? LinkedInUrl { get; set; }
    public string? GitHubUrl { get; set; }
    public string? WebsiteUrl { get; set; }
}

public enum QuestionStatus
{
    Approved = 0,
    Pending = 1,
    Rejected = 2
}

public enum OfferStatus { New, Considered, Sent, Rejected }
public enum OfferSource { LinkedIn, NoFluffJobs, JustJoinIT, Pracuj, Email, Direct, Other }
public enum WorkMode { Remote, Hybrid, Onsite }
public enum SalaryCurrency { PLN, EUR, USD }
public enum SalaryType { Monthly, Hourly, Annual }
public enum InterviewStage { Screening, Technical, HR, Offer, Onboarding }
public enum InterviewStatus { Scheduled, Completed, Failed, Cancelled }
public enum DictionaryKey { Cities, Positions, Reasons }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasPostgresEnum<OfferStatus>();
    modelBuilder.HasPostgresEnum<OfferSource>();
    modelBuilder.HasPostgresEnum<WorkMode>();
    modelBuilder.HasPostgresEnum<SalaryCurrency>();
    modelBuilder.HasPostgresEnum<SalaryType>();
    modelBuilder.HasPostgresEnum<InterviewStage>();
    modelBuilder.HasPostgresEnum<InterviewStatus>();
    modelBuilder.HasPostgresEnum<DictionaryKey>();
}

modelBuilder.Entity<KeyValueListEntity>().HasData(
    new { Key = DictionaryKey.Positions, Values = new[] {
        "Software Developer", "Frontend Developer", "Backend Developer",
        "Fullstack Developer", "Junior Software Developer", "Junior Frontend Developer",
        "Junior Backend Developer", "Junior Fullstack Developer", "Mid Software Developer",
        "Mid Frontend Developer", "Mid Backend Developer", "Mid Fullstack Developer",
        "Senior Software Developer", "Senior Frontend Developer", "Senior Backend Developer",
        "Senior Fullstack Developer", "Lead Software Developer", "Lead Frontend Developer",
        "Lead Backend Developer", "Lead Fullstack Developer", "Software Architect",
        "Frontend Architect", "Backend Architect", "DevOps"
    } },
    new { Key = DictionaryKey.Cities, Values = new[] {
        "Warszawa", "Kraków", "Wrocław", "Łódź", "Poznań", "Gdańsk",
        "Gdynia", "Sopot", "Trójmiasto", "Szczecin", "Lublin", "Bydgoszcz",
        "Białystok", "Katowice", "Śląsk", "Remote", "Inne"
    } },
    new { Key = DictionaryKey.Reasons, Values = new[] {
        "Stos technologiczny", "Stawka", "Forma współpracy UoP",
        "Forma współpracy B2B", "Praca zdalna", "Praca hybrydowa",
        "Praca z biura", "Język obcy", "Język ojczysty",
        "Zakres obowiązków", "Lokalizacja", "Godziny pracy",
        "Opieka medyczna", "Multisport", "Wyjazdy służbowe",
        "Integracje", "Biuro", "Inne"
    } }
);

// Always AsNoTracking for reads
var questions = await db.Questions
    .AsNoTracking()
    .Where(q => q.CategoryId == categoryId)
    .ToListAsync(ct);

// Project to DTOs in Select
var response = await db.Questions
    .AsNoTracking()
    .Where(q => q.Status != QuestionStatus.Rejected)
    .OrderBy(q => q.Order)
    .Select(q => new QuestionResponse(
        q.Id,
        q.Name,
        q.Description,
        q.PromoteCount,
        q.Votes.Any(v => v.UserId == userId),
        db.LearningQuestions.Any(l => l.QuestionId == q.Id && l.UserId == userId),
        q.Status == QuestionStatus.Pending))
    .ToListAsync(ct);

// Consistent ordering
.OrderBy(q => q.Order)
.OrderBy(o => o.CreatedOn)

// Missing AsNoTracking for read-only queries
var questions = await db.Questions.ToListAsync(ct);

// Over-fetching with Include
var questions = await db.Questions
    .Include(q => q.Category)
    .Include(q => q.Votes)
    .ToListAsync(ct);

// N+1 queries (fetching related data in loop)
foreach (var question in questions)
{
    question.Votes = await db.QuestionVotes
        .Where(v => v.QuestionId == question.Id)
        .ToListAsync(ct);
}