The only thing I could agree with here is the statement that "New technologies could reveal more deposits as you progress along the tech tree."
The problem with having a 'set' amount of Production - or any other Game Resource - from a deposit is that the usefulness of each and every deposit depends on when it is harvested.
Copper = first tool and weapon metal, so Prime Importance for Production in the early Ancient Era. After Ironworking, not so much, a 'fringe' metal for alloys like bronze and brass, until the Industrial Era and Electricity. Then it is massively important for copper wiring, electrical motor armatures, and virtually everything you want to do with electricity.
Iron = only after Ironworking and the ability with kilns and bellows to produce enough heat to work it. Even then, less than 200 tons of Iron will produce all the weapons, armor, and equipment for an entire Roman Legion: the quantities required are not really huge, so piling up a few thousand tons by 'harvesting' just gets you a pile of rusting iron ingots that you can't use up before they flake away.
Until - again- the Industrial Era, when it takes 200 tons of Iron just for a mile or two of railroad, let alone the steam locomotives, steam engine power plants for factories, Ironclads - even the cast-iron artillery on one Frigate represents 60 - 100 tons of Iron. The quantities required are suddenly Off The Chart, and they only get larger when you throw in requirements for Battleships, Tanks, other motor vehicles, aircraft engines, skyscraper framing, etc., etc.
Niter = is a 'false resource' which doesn't belong in the game as a Deposit at all. The huge majority of 'niter' used to make gunpowder came from Nitraries, manufacturing facilities that made Nitre from decomposition of organic matter and urine. They could be built virtually anywhere, but in game terms, reduce the Appeal of surrounding tiles to about - 1000 or descriptively: "Stomach Turning". The few natural deposits that were exploited for both explosives and fertilizer can be numbered on the fingers of one hand and really could be better represented as a form of Natural Wonder.
Coal = again, no use at all until the early Renaissance, when you start running out of firewood and turn to charcoal or coal as a substitute, and then of course a Major Resource in the (here it is again) Industrial Era, when it powers everything.
Oil = except for using Bitumin and other 'natural' oil seeps to waterproof reed boats and, occasionally, as the basis for medicines, again pretty much a 'fringe' product until Internal Combustion. Then it becomes the indispensable Military Resource, because it powers everything that moves from Submarines to Battleships to Tanks to Aircraft, and despite all the 21st century/Information Age scientific advances, it still does.
Aluminum = couldn't be used at all until the late Industrial Era, when the processes for refining the ore using hydroelectricity were developed. A Nothing resource until nearly the Modern Era, when, of course, it becomes a material or an alloy for building everything from buildings to aircraft to modern (Information Era) ships and armored vehicles.
Uranium = has, as far as I know, only three uses: the ore can be used to make a bright yellow glaze for pottery, depleted uranium makes a very effective penetrator for antitank missiles, and, of course, it can be made to go Very Boom, but not before, by definition, the Atomic Era. Before then, it does not provide Production except to the occasional Potter. In fact, to make it useable Requires investment of massive amount of 'Production Points' - see the Manhattan Project, for example.
BUT Resources definitely need some rethinking. Here are my thoughts:
1. As you use them, ALL deposits Deplete. With a few exceptions any deposit you start exploiting will start depleting until One Fine Turn it disappears. BUT you should also be finding new Deposits all the time. As a Game Mechanic, this has the advantage of keeping you busy even in the late game hunting for new deposits with new Technologies, Scouts, or some specialized Great Person, and exploiting them, which may require taking them away from someone else.
2. The speed with which you deplete Deposits/Resources varies during the game. Gold and Silver, for instance, are always exploited to the Max, because they have been valued since before the Start of Game so that any deposit found will be dug into with every resource of manpower and tools available. For almost all other resources, requirements are real small until the Industrial Era. Then, the required quantities become massive: 'Industrial Quantities' if you will. BUT at the same time the technology to exploit those deposits becomes much more efficient: the first steam engines were used to pump water out of mines so that coal and iron and other minerals could be dug from much deeper - previously inaccessible - veins of ore. Hydraulic Mining and massive earthmoving machines for Open Pit Mines followed, so that 'old' deposits frequently become Exploitable again, and entirely new deposits 'open up': but they required people (and should require you, rtes Gamer) to invest the money, resources, and technology required to extract the Deposit Resource using the new techniques - some of which, like Open Pit Mines and Hydraulic Mining (and some specialized Oil-extraction techniques, like Fracking) will radically change the tiles on which you use them.
3. Let's not forget Renewable Resources. Anything that can be Planted or Grown an be planted or grown somewhere else. It takes certain technologies, but they are available (for some things) all the way back to the Ancient Era. The limitation is, in many cases, very specific terrain/climate requirements for things like Wine grapes, silkworms and their food, Bananas (which I've always assumed simply meant Any Tropical Fruit/Edible Plant), etc. Historically, the spread of some animals and plants had huge impact on Civilizations, and it is really a sign of laziness on the part of the Design Teams that such an impact is utterly missing from the game.
So, to return to your Original Idea, rather than Harvesting, which is really a pretty artificial Game Mechanic, why not have varying degrees of Production, Gold, or other result based on the Technology and techniques you are using to extract and exploit the Resource? Digging up Copper with a bronze shovel and pick so you can make a helmet out of it in the Ancient Era will give you one amount of Production. Removing the entire hill and turning the tile into one of the infamous Circular Lakes of Civ 6 in the Modern Era so you can electrically wire up 7 cities is a whole other level of Production Points, and probably should provide Amenity (electrical appliances, lights) and Gold as well.
And, unless you are exploiting the Messabi Range, the same tile of Copper you've been mucking about with since 3800 BCE and the Ancient Era may be depleted within 10 turns of Mechanized Open Pit Mining in the Modern Era, requiring you to find, trade, steal or otherwise obtain a new source for your Copper/Production Points/Amenity/Gold.
