wp-config.php constants vs database options for WP_HOME and WP_SITEURL across local and production

I'm setting up a WordPress deployment where the same codebase runs locally (e.g., http://mysite.test) and in production (https://mysite.com). The documented options for defining the site address are the WP_HOME and WP_SITEURL constants in wp-config.php, or the home and siteurl rows in the wp_options table.

Each approach has a documented trade-off. Constants override the database values and prevent editing them in Settings → General, which avoids accidental changes but hardcodes environment-specific URLs into versioned files. Database values keep wp-config.php environment-neutral, but a database copied from production to local (or vice versa) carries the wrong domain and can trigger redirect loops or broken asset URLs.

Assume WordPress 6.x on Apache or Nginx, with the database occasionally synced between environments.

• Which approach keeps local and production working without manual edits after each database sync?
• If constants are used, is there a documented pattern for making them environment-aware (e.g., reading from environment variables) without breaking core behavior?
• Does defining the constants change anything about how wp search-replace or URL-rewriting plugins behave during migrations?