Okay, I see your point with staging table. That's a good idea! The only problem I see here is the transfer-to-archive-table process. As you've correctly noticed, the system is kind of a real-time and there can be dozens of processes writing to the staging table, i cannot see how to make the transfer/flush process right and clear...
The simplest way to do this is to create view and add/remove first/last day by recreating the view on daily interval.
