Deleting User Data Ad-Hoc

Learn how to delete a user's data from your database when you're running the On-Premises API.

Before You Start

• For both MySQL steps and PostgresQL steps, we need access to the database, so ensure that you are connected to the database server or docker file if running database as a docker container.
• The input for both the function in PostgresQL and the MySQL stored procedure, require a big int, which is generally the phone number without any formatting and any special characters. For instance, the phone number "+1 365 555 9020" should be passed to the functions and procedures as "13655559020".

Using MySQL

For MySQL, we rely on stored procedure for handling the deletion of user data. Therefore, we will create a stored procedure that we store in one of the data stores, preferable contactStore. To do this, follow these steps:

1. You need to have access to the database server and connect to it. If using docker container for database, execute the following commands:

   docker ps -a // to get the full list of containers running
   docker exec -it <database-container> mysql -h <hostname> -u <username for db> -p <port number> //for mysql

2. Ensure the contactStore is selected by doing:

   use contactStore;

3. Now create the procedure in this store as follows:

   DELIMITER //
   CREATE PROCEDURE `deleteUserDataByPhoneNumber`(IN wa_id BIGINT)
   BEGIN
   DECLARE user_jid VARCHAR(50);
   DECLARE phone_number_ VARCHAR(50);
   
   SET phone_number_ = concat('+', wa_id);
   SET user_jid = concat(wa_id, '@s.whatsapp.net');
   
   DELETE FROM callbackStore.queue 
   WHERE
       key_remote_jid = user_jid;
   DELETE FROM messageStore.chats 
   WHERE
       key_remote_jid = user_jid
       OR group_creator = user_jid;
   DELETE FROM messageStore.group_participants_history 
   WHERE
       jid = user_jid;
   DELETE FROM messageStore.messages 
   WHERE
       key_remote_jid = user_jid
       OR remote_resource = user_jid;
   DELETE FROM messageStore.messages_pending_actions 
   WHERE
       key_remote_jid = user_jid
       OR remote_resource = user_jid;
   DELETE FROM messageStore.messages_receipt_log 
   WHERE
       key_remote_jid = user_jid;
   DELETE FROM messageStore.sender_key_distribution_status 
   WHERE
       jid = user_jid;
   DELETE FROM axolotlStore.identities 
   WHERE
       recipient_id = wa_id;
   DELETE FROM axolotlStore.message_base_key 
   WHERE
       msg_key_remote_jid = user_jid;
   DELETE FROM axolotlStore.sender_keys 
   WHERE
       sender_id = wa_id;
   DELETE FROM axolotlStore.sessions 
   WHERE
       recipient_id = wa_id;
   DELETE FROM contactStore.wa_contacts 
   WHERE
       jid = user_jid;
   DELETE FROM contactStore.wa_phones 
   WHERE
       jid = user_jid;
   END//

4. After this, we should have our store procedures created in the contactStore database. Confirm this by running:

   SHOW PROCEDURE STATUS WHERE Db = 'contactStore';

Now you have your procedure set up. In case you need to delete a user's data, you can just run the following command:

call contactStore.deleteUserDataByPhoneNumber('wa_id');

A successful response looks like this:

mysql> call contactStore.deleteUserDataByPhoneNumber(XXXXXXXXXXXX);
Query OK, 0 rows affected (0.01 sec)

Using Postgres

Since the version of Postgres we are using (10.x) does not support stored procedures, we rely on functions yo implement this logic:

1. Connect to the database server. For docker users you can do:

   docker ps -a // this will list all the current running containers
   docker exec -it <database-container> psql -h <hostname> -U <username for db> -p <port-numer>   //for postgres

2. Connect to the contactStore database by doing:

   connect contactStore;

   Postgres doesn't allow us to easily run queries that operate on different databases. So, we have to rely on foreign data wrappers to accomplish this. More information on this can be found in the following:
   • Postgres: How do I perform queries using multiple databases?
   • postgres_fdw: Details on how to create the the foreign data wrappers needed to access data stored in other databases.
3. To create the data wrappers follow the guide from the postgres_fdw doc. The steps are:
   • Install postgres_fdw extension
   • Create a foreign server
   • Create user mappings
   • Create the foreign tables
4. Run this query to complete step 3 for all of the required databases:

   CREATE EXTENSION IF NOT EXISTS postgres_fdw;
   -- Create connection to the callback Store
   CREATE SERVER callbackStore
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'db-hostname', port 'db-port number', dbname 'callbackStore');
   CREATE USER MAPPING FOR root
   SERVER callbackStore
   OPTIONS (user 'root', password 'testpass');
   CREATE FOREIGN TABLE queue (
   key_remote_jid character varying(256) COLLATE pg_catalog."default" NOT NULL
   )
   SERVER callbackStore
   OPTIONS (schema_name 'public', table_name 'queue');
   
   -- Create connection to the messages store
   CREATE SERVER messageStore
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'db-hostname', port 'db-port number', dbname 'messageStore');
   CREATE USER MAPPING FOR root
   SERVER messageStore
   OPTIONS (user 'root', password 'testpass');
   -- message store tables
   CREATE FOREIGN TABLE chats (
   key_remote_jid character varying(256) COLLATE pg_catalog."default" NOT NULL,
   group_creator text COLLATE pg_catalog."default"
   )
   SERVER messageStore
   OPTIONS (schema_name 'public', table_name 'chats');
   CREATE FOREIGN TABLE group_participants_history (
   jid character varying(256) COLLATE pg_catalog."default" NOT NULL
   )
   SERVER messageStore
   OPTIONS (schema_name 'public', table_name 'group_participants_history');
   CREATE FOREIGN TABLE messages (
   key_remote_jid character varying(256) COLLATE pg_catalog."default" NOT NULL,
   remote_resource text COLLATE pg_catalog."default"
   )
   SERVER messageStore
   OPTIONS (schema_name 'public', table_name 'messages');
   CREATE FOREIGN TABLE messages_receipt_log (
   key_remote_jid character varying(256) COLLATE pg_catalog."default" NOT NULL
   )
   SERVER messageStore
   OPTIONS (schema_name 'public', table_name 'messages_receipt_log');
   CREATE FOREIGN TABLE messages_pending_actions (
   key_remote_jid character varying(256) COLLATE pg_catalog."default" NOT NULL,
   remote_resource text COLLATE pg_catalog."default"
   )
   SERVER messageStore
   OPTIONS (schema_name 'public', table_name 'messages_pending_actions');
   CREATE FOREIGN TABLE sender_key_distribution_status (
   jid character varying(256) COLLATE pg_catalog."default" NOT NULL
   )
   SERVER messageStore
   OPTIONS (schema_name 'public', table_name 'sender_key_distribution_status');
   
   -- Create connection to the axolotlStore
   CREATE SERVER axolotlStore
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'db-hostname', port 'db-port number', dbname 'axolotlStore');
   CREATE USER MAPPING FOR root
   SERVER axolotlStore
   OPTIONS (user 'root', password 'testpass');
   CREATE FOREIGN TABLE identities (
   recipient_id bigint
   )
   SERVER axolotlStore
   OPTIONS (schema_name 'public', table_name 'identities');
   
   CREATE FOREIGN TABLE message_base_key (
   msg_key_remote_jid character varying(256) COLLATE pg_catalog."default" NOT NULL
   )
   SERVER axolotlStore
   OPTIONS (schema_name 'public', table_name 'message_base_key');
   
   CREATE FOREIGN TABLE sessions (
   recipient_id bigint
   )
   SERVER axolotlStore
   OPTIONS (schema_name 'public', table_name 'sessions');

5. Next, create the function below:

   CREATE OR REPLACE FUNCTION public."deleteUserDataByPhoneNumber"(
       wa_id bigint)
       RETURNS void
       LANGUAGE 'plpgsql'
       COST 100
       VOLATILE PARALLEL UNSAFE
   AS $BODY$
   DECLARE
       user_jid VARCHAR(56);   
       phone_number_ VARCHAR(56);
   BEGIN
       SELECT INTO phone_number_ concat('+', wa_id);
       SELECT jid into user_jid FROM wa_phones where phone_number=phone_number_;
       IF NOT FOUND THEN
           SELECT INTO user_jid concat(wa_id, '@s.whatsapp.net');
       END IF;
       DELETE FROM queue 
       WHERE
           key_remote_jid = user_jid;
       DELETE FROM chats 
       WHERE
           key_remote_jid = user_jid
           OR group_creator = user_jid;
       DELETE FROM group_participants_history 
       WHERE
           jid = user_jid;
       DELETE FROM messages 
       WHERE
           key_remote_jid = user_jid
           OR remote_resource = user_jid;
       DELETE FROM messages_pending_actions 
       WHERE
           key_remote_jid = user_jid
           OR remote_resource = user_jid;
       DELETE FROM messages_receipt_log 
       WHERE
           key_remote_jid = user_jid;
       DELETE FROM sender_key_distribution_status 
       WHERE
           jid = user_jid;
       DELETE FROM identities 
       WHERE
           recipient_id = wa_id;
       DELETE FROM message_base_key 
       WHERE
           msg_key_remote_jid = user_jid;
       DELETE FROM sessions 
       WHERE
           recipient_id = wa_id;
       DELETE FROM wa_contacts 
       WHERE
           jid = user_jid;
       DELETE FROM wa_phones 
       WHERE
           jid = user_jid;
   END
   $BODY$;

6. Make the root user or the main db user you are using the owner of this function:

   ALTER FUNCTION public."deleteUserDataByPhoneNumber"(bigint) OWNER TO root; -- fill free to replace this with the correct user

Now you can run the function if you need to delete a user's data:

SELECT public."deleteUserDataByPhoneNumber"(wa-id); //wa-id is the phone number with the conditions stated above

If you also want to delete the foreign data handlers at the end you can run:

DROP USER MAPPING IF EXISTS FOR root SERVER local_server;
DROP SERVER IF EXISTS local_server CASCADE;
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;