Database Setup - Happy Habitat

Overview

Happy Habitat uses SQL Server as its database engine with Entity Framework Core for data access and migrations. This guide covers production database setup, migration management, and maintenance.

Prerequisites

SQL Server 2019+ or Azure SQL Database
.NET 8 SDK (for running migrations)
Database admin credentials

SQL Server Installation

Windows Server

Download SQL Server:
- SQL Server 2022 Express (free)
- SQL Server Developer Edition (free for dev/test)
- SQL Server Standard/Enterprise (production)
Install SQL Server:

# Run installer and select:
# - Database Engine Services
# - Mixed Mode Authentication (SQL + Windows)
# - Set sa password

Configure SQL Server:

-- Enable TCP/IP connections
EXEC sys.sp_configure N'remote access', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Configure Firewall:

New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

Linux (Ubuntu/Debian)

Install SQL Server:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)"
sudo apt-get update
sudo apt-get install -y mssql-server

Configure SQL Server:

sudo /opt/mssql/bin/mssql-conf setup
# Choose: 2) Developer (free)
# Set SA password (min 8 chars, uppercase, lowercase, numbers, symbols)

Start SQL Server:

sudo systemctl enable mssql-server
sudo systemctl start mssql-server
sudo systemctl status mssql-server

Install SQL Tools:

sudo apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Azure SQL Database

Create Azure SQL Server:

az sql server create \
  --name happyhabitat-sql \
  --resource-group happyhabitat-rg \
  --location "East US" \
  --admin-user sqladmin \
  --admin-password "SecureP@ssw0rd!"

Create Database:

az sql db create \
  --name HappyHabitat \
  --server happyhabitat-sql \
  --resource-group happyhabitat-rg \
  --service-objective S0

Configure Firewall:

# Allow Azure services
az sql server firewall-rule create \
  --server happyhabitat-sql \
  --resource-group happyhabitat-rg \
  --name AllowAzureServices \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

# Allow your IP
az sql server firewall-rule create \
  --server happyhabitat-sql \
  --resource-group happyhabitat-rg \
  --name AllowMyIP \
  --start-ip-address YOUR_IP \
  --end-ip-address YOUR_IP

Database Creation

Option 1: Automatic (Recommended)

The application automatically creates the database and runs migrations on startup (see Program.cs:240-276).

On first deployment, the application will:

Create the database if it doesn’t exist
Apply all pending migrations
Seed initial data (roles and admin user)

Option 2: Manual

Create the database manually:

CREATE DATABASE HappyHabitat
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO

USE HappyHabitat;
GO

Create a dedicated application user:

CREATE LOGIN HappyHabitatApp WITH PASSWORD = 'SecureP@ssw0rd!';
GO

USE HappyHabitat;
CREATE USER HappyHabitatApp FOR LOGIN HappyHabitatApp;
ALTER ROLE db_owner ADD MEMBER HappyHabitatApp;
GO

Connection Strings

SQL Authentication

Server=your-server.database.windows.net;Database=HappyHabitat;User Id=sqladmin;Password=SecureP@ssw0rd!;TrustServerCertificate=True;Encrypt=True;

Windows Authentication (On-Premises)

Server=(local);Database=HappyHabitat;Trusted_Connection=True;TrustServerCertificate=True;

Azure SQL Database

Server=tcp:happyhabitat-sql.database.windows.net,1433;Initial Catalog=HappyHabitat;Persist Security Info=False;User ID=sqladmin;Password=SecureP@ssw0rd!;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Connection String Parameters

TrustServerCertificate=True - Accept self-signed certificates (development only)
Encrypt=True - Use SSL/TLS encryption (required for Azure SQL)
MultipleActiveResultSets=True - Enable MARS (multiple active result sets)
Connection Timeout=30 - Connection timeout in seconds

Database Migrations

Happy Habitat uses Entity Framework Core Migrations for schema management.

View Migration History

cd ~/workspace/source/happy-habitat-backend/HappyHabitat.Infrastructure

# List all migrations
dotnet ef migrations list --startup-project ../HappyHabitat.API

