feat(prepare-to-deploy):done of 90percent to deploy

See merge request !5

fix(session-pages): avoid both project use one session

See merge request !4

Feat/email smtp

See merge request !1

Co-authored-by: Cursor <cursoragent@cursor.com>

Comprehensive scan of backend (40 migrations, 64 models, OIDC provider,
multi-pool service, audit service) + 2 demo apps (project-a, project-b)
+ cross-project SSO analysis revealed 8 critical gaps and 7 pre-existing
TS errors that block end-to-end testing.

Key findings:
- validateCredentials is still a TODO stub (login does not work yet)
- oidcAdapterService is missing findSession/upsertSession (session not persisted)
- No prompt=none handling (silent SSO impossible)
- Cookie domain .meucorp.com does not match localhost pattern
- findAccount returns only static { sub } (userinfo has no claims)
- 7 pre-existing TS errors in verify-email, resend-verification, server.ts
- 18 untracked files from stale sso-vietprodev-old stash (cleanup needed)
- .env points to port 5433 (legacy from HA cluster) but Docker only maps 5432

User-confirmed decisions (locked in PLANS.md section 4):
1. Two PostgreSQL instances from Phase 0 (main:5432, backup:5433)
2. Cross-project silent SSO via prompt=none (OIDC standard) - rejects
   subdomain pattern as it would require refactor on production deploy
3. Drop old vietprodev_sso database after SQL backup safety net
4. Docker credentials simplified to sso/sso (instead of postgres/@dmin123)
5. PostgreSQL 17-alpine for both containers (stable, well-documented)
6. Clean up 18 untracked files after diff-verify against stash backup

PLANS.md contains:
- 5 phases (0-4) totaling 24-33h
- Architecture diagram (main + backup + mongo + redis + minio)
- 4 ADRs (OIDC adapter, audit destination, SSO mechanism, port layout)
- File touch list per phase
- Risk register with mitigations
- Status tracking table

Refs: silent SSO, prompt=none, OIDC, cross-project session,
pre-existing TS errors, PostgreSQL HA, MongoDB audit
Co-authored-by: Cursor <cursoragent@cursor.com>

Co-authored-by: Cursor <cursoragent@cursor.com>

Wire the OIDC /interaction/:uid/register handler end-to-end so a brand-
new account is created in 'pending_verification' status, a single-use
token is persisted (SHA-256 hashed) in email_verify_tokens, and a
verify-pending screen is rendered instead of the previous 501 stub.
Auto-login is intentionally skipped: the user must click the link in
the verification email before sign-in is allowed.

Highlights:
- EmailVerificationService: mints tokens, persists hashes, queues mail
  via MailService; dev mode returns the raw verify URL so the flow
  is testable without a working SMTP transport.
- New POST /:uid/resend-verification: re-issues a fresh token; the
  response is identical for known and unknown emails to avoid leaking
  account existence.
- Default 'user' role is assigned inside the same transaction as
  User + UserAuth creation; missing role is non-fatal.
- New view verify-pending.hbs with resend + back-to-sign-in actions.
- register.hbs upgraded to a 2-column responsive grid covering all
  OIDC profile fields (name, phone, gender, address, DOB) plus a
  required terms_accepted checkbox.
- 8 unit tests in tests/unit/services/emailVerification.service.test.ts
  (buildVerificationUrl, createToken hash, verifyToken outcomes,
  resendVerificationEmail guard) - all passing.
- RUN.md gained section 6.5 'Email Verification Flow' with REST/OIDC
  step-by-step, dev mode fallback, curl examples, env vars and a
  manual test checklist.
- PROGRESS.md updated to log Phase 3 re-apply + Phase 4 + Phase 5,
  pre-existing TS errors out of scope, and rollback cheatsheet.

Phase 4b (fix pre-existing TS errors in
src/controllers/api/v1/auth/{verify-email,resend-verification}.ts and
src/server.ts) is documented but deliberately out of scope for this
commit to keep the blast radius small.

Refs: pending_verification, email_verified_at columns,
email_verify_tokens table (migration 040)
Co-authored-by: Cursor <cursoragent@cursor.com>

- Add OidcService: lazy-initialized singleton wrapping oidc-provider v9
  - PostgreSQL adapter (via OidcAdapterService)
  - Configurable TTL, claims, routes, cookie keys
  - findAccount for token introspection
- Add oidcRoutes: mount all OIDC discovery + token endpoints
  - /.well-known/openid-configuration
  - /oauth/authorize, /oauth/token, /oauth/userinfo
  - /oauth/jwks, /oauth/introspect, /oauth/revoke, /oauth/logout
- Add oidcInteractionsController: interactive login/register/consent flows
  - GET /oidc/interaction/:uid — render login or consent page
  - POST /oidc/interaction/:uid/login — validate credentials
  - POST /oidc/interaction/:uid/register — create account
  - POST /oidc/interaction/:uid/confirm — approve consent
  - POST /oidc/interaction/:uid/cancel — deny consent
  - Audit logging for LOGIN_SUCCESS/FAILED, REGISTER_SUCCESS/FAILED
- Wire Handlebars view engine for OIDC interaction pages
- Initialize OIDC provider at server startup (dev + prod)
- Add MongoDB health check to /health endpoint
- Close OIDC + MongoDB on graceful shutdown
- Add database/index.ts and audit/index.ts for NodeNext module resolution
- Add #database/mongo and #audit path aliases to tsconfig
Co-authored-by: Cursor <cursoragent@cursor.com>

- Add MultiPoolService: registry of named Sequelize pools with create/get/close/healthcheck
- Add multi-pool config: write host + read replica host with separate ports
- Add DB_CONNECTION_STRING and DB_READ_HOST/DB_READ_PORT env vars
- Add docker-compose.ha.yml: Patroni + etcd cluster, HAProxy, PgBouncer
- Add HAProxy config: routes 5432 (write/primary) / 5433 (read/replica)
- Add PgBouncer userlist template
- Update dev/prod/staging configs with multi-pool hosts
Co-authored-by: Cursor <cursoragent@cursor.com>

- Add oidc-provider v9 for OIDC/OAuth2 authentication
- Add MongoDB client service for audit logging (sso_audit database)
- Add audit logger service with retry queue (up to 3 retries, 60s timeout)
- Add audit repository (insert, findByUserId, findByEventType)
- Add OIDC PostgreSQL adapter (grants storage)
- Add Handlebars views (login, register, consent)
- Add OIDC config service (issuer, TTL, cookie keys)
- Add oidc-grants and clients SQL migrations
- Update docker-compose: add PostgreSQL, MongoDB, rename containers
- Update .env.example: add OIDC, MongoDB, PostgreSQL variables
- Update package.json: add oidc-provider, mongodb, express-handlebars
- Update README with OIDC endpoints and architecture diagram
Co-authored-by: Cursor <cursoragent@cursor.com>

Co-authored-by: Cursor <cursoragent@cursor.com>