Being able to identify media not uploaded

[mahalakshme]

https://avni.freshdesk.com/a/tickets/3932

Need:

Currently we don't have a mechanism to know if media URLs synced have been successfully uploaded to s3. This is important considering the frequent media anomalies we have encountered so far like media URLs present but media not present in S3, thumbnails not generated, etc., This will help us to establish a monitoring mechanism before user reports it. When there is no automatic way to determine we miss to check it.

We are not doing the same to make sure data(other than images) are synced because of the below reasons:

• With data it is straightforward - eg: when some mandatory fields are alone not updated we get to know easily from bugsnag. Here, when the URLs are there and when the media is missing, there is no way for us to know currently.
• Say when no data has got saved(synced), there are reports maintained setup for the organisations to track daily statistics(like registrations and due visits done) via which we will come to know. But in media, even if we have reports now based on database entries it will appear as if the media is synced, but actually the images are missing in S3.

Context:

Media anomaly: One of the below:

• Invalid url in db
• Valid url in db but not present in S3 bucket
• Present in S3 bucket but thumbnail not generated

AC:

• Just automate the process something like mentioned here: https://avni.freshdesk.com/support/solutions/articles/36000504548-comparing-image-files-in-s3-bucket-and-media-table. The process can be changed based on whatever developers feels easy or better.
• Need to run once a week in a background job and dump the results in a table. Sample column names for the table: column_referencing_media_table(media.id), Valid url (Y/N), Present in S3 (Y/N), Thumbnail generated (Y/N), audit_info
• Anomalies need to be determined for invalid URLs, actual image missing in s3 bucket, thumbnail missing in s3 bucket
• Every week after the job is run, the table should ve the updated information. Should not have information on any anomalies fixed in the past 1 week, incase we do incremental update of tables.

Post deployment steps

• Link the table to a report such that when a new anomaly is created. In the report have a threshold count based on existing anomalies count and when this increases, only then raise an alert and consequently a ticket on supprt.
• For now enable for Goonj on production alone since they use media heavily and we will get to know the media issues via them anyways.

Tech details if needed for reference: Ignore if not relevant

• To get s3 contents after a particular date: Link
• to check for presence of media and thumbnail on s3: Link
• to fetch the entire list: Link
• Code to be in ETL repo

Old:

AC:

• In ETL when creating each new entry in media table check in s3 for media anomaly
• table entry/update in media_anomaly table with columns: column_referencing_media_table, Valid url (Y/N), Present in S3 (Y/N), Thumbnail generated (Y/N), audit_info
• Once in a week update media_anomaly table
• To be done for all orgs for which ETL is enabled

Out of scope:

To check if there are media anomalies for the existing data in the media table

Added above based on suggestions from here

Old: Ignore

Use cases

• Determine current list of media anomalies for an org
• Check if action taken has fixed an anomaly

Definitions

Media anomaly: One of the below:

• Invalid url in db
• Valid url in db but not present in S3 bucket
• Present in S3 bucket but thumbnail not generated

AC: (Based on the suggestions from here)

If never run for org,

• Use s3 listObjectsV2 api with prefix as org s3 prefix to fetch all file names (with pagination) for the org in s3 and store temporarily.
• If ETL is enabled for org, compare media table entries with list of filenames retrieved in step 1 to detect missing images. Store anomalies in db. Else return 501 HTTP status code.
• Check list output from s3 for missing thumbnails and store anomalies in db

If previously run for org,

• For each previously stored anomaly + media last_modified_date_time (from ETL or from actual tx table) > last anomaly job run time, use s3 headObject api to check for presence of media and thumbnail on s3 and update anomalies.

To get s3 contents after a particular date:

• Strategy

Input:

• In /admin/organisationDetails page, add a toggle saying 'Determine media anomalies'
• If enabled for an org, run the bg job once in a day
• Once disabled the bg job should not run

Output:

• table entry/update in media_anomaly table with columns: column_referencing_media_table, Valid url (Y/N), Present in S3 (Y/N), Thumbnail generated (Y/N), audit_info

Input:

• from scratch - some ETL running will take more time
• himesh wrote aws shell script
• monitoring job - regularly running -
• check when sync itself
• thumbnails - different issue

Input issues:

• We need a easier automatic monitoring mechanism that sends alerts
• checking in mobile db for issue in mobile sync - doesn't seem reliable
• what is the concern if ETL takes longer time
• support team or scheduled job - weekend

[himeshr]

Discussion point for later implementation:

• Find duplicate media urls for same entity_concept

[himeshr]

Additionally introudced isHavingDuplicates column to mediaAnalysis table, which specifies if duplicates exist in the media table, the count isn't stored as a separate column but can be determined by joining with media table.

select * from media m join media_analysis ma on m.uuid = ma.uuid and m.image_url = ma.image_url;
-- where m.uuid  = '60b99f84-37cf-4873-b1a6-5e3f7f88af3b';

[himeshr]

Sql query to use in setup alert on metabase when there are new anomalies.

-- set role goonj;
with counts as (select count(*) AS Total,
    sum(case when is_valid_url = false then 1 else 0 end) AS InvalidURLsCount,
    sum(case when is_present_in_storage =  false then 1 else 0 end) AS MissingMediaCount,
    sum(case when is_thumbnail_generated =  false then 1 else 0 end) AS MissingThumbnailCount,
    sum(case when is_having_duplicates =  true then 1 else 0 end) AS MediaWhichHasAtleastOneDuplicateCount
from media_analysis) select * from counts
where Total > 0 and (InvalidURLsCount > 0 or MissingMediaCount > 138 or MissingThumbnailCount > 372 or MediaWhichHasAtleastOneDuplicateCount > 291);

[himeshr]

For reference and dev testing, use postman collection for triggering ETL Jobs for Sync and/or MediaAnalysis.

AVNI ETL.postman_collection.json

[1t5j0y]

Add sensible values for staging/prerelease/prod for AVNI_MEDIA_ANALYSIS_JOB_REPEAT_INTERVAL in avni-infra. Also isn't the default of 2 minutes too low (if env var not configured)?

MediaAnalysisTableRegenerateAction.process

• Create different MediaDTO in to have only the fields we are interested in for analysis (url and uuid) to reduce memory footprint?
• We should be able to get rid of some of the local list objects as well to reduce memory footprint i.e. generate Map from the unpartitioned List instead of via intermediate Lists.

[himeshr]

@1t5j0y Absorbed the review comments