Current migrations (as of March 2026):

20260223063853_InitialCreate - Initial database schema with all core tables
20260224011819_AddPagosResidente - Resident payment tracking
20260224030206_AddConceptoToPagosResidente - Payment concept field
20260224030619_AddUrlComprobanteToPagosResidente - Payment receipt URLs
20260301060700_AddSaldoCuentaBancaria - Bank account balance tracking

Apply Migrations

Automatic (Startup)

The application runs migrations automatically on startup (Program.cs:262):

await context.Database.MigrateAsync();

Ensure Database:RecreateOnStartup is set to false in production. The application will fail to start if this is true.

Manual

Apply migrations manually using the CLI:

cd ~/workspace/source/happy-habitat-backend

dotnet ef database update \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API \
  --connection "Server=...;Database=HappyHabitat;..."

Apply to specific migration:

dotnet ef database update InitialCreate \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API

Create New Migration

When modifying entity models:

dotnet ef migrations add YourMigrationName \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API \
  --output-dir Migrations

Generate SQL Script

For manual review or DBA-controlled deployments:

# Generate script for all migrations
dotnet ef migrations script \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API \
  --output migrations.sql

# Generate script for specific range
dotnet ef migrations script InitialCreate AddSaldoCuentaBancaria \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API \
  --output update.sql

Rollback Migration

# Rollback to previous migration
dotnet ef database update PreviousMigrationName \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API

# Remove last migration (if not applied)
dotnet ef migrations remove \
  --project HappyHabitat.Infrastructure \
  --startup-project HappyHabitat.API

Data Seeding

Initial Seed Data

The application seeds essential data on first run (Program.cs:266-268): Roles (see InitialSeeder.cs:26-32):

SYSTEM_ADMIN - System Administrator
ADMIN_COMPANY - Company Administrator
COMITEE_MEMBER - Committee Member
RESIDENT - Resident
RENTER - Tenant
VIGILANCE - Vigilance

Vehicle Types (see InitialSeeder.cs:42-48):

Car, Motorcycle, Truck, SUV, Van

Ticket Categories (see InitialSeeder.cs:104-114):

Areas comunes, Amenidades, Mantenimiento, Mascotas, Pregunta/comentario, Ruido, Servicios, Sugerencia, Vigilancia, Otro

Ticket Statuses (see InitialSeeder.cs:121-128):

Nuevo, En revisión, En investigación, En proceso, Cancelado, Resuelto

Default Admin User (see InitialSeeder.cs:65-76):

Username: elgrandeahc
Email: [email protected]
Password: abc123 (hashed with BCrypt)
Role: SYSTEM_ADMIN

Initial seeding runs automatically on first deployment. Check InitialSeeder.cs for details.

Development Data

In development environments, dummy data is seeded automatically (Program.cs:270-275):

if (app.Environment.IsDevelopment())
{
    var dummySeeder = services.GetRequiredService<DummySeeder>();
    await dummySeeder.SeedAsync();
}

Manual Seeding

Run seed without starting the API:

cd ~/workspace/source/happy-habitat-backend/HappyHabitat.API
dotnet run --seed-only

The --seed-only flag applies migrations and seeds data, then exits (Program.cs:279-282).

Database Schema

Core Tables

The database schema includes 27+ tables covering all aspects of condominium management (see ApplicationDbContext.cs:13-46): User Management:

Roles - System roles (SYSTEM_ADMIN, ADMIN_COMPANY, etc.)
Users - Application users with authentication
UserRoles - Many-to-many user-role assignments
UserCommunities - User-community associations

Community Management:

Communities - Condominium communities
Residents - Community residents linked to users
ResidentConfigurations - Resident-specific settings

Assets & Visits:

Vehicles - Resident vehicles
VehicleTypes - Vehicle type catalog
Pets - Resident pets
ResidentVisits - Visitor tracking and authorization

Amenities & Communications:

Amenities - Community amenities (pools, gyms, etc.)
Banners - Visual announcements
Comunicados - Official communications
Documents - Document repository

Financial Management:

