PlayMan/Obsidian
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Obsidian/Notes/SQLHosting/Design.md.md
T
Andrej Spielmann ea628b9a0a Initial commit
2026-04-21 10:53:28 +02:00

12 KiB
Raw Permalink Blame History

SQL Server Hosting Platform — Design Document

Session ID: 2026-04-21-sqlserver-hosting-platform

Created: 2026-04-21T00:00:00Z

Status: Approved

1. Overview

The SQL Server Hosting Platform is a full-stack web application that enables admins to manage SQL Server pools (Standalone and High Availability) and allows customers to book database applications hosted on those pools. The platform itself runs in Docker containers; customer SQL Servers are external physical/virtual instances.

Key Decisions

  • Platform DB: Microsoft SQL Server (external at 192.168.101.42:1433, database: SQLHostingDesk).

  • Customer SQL Server access: pyodbc exclusively (no ORM).

  • Dev/Prod separation: Docker Compose for local dev; the MSSQL platform DB is a shared, external dev instance.

  • Phased build: 3 phases (Foundation → Core Platform → Advanced) to manage complexity and allow early feature validation.

2. Architecture


┌──────────────────────────────┐

│ Next.js Frontend │ Port 3000

│ Admin Panel + Kunden-UI │

└─────────────┬────────────────┘

│ REST / BFF

┌─────────────▼────────────────┐

│ Django REST API │ Port 8000

│ JWT Auth │ API Endpoints │

└──────┬──────────────┬────────┘

│ │

┌──────▼──────┐ ┌────▼─────────────┐

│ Redis │ │ MSSQL Platform │ ← External via network

│ (Celery) │ │ 192.168.101.42 │

└──────┬──────┘ └──────────────────┘

│

┌──────▼──────────────┐

│ Celery Workers │

│ Discovery │

│ Provisioning │

│ Health Checks │

│ Quota Monitoring │

└──────┬──────────────┘

│ pyodbc

┌──────▼─────────────────────────────────┐

│ External Customer SQL Server │

│ (Unraid: 2x HA, 1x Standalone) │

└────────────────────────────────────────┘

Technology Stack

| Layer | Technology | Version |

|-------------|-------------------------------|---------|

| Frontend | Next.js (App Router), TS | 16.x |

| Backend | Django + DRF | 5.2 LTS |

| Platform DB | MSSQL (external) | 2019+ |

| Task Queue | Celery + Redis | 5.x |

| SQL Access | pyodbc | 18.x |

| Auth | JWT (djangorestframework-simplejwt) | 5.x |

| Container | Docker + Docker Compose | 24.x |

| Testing | pytest + Vitest + Playwright | — |

3. Phases

| Phase | Content | Deliverable |

|-------|---------|-------------|

| 1. Foundation | Docker-Compose (Django, Redis, Celery, Next.js), Django project setup, Custom User + JWT, Next.js base + auth flow | Platform reachable, login/register works |

| 2. Core Platform | ServerPool CRUD, Server Discovery (Celery + pyodbc), IP management, Application/DB models, Standalone booking wizard (incl. DB creation via pyodbc), Admin query log | Customers can book standalone apps; admin sees servers & logs |

| 3. Advanced | HA-pair management, AG listener, AG provisioning (real CREATE AVAILABILITY GROUP on Unraid HA pair), Soft delete with Celery, DB login management, Quota checks, Audit log | Full platform as per SPEC |

4. Database & Django Models

Database Configuration


DATABASES = {

'default': {

'ENGINE': 'mssql',

'NAME': 'SQLHostingDesk',

'USER': 'SQLHosting',

'PASSWORD': 'Test!123',

'HOST': '192.168.101.42',

'PORT': '1433',

'OPTIONS': {

'driver': 'ODBC Driver 18 for SQL Server',

'TrustServerCertificate': 'yes',

'Encrypt': 'yes',

},

}

}

Django Apps

| App | Models |

|-----|--------|

| user_auth | User, CustomerProfile |

| serverpool | ServerPool, Server, HAServerPair, ServerSelectionThreshold |

| networking | IPNetwork, IPAddress, AGListener |

| applications | CustomerApplication, CustomerDatabase, DatabaseLogin, DatabaseQuota |

