Database and Data Modeling
Commerce uses declarative schema (db_schema.xml) as the source of truth, with db_schema_whitelist.json controlling which changes are applied. Understand EAV versus flat tables: products and customers are EAV (entity-attribute-value) with attribute sets, while sales, quote, and most operational data are flat. EAV gives flexibility at the cost of joins and indexing complexity; questions often ask why a product attribute query is slow and the answer involves EAV joins or missing indexers.
Know indexers and their modes (update on save vs update by schedule), the mview mechanism that tracks changed entities, and how bin/magento indexer:reindex interacts with cron. Understand repositories and the search criteria API: SearchCriteriaBuilder, filters, sorting, and pagination map to getList() methods. Traps include assuming a repository method returns a collection, confusing get (by ID) with getList (search criteria), and forgetting that direct SQL against EAV requires resolving attribute IDs and backend tables.
Worked example
Requirement: "Add a custom field to the order grid and make it filterable." Add the column to the sales_order table via db_schema.xml (flat table, simple column), declare it in di.xml as an extension attribute or grid UI component data provider, and register the column in the grid's ui_component XML. Because sales_order is flat, no EAV setup is needed; if it were a product attribute you would use eav_setup or a data patch with addAttribute.
Rule of thumb: EAV for user-defined, variable attributes; flat columns for fixed operational data queried at scale.