Migration to SQL database

[jacob-8]

We would like to move our backend from Firebase to Supabase.

The reason for choosing Firebase in the beginning is because it was a complete backend that was very easy to setup and offered everything needed to build an initial dictionary platform. Part of Firebase' simplicity is its NoSQL database. It is easy to setup and works great when all the data for an entry can be contained in a single document. As our feature set grows, it's becoming extremely painful and prohibitive however as the friction of adding data relationships is extremely high, bug prone and hard to maintain. To illustrate: Firebase makes it easy to have 1 entry with 1 sense with 1 photo with 1 audio file and 1 speaker whose name never changes but we would like to have 1 entry with 2 senses with 5 photos, 2 audio files (each with 2 different speakers whose name spellings we can fix whenever), 1 video file, and a history of changes made to all that related data. There's more to, like example sentences in which this entry is found and related entries but that's enough to illustrate.

We are past due for using a relational database (SQL) which allows us to have relational data and make data changes, updates, and refactorings easy.

Features we have discussed and would like to add in the short term but have too much friction with NoSQL (Firebase) to just add:

• entry change history (impossible with Firebase)
• multiple senses (cumbersome data structure, complicated saves)
• multiple media (cumbersome data structure, complicated saves)
• speaker name changes (duplicated data requires changing a speaker name everywhere used)
• entry relationships

Additionally, our current search via Algolia while fast, powerful and generous of them is also a blocker to moving forward. With the number of operations given us per month, any data structure changes that we want to make require re-indexing which is no longer possible with the growing number of dictionary entries we have without risking killing our search feature until we get a rep to help us unlock it.

Some of the above relational challenges are why we sometimes don't have very good support for speaker names (like how you can't change them in the table view). The connections are possible to make in Firebase but the friction is high and we haven't made all the connections.

The following features are a short list that become possible or much easier with a SQL database like Supabase offers:

• 5 features listed above
• geo-search
• offline dictionary usage and editing (not media, just text)
• offline search
• custom tags
• custom search features targeting linguistics like you could find in a phoneme analysis tool, like all words containing a certain vowel word medially

The Supabase platform is entirely open-source with a large contributor base which has the benefit that bug fixes and new features are released much more rapidly than Firebase. Firebase is primarily focused on Android and iOS apps and their rate of new features for web users is much slower. That's fine for many as they have a stable product that works good for many use cases. But there are a number of useful features beyond SQL that Supabase offers which Firebase has not put work into building.

Current plan:

• Save additional senses to Supabase
• Migrate users into Supabase and prepare all needed peices to use Supabase auth once all Firebase saves are migrated or done via the backend
• Prepare database tables and relationships for entries/senses/sentences/texts with photos/audio/videos/speakers
• Sentences in senses example sentences for additional senses #414
• Local search (unlocks the ability to work on offline searching/reading of text in the future)
• Restore custom sort for Onondaga
• Run all Firebase operations through page load functions, decouples components from db for easier mocking and switching db
• Move function for dev/admin toggle to kit endpoint
• Migrate entries to Supabase and load entries from Supabase migration #486
  • pull down whole db and create interface
  • save data to Supabase
  • create hourly materialized view for entries and use this for local search data
  • pull hour's updates and current changes into the local entry set
  • run local search from Supabase entries
• Save to Supabase
  • save entry data directly to Supabase
  • Stop using convert_and_expand_entry
  • Turn off Algolia
• Update dictionary Use transformers in import files #312 for importing to Supabase (unfreeze new dictionary imports)
• dictionary, about, grammar edits save via endpoint
• Transition to Supabase Auth
• Display all example sentences
• Display change history
• Add custom tags feature
• Upload images via server endpoint
• Upload audio via server endpoint
• Upload video via server endpoint (check timeouts)
• Save additional media to Supabase (save via server endpoint, load client-side)
• Add geo-search from Supabase data (map view for entries), decide when labels for points/regions is happening
• Add audio to example sentences
• offline editing (not media, just text)

That will bring us to a state where we have as good offline support for searching, editing text; a robust database that supports complex relationships between entries, senses, media, speakers, stories and other entries. Our media will remain in Google Cloud, our data will all be in Supabase' Postgres database. Auth, triggers, and other features are also in Supabase. Google stored media can easily be proxied in locations where Google is blocked. Our ability to have a good testing dev database will be back (we had to cripple that one as we approached Algolia's limits).