Mods are community-created files that add functional, tuning, and gameplay additions to the Sims 4.
What are the different kinds of mods?
Script mods are custom code scripts that add functionality. Some examples would be MCCC, UI Cheats Extension, and Wonderful Whims. These files will have a .ts4script file included.
A tuning mod is a package file that changes the default ways in which the game operates. Some popular examples would be no singing in the shower, meals that are group servings default to some other number instead of 8, no (fewer, or more) zombies spawning, more difficult (or easier) homework, etc. ( Taken from this Sims Forum question). Tuning mods are typically in a .package file format.
An override mod is a package file that overrides EA functionality. These can be tuning mods, but aren't limited to them.
For a more in-depth view of mods, I highly suggest reading this article by Carl Sims.