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.sql exists in both platform/deployment/prod/initdb/ and platform/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.sql and platform/deployment/prod/initdb/2_add_otp_verifications.sql are part of the production initialization path.
  • platform/database/2_add_otp_verifications.sql sits 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. 1_create_database.sql establishes the foundational bootstrap schema under production initdb assets.
  2. 2_add_otp_verifications.sql under platform/deployment/prod/initdb/ extends that bootstrap with OTP verification persistence.
  3. platform/database/2_add_otp_verifications.sql mirrors the OTP verification schema in the repository’s database asset path.
  4. 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.