Data Models, Persistence, and Schema Management - Application, User, and OTP Bootstrap Schema
Overview
This section covers the bootstrap SQL that establishes the core persistence shape for Lita’s housing platform: users, applications, phone_applications, and otp_verifications. The schema is split across deployment assets and repository-managed database files, which means the same model family is initialized through more than one operational path.
The practical result is a two-phase persistence story. Fresh environments can be initialized directly from the platform/deployment/prod/initdb/ assets, while the repository also carries a schema file under platform/database/ for the OTP verification table. That split is operationally significant because it creates separate execution paths for initial database provisioning and for schema management inside the repo.
Architecture Overview
flowchart TB
InitdbProvisioning[MySQL initdb provisioning]
RepoSchemaApplication[Repository schema application]
CreateDatabaseSQL[1_create_database.sql]
OtpInitdbSQL[2_add_otp_verifications.sql]
OtpDatabaseSQL[2_add_otp_verifications.sql]
BootstrapSchema[Bootstrap schema state]
UsersTable[users]
ApplicationsTable[applications]
PhoneApplicationsTable[phone_applications]
OtpVerificationsTable[otp_verifications]
InitdbProvisioning --> CreateDatabaseSQL
InitdbProvisioning --> OtpInitdbSQL
RepoSchemaApplication --> OtpDatabaseSQL
CreateDatabaseSQL --> BootstrapSchema
OtpInitdbSQL --> BootstrapSchema
OtpDatabaseSQL --> BootstrapSchema
BootstrapSchema --> UsersTable
BootstrapSchema --> ApplicationsTable
BootstrapSchema --> PhoneApplicationsTable
BootstrapSchema --> OtpVerificationsTable
Schema Files and Table Intent
Bootstrap SQL Assets
| File | Path | Role | Tables Established or Extended |
|---|---|---|---|
1_create_database.sql |
platform/deployment/prod/initdb/1_create_database.sql |
Initial database bootstrap for production container initialization | users, applications, phone_applications |
2_add_otp_verifications.sql |
platform/deployment/prod/initdb/2_add_otp_verifications.sql |
Production initdb extension for OTP verification persistence | otp_verifications |
2_add_otp_verifications.sql |
platform/database/2_add_otp_verifications.sql |
Repository-managed schema file for the same OTP verification persistence change | otp_verifications |
Table Intent
| Table | Intent |
|---|---|
users |
Core user identity and account persistence for the housing platform bootstrap schema. |
applications |
Primary application records used by the housing workflow. |
phone_applications |
Phone-scoped application records in the bootstrap persistence set. |
otp_verifications |
One-time password verification records used for verification flows. |
Persistence Model
[!NOTE]
2_add_otp_verifications.sqlexists in bothplatform/deployment/prod/initdb/andplatform/database/. That means the OTP verification schema is maintained through two operational paths: one for environment bootstrap and one for repository-managed schema application.
The bootstrap schema establishes the minimum durable state required by the platform’s application and verification flows. The table set is small and intentionally focused: user records, application records, phone-oriented application records, and OTP verification records.
Because the SQL files are bootstrap-oriented, the table definitions themselves carry the authoritative defaults and constraints. Their placement under initdb and platform/database determines when the DDL is executed, not just what it creates.
Default Constraint and Bootstrap Behavior
| Aspect | Operational Meaning |
|---|---|
| Default values | Applied at database creation or schema application time through the SQL files. |
| Constraint enforcement | Enforced by MySQL when the bootstrap SQL is executed. |
| Initial data shape | Defined by the table DDL in the bootstrap scripts. |
| OTP schema rollout | Delivered both as part of fresh initdb provisioning and as a repository-managed SQL asset. |
Operational Implications
The split location of the bootstrap SQL matters operationally:
platform/deployment/prod/initdb/1_create_database.sqlandplatform/deployment/prod/initdb/2_add_otp_verifications.sqlare part of the production initialization path.platform/database/2_add_otp_verifications.sqlsits with the repository’s database assets, so the OTP verification schema is also represented in the non-deployment schema management path.- The same logical schema change therefore has to remain aligned across both locations to preserve consistent bootstrap behavior.
This layout makes the schema lifecycle easy to reason about during deployment, but it also means the repository contains more than one authoritative entry point for related DDL.
Schema Management Flow
Bootstrap Provisioning Flow
sequenceDiagram
participant Ops as Deployment process
participant Initdb as MySQL initdb
participant Db as MySQL database
participant Repo as Repository schema assets
Ops->>Initdb: Load 1_create_database.sql
Initdb->>Db: Create bootstrap tables
Ops->>Initdb: Load 2_add_otp_verifications.sql
Initdb->>Db: Add otp_verifications
Repo->>Db: Apply platform/database/2_add_otp_verifications.sql
Db-->>Ops: Bootstrap schema ready
Schema File Lifecycle
1_create_database.sqlestablishes the foundational bootstrap schema under production initdb assets.2_add_otp_verifications.sqlunderplatform/deployment/prod/initdb/extends that bootstrap with OTP verification persistence.platform/database/2_add_otp_verifications.sqlmirrors the OTP verification schema in the repository’s database asset path.- The runtime database ends up with the same logical table family regardless of whether the schema was introduced through initdb bootstrap or repository-managed application.
Data Model Notes
users
- Part of the bootstrap schema created by
1_create_database.sql. - Represents the core user persistence anchor for the platform.
applications
- Part of the bootstrap schema created by
1_create_database.sql. - Represents primary application records in the housing workflow.
phone_applications
- Part of the bootstrap schema created by
1_create_database.sql. - Represents the phone-based application persistence surface.
otp_verifications
- Added by
2_add_otp_verifications.sql. - Present in both deployment initdb assets and
platform/database/. - Represents OTP verification records used by verification flows.
Key Classes Reference
| Class | Location | Responsibility |
|---|---|---|
1_create_database.sql |
platform/deployment/prod/initdb/1_create_database.sql |
Creates the bootstrap database schema for users, applications, and phone_applications. |
2_add_otp_verifications.sql |
platform/deployment/prod/initdb/2_add_otp_verifications.sql |
Extends the production initdb schema with otp_verifications. |
2_add_otp_verifications.sql |
platform/database/2_add_otp_verifications.sql |
Holds the repository-managed OTP verification schema definition. |