# How I Built a Twitter App for the APEX & ORDS Community

# Introduction

> Note (18-MAR-2023): When Twitter announced they were going to charge for using their APIs, I closed down the Twitter App that I mention in this post. Aside from this, the content remains relevant.

This post will describe the Web App I built to track Twitter activity for Oracle Application Express (APEX) and Oracle REST Data Services (ORDS). The goal of the App is to feature developers that contribute to these communities via Twitter and also to understand better how the community uses and responds to Twitter content.

🎁 The bonus for me is that this project allowed me to use the development tools I love the most to help the developer community.

🔔 You will learn how to set up a Twitter Developer account, call several Twitter APIs via Postman, and see the inner workings of the App itself. I have also made the code available from this [GitHub Repositiory](https://github.com/cloudnueva/cn_twitter).

# Demonstration

Let's start with a brief screen recording of the App.

![Oracle_APEX_Twitter_App_ScreenRecording.gif](https://cdn.hashnode.com/res/hashnode/image/upload/v1661294142856/YmCseeFdS.gif align="left")

# Architecture

This section describes the software the App was developed with and the hardware on which it is running.

## Software

The App was built using [Oracle Application Express](https://apex.oracle.com/en/) (APEX) version 22.1 running on Oracle Autonomous Database and [Oracle REST Data Services](https://www.oracle.com/database/sqldeveloper/technologies/db-actions/) Version 22.2.

> Oracle APEX is a low-code development platform that enables you to build scalable, secure enterprise apps with world-class features that can be deployed anywhere. Using APEX, developers can quickly develop and deploy compelling apps that solve real problems and provide immediate value.

## Hardware

The App is running on the Oracle Cloud Infrastructure (OCI) [Free Tier](https://www.oracle.com/cloud/free/) Autonomous Database Service at the cost of 💲0 per month. Read my [post](https://blog.cloudnueva.com/production-ready-apex-oci-free) for more details on this architecture.

# Design

In this section, I will focus on some of the critical design elements for the App.

## Mobile First

From the beginning, I wanted the App to run just as well on mobile as on desktop. APEX's out-of-the-box Progressive Web App (PWA) and Responsive UI capabilities made this possible without any additional coding on my part.

## Performance

Even though the Tweet and Tweeter data is being fetched from Twitter over REST, I wanted to ensure the App was fast. I did this by caching much of the Tweet-related data (Tweets, Re-Tweets, Quoted Tweets) in tables on the Oracle database in which APEX runs.

Local table caching was made possible using [APEX Automations](https://blog.cloudnueva.com/apex-automations), which allowed me to quickly create an hourly schedule to capture the latest Tweet and Tweeter information.

Caching data locally makes searches and charting super fast. Detailed information about a user's profile is fetched over REST in real-time if the Tweeter profile has not already been captured.

## Data Model

The data model is simple and consists of four tables

* CNDEMO\_TWT\_CAPTURE
    
    * Stores search criteria used by the Tweet Capture Process
        
* CNDEMO\_TWT\_TWEETS
    
    * Stores Tweets and Re-Tweets captured by the Tweet Capture Process
        
* CNDEMO\_TWT\_RELS
    
    * Stores relationships between tweets and the Search Criteria. This handles the scenario where a Tweet could belong to more than one set of search/capture criteria
        
* CNDEMO\_TWT\_USERS
    
    * Stores user profile information captured by the User Capture Process
        

## Flexibility

The table `CNDEMO_TWT_CAPTURE` stores the hashtags and @ mentions, I want to capture. The hourly capture process uses this table to determine which Tweets to search. Having this flexibility allows me to easily add additional hashtags in the future.

# Twitter APIs

This section focuses on the [Twitter APIs](https://developer.twitter.com/en/docs/twitter-api) that I used to capture the data used by the App.

## Getting Started

Signing up for a developer account was easy. You can get started by following this [link](https://developer.twitter.com/).

Optionally signup for 'Elevated' access. Amongst other things, 'Elevated' access allows you to query 2 million Tweets/month instead of the Standard 500k. Use this [link](https://developer.twitter.com/en/docs/twitter-api/getting-started/about-twitter-api#v2-access-level) to compare the different plan levels. Aside from having to complete a brief questionnaire, upgrading to 'Elevated' access was straightforward. 'Elevated' access was granted almost immediately after submitting the questionnaire. I am assuming this was an automated approval.

You will also find these links useful when getting started with the Twitter APIs:

* 🔗 [Get started with the Twitter Developer Platform](https://developer.twitter.com/en/docs/platform-overview)
    
* 🔗 [Twitter V2 API Documentation](https://developer.twitter.com/en/docs/api-reference-index)
    
* 🔗 [Tool to try out the Twitter V2 APIs](https://developer.twitter.com/apitools/api)
    

## Authentication

After signing up for a developer account, you must create an App within a Project. In the below screenshot, you can see I have one App called 'Cloud Nueva APEX Demo' in the project 'Cloud Nueva'.

![Screenshot_of_Twitter_Developer_Portal.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661295526588/lYskJROto.png align="left")

Once you create an App, Twitter will present you with 'Keys & Tokens'; keep these in a safe place. We will use the 'API Key' and 'API Secret Key' shortly. The screenshot below shows the end of the Wizard process to create a new App (the process is straightforward).

![Screenshot_of_Twitter_Developer_App_Keys.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661295803980/Yi6GnGos7.png align="left")

Don't worry; I have already deleted all of the API Keys and Bearer Tokens you may see in this post. 😉

We can then use these `API Key` and the `API Secret Key` keys as part of the 'OAuth2 Client Credentials' flow in Postman to get an `access_token` as follows:

![Postman_Authentication_Step_1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661296024283/S5h9rpCU8.png align="left")

![Postman_Authentication_Step_2.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661296032158/0qlE_CLu9.png align="left")

**Note**: Unlike many OAuth services that expire the `access_token` (also known as the Bearer Token) after a period of time, Twitter does not expire this `access_token`. If you call `https://api.twitter.com/oauth2/token` multiple times, you will get back the same `access_token`. This is in fact the same 'Bearer Token' generated when we created the 'App' in the Twitter Developer Account in the previous step.

To revoke the Bearer Token / `access_token`, you can click the 'Revoke' button from the Twitter Developer Portal as seen in the screenshot below.

![Screenshot_of_Twitter_Developer_Revoke_Bearer.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661556704514/DUJB4vkp4.png align="left")

🔗 You can read more about using the 'App only authentication and OAuth 2.0 Bearer Token' authentication method [here](https://developer.twitter.com/en/docs/authentication/oauth-2-0/application-only)

## Tweet Search Recent

The main API is the [Tweet Search Recent API](https://developer.twitter.com/en/docs/twitter-api/tweets/search/api-reference/get-tweets-search-recent). This API allows you to search Tweets created in the past seven days.

The seven-day search limit is one notable limitation of even the 'Elevated' level Twitter Developer Account. As you will see later, I have worked around this by implementing a nightly process to loop through all tweets captured between seven and twenty-one days ago and call the [Tweets Lookup API](https://developer.twitter.com/en/docs/twitter-api/tweets/lookup/api-reference/get-tweets) to update them. Looking back at older tweets was another reason I chose to store Tweets in the Oracle database.

The following screenshot shows how to fetch Tweets with a mention of `@oracleAPEX` or the hashtag `#orclAPEX`.

![Postman_Tweet_Search_Step_1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661296953030/JFnlP7VdR.png align="left")

![Postman_Tweet_Search_Step_2.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661296960230/XKxuuz_ue.png align="left")

* The Bearer token is the `access_token` obtained in the authentication step.
    

**Pagination**

Pagination is handled via the `max_results` and the `next_token` query string parameters.

* Passing `max_results` of 100 (the maximum value) will fetch up to 100 Tweets at a time (assuming your search returns 100 or more Tweets.
    
* The `meta` section at the end of the API response includes the number of Tweets returned by the search. If there are more than 100, then the API will also include a field called `next_token`.
    

```JSON
"meta": {
        "newest_id": "1562224116723490817",
        "oldest_id": "1561742996932108290",
        "result_count": 100,
        "next_token": "b26v89c19zqg8o3fpz8kbizzndmockeoekrzulr87t3zx"
    }
```

If you call the API again and pass in a parameter called `next_token` with the value you just received, the API will fetch the next 100 Tweets. This way, you can fetch as many search results as you like (within your account limits).

![Postman_Tweet_Search_Step_3.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661299818805/my08q15rA.png align="left")

## Users

The other API, the APEX App, uses extensively is the [Users Lookup API](https://developer.twitter.com/en/docs/twitter-api/users/lookup/introduction). The App uses this API to fetch details for users that have Tweeted about APEX or ORDS. It allows you to pass a list of up to 100 usernames and returns details for these usernames.

The screenshots below show the Postman setup to fetch details for these two users `jondixonus,cloud_nueva`.

![Postman_Users_Step_1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661299363381/-88xyHGyq.png align="left")

![Postman_Users_Step_2.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661299371190/wMuHh2tQh.png align="left")

# APEX Application

Now that we understand the Twitter APIs, we can look at some of the more significant code behind the App. I don't plan to explain every line of code, so I encourage you to download the code from my [Github Repository](https://github.com/cloudnueva/cn_twitter) now.

## APEX Web Credentials

We first need to create some APEX Web Credentials to store the Twitter OAuth2 Client ID and Client Secret. APEX Web Credentials make securely storing credentials like these super easy.

![APEX_Web_Credentials.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661345552359/pM4Cj15tc.png align="left")

🔗 See my post [Secure your Secrets with APEX Web Credentials](https://blog.cloudnueva.com/apex-web-credentials) for more details on APEX Web Credentials.

## APEX Automations

APEX Automations allow you to schedule code to run (locally or on a remote database) based on an event or schedule.

🔗 See my post [Scheduling Made Easy with APEX Automations](https://blog.cloudnueva.com/apex-automations) for more details on APEX Automations.

The Twitter App uses two Automations:

1. Capture Tweets - Hourly
    
2. Refresh Tweets and Users - Nightly
    

**Capture Tweets - Hourly**

* This Automation calls the procedure `cndemo_twtr_utl_pk.capture_tweets` every hour. The procedure uses the APEX PL/SQL API `apex_web_service.make_rest_request` to call the Twitter Recent Search API, fetching all APEX-related Tweets for the past seven days in batches of 100.
    
    * Any Tweets not already in the database are added, and any existing Tweets are updated.
        
    * The reason for the update is to update Tweets that have received additional likes etc., since their original capture.
        

**Refresh Tweets and Users - Nightly**

This Automation runs nightly and calls two procedures, one after the other.

* `refresh_tweets`
    
    * This procedure calls the [Tweets lookup](https://developer.twitter.com/en/docs/twitter-api/tweets/lookup/introduction) endpoint to get updated data for Tweets in my database between 7 and 21 days old.
        
    * The 'Tweets lookup' API allows you to submit up to 100 Tweet `ids` to fetch details for. It will fetch details for these Tweets no matter how old they are.
        
    * Fetching details of specific older tweets helps me get around the problem that the Search Recent API only beings back Tweets for the past seven days.
        
    * I assume that any Tweets older than 21 days are not worth updating.
        
* `refresh_users`
    
    * This procedure uses the [Users lookup](https://developer.twitter.com/en/docs/twitter-api/users/lookup/introduction) endpoint to get updated follower counts etc., for all users that have Tweeted about APEX.
        
    * The 'Users lookup' API allows you to submit up to 100 User `ids` to fetch details for. The procedure loops through all users in the Oracle database, calling the 'Users lookup' API in batches of 100.
        

> Batching Users and Tweets into fewer REST API calls reduce network round trips and significantly improves performance.

## PL/SQL Code

The bulk of the code in the package `cndemo_twtr_utl_pk` is used in the Tweet and Tweeter capture Automations. I have added many comments to the code, so hopefully, it is self-explanatory.

## APEX Pages

### Home Page - Page 1

This page uses the APEX Cards Region to summarize information for each configured search. I am utilizing APEX Template Directives to present a summarized information table in the center of each card.

![APEX_Twitter_Home_Page.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661358856374/FPUJxpiqp.png align="left")

🔗 See my post [Client-Side Formatting with APEX Template Directives](https://blog.cloudnueva.com/apex-template-directives) for more on Template Directives.

### Top 10s - Page 10

This page highlights the most active Tweets and Tweeters. I implemented a scoring system based on Original Tweets Vs. ReTweets and Likes to illustrate the most effective Tweets and Tweeters. The page allows you to easily navigate to specific Tweets so that you can like or re-tweet them. I used APEX Classic Reports to build the four regions on this page.

![APEX_Twitter_Top10s_1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661359868604/2cXjOvcSb.png align="left")

You can click on a username to view their Twitter profile information.

![APEX_Twitter_Top10s_2.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661359882127/V4U4zjrp_.png align="left")

### Charts - Page 30

This page shows three charts illustrating week-by-week Twitter activity and the most popular days of the week to tweet. When you visit this page, it calls the procedure `page30_load`, which summarizes Tweet data into an [APEX Collection](https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/APEX_COLLECTION.html#GUID-859B488C-2628-44D7-969F-50872C685B76).

APEX Collections are a great way to cache session specific data. Summarizing the data in this way improves the page's overall performance (each chart fetches 10s of records instead of thousands) while still giving the user real-time data.

![APEX_Twitter_Charts.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661557808181/H7HGMBnVn.png align="left")

### Tweet Search - Page 20

This page allows you to search across all Tweets captured by the App. This search capability provides users a great way to search for Tweets focused entirely on APEX. This page utilizes two important APEX Features; [Smart Filters](https://blogs.oracle.com/apex/post/smart-filters-in-oracle-apex-212) and [Order By Items](https://docs.oracle.com/en/database/oracle/apex/22.1/htmdb/managing-sorting-with-order-by-item.html#GUID-48FBEAC0-82C3-486E-A87C-5C7F98341F53).

Smart filters are particularly effective as they combine 'Google Style' row search criteria entry with the ability to present lists or ranges from which a user can select.

In the below screenshot I am selecting tweets for a specific tweeter:

![APEX_Twitter_Tweeter_Search.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661360694145/33J8w8vDy.png align="left")

In the screenshot below, I searched for Tweets that contained `ckeditor1 and image` and applied a filter to only show 'Original' Tweets.

![APEX_Twitter_Tweeter_Search_2.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661360960567/mLqhhzBPJ.png align="left")

# Conclusion

I had a lot of fun building this App. Side projects like these are a great way to flex features that may not get used in your day-to-day corporate development job. They can also benefit the development community. I encourage you to find a side project that gets you excited!

## 🔗 Read More

* 📖 [4 Blogs in 4 Weeks, Week 1 - What made me want to be a developer?](https://blog.cloudnueva.com/what-made-me-want-to-be-a-developer)
    
* 📖 [4 Blogs in 4 Weeks, Week 2 - What do I do when I get stuck](https://blog.cloudnueva.com/what-do-i-do-when-i-get-stuck)
    
* 🩳 [APEX Shorts](https://blog.cloudnueva.com/series/apex-shorts)
    
* #️⃣ [APEX Posts](https://blog.cloudnueva.com/tag/orclapex)