Self Service Reports : Add a switch to enable metabase setup , button to check sync status and refresh tables [DMP 2024]

[ombhardwajj]

What

A switch which would trigger metabase to setup , the below listed items get added to metabase :

• Database
• Collection
• Permissons Group

Moreover permissions would be assigned like this:

• Only the group created would have unrestricted access to the Database.
  All other users(except admin) have no access (no self service)
• The group createwd would have Curate access to colelction and
  All other users (except admin) would have no access

Acceptance Criteria:

• A switch to enable metabase setup
• A button to check sync status
• A button to refresh tables

Related Issue : avniproject/avni-product#1562

[mahalakshme]

yet to be reviewed and merged: avniproject/avni-server#789

[mahalakshme]

Need:

To test, METABASE_API_URL and METABASE_API_KEY need to be configured for Avni staging and prerelease

Issue:

env: staging,
On clicking on the toggle, 'Refresh tables' in webapp -> Reports -> Self-service reports, shows the below stacktrace:

2024-12-02 07:23:09.865  INFO 2267065 --- [http-nio-8021-exec-7] o.a.s.f.security.AuthenticationFilter    : Received request POST /api/metabase/setup-toggle?enabled=true
2024-12-02 07:23:09.938 ERROR 2267065 --- [http-nio-8021-exec-7] org.avni.server.util.BugsnagReporter     : 404 Not Found

org.springframework.web.client.HttpClientErrorException: 404 Not Found
	at org.springframework.web.client.DefaultResponseErrorHandler.handleError(DefaultResponseErrorHandler.java:94)
	at org.springframework.web.client.DefaultResponseErrorHandler.handleError(DefaultResponseErrorHandler.java:79)
	at org.springframework.web.client.ResponseErrorHandler.handleError(ResponseErrorHandler.java:63)
	at org.springframework.web.client.RestTemplate.handleResponse(RestTemplate.java:777)
	at org.springframework.web.client.RestTemplate.doExecute(RestTemplate.java:730)
	at org.springframework.web.client.RestTemplate.execute(RestTemplate.java:686)
	at org.springframework.web.client.RestTemplate.exchange(RestTemplate.java:602)
	at org.avni.server.dao.metabase.MetabaseConnector.getForObject(MetabaseConnector.java:53)
	at org.avni.server.dao.metabase.DatabaseRepository.getDatabaseByName(DatabaseRepository.java:34)
	at org.avni.server.dao.metabase.DatabaseRepository$$FastClassBySpringCGLIB$$14e36dca.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
	at org.avni.server.dao.metabase.DatabaseRepository$$EnhancerBySpringCGLIB$$8ed27c18.getDatabaseByName(<generated>)
	at org.avni.server.service.metabase.MetabaseService.setupMetabase(MetabaseService.java:45)
	at org.avni.server.web.MetabaseController.toggleSetupMetabase(MetabaseController.java:49)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Possible solution:

API_URL can be pointed to EC2 - 'Reporting Metabase Green' and accordingly API_KEY can be configured.

[himeshr]

Pending task

• Generate METABASE_API_KEY, with Administrators group access on Prod metabase and specify the same in prod-secret

[himeshr]

Dev testing, validated for IPHSCD org in staging environment locally, with reporting-green metabase.

[1t5j0y]

Can we avoid the hardcoded ORG_DB_USER_PASSWORD in code? There are a few other existing instances of this so we could do this via a separate issue but would be good if we could pick up the default password from env vars at least. End state solution should probably to get the password from the UI.

[himeshr]

Can we avoid the hardcoded ORG_DB_USER_PASSWORD in code? There are a few other existing instances of this so we could do this via a separate issue but would be good if we could pick up the default password from env vars at least. End state solution should probably to get the password from the UI.

Ya, i had the same thought, pushed it through to reduce the effort spent on this card..

• Ideally, have default value from env var
• override from UI input

@mahalakshme should i do this change in this card.?

[mahalakshme]

@himeshr isn't this a urgent issue: we should ve not pushed the password no? Should we force push? Or may be change the db password. I think we can do it as part of this card itself. I am fine with separate also. But it feels like fixing this is urgent to me.

[himeshr]

@himeshr isn't this a urgent issue: we should ve not pushed the password no? Should we force push? Or may be change the db password. I think we can do it as part of this card itself. I am fine with separate also. But it feels like fixing this is urgent to me.

Its not urgent, we have create_db_user command litererd all over the repo

[1t5j0y]

created avniproject/avni-server#826 to address the hard coding issue. Moving this to QA ready.

[dinesh2096]

When we try to enable the metabase setup getting the error

Reference video :

Screen.Recording.2024-12-09.at.5.24.01.PM.mov

[himeshr]

PermissionsError_stacktrace.txt
Self note, metabase error stacktrace encountered on first org Metabase sync trigger.

[himeshr]

Known issues with Metabase self-service

• First time enabling Metabase Setup-toggle always fails, second attempt after a few minutes succeeds => Cause: while performing fingerprint analysis for old table in Public schema

2024-12-11 13:09:24,009 WARN sync.util :: Error fingerprinting Table 67487 ''public.mother_program_encounters''
clojure.lang.ExceptionInfo: Error executing query: ERROR: function coded_obs(json) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

• Tables used for creation of questions are not filtered by org_schema

Sometimes public.individual is used instead of org_schema_name.individual resulting in data of all SubjectTypes shown in Individual table Question instead of just the SubjectType "Individual".

• 'Setup-toggle' or 'Refresh Tables' actions will timeout for some organisations, when the operation takes too long to complete

[dinesh2096]

• Refreshing the page status is not retained
• If the user is non admin and try to access the report user is displayed with the oops error
• Questions are not created, when org ETL schema does not have Address table
• We need to add more details on the report while creating the metabase.Currently we have only address,media and sync telemetry.
• If the individual is not added then we are getting the empty report as my suggestion if the user want the report regarding the address creation it should get created once the location is created even tough the individual data is not created.

Reference Image :

[mahalakshme]

We will address the above issues as part of separate cards in consecutive release.