This PR is a continuation of #54643, which was mis-closed by the Git LFS migration (#55759).

Previous Comments:

• snickell commented - Another way of looking at this is not just where we use "firebase" in method names, but where do we actually use the firebase(TM) library. That's limited to two files:

• snickell commented - This is the one function used outside of apps/storage/firebaseMetadata.js that I wonder if we could easily handle through SQL, it feels like a firebase "subscription".... is it?

export function onColumnsChange(database, tableName, callback) {
  getColumnsRef(database, tableName).on('value', snapshot => {
    const columnsData = snapshot.val() || {};
    const columnNames = _.values(columnsData).map(column => column.columnName);
    callback(columnNames);
  });
}

In applab.js this is just used to update data preview pages when columns are renamed, seems like that doesn't necessitate a full firebase-level DB.

• snickell commented -

The only thing that looks like it might need subscription like functionality is onRecordEvent:

onRecordEvent("MyNewTable", function(record, eventType) {
  console.log(eventType);
  console.log(record);
});

• All of our APIs update one record at a time, using the record ID, so its easy on the backend to know exactly what change message to publish
• onRecordEvent gives you the JSON record contents, including the record.id, and whether it was a create, update, or delete event. Events are all about ONE ROW.
• snickell commented - Idea: could we /just/ use redis? Like actually use that as our DB?
• snickell commented - Discussing with @cnbrenci we naively might consider four approaches:

1. Back our data feature with MySQL and deprecate support for onRecordEvent (see question 1)
2. Back our data feature with MySQL, applab projects hold an open websocket (or poll???), and use a redis pubsub channel to broadcast (tableId, recordId) change events (or potentially the whole row value, either way), and then rails backend to the websocket sends the new row data to the client along with a changeEvent
3. Like store sprite.value again, use it properly in setSpriteSize #2, but back our data feature directly with redis, skip the whole mysql in the middle bit. Like aurora for mysql, AWS has a hosted durable redis option, and the pricing is very reasonable.... something like $600/mo would get us 12gbps of redis bandwidth.

Questions to answer:

1. How actively used by student projects is onRecordEvent? How many calls to onRecordEvent do we see per day? How many projects use it? TODO: download all student code that uses onRecordEvent and skim through them, see how frequent it is overall, how its generally used, etc
2. How many concurrent applab users do we see? Can we hold that many websockets open with our existing rails infra? If we don't have an approach to holding lots websockets open, is there an approach to this we can implement? How hard is it?

• snickell commented - Per first convo with product, we may be willing to just deprecate onRecordEvent, breaking existing apps that use it. Conventional wisdom is that its mainly used by chat apps that are technically not allowed under our terms of service anyway, which might explain some of the high cost issues with firebase.

I'd personally like to see how widely used the onRecordEvent block is in Applab projects that have been used/run/loaded in, say, the last 3 months. Ideally used would mean loaded even just to use the student applab app as a user, vs developed on, but if we can't get "when was this last run by anyone", we could use "code was changed in the last 6 months" as a proxy.

From this we can compute: "percent of active applab projects using the onRecord event block", which can then inform a decision to just deprecate onRecordEvent, making option #1 above viable, and not having to add a need to hold constant websockets open to our rails server (expensive and possibly not scalable).

