Automate SQL schema installation in install.js controller

[Copilot]

The Bhima installation process currently requires manual execution of SQL scripts via shell scripts to build the database schema. This PR bundles SQL schema files with the application and automates their execution through the install.js controller, enabling zero-script deployment.

Implementation

Schema Setup Functions

• getSQLFiles() - Discovers and sorts .sql files from server/models/ by filename
• checkSchemaExists() - Queries information_schema to verify if critical tables exist (idempotency)
• processSQLWithDelimiters() - Parses SQL files containing DELIMITER commands for stored procedures/functions
• executeSQLFile() - Executes SQL statements sequentially, ignoring duplicate object errors (ER_TABLE_EXISTS_ERROR, ER_SP_ALREADY_EXISTS, etc.)
• setupDatabaseSchema() - Orchestrates complete schema setup (exported for external use)

Integration Point

Modified proceedInstall() to call setupDatabaseSchema() before creating enterprise/project/user:

exports.proceedInstall = async (req, res) => {
  const { enterprise, project, user } = req.body;

  const isInstalled = await basicInstallExist();
  if (isInstalled) {
    throw new BadRequest('The application is already installed');
  }

  // Setup database schema if it doesn't exist
  await setupDatabaseSchema();

  const location = await defaultEnterpriseLocation();
  await createEnterpriseProjectUser(enterprise, project, user, location.uuid);
  res.redirect('/');
};

Behavior

• First install: Executes all SQL files in order (01-schema.sql, 02-functions.sql, ..., 99-debug.sql)
• Existing schema: Detects presence via information_schema query and skips execution
• Partial failure: Idempotent error codes ignored, other errors logged and re-thrown
• DELIMITER handling: Correctly parses stored procedures/functions by tracking delimiter state per line

Backward Compatibility

Existing installation process unchanged. If schema was already built via build-database.sh, the existence check detects it and skips SQL execution.

[!WARNING]

Firewall rules blocked me from connecting to one or more addresses (expand for details)

I tried to connect to the following addresses, but was blocked by firewall rules:

• googlechromelabs.github.io
  • Triggering command: node install.mjs (dns block)
• https://storage.googleapis.com/chrome-for-testing-public/141.0.7390.78/linux64/chrome-headless-shell-linux64.zip
  • Triggering command: node install.mjs (http block)
• https://storage.googleapis.com/chrome-for-testing-public/141.0.7390.78/linux64/chrome-linux64.zip
  • Triggering command: node install.mjs (http block)

If you need me to access, download, or install something from one of these locations, you can either:

• Configure Actions setup steps to set up my environment, which run before the firewall is enabled
• Add the appropriate URLs or hosts to the custom allowlist in this repository's Copilot coding agent settings (admins only)

Original prompt

Feature Request

The Bhima installation process currently requires manual execution of SQL scripts to build the database schema. To simplify and fully automate setup, refactor the application so that all SQL files in server/models/ (e.g., 01-schema.sql, 02-functions.sql, etc.) are bundled with the application. The install.js module should be updated to:

• Locate all SQL files in server/models/ matching the pattern *.sql.
• Sort the files in ascending order by filename (e.g., 01-schema.sql, 02-functions.sql, ...).
• Execute each SQL file against the target database if the required tables/functions/procedures do not exist.
• Ensure idempotency: only create missing objects, do not overwrite existing data.
• Use the latest supported NodeJS and MySQL features for file reading and execution.
• Integrate this logic into the install.js controller so that the application can reliably set up its own schema without requiring a separate install script.

This will enable direct, zero-script installation from the application and streamline deployment in Docker and other automated environments.

Closes https://github.com/third-culture-software/bhima/issues/8418.

---

💡 You can make Copilot smarter by setting up custom instructions, customizing its development environment and configuring Model Context Protocol (MCP) servers. Learn more Copilot coding agent tips in the docs.