Contratos - Community service contracts
PaymentHistory - Contract payment history
CargosComunidad - Community charges
PagoComunidad - Community payments
PagoCargoComunidad - Payment-charge junction table
CargoResidente - Individual resident charges
PagoResidente - Individual resident payments
SaldoCuentaBancaria - Bank account balances

Support & Feedback:

CategoriaTicket - Ticket categories
StatusTicket - Ticket statuses
Tickets - Support tickets
Comentarios - Comments (on tickets, etc.)
Encuestas - Surveys
PreguntaEncuesta - Survey questions
OpcionRespuesta - Answer options
RespuestaResidente - Resident survey responses

Configuration:

CommunityConfigurations - Community-specific settings
CommunityPrices - Community pricing catalog
CommunityProviders - Vendor/provider directory

Relationships

The database uses foreign keys to maintain referential integrity:

Users belong to Communities
Residents belong to Communities
Vehicles/Pets belong to Residents
Tickets reference Users and Communities
Charges reference Communities

Backup and Recovery

Automated Backups (SQL Server)

Create a maintenance plan:

-- Full backup daily at 2 AM
BACKUP DATABASE HappyHabitat
TO DISK = 'C:\Backups\HappyHabitat_Full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;
GO

-- Transaction log backup every hour
BACKUP LOG HappyHabitat
TO DISK = 'C:\Backups\HappyHabitat_Log.trn'
WITH COMPRESSION, STATS = 10;
GO

Automated Backups (Azure SQL)

Azure SQL automatically creates:

Full backup weekly
Differential backup every 12-24 hours
Transaction log backup every 5-10 minutes
Retention: 7-35 days (configurable)

Configure long-term retention:

az sql db ltr-policy set \
  --server happyhabitat-sql \
  --database HappyHabitat \
  --resource-group happyhabitat-rg \
  --weekly-retention P4W \
  --monthly-retention P12M \
  --yearly-retention P5Y \
  --week-of-year 1

Manual Backup

# Using sqlcmd (Windows/Linux)
sqlcmd -S your-server -U sqladmin -P password -Q "BACKUP DATABASE HappyHabitat TO DISK = '/var/opt/mssql/backup/HappyHabitat.bak' WITH COMPRESSION"

# Using Azure CLI
az sql db export \
  --name HappyHabitat \
  --server happyhabitat-sql \
  --resource-group happyhabitat-rg \
  --admin-user sqladmin \
  --admin-password password \
  --storage-key-type StorageAccessKey \
  --storage-key "storage-key" \
  --storage-uri "https://storage.blob.core.windows.net/backups/HappyHabitat.bacpac"

Restore Database

-- Restore full backup
RESTORE DATABASE HappyHabitat
FROM DISK = 'C:\Backups\HappyHabitat_Full.bak'
WITH REPLACE, RECOVERY;
GO

Azure SQL:

az sql db import \
  --name HappyHabitat \
  --server happyhabitat-sql \
  --resource-group happyhabitat-rg \
  --admin-user sqladmin \
  --admin-password password \
  --storage-key-type StorageAccessKey \
  --storage-key "storage-key" \
  --storage-uri "https://storage.blob.core.windows.net/backups/HappyHabitat.bacpac"

Performance Optimization

Indexes

Entity Framework Core creates indexes automatically based on entity configurations. Review generated indexes:

-- List all indexes
SELECT 
    TableName = t.name,
    IndexName = i.name,
    IndexType = i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name IN (SELECT name FROM sys.tables WHERE type = 'U')
ORDER BY t.name, i.name;

Query Performance

Enable query statistics:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Your query here

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Maintenance Tasks

Regular maintenance improves performance:

-- Update statistics
EXEC sp_updatestats;

-- Rebuild indexes (weekly)
ALTER INDEX ALL ON HappyHabitat.dbo.Users REBUILD;

-- Reorganize indexes (daily)
ALTER INDEX ALL ON HappyHabitat.dbo.Users REORGANIZE;

Monitoring

View Active Connections

SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    last_request_start_time
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('HappyHabitat');