| provisioning | QueryTemplate |

| auditlog | QueryLog, AuditLog |

| security | ElevatedAccount |

Core Relationships

  • User (role: ADMIN/CUSTOMER, is_approved) ↔ CustomerProfile (1:1)

  • CustomerProfile ↔ CustomerApplication (1:N) ↔ CustomerDatabase (1:N)

  • ServerPool (STANDALONE/HA) ↔ Server (1:N) ↔ HAServerPair (1:1 for primary, 1:1 for secondary)

  • HAServerPair ↔ AGListener (1:1)

  • CustomerDatabase ↔ DatabaseLogin (1:N), DatabaseQuota (1:1)

  • ServerPool ↔ ElevatedAccount (1:1)

Design Decisions

  • Soft Delete: django-safedelete library.

  • Audit Trail: django-auditlog signals + custom AuditLog for user actions.

  • Password Encryption: cryptography.fernet for ElevatedAccount.password_encrypted.

  • Generic FK: contenttypes framework for IPAddress.assigned_to.

  • UUID: Secure deletion confirmation tokens (CustomerDatabase.deletion_token).

5. API Design

Authentication (SimpleJWT)

| Endpoint | Method | Description |

|----------|--------|-------------|

| /api/v1/auth/register/ | POST | Register (status: pending) |

| /api/v1/auth/login/ | POST | Login, get tokens |

| /api/v1/auth/token/refresh/ | POST | Refresh access token |

| /api/v1/auth/me/ | GET | Current user profile |

Admin APIs (ADMIN role required)

| Endpoint | Method | Description |

|----------|--------|-------------|

| /api/v1/admin/users/pending/ | GET | List unapproved customers |

| /api/v1/admin/users/{id}/approve/ | PATCH | Approve customer |

| /api/v1/admin/users/{id}/reject/ | PATCH | Reject customer |

| /api/v1/admin/server-pools/ | GET, POST | Manage pools |

| /api/v1/admin/server-pools/{id}/add-server/ | POST | Add single server |

| /api/v1/admin/server-pools/{id}/add-ha-pair/ | POST | Add HA pair |

| /api/v1/admin/servers/{id}/discover/ | POST | Trigger discovery task |

| /api/v1/admin/servers/{id}/status/ | GET | Get server status |

| /api/v1/admin/ip-networks/ | GET, POST | Manage IP networks |

| /api/v1/admin/ip-addresses/ | GET | List IPs |

| /api/v1/admin/ag-listeners/ | GET, POST | Manage AG listeners |

| /api/v1/admin/ha-pairs/{id}/assign-listener/ | PATCH | Assign listener to HA pair |

| /api/v1/admin/query-logs/ | GET | Filter query logs |

| /api/v1/admin/query-logs/{id}/override/ | PATCH | Set override query |

Customer APIs (CUSTOMER + is_approved required)

| Endpoint | Method | Description |

|----------|--------|-------------|

| /api/v1/customer/applications/ | GET, POST | List / book apps |

| /api/v1/customer/applications/{id}/ | GET | App details |

| /api/v1/customer/applications/{id}/databases/ | GET, POST | List / add DBs |

| /api/v1/customer/applications/{id}/delete-request/ | DELETE | Request deletion |

| /api/v1/customer/applications/{id}/delete-confirm/ | DELETE | Confirm deletion |

| /api/v1/customer/databases/{id}/logins/ | GET, POST | Manage DB logins |

BFF Pattern (Next.js API Routes)

Next.js API Routes act as a proxy to Django to avoid CORS in the browser.

6. Celery Tasks & Background Processing

| Task | Trigger | Description |

|------|---------|-------------|

| discover_server | API call | Run 7 discovery queries via pyodbc, log all queries |

| health_check_all | Celery-Beat (configurable interval) | Check server resources against thresholds |

| provision_application | API call (new app) | Create DB (standalone) or AG+DB+Listener (HA) using Jinja2 templates |

| provision_add_database | API call | Add DB to existing app |

| delete_confirmed_application | Celery-Beat/manual | Execute DROP / AG remove |

| delete_confirmed_database | Celery-Beat/manual | Drop single DB |

| check_quota_all | Celery-Beat (hourly) | Check actual DB size, warn at 80%/95% |

