database-schema-rules

Instruction file imported from JDKoll1982/MTM_Waitlist (.github/instructions/database-schema-rules.instructions.md). Copyright stays with the author.

MTM_Waitlist Database SQL Rules (Locked)

Platform

• Engine: MySQL 5.7.
• Charset/collation defaults: utf8mb4 / utf8mb4_unicode_ci.
• Keep all SQL identifiers lowercase snake_case.
• Avoid quoted identifiers unless unavoidable.

Environment Isolation

• Use physically separate databases for dev, test, and prod.
• Never move raw production user/session data to non-production.
• Dev/test seed data must be masked/synthetic.

Naming Conventions (Hard Rules)

• Tables are plural and follow structured module prefixes:
• {category}_{table}_{action_or_purpose}
• Primary key column is always id.
• Public UUID column is always public_id.
• Foreign key columns use custom relationship descriptors (for example, user_id, employee_id, workstation_id).
• Boolean columns start with is_ or has_.
• UTC datetime columns end with _utc.

Constraint and Index Naming

• Primary key: use MySQL default PRIMARY naming.
• Foreign keys: fk_<from_table>_<to_table>_<column>.
• Unique constraints: uq_<table>_<column_or_purpose>.
• Non-unique indexes: idx_<table>_<column_or_purpose>.
• Composite index order: left-to-right by filter/selectivity.
• MySQL identifier max length is 64 characters; all key/constraint/index names must stay at or below this limit.
• When names would exceed 64 characters, shorten descriptive middle segments while preserving required prefixes and uniqueness.

Reserved/Banned Terms and Abbreviations

• Allowed abbreviations: id, utc, mac, ip, rbac, uuid.
• Banned abbreviations: cfg, usr, ws, sess, rpt.
• Banned words: class, delete, order.
• Exceptions:
  • value_type is allowed as an explicit approved key-value column name.
  • order is allowed where it forms the domain compound work_order (for example work_order, normalized_work_order, open_work_order_quantity), because that is established Infor Visual vocabulary. A bare order identifier is still banned.

Required Startup-Core Tables (v1)

• core_users_profiles
• core_computers_registry
• auth_roles_catalog
• auth_roles_assignments
• auth_sessions_tokens
• ops_startup_logs
• config_settings_values
• config_settings_history

Security and Session Rules

• Never store plaintext tokens.
• Store salted token hashes and metadata only.
• Session validity must be comparable to DB server UTC (for example, fn_server_utc_now() or utc_timestamp()).

Data Lifecycle

• Hard delete policy only for primary entities.
• Keep auditability through explicit history tables (for example, *_history).
• Retention windows must be settings-driven from config_settings_values.

Migration and Deployment

• Migration model: hand-maintained, reviewed, file-per-artifact SQL with paired create.sql / rollback.sql. There is no FluentMigrator runner and no migration-runner layer in this repository.
• Use a file-per-artifact layout under Database/:
  • Bootstrap/create_database.sql
  • Bootstrap/update_table_descriptions.sql (mandatory maintenance file)
  • Tables/<table_name>/create.sql and rollback.sql
  • StoredProcedures/<procedure_name>/create.sql and rollback.sql
  • Views/<view_name>/create.sql and rollback.sql
  • Seeds/<seed_name>/create.sql and rollback.sql
  • Validation/<validation_name>/validate.sql
• Every schema artifact requires matching rollback or validation where appropriate.
• Any SQL add/edit/remove under Database/ must include corresponding updates to Database/Bootstrap/update_table_descriptions.sql in the same change.
• Production rollout is manual DBA-reviewed promotion.
• Do not auto-apply schema changes on app startup.

PR Compliance

• SQL naming convention drift must fail PR validation.
• Any exception requires explicit written approval in migration PR notes.
• Pre-existing violations are grandfathered and tracked in a report — .github/reports/sql-naming-violations.md — instead of being renamed in a bulk normalization pass (decision D-7, 2026-09-20). The guard keeps failing on anything new; a change that touches a grandfathered file is expected to fix the violations it touches.