• snickell commented - Step Employee Engagement basics: Create code.org/company-profiles/* landing page #1 then would be to implement a script to find "percent of active applab projects using the onRecord event block". Maybe as a first step, we work on getting access to S3, looking up paths to relevant main.json student saves. Say we start with implementing blockFinder / block_finder.rb. Then you could do, say:

require_relative './block_finder'
require 'active_support/time'
blockFinder(blockName: 'onRecord', game: 'applab', lastActiveAfter: 90.days.ago.to_date)

@cnbrenci and I tracked down where applab student source is being saved, and its in main.json files in S3, e.g.: https://s3.console.aws.amazon.com/s3/object/cdo-v3-sources?region=us-east-1&prefix=sources/50000063/131542020/main.json

• snickell commented - Say we start by writing a script to determine percent_active_applabs_using_on_record_event():

def sample_random_student_source(game, last_active_after)
  # TO IMPLEMENT
end

def source_contains_block(block_type)
  # TO IMPLEMENT
end

def days_to_seconds (days)
  return days * 24 * 60 * 60
end

def percent_applabs_actively_using_on_record_event()
  num_samples = 1000
  num_samples_using = 0
  for i in O.num_samples do
    source = sample_random_student_source(game: applab, last_active_after: Time.now - days_to_seconds(90))
    if source_contains_block(source)
      nun_samples_using += 1
    end
    return num_samples_using / num_samples
  end

  puts "Percent of recent applab projects using 'onRecordEvent':"
  puts(percent_applabs_actively_using_on_record_event())
end

@cnbrenci and I tracked down where applab student source is being saved, and its in main.json files in S3, e.g.:
https://s3.console.aws.amazon.com/s3/object/cdo-v3-sources?region=us-east-
1&prefix=sources/50000063/131542020/main.json

• implement sample_random_student_source()
  • research (then implement) how to get list of student applab sources active in last N days using rails DB models in dashboard/app/models
  • research (then implement) how to get S3 path to cdo-v3-sources bucket for the main.json given applab source (using more rails DB models I think?)
  • fetch relevant main.json path from S3, possibly using lib/cdo/aws/s3.rb
• implement source_contains_block()
  • parse main.json, determine if it contains the 'onRecordEvent' block
• get relevant S3 credentials, and run sampler on real student source code
• snickell commented - The "Projects" table (project.rb, Project model) has all the data we need:
• The projects table includes both updated_at and project_type="applab"
• the ID column of the Projects table is the "storage_app_id", which is the SECOND number in the S3 paths
• the storage_id column of the projects table is the FIRST number in the S3 paths

So we can go:

project = Project.where(project_type: 'applab', updated_at: 30.days.ago...).take!
s3_path_prefix = "sources" # or in dev: "sources_development"
s3_path = "#{s3_path_prefix}/#{project.storage_id}/#{project.id}/main.json"
s3_bucket = "cdo-v3-sources"
source_code = AWS::S3.download_from_bucket(‘cdo-v3-sources’, s3_path)

Now we need to figure out how to do download_from_s3()

• cnbrenci commented - > oject = Project.where(project_type: 'applab', updated_at: 30.days.ago...).take!

s3_path_prefix = "sources" # or in dev: "sources_development"
s3_path = "#{s3_path_prefix}/#{project.storage_id}/#{project.id}/main.json"
s3_bucket = "cdo-v3-sources"
source_code = AWS::S3.download_from_bucket(‘cdo-v3-sources’, s3_path)

Confirmed that this does work (I successfully downloaded the source)

• cnbrenci commented - open Q to look into: can Project.state be used to determine if the project is active rather than updated time? (I see in my dev env all my projects are state='active')
• cnbrenci commented - First pass on full script after everything we figured out yesterday.

The last step would be we need to run it against the prod dashboard DB with the "sources" s3_path_prefix instead of "sources_development"

#!/usr/bin/env ruby
require_relative('../config/environment')

# Gets a sampling of applab source code from s3
# computes the percentage of those sources that use the onRecordEvent block

def percent_projects_actively_using_block(project_type, block_type, last_active_after, sample_size)
  projects = Project.where(project_type: project_type, updated_at: last_active_after...).take(sample_size)
  s3_path_prefix = "sources_development"
  # s3_path_prefix = "sources" # or in dev: "sources_development"
  s3_bucket = "cdo-v3-sources"

  projects_have_block = projects.map do |project|
    s3_path = "#{s3_path_prefix}/#{project.storage_id}/#{project.id}/main.json"
    file_contents = AWS::S3.download_from_bucket(s3_bucket, s3_path)
    contains_block = JSON.parse(file_contents)["source"].include?("onRecordEvent(")
    contains_block
  end

  percent_using_block = (projects_have_block.count(true).to_f / projects_have_block.size) * 100
  puts "Percentage of #{project_type} projects using the #{block_type} block is #{percent_using_block}"
end

def percent_applabs_actively_using_on_record_event
  num_samples = 1000
  timeframe = 30.days.ago
  percent_projects_actively_using_block("applab", "onRecordEvent", timeframe, num_samples)
end

puts "Percent of recent applab projects using 'onRecordEvent':"
puts(percent_applabs_actively_using_on_record_event)

• snickell commented - Nice!! Curious what percent we're gonna end up with 🤞 when we run on production.

• We should probably use random samples rather than whatever MySQL happens to give for a default ordering. I generally avoid using DB-specific SQL, but since we're just a one-off script.... Project.where(...).order('RANDOM()').take(sample_size) (https://www.geeksforgeeks.org/sql-select-random/). This does require the DB to internally walk the entire set of matching rows, join with a random number each, and then sort N rows. So it would probably suck if we had a million results.... but in reality: I'm betting this will work in our case if "applabs updated in the last N days" isn't too huge.

• In reality on prod we might want to do 10k samples instead of 1k since I'm guessing we're going to get a value thats less than 1%.

• Thinking 90 days might be a good date range

• I think we should save each file_contents to a .json file, and have a copy of those in a tarball or whatever, because the next question after we get a percentage is probably for you and I to pick a dozen or so "uses onRecord" code bits, and loosely read them to find out WHAT its being used for (notably: "is it a chat app?" since we don't consider nixing things against our TOS as a deprecation)

I guess a next step is figuring out how/where we can run this to have production DB creds (preferably read-only) available. One option, which might actually be simplest, is to run this on our production-console instance.

• snickell commented - We sampled the prod DB to find instances of the onRecordEvent block being used in applab projects. Where we found instances, we read the code to see if they were broken or TOS violations.

TL;DR: 0.002% of applab projects (that aren't stubs or chat apps) created in the last 2 months use the onRecordEvent block, with a sample size of 100k.

1. Sampling applab projects created in the last 2 months, 6 out of 100000 applab projects created in the last 2 months used onRecordEvent. We read the source code for the 6 projects, and found 4 of the 6 were chat apps or non-functional stub code. Thus only 2 out of 100000 or 0.002% of this sample would be of concern.

   • "who needs assistance" crud app, 1200LOC
   • daily habit tracker, 160LOC (single-user: could be easily written w/o onRecordEvent)
   • 3x non-functional stub code
   • 1x chat app

2. In case onRecordEvent was more used for older projects with active current usage, we sampled older applab projects that were updated_at the last 3 months, 7 out of 9000 older projects used onRecordEvent. We read the source code for the 7 projects, and found 5 out of 7 where either chat apps or non-functional stub code. Thus only 2 out of 9000 or 0.02% of this sample would be of concern if we remove the onRecordEvent block.

   • 4x chat app
   • 1x non-functional stub code
   • "multiplayer farming simulator", 600LOC
   • math game (used for high score table), 200LOC

Also worth noting that none of the apps appear to be finished to an extent that I think its likely anyone is actively using them in a multiplayer context, including the chat apps.

• snickell commented - Product noticed (TY @moneppo) we hadn't considered that students use different blocks throughout the year as they complete different curriculum.

So we went back to the sampling, and grabbed 105k samples covering all of 2022...

We scanned 2022 (samples from each month), and we found 3 "valid" apps in 2022 out of 105k samples (=0.003%, about the same as the "last two months" sample). "Valid" meant apps that weren't either chat apps, or stub code (e.g. "drag some blocks out"). We found 11 chat apps out of the 105k, so chat apps were 0.01%, though some of the 11 are variations on the same "WUT by Adam" chat.

So basically, looks like our rate of apps that use the onRecordEvent block is ~~0.002 - 0.003% (pretty in the noise given how few examples there were even with 100k samples each time)

• snickell commented - We couldn't figure out how people were writing gamelab based chat apps. I found the "secret" gamelab APIs thanks to Hannah Nees giving me a great sample of "banned chat apps", including samples from Gamelab. These use "setKeyValue" and "getKeyValue" functions.... which aren't exposed as blocks (?!?).

Relevant code is here: https://github.com/code-dot-org/code-dot-org/blob/e6905b0c899383ca2c7e43885f847cedf84478b2/apps/src/p5lab/gamelab/commands.js/#L25-L47

The PR that added them is here: #11951

@davidsbailey any chance you remember why we were adding these APIs without adding them as blocks?

• davidsbailey commented - > @davidsbailey any chance you remember why we were adding these APIs without adding them as blocks?

no, sadly I do not think I was involved in this decision.

• snickell commented - - Next we should start implementing more methods from the firebase interface we extracted, and get corresponding tests passing.
• We should also consider adding tests that actually create student source using the data blocks, and thereby test end to end, from JS api through the Rails controller. I think these tests, while a little more bulky, will actually catch much more and more helpful potential breakages in the future.

Previous Reviews: