# SSO VietProDev — Database Cleanup & Redesign

**Date:** 2026-06-18
**Status:** ✅ Fully Implemented (sessions 4 + 5, 2026-06-20)

---

## 1. Root Cause: Why the App Crashed

```
[WARN] autoLoadPools failed: SequelizeDatabaseError: column "description" does not exist
```

**Cause:** `MultiPoolService.autoLoadPools()` queries `project_db_connections` table (a beKind-era table) expecting a `description` column that doesn't exist in this database. Since it's in a `catch()` block as a warning, it doesn't crash directly — but it's a sign of stale infrastructure.

The `multiPoolService.ts` reads from `project_db_connections` to dynamically load project-specific database pools. This mechanism is a remnant from the multi-project architecture that doesn't apply to the SSO project.

**Fix:** Either drop the query or create the `project_db_connections` table. Since SSO doesn't use dynamic per-project pools, the safest fix is to make `autoLoadPools()` graceful when the table is missing.

---

## 2. Current Swagger Title — "BEKIND API"

The compiled `storage/swagger/openapi.json` still has:
```json
"title": "BEKIND API",
"version": "1.0.0",
"description": "BEKIND backend REST API..."
```

**Cause:** `pnpm swagger:generate` was run from old source. The new title should be regenerated from updated contracts.

**Fix:** Run `pnpm swagger:generate` after cleaning up contracts, or manually update the `storage/swagger/openapi.json`.

---

## 3. Migration Analysis — What We Found

### 37 migrations in dependency order:

| File | Purpose | Keep for SSO? |
|------|---------|--------------|
| 001 | Auth schema (users, user_auth, roles, permissions, sessions) | ✅ SSO core |
| 002 | File storage (files, file_variants) | ✅ SSO core |
| 003 | Notification schema (messages, receipts, devices, delivery attempts) | ✅ SSO core |
| 004 | OAuth-only flag, avatar migration, password history | ✅ SSO core |
| 005 | **BeKind Resident & Facility (buildings, apartments, rooms, beds, residents)** | ⚠️ See note |
| 006 | Add user_id to residents | ✅ |
| 007 | Active views (buildings, apartments, rooms, beds) | ✅ |
| 008 | Remove unique constraint (resident_stays) | ✅ |
| 009 | Remove overlap constraint (resident_stays) | ✅ |
| 010 | Add nationality, job to residents | ✅ |
| 011 | Add CCCD document types | ✅ SSO-friendly |
| 012 | Resident contracts schema | ✅ |
| 013 | Remove file duplicate constraint | ✅ |
| 014 | Audit log system (enterprise) | ✅ |
| 015 | Drop duplicate audit tables | ✅ |
| 016 | Contract types reference table | ✅ |
| 017 | Optimize user_sessions indexes | ✅ |
| 018 | Disable outbox NOTIFY trigger | ✅ |
| 019 | Maintenance incidents schema | ⚠️ Domain-specific |
| 020 | Add incident code and deadline | ⚠️ Domain-specific |
| 021 | Add status to resident_documents | ✅ |
| 022 | Link technicians to users | ⚠️ Domain-specific |
| 023 | Posts and post_targets | ⚠️ Domain-specific |
| 024 | Add apartment to posts.target_type | ⚠️ Domain-specific |
| 025 | Add unique constraint on beds.code | ✅ |
| 026 | Bills schema | ⚠️ Domain-specific |
| 027 | Alter bills billing_period to VARCHAR(7) | ⚠️ Domain-specific |
| 028 | Add soft delete to bills | ⚠️ Domain-specific |
| 029 | Add webhook_logs | ✅ SSO core |
| 030 | Recreate webhook_logs with enum | ✅ SSO core |
| 031 | Add thumbnail_id to posts | ⚠️ Domain-specific |
| 032 | Notification templates | ✅ |
| 033 | System configs | ⚠️ See note |
| 034 | Debt reminder policies | ⚠️ Domain-specific |
| 035 | OIDC grants schema | ✅ SSO core |
| 036 | OIDC clients schema | ✅ SSO core |
| 040 | email_verified_at and address on users | ✅ SSO core |

### What the 37 migrations create:

**41 Tables total** across these domains:
- Auth (users, user_auth, user_sessions, roles, permissions, user_roles, role_permissions, password_reset_tokens, email_verify_tokens, user_password_history)
- Files (files, file_variants)
- Notifications (notification_messages, notification_receipts, notification_devices, notification_delivery_attempts, notification_preferences, notification_templates)
- Resident Management (residents, resident_stays, resident_events, resident_documents)
- Facility (buildings, apartments, rooms, beds)
- Contracts (contracts, contract_files, contract_types)
- Maintenance (incidents, incident_types, incident_files, technicians)
- Posts (posts, post_targets)
- Billing (bills, bill_items)
- Webhooks (webhook_logs)
- Config (system_configs, debt_reminder_policies, debt_reminder_policy_targets)
- OIDC (oidc_grants, clients)
- Audit (audit schema: system_audit_logs, entity_change_logs, auth_audit_logs, refresh_token_audit_logs, audit_outbox, audit_dead_letter)

---

## 4. What's SSO-Specific vs Legacy BeKind?

### SSO Core (must keep):
- `001-auth-schema` — Users, roles, permissions, sessions
- `002-file-schema` — File storage
- `003-notification-schema` — Notifications
- `035-oidc-schema` — OIDC grants
- `036-clients-schema` — OIDC clients
- `014-audit-log-system` — Enterprise audit
- `029-030-webhook-logs` — Webhook tracking
- `040-email-verified-at` — User profile

### Domain Modules (residual from beKind "Khu trọ" use case):

These modules represent the old rental property management domain and are **not related to SSO**:

| Module | Tables | API Endpoint | Notes |
|--------|--------|-------------|-------|
| Buildings | `buildings` | `/api/v1/buildings` | Facility hierarchy |
| Apartments | `apartments` | `/api/v1/apartments` | Facility hierarchy |
| Rooms | `rooms` | `/api/v1/rooms` | Facility hierarchy |
| Beds | `beds` | `/api/v1/beds` | Facility hierarchy |
| Residents | `residents`, `resident_stays`, `resident_events`, `resident_documents` | `/api/v1/residents` | Resident management |
| Contracts | `contracts`, `contract_files`, `contract_types` | `/api/v1/contracts` | Rental contracts |
| Technicians | `technicians`, `incidents`, `incident_types`, `incident_files` | `/api/v1/incidents`, `/api/v1/technicians` | Maintenance |
| Posts | `posts`, `post_targets` | `/api/v1/posts` | Announcements |
| Bills | `bills`, `bill_items` | `/api/v1/bills` | Billing |
| Debt Reminders | `debt_reminder_policies` | `/api/v1/debt-reminder-policies` | Debt collection |

### Config Issues:
- `033-system-configs` seeds "BeKind Property" branding
- `034-debt-reminder-policies` seeds "Nhắc nợ" (debt reminder) policies

---

## 5. Cleanup Recommendations

### Option A: Full Cleanup (Recommended for new project)
Delete all 11 legacy domain controllers + their models, services, contracts:

