Simplify FeedLog table design

[aronza]

Describe the problem

In current database design, there is Feed table and FeedLog table with all the same columns except FeedLog has an extra historical_record_time column. If this is designed to track active and historical states of feeds, would it be easier to maintain only one feed table with record_time column?

This way the API can still serve the latest feeds when requested by querying for the feed's with latest record_time column per stable_id. Also maintaining these table schemas and inserting new data to them would be simpler.

I think we already have these table schemas diverged where Feed was added feed_contact_email at https://github.com/MobilityData/mobility-feed-api/blob/main/liquibase/changes/feat_15.sql but FeedLog doesn't have this column.

Proposed solution

Add record_time column to Feed table and remove FeedLog table

Alternatives you've considered

Existing schema

Additional context

This is not a blocker in my view but a nice discussion to have before going to production. If team feels there is use case for FeedLog that we can't handle easily with Feed table alone, I would be happy to keep the existing design.

[welcome]

Thanks for opening your first issue in this project! If you haven't already, you can join our slack and join the #mobility-database channel to meet our awesome community. Come say hi 👋!

Welcome to the community and thank you for your engagement in open source! 🎉

[emmambd]

+1 to this simplification, particularly since we no longer anticipate as frequent updates to feed metadata when we stop switching URLs.

My only thought is that record_time may be unclear. Thoughts on updated_at instead? cc @qcdyx @cka-y

[cka-y]

+1
our goal with the Feed table was that a single Feed in the table is related to a stable_id. Since we changed the definition of the Feed, I think the logs aren't required anymore. We could just update the Feed directly.

Also +1 for updated_at instead of record_time. However, we will lose historical records on what the update was (ex: the name of the feed was changed). Is that something we'd be ok with @emmambd ?

[emmambd]

@cka-y Since we're still storing the historical versions of the feed itself (not its metadata) in v1/datasets/gtfs/{id} and the URL itself will no longer change for a feed, yes.

[emmambd]

@aronza After some discussion and MobilityData/mobility-database-catalogs#291, we no longer see a need for a feedlog or record. Plan currently is to

• Remove Feedlog table
• Remove feedlog endpoint

Users will get historical information on the dataset level, not about the feed.

Data Clinic is welcome to take this on!