| verify_elevated_account | Celery-Beat (daily) | Verify dbcreator+securityadmin, disable pool on failure |

Beat Schedule (example)


CELERY_BEAT_SCHEDULE = {

'health-check': {

'task': 'serverpool.tasks.health_check_all',

'schedule': 300.0, # 5 min (override via config)

},

'quota-check': {

'task': 'applications.tasks.check_quota_all',

'schedule': 3600.0, # hourly

},

'account-verify': {

'task': 'security.tasks.verify_elevated_account',

'schedule': 86400.0, # daily

},

}

Jinja2 Query Templates

All T-SQL queries (Discovery, Provisioning, Delete, Login) are stored as QueryTemplate records in the database. Before execution, Celery renders the template with current variables. Admins can override queries via the Query Log admin panel.

7. Frontend Architecture

Next.js App Router Structure


frontend/

├── src/

│ ├── app/

│ │ ├── (auth)/ # login, register

│ │ ├── (admin)/ # dashboard, server-pools, servers, ip-networks,

│ │ │ # ag-listeners, query-logs, users/pending

│ │ ├── (customer)/ # dashboard, applications, applications/new (wizard),

│ │ │ # applications/[id]

│ │ └── api/ # BFF proxy routes

│ ├── components/

│ │ ├── ui/ # shadcn/ui components

│ │ ├── layout/ # Sidebar, Header

│ │ ├── forms/ # Reusable forms

│ │ └── modals/ # Confirmation dialogs

│ ├── hooks/

│ ├── services/

│ ├── stores/

│ └── lib/

├── tests/e2e/

├── components.json

├── tailwind.config.ts

└── next.config.js

State Management

  • Zustand: Auth state (user, token, role)

  • TanStack Query: Server state (caching, refetching, mutations)

  • React Hook Form + Zod: All forms (login, wizard, modals)

Booking Wizard (5 Steps)

  1. Application name (letters, digits, underscores only)

  2. HA type selection (Standalone vs HA)

  3. Collation (dropdown from discovered server collations)

  4. First database name + size (GB)

  5. Summary + "Book now"

8. Docker Compose Setup

Services (no MSSQL container — external DB)

| Service | Image | Port |

|---------|-------|------|

| django | Custom (Python 3.12) | 8000 |

| celery-worker | Custom | — |

| celery-beat | Custom | — |

| redis | redis:7-alpine | 6379 |

| nextjs | Custom (Node 22) | 3000 |

Notes

  • ODBC Driver 18: Installed in Django container (msodbcsql18).

  • Debian 12+: Modern GPG keyring approach (no apt-key).

  • Volumes: Django source bind-mount (dev), Redis persistence.

9. Security Considerations

  • JWT: Access 15min, Refresh 7 days.

  • Service account passwords: Fernet encrypted (key in .env).

  • Role-based access: IsAdmin / IsCustomer + is_approved checks.

  • Query override: Admin-only, logged to AuditLog.

  • No secrets in Docker images (all via .env).

10. Exit Criteria (by Phase)

Phase 1: Foundation

  • docker compose up starts all 5 services without errors.

  • Django migrations run successfully against MSSQL SQLHostingDesk.

  • JWT login/register API works (tested via curl).

  • Next.js frontend loads, login page accessible, auth state managed.

  • ADMIN user can log in and see admin dashboard shell.

Phase 2: Core Platform

  • Admin can create Server Pools and add single servers.

  • Discovery task runs and populates Server fields (RAM, CPU, Disk, Version, Collation).

  • IP networks can be created and IPs auto-assigned.

  • Customer can register, admin approves, customer logs in.

  • Booking wizard (Standalone only) creates a real database on the external SQL Server.

  • Query log shows all discovery and provisioning queries.

Phase 3: Advanced

  • Admin can create HA pairs and assign AG listeners.

  • HA booking wizard creates a real Availability Group on the Unraid HA pair.

  • Adding databases to an existing HA app uses ALTER AVAILABILITY GROUP.

  • Soft delete (3-step) works for both applications and databases.

  • Quota monitoring checks actual sizes and logs warnings.

  • Audit log records all user actions immutably.

Design document written and ready for implementation planning.

Reference in New Issue View Git Blame Copy Permalink