```
Controllers to DELETE:
- src/controllers/api/v1/apartments/
- src/controllers/api/v1/bills/
- src/controllers/api/v1/buildings/
- src/controllers/api/v1/beds/
- src/controllers/api/v1/contracts/ (keep if contracts are useful for SSO)
- src/controllers/api/v1/contract-files/
- src/controllers/api/v1/contract-types/
- src/controllers/api/v1/debt-reminder-policies/
- src/controllers/api/v1/incidents/
- src/controllers/api/v1/incident-files/
- src/controllers/api/v1/incident-types/
- src/controllers/api/v1/posts/
- src/controllers/api/v1/resident-documents/
- src/controllers/api/v1/residents/
- src/controllers/api/v1/rooms/
- src/controllers/api/v1/technicians/

Models to DELETE:
- ActiveApartment, ActiveBed, ActiveBuilding, ActiveResident, ActiveResidentStay, ActiveRoom
- Apartment, Bed, Building, Resident, ResidentDocument, ResidentStay, ResidentEvent
- Room, Contract, ContractFile, ContractType
- Bill, BillItem, DebtReminderPolicy, DebtReminderPolicyTarget
- Incident, IncidentFile, IncidentType, Technician
- Post, PostTarget
- residentExcelService.ts (service)
- webhookService.ts (check — may be relevant)

Contracts to DELETE:
- src/contracts/apartment/
- src/contracts/bill/
- src/contracts/building/
- src/contracts/bed/
- src/contracts/contract/, contract-file/, contract-type/
- src/contracts/debt-reminder-policy/
- src/contracts/file/ (check)
- src/contracts/incident/, incident-file/, incident-type/
- src/contracts/notification/ (check)
- src/contracts/post/
- src/contracts/resident/
- src/contracts/room/
- src/contracts/technician/
```

### Option B: Incremental (Keep everything, just clean branding)
- Update `storage/swagger/openapi.json` title
- Update system_configs branding
- Fix `autoLoadPools` to handle missing `project_db_connections` gracefully
- Regenerate Swagger

---

## 6. New Clean Migration: `sql/clean-migration.sql`

**Location:** `sql/clean-migration.sql`
**Lines:** ~2090
**Size:** Clean consolidated, single-file migration

### What's consolidated:
- All 37 original migrations merged into 20 logical phases
- 20 enums, 41 tables, 90+ indexes, 15+ triggers, 10+ functions/procedures
- All redundant/duplicate operations removed (e.g., `DROP INDEX` + `CREATE` → single `CREATE IF NOT EXISTS`)
- Seed data updated: "BeKind Property" → "SSO VietProDev"
- Comment headers with table descriptions for auto-documentation

### Phase breakdown:
1. Extensions (uuid-ossp, pgcrypto, citext, btree_gist, pg_trgm)
2. Enum Types (20 enums)
3. Core Auth Tables
4. File Storage
5. Notifications
6. Resident & Facility
7. Contracts
8. Maintenance Incidents
9. Posts
10. Bills
11. Webhooks
12. System Config
13. Debt Reminder Policies
14. OIDC
15. Audit Schema
16. Indexes
17. Triggers
18. Views
19. Stored Procedures
20. Comments

---

## 7. Implementation Summary

All Option A (Full Cleanup) items have been implemented:

- ✅ `MultiPoolService` — graceful skip when `project_db_connections` missing
- ✅ Swagger — regenerated as "SSO VietProDev API" (50 paths)
- ✅ All 27 legacy models deleted (residents, beds, bills, contracts, incidents, etc.)
- ✅ All 18 legacy controllers deleted
- ✅ All 17 legacy contracts deleted
- ✅ `init-models.ts` rewritten (33 models, 297 lines)
- ✅ Legacy services deleted (webhookService, residentExcelService, etc.)
- ✅ BeKind residue cleaned from all source files
- ✅ `clean-migration.sql` rewritten (1063 lines, SSO-only enums)
- ✅ `npx tsc --noEmit` → exit 0
- ✅ `pnpm swagger:generate` → 50 SSO paths

**Implementation details:** See PROGRESS.md §17 (session 4).

---

## 8. File Locations Reference

| File | Purpose |
|------|---------|
| `sql/migrations/` | Original 37 incremental migrations |
| `sql/clean-migration.sql` | **New consolidated migration** |
| `src/controllers/api/v1/` | API endpoints (30 directories) |
| `src/models/` | Sequelize models (58 files) |
| `src/contracts/` | Zod validation schemas |
| `src/services/` | Business logic |
| `storage/swagger/openapi.json` | Compiled OpenAPI spec |
