AppSec: Rated E for Everyone

Published: 2024-11-24 | By: Tyler Talaga Author

Intro

Application Security (AppSec) often gets a bad rap—tedious workflows, heaps of data, and no clear way to wrangle it all into something useful. But what if we could make it easier, even enjoyable? Enter SonarCloud, Airtable, and Tines, the unlikely trio that transformed chaos into a symphony of automated insight.

This article walks you through how we set up a workflow to analyze code, extract metrics, and build dashboards—all using free-tier tools. And the best part? You can try it yourself:

  1. Sign up for SonarCloud and add your organization.
  2. Import the Tines story: appsec-manager.json.
  3. Clone our Airtable base: AppSec Manager Base.

Let’s dive in and see how it all works.

SonarCloud: Scanning Smarter

SonarCloud became the starting point for our AppSec journey, offering static application security testing (SAST) for public GitHub repositories on the free tier. Think of it as the official cat-herder that ensures your code behaves itself.

For most supported languages, SonarCloud’s automatic analysis feature takes care of scanning without any additional configuration. Once a repository is onboarded, it generates a Quality Gate badge—a simple, visual indicator of whether the code passes defined quality and security thresholds. This badge serves as an instant status update, like a report card for your project’s health.

To onboard our repositories, we used Tines to call SonarCloud’s /alm_integration/provision_projects API. Here’s an example of the request: 

curl -X POST \
  "https://sonarcloud.io/api/alm_integration/provision_projects" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -H "Authorization: Bearer <<YOUR_SONARCLOUD_TOKEN>>" \
  -d "installationKeys=<organization>/<repository>|<repo_id>&organization=<organization>"

This API call linked our GitHub repositories to SonarCloud, enabling automatic analysis for eligible projects. When automatic analysis wasn’t supported, we fell back to manual configuration, ensuring all repositories could still be tracked.

Tines also automatically updated each repository’s README to include the Quality Gate badge, signaling its current status. Using the GitHub API, Tines inserted the badge near the top of the README file: 

curl -X PUT \
  "https://api.github.com/repos/<username>/<repository>/contents/README.md" \
  -H "Authorization: token <<YOUR_GITHUB_TOKEN>>" \
  -d '{
    "message": "Add SonarCloud Quality Gate Badge",
    "content": "<BASE64_ENCODED_README>",
    "sha": "<CURRENT_FILE_SHA>"
  }'

sonarcloud_readme_badge

By leveraging Tines to dynamically generate and inject the badge, we ensured visibility without manual effort. However, it’s worth noting that SonarCloud’s free tier supports public repositories only, so private ones are out of scope for the time being.

Airtable: Structuring Metrics

Once SonarCloud completed its analysis, we needed a way to organize and store the data for easy access and visualization. Airtable served as the perfect destination, acting as a central repository for all our AppSec metrics.

We categorized data into four Airtable tables:

  1. Project Metrics: A point-in-time snapshot of overall project health.
  2. Trends: Historical deltas for metrics like bugs, vulnerabilities, and code smells.
  3. Issues: A detailed inventory of unresolved problems, categorized by severity and type.
  4. Security Insights: Focused on vulnerabilities and security hotspots.

Before building the automation, we carefully designed the schema to align with our reporting goals. Frontloading planning ensured that every metric had a clear purpose and a designated destination in Airtable. Once the schema was in place, Tines seamlessly synced data from SonarCloud to Airtable using the API like so:

curl -X POST \
  "https://api.airtable.com/v0/<base_id>/<table_name>" \
  -H "Authorization: Bearer <<YOUR_AIRTABLE_API_KEY>>" \
  -H "Content-Type: application/json" \
  -d '{
    "records": [
      {
        "fields": {
          "Project Name": "My Project",
          "Bugs": 5,
          "Vulnerabilities": 1,
          "Code Smells": 12
        }
      }
    ]
  }'

With this setup, Airtable dynamically our tables updated as new metrics came in. For example, as SonarCloud completed scans, Tines calculated deltas for historical trends and synced these alongside new point-in-time metrics. This approach gave us a robust system for tracking progress—or spotting regressions—over time.

appsec_issues_dash

Tines: Automation with Flair

If SonarCloud was the source and Airtable was the destination, Tines was the plumbing that connected everything together. It automated onboarding, extracted metrics, calculated deltas, and kept everything up to date with minimal intervention.

The onboarding workflow ensured smooth API interactions by staggering repository imports—processing one repository per day to give us time to digest new metrics and prioritize issues without being totally overwhelmed. If automatic analysis wasn’t possible, Tines gracefully fell back to manual configuration, ensuring every repository was accounted for.

For metrics extraction, Tines queried SonarCloud’s API, pulling data such as bugs, vulnerabilities, and code smells. To track historical changes, Tines calculated deltas by comparing current metrics with previous snapshots. For example:

{
  "current_metrics": {
    "Bugs": 10,
    "Vulnerabilities": 2
  },
  "previous_metrics": {
    "Bugs": 12,
    "Vulnerabilities": 3
  },
  "output": {
    "Bug Delta": -2,
    "Vulnerability Delta": -1
  }
}

These deltas were then synced to Airtable alongside other metrics, ensuring our dashboards remained current. This fully automated system not only saved time but also eliminated human error, giving us confidence in the accuracy of our data.

Putting It All Together

With SonarCloud analyzing code, Airtable structuring metrics, and Tines handling the automation, we built a cohesive AppSec workflow that just works. The result is a streamlined system that:

  1. Scans code to uncover issues and calculate key metrics.
  2. Tracks those metrics and trends in Airtable, offering actionable insights.
  3. Updates dynamically, reducing the need for manual intervention.

appsec_dash

For readers interested in replicating this setup, we’ve made the Airtable base publicly available! Click here to access the base and clone it into your own Airtable workspace.

Wrapping Up

AppSec doesn’t have to be a chore. By combining free tools like SonarCloud, Airtable, and Tines, we automated the hard stuff and focused on what mattered: improving code quality and security.

Here’s how you can try it yourself:

  1. Sign up for SonarCloud and add your organization.
  2. Import the Tines story: appsec-manager.json.
  3. Clone our Airtable base: AppSec Manager Base.

Automation isn’t just about doing less work—it’s about doing better work. With tools like these, there’s no excuse not to start.