Migration 006 | A11yzer Table

[TheBoatyMcBoatFace]

Create postgres table for Tech Scans

[TheBoatyMcBoatFace]

🎯 PostgreSQL Migration Script - 006 | Allyzer Table

Overview

The upcoming migration script is directed to create a results.tech table in the gova11y database. This table will hold detailed technology data extracted from different URLs, facilitating the organization and analysis of tech stacks across a variety of websites. Let's make sure to get it right, covering all aspects from URL status to technology details.

Tasks

Database Schema Design

• URLs: Develop fields to store URL strings and their corresponding HTTP status codes (e.g., 200, 301).
• Technologies: Create a structured format to hold details including but not limited to:
  • Technology name
  • Description
  • Confidence level
  • Version
  • Icon
  • Website URL
  • Categories (array with id, slug, name)
  • Root Path (boolean)

Table Creation

• Create the results.tech table with the designed schema, ensuring all fields have the most appropriate data types.

Relationship & Indexes

• Establish relationships with relevant existing tables if necessary.
• Set up indexes on the columns frequently queried to optimize performance.

Test Data Insertion

• Test Insertions: Before the full-scale data insertion, perform tests with diverse data samples to ensure the table works seamlessly.
• Feedback Loop: Establish a feedback loop with the team to iteratively update the schema based on the test results.

[TheBoatyMcBoatFace]

Completing schema testing locally. Pushing to test then staging this evening. Pending the outcome of those deployments, the postgres image will update/upgrade on prod systems.