In an environment, the Project.toml file describes the packages you added explicitly. These can be thought of as the direct dependencies of your environment (which could be a package). Manifest.toml has all the packages that were pulled in either because you added them explicitly (so they also show up in Project.toml) or because they are dependencies of some package that you added. The Manifest.toml is like a screenshot of your current environment that has all the information needed to replicate this environment elsewhere, e.g. the URL of unregistered packages, branch, commit, etc. So how is the Project.toml useful? Well, it is kind of a minimal representation of the environment. It is also typically used to define direct and test dependencies of packages, including version constraints, since packages can be considered to be special types of environments. So a Project.toml works as a replacement to REQUIRE. Unfortunately, until now REQUIRE still has to be there beside the optional Project.toml because the transition is not complete yet.
When running Julia in REPL, you are running it in an environment, the default one. There is nothing special about this environment, you can change it if you want. When adding packages, the packages and their dependencies are pulled in, sometimes existing packages get upgraded or downgraded to have a “feasible” set of packages. This automatically updates the Project.toml and Manifest.toml files of the active environment. The Project.toml file will have the explicitly added packages added to it, and the Manifest.toml will have the whole environment’s new state, e.g. new versions, packages, branches, etc. A feasible set of packages is a set that satisfies the version constraints of all the Project.toml (or REQUIRE) files of all your packages.
When you are defining Project.toml for a package as a replacement to REQUIRE, you can activate the environment that is local to this package. This is possible because packages have Project.toml and Manifest.toml files. You can then add the dependencies and define the constraints as explained in Convert REQUIRE to Project.toml and Manifest.toml - #2 by mohamed82008.
This is the best intuition I have for how the new package manager works. I may have missed a couple of things, so perhaps other people can correct me if I said something wrong.