GitHub's security team wants to audit active repositories against the organization's required security controls. Every active repository should be checked against every required control. A control is considered satisfied only when a matching repository configuration exists with status `enabled`. Return one row for every missing or disabled control. Return the columns in this exact order: `repo_id`, `repo_name`, `control_name`, `control_priority`, and `gap_reason`. Use `Not Configured` when the repository has no record for the control. Use `Disabled` when a record exists but its status is not `enabled`. Ignore archived repositories and optional controls. Sort by `control_priority` descending, then `repo_id` descending, then `control_name` descending. ### Table Schema | Table | Column | Type | |---|---|---| | `github_repositories` | `repo_id` | integer | | `github_repositories` | `repo_name` | varchar(80) | | `github_repositories` | `organization` | varchar(60) | | `github_repositories` | `is_archived` | boolean | | `security_controls` | `control_id` | integer | | `security_controls` | `control_name` | varchar(80) | | `security_controls` | `control_priority` | integer | | `security_controls` | `is_required` | boolean | | `repository_controls` | `repo_id` | integer | | `repository_controls` | `control_id` | integer | | `repository_controls` | `status` | varchar(20) | | `repository_controls` | `configured_at` | timestamp | ### Example Input: `github_repositories` | repo_id | repo_name | organization | is_archived | |---:|---|---|---| | 1 | api-service | octo-platform | false | | 2 | web-client | octo-platform | false | | 3 | old-demo | octo-platform | true | ### Example Input: `security_controls` | control_id | control_name | control_priority | is_required | |---:|---|---:|---| | 10 | Secret Scanning | 3 | true | | 11 | Dependabot Alerts | 2 | true | | 12 | Code Coverage | 1 | false | ### Example Input: `repository_controls` | repo_id | control_id | status | configured_at | |---:|---:|---|---| | 1 | 10 | enabled | 2025-01-01 09:00:00 | | 1 | 11 | disabled | 2025-01-01 09:00:00 | | 2 | 10 | enabled | 2025-01-02 09:00:00 | ### Example Output Explanation The `api-service` repository has Secret Scanning enabled but Dependabot Alerts disabled, so it produces one `Disabled` row. The `web-client` repository has Secret Scanning enabled but no Dependabot Alerts record, so it produces one `Not Configured` row. The archived repository and optional Code Coverage control are ignored.
DROP TABLE IF EXISTS repository_controls; DROP TABLE IF EXISTS security_controls; DROP TABLE IF EXISTS github_repositories; CREATE TABLE github_repositories ( repo_id INT, repo_name VARCHAR(80), organization VARCHAR(60), is_archived BOOLEAN ); CREATE TABLE security_controls ( control_id INT, control_name VARCHAR(80), control_priority INT, is_required BOOLEAN ); CREATE TABLE repository_controls ( repo_id INT, control_id INT, status VARCHAR(20), configured_at TIMESTAMP ); INSERT INTO github_repositories VALUES (1,'api-service','octo-platform',FALSE), (2,'web-client','octo-platform',FALSE), (3,'old-demo','octo-platform',TRUE); INSERT INTO security_controls VALUES (10,'Secret Scanning',3,TRUE), (11,'Dependabot Alerts',2,TRUE), (12,'Code Coverage',1,FALSE); INSERT INTO repository_controls VALUES (1,10,'enabled','2025-01-01 09:00:00'), (1,11,'disabled','2025-01-01 09:00:00'), (2,10,'enabled','2025-01-02 09:00:00');