View Long-Running Queries

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.database_id = DB_ID('HappyHabitat')
AND r.total_elapsed_time > 5000; -- Queries running > 5 seconds

Database Size

SELECT 
    database_name = DB_NAME(),
    size_mb = SUM(size * 8 / 1024),
    used_mb = SUM(FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024)
FROM sys.database_files;

Security

Encrypt Connections

Always use encrypted connections in production:

Server=...;Database=HappyHabitat;...;Encrypt=True;

Principle of Least Privilege

Create read-only users for reporting:

CREATE LOGIN ReportUser WITH PASSWORD = 'SecureP@ssw0rd!';
USE HappyHabitat;
CREATE USER ReportUser FOR LOGIN ReportUser;
ALTER ROLE db_datareader ADD MEMBER ReportUser;

Transparent Data Encryption (TDE)

Enable TDE for data-at-rest encryption:

-- Create master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecureP@ssw0rd!';

-- Create certificate
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate';

-- Create database encryption key
USE HappyHabitat;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

-- Enable TDE
ALTER DATABASE HappyHabitat SET ENCRYPTION ON;

Troubleshooting

Cannot Connect to Database

Verify SQL Server is running:

sudo systemctl status mssql-server  # Linux
Get-Service MSSQLSERVER  # Windows

Test connection:

sqlcmd -S your-server -U sqladmin -P password -Q "SELECT @@VERSION"

Check firewall rules
Verify connection string

Migration Fails

Check database permissions
Verify connection string
Review migration code for errors
Check for schema conflicts

Performance Issues

Update statistics: EXEC sp_updatestats
Rebuild indexes
Review slow query log
Check for missing indexes

Next Steps

Backend Deployment - Deploy the API with database connection
Environment Variables - Configure connection strings
Frontend Deployment - Deploy the client application

Getting Started

Core Features

User Guides

Deployment

​Overview

​Prerequisites

​SQL Server Installation

​Windows Server

​Linux (Ubuntu/Debian)

​Azure SQL Database

​Database Creation

​Option 1: Automatic (Recommended)

​Option 2: Manual

​Connection Strings

​SQL Authentication

​Windows Authentication (On-Premises)

​Azure SQL Database

​Connection String Parameters

​Database Migrations

​View Migration History

​Apply Migrations

​Automatic (Startup)

​Manual

​Create New Migration

​Generate SQL Script

​Rollback Migration

​Data Seeding

​Initial Seed Data

​Development Data

​Manual Seeding

​Database Schema

​Core Tables

​Relationships

​Backup and Recovery

​Automated Backups (SQL Server)

​Automated Backups (Azure SQL)

​Manual Backup

​Restore Database

​Performance Optimization

​Indexes

​Query Performance

​Maintenance Tasks

​Monitoring

​View Active Connections

​View Long-Running Queries

​Database Size

​Security

​Encrypt Connections

​Principle of Least Privilege

​Transparent Data Encryption (TDE)

​Troubleshooting

​Cannot Connect to Database

​Migration Fails

​Performance Issues

​Next Steps

Build docs developers (and LLMs) love

Overview

Prerequisites

SQL Server Installation

Windows Server

Linux (Ubuntu/Debian)

Azure SQL Database

Database Creation

Option 1: Automatic (Recommended)

Option 2: Manual

Connection Strings

SQL Authentication

Windows Authentication (On-Premises)

Azure SQL Database

Connection String Parameters

Database Migrations

View Migration History

Apply Migrations

Automatic (Startup)

Manual

Create New Migration

Generate SQL Script

Rollback Migration

Data Seeding

Initial Seed Data

Development Data

Manual Seeding

Database Schema

Core Tables

Relationships

Backup and Recovery

Automated Backups (SQL Server)

Automated Backups (Azure SQL)

Manual Backup

Restore Database

Performance Optimization

Indexes

Query Performance

Maintenance Tasks

Monitoring

View Active Connections

View Long-Running Queries

Database Size

Security

Encrypt Connections

Principle of Least Privilege

Transparent Data Encryption (TDE)

Troubleshooting

Cannot Connect to Database

Migration Fails

Performance Issues

Next Steps