schema.sql

-- SpecMine v1.0 schema (matches the Parquet files in data/).
-- The PR layer is stored internally as 11 per-tool tables (<tool>_prs / <tool>_pr_files);
-- this release unions them and adds a `tool` column. Commit-email, avatar-URL, and
-- GPG-signature columns are dropped at release; file_content ships in specs.jsonl.gz.

CREATE TABLE `spec_files` (
  `file_url_sha16` char(16),
  `file_url` text,
  `file_path` text,
  `file_name` varchar(255),
  `file_sha` varchar(64),
  `git_url` text,
  `search_run_id` varchar(64),
  `repo_id` bigint(20),
  `repo_node_id` varchar(255),
  `repo_name` varchar(511),
  `repo_url` text,
  `repo_homepage` text,
  `repo_description` text,
  `repo_fork` tinyint(1),
  `repo_private` tinyint(1),
  `repo_visibility` varchar(32),
  `owner_login` varchar(255),
  `owner_id` bigint(20),
  `owner_node_id` varchar(255),
  `owner_type` varchar(64),
  `owner_html_url` text,
  `owner_site_admin` tinyint(1),
  `repo_stars` int(11),
  `repo_forks` int(11),
  `repo_watchers` int(11),
  `repo_subscribers_count` int(11),
  `repo_network_count` int(11),
  `repo_open_issues` int(11),
  `repo_size_kb` int(11),
  `repo_language` varchar(255),
  `repo_license` varchar(255),
  `repo_license_key` varchar(128),
  `repo_license_spdx_id` varchar(128),
  `repo_default_branch` varchar(255),
  `repo_topics` text,
  `repo_archived` tinyint(1),
  `repo_disabled` tinyint(1),
  `repo_is_template` tinyint(1),
  `repo_allow_forking` tinyint(1),
  `repo_web_commit_signoff_required` tinyint(1),
  `repo_has_issues` tinyint(1),
  `repo_has_projects` tinyint(1),
  `repo_has_wiki` tinyint(1),
  `repo_has_pages` tinyint(1),
  `repo_has_downloads` tinyint(1),
  `repo_has_discussions` tinyint(1),
  `repo_clone_url` text,
  `repo_ssh_url` text,
  `repo_svn_url` text,
  `repo_git_url` text,
  `repo_mirror_url` text,
  `repo_created_at` varchar(64),
  `repo_updated_at` varchar(64),
  `repo_pushed_at` varchar(64),
  `file_last_commit_sha` varchar(64),
  `file_last_commit_node_id` varchar(255),
  `file_last_commit_html_url` text,
  `file_last_commit_message` longtext,
  `file_last_commit_comment_count` int(11),
  `file_last_commit_at` varchar(64),
  `file_last_commit_author` text,
  `file_last_commit_author_login` varchar(255),
  `file_last_commit_author_id` bigint(20),
  `file_last_commit_author_type` varchar(64),
  `file_last_commit_author_html_url` text,
  `file_last_commit_author_site_admin` tinyint(1),
  `file_last_commit_committer` text,
  `file_last_commit_committer_at` varchar(64),
  `file_last_commit_committer_login` varchar(255),
  `file_last_commit_committer_id` bigint(20),
  `file_last_commit_committer_type` varchar(64),
  `file_last_commit_committer_html_url` text,
  `file_last_commit_committer_site_admin` tinyint(1),
  `file_last_commit_tree_sha` varchar(64),
  `file_last_commit_parents` text,
  `file_last_commit_verified` tinyint(1),
  `file_last_commit_verification_reason` varchar(64),
  `file_last_commit_verified_at` varchar(64),
  `size_partition_label` text,
  `updated_at` timestamp,
  `not_spec_filter` tinyint(1),
  `file_first_commit_sha` varchar(64),
  `file_first_commit_node_id` varchar(255),
  `file_first_commit_html_url` text,
  `file_first_commit_message` longtext,
  `file_first_commit_comment_count` int(11),
  `file_first_commit_at` varchar(64),
  `file_first_commit_author` text,
  `file_first_commit_author_login` varchar(255),
  `file_first_commit_author_id` bigint(20),
  `file_first_commit_author_type` varchar(64),
  `file_first_commit_author_html_url` text,
  `file_first_commit_author_site_admin` tinyint(1),
  `file_first_commit_committer` text,
  `file_first_commit_committer_at` varchar(64),
  `file_first_commit_committer_login` varchar(255),
  `file_first_commit_committer_id` bigint(20),
  `file_first_commit_committer_type` varchar(64),
  `file_first_commit_committer_html_url` text,
  `file_first_commit_committer_site_admin` tinyint(1),
  `file_first_commit_tree_sha` varchar(64),
  `file_first_commit_parents` text,
  `file_first_commit_verified` tinyint(1),
  `file_first_commit_verification_reason` varchar(64),
  `file_first_commit_verified_at` varchar(64),
  `file_total_commits` int(11),
  `commit_history_status` varchar(16),
  `commit_history_fetched_at` varchar(32),
  `spec_tool` varchar(32),
  `spec_role` varchar(24),
  `file_content_sha` char(64),
  `file_content_bytes` int(11),
  `content_fetch_status` varchar(16),
  `content_fetched_at` varchar(32),
  `feature_branch` varchar(255),
  `n_linked_prs` int(11),
  `n_linked_issues` int(11),
  `n_sibling_docs` int(11),
  `n_code_links` int(11),
  `has_tasks` tinyint(4),
  `has_plan` tinyint(4),
  `has_proposal` tinyint(4),
  `has_design` tinyint(4),
  `trace_rollup_at` varchar(32)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `spec_file_commits` (
  `id` bigint(20),
  `file_url_sha16` char(16),
  `repo_name` varchar(512),
  `file_path` text,
  `seq_from_oldest` int(11),
  `commit_sha` varchar(64),
  `node_id` varchar(255),
  `html_url` text,
  `message` longtext,
  `comment_count` int(11),
  `authored_at` varchar(64),
  `author_name` text,
  `author_login` varchar(255),
  `author_id` bigint(20),
  `author_type` varchar(64),
  `committed_at` varchar(64),
  `committer_name` text,
  `committer_login` varchar(255),
  `committer_id` bigint(20),
  `committer_type` varchar(64),
  `tree_sha` varchar(64),
  `parents` text,
  `verified` tinyint(1),
  `verification_reason` varchar(64)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `spec_content_features` (
  `file_url_sha16` char(16),
  `n_bytes` int(11),
  `n_lines` int(11),
  `n_words` int(11),
  `n_headings` int(11),
  `max_heading_depth` int(11),
  `n_code_fences` int(11),
  `n_mermaid` int(11),
  `n_tables` int(11),
  `n_links` int(11),
  `n_images` int(11),
  `n_list_items` int(11),
  `n_checkboxes` int(11),
  `n_checked` int(11),
  `n_shall` int(11),
  `n_gherkin` int(11),
  `n_scenario_blocks` int(11),
  `n_delta_headers` int(11),
  `n_needs_clarification` int(11),
  `n_todo` int(11),
  `has_ears` tinyint(4),
  `has_gherkin` tinyint(4),
  `has_user_story` tinyint(4),
  `has_requirements` tinyint(4),
  `has_acceptance_criteria` tinyint(4),
  `has_user_scenarios` tinyint(4),
  `has_non_goals` tinyint(4),
  `has_out_of_scope` tinyint(4),
  `has_overview` tinyint(4),
  `has_success_criteria` tinyint(4),
  `has_edge_cases` tinyint(4),
  `has_open_questions` tinyint(4),
  `has_dependencies` tinyint(4),
  `has_purpose` tinyint(4),
  `has_mandatory_marker` tinyint(4),
  `is_tiny` tinyint(4),
  `has_unfilled_placeholder` tinyint(4),
  `has_lorem` tinyint(4),
  `lang` varchar(8),
  `content_family` varchar(24)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `spec_links` (
  `id` bigint(20),
  `spec_url_sha16` char(16),
  `rel` varchar(16),
  `target` varchar(500),
  `provenance` varchar(24)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `kiro_files` (
  `id` bigint(20),
  `repo_name` varchar(511),
  `owner_login` varchar(255),
  `repo_id` bigint(20),
  `file_path` varchar(1000),
  `file_name` varchar(255),
  `kind` varchar(16),
  `feature` varchar(400),
  `file_sha` varchar(64),
  `html_url` text,
  `git_url` text,
  `fetched_at` varchar(32)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `openspec_artifact_files` (
  `id` bigint(20),
  `repo_name` varchar(511),
  `file_path` varchar(700),
  `kind` varchar(24),
  `content` longtext,
  `content_sha` char(64),
  `n_bytes` int(11),
  `n_tasks` int(11),
  `n_tasks_done` int(11),
  `n_code_refs` int(11),
  `fetch_status` varchar(16),
  `fetched_at` varchar(32)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `openspec_code_refs` (
  `id` bigint(20),
  `repo_name` varchar(511),
  `artifact_path` varchar(700),
  `code_path` varchar(500),
  `task_done` tinyint(4),
  `anchor_sha` char(40),
  `exists_at_anchor` tinyint(4),
  `resolved_path` varchar(700)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `repo_trees` (
  `repo_name` varchar(511),
  `status` varchar(16),
  `n_entries` int(11),
  `truncated` tinyint(4),
  `fetched_at` varchar(32)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `pull_requests` (
  `tool` varchar(16),
  `id` bigint(20),
  `repo_name` varchar(511),
  `pr_number` int(11),
  `title` text,
  `state` varchar(12),
  `is_merged` tinyint(4),
  `merged_at` varchar(32),
  `created_at` varchar(32),
  `closed_at` varchar(32),
  `author_login` varchar(255),
  `head_branch` varchar(400),
  `base_branch` varchar(255),
  `n_files` int(11),
  `n_specdir_files` int(11),
  `n_spec_files` int(11),
  `n_code_files` int(11),
  `n_md_files` int(11),
  `n_other_files` int(11),
  `additions` int(11),
  `deletions` int(11),
  `files_status` varchar(12),
  `touches_code` tinyint(4),
  `n_code_outside` int(11),
  `n_prs_in_repo` int(11)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `pr_files` (
  `tool` varchar(16),
  `id` bigint(20),
  `repo_name` varchar(511),
  `pr_number` int(11),
  `file_path` varchar(1000),
  `change_status` varchar(12),
  `additions` int(11),
  `deletions` int(11),
  `is_specdir` tinyint(4),
  `is_spec` tinyint(4),
  `is_code` tinyint(4),
  `ext` varchar(16)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;