Add import_sync database tables and models

What does this MR do and why?

This merge request ports the database schema and ActiveRecord models for a GitHub continuous sync feature from a large, never-reviewed proof-of-concept merge request that is being closed. Rather than merging the entire 40,000-line PoC at once, this feature is being broken into a series of small, reviewable pieces. This MR is the first: it adds only the database tables and models, with services, workers, and controllers to follow in later merge requests.

The MR adds three tables and their corresponding models: import_sync_repositories (Import::Sync::Repository, one row per GitHub repository kept updated from a GitLab project), import_sync_repository_states (Import::Sync::RepositoryState, per-domain generation counters and health for inventory, source, and work_items), and import_sync_external_objects (Import::Sync::ExternalObject, the mapping between a GitHub object and the GitLab issue, merge request, or note it produced). A scope audit of the PoC dropped its other three tables entirely: connections, webhook_deliveries, and import_grants. Their installation fields move onto import_sync_repositories itself, webhooks move to a hint-then-verify design that needs no durable delivery row, and import provisioning resolves and authorizes each request directly instead of using a short-lived grant.

Sharding for the three new tables is now based on project_id instead of organization_id. The original plan followed the proof-of-concept and used organization_id, but a reviewer pointed out that project_id fits better since all this data belongs to a single project. It's also more stable: if an organization ever moves to a different cell, organization_id would need to move with it, dragging a lot of data along. project_id doesn't have that problem. To make this work, import_sync_repository_states and import_sync_external_objects got a new project_id column, and their organization_id column, foreign key, and index were dropped since they weren't used for anything else.

We also dropped the unique index on import_sync_repositories that only allowed one project per organization to sync a given GitHub repository. That restriction doesn't make sense anymore: a GitHub repository can now be connected to more than one project at the same time. Since that constraint was the only reason import_sync_repositories still had organization_id, we removed the column, its foreign key, and its index from that table too. We kept a plain, non-unique index on provider_repository_xid so lookups by that field are still fast.

A deliberate improvement over the PoC: import_sync_external_objects replaces a polymorphic local_record_type/local_record_id pair with three nullable foreign key columns (issue_id, merge_request_id, note_id), each with a real, cascading database foreign key, per GitLab's guidance against polymorphic associations. A database check constraint and an ExactlyOnePresentValidator both enforce that exactly one of the three is set.

Everything lands behind the existing github_continuous_import feature flag (type wip, default off), created in a prerequisite issue this one depends on, so it has no effect on any real project. The PoC's 30 migrations are consolidated into three create_table migrations plus three foreign-key migrations, one per table, per GitLab's migration style guide.

References

• Implements #628581 (closed)
• Follow-up work items owning the replacement designs for the dropped tables: #628589 (webhooks), #628586 (import grants)
• Ported from !251883 (closed) (Draft: POC - Import GitHub repositories with continuous sync), which is being closed

Screenshots or screen recordings

Not applicable — this MR adds only database tables and models, no UI.

How to set up and validate locally

1. Run migrations: bundle exec rake db:migrate
2. Confirm the three tables exist with the expected columns, indexes, and constraints
3. Run the new specs: bundle exec rspec ee/spec/models/import/sync/

Query plans

Seeded real data on caproni (1 organization, 1 project, 1 repository, 1 issue, 1 existing mapping) and ran EXPLAIN (ANALYZE, BUFFERS) on the actual generated SQL, re-run after the project_id sharding-key change. All hit their intended index or the primary key; none seq scan except where noted. Table has 1 row locally, so this confirms plan shape (index usage), not production-scale cost.

Import::Sync::ExternalObject.record! (insert/update/upsert)

Insert path (update_mapping! building a new mapping):

INSERT INTO "import_sync_external_objects" ("project_id", "import_sync_repository_id", "issue_id",
  "created_at", "updated_at", "provider_updated_at", "provider_object_xid", "kind")
VALUES (1, 1, 3, now(), now(), now(), 888888, 0) RETURNING "id"

Insert on import_sync_external_objects  (cost=0.00..0.02 rows=1 width=82) (actual time=0.096..0.096 rows=1 loops=1)
  Buffers: shared hit=109
  ->  Result  (cost=0.00..0.02 rows=1 width=82) (actual time=0.017..0.017 rows=1 loops=1)
        Buffers: shared hit=13
Planning Time: 0.012 ms
Trigger for constraint fk_9208212b58: time=0.108 calls=1
Trigger for constraint fk_3b67b4de12: time=0.034 calls=1
Trigger for constraint fk_23420d47c1: time=0.054 calls=1
Trigger for constraint fk_3f49aae4b8: time=0.067 calls=1
Trigger for constraint fk_bcd404d0e1: time=0.046 calls=1
Execution Time: 0.411 ms

Update path (existing mapping, by PK):

UPDATE "import_sync_external_objects" SET "updated_at" = now(), "provider_updated_at" = now()
WHERE "import_sync_external_objects"."id" = 3

Update on import_sync_external_objects  (cost=0.15..8.17 rows=0 width=0) (actual time=0.024..0.024 rows=0 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using import_sync_external_objects_pkey on import_sync_external_objects (actual time=0.003..0.003 rows=1 loops=1)
        Index Cond: (id = 3)
        Buffers: shared hit=2
Planning Time: 0.017 ms
Execution Time: 0.043 ms

Uniqueness-validator SELECTs that run before either (compound provider_object_xid+kind+repo, and issue_id):

SELECT 1 AS one FROM "import_sync_external_objects"
WHERE "import_sync_external_objects"."provider_object_xid" = 888888
  AND "import_sync_external_objects"."import_sync_repository_id" = 1
  AND "import_sync_external_objects"."kind" = 0
LIMIT 1

Limit (actual time=0.008..0.008 rows=1 loops=1)
  ->  Index Only Scan using idx_import_sync_external_objects_on_repository_kind_provider (actual time=0.007..0.008 rows=1 loops=1)
        Index Cond: ((import_sync_repository_id = 1) AND (kind = 0) AND (provider_object_xid = 888888))
        Heap Fetches: 1
Execution Time: 0.012 ms

SELECT 1 AS one FROM "import_sync_external_objects" WHERE "import_sync_external_objects"."issue_id" = 3 LIMIT 1

Limit (actual time=0.003..0.003 rows=1 loops=1)
  ->  Index Only Scan using idx_import_sync_external_objects_on_issue_id (actual time=0.003..0.003 rows=1 loops=1)
        Index Cond: (issue_id = 3)
        Heap Fetches: 1
Execution Time: 0.007 ms

Import::Sync::Repository lookup by provider_repository_xid

Now that there's no per-organization uniqueness constraint, this plain index is what backs a direct lookup — the shape future webhook routing and the repair cron (mentioned in the linked issue) will use, one call per provider repository ID:

SELECT "import_sync_repositories".* FROM "import_sync_repositories"
WHERE "import_sync_repositories"."provider_repository_xid" = 1

Bitmap Heap Scan on import_sync_repositories (actual time=0.004..0.005 rows=1 loops=1)
  Recheck Cond: (provider_repository_xid = 1)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on index_import_sync_repositories_on_provider_repository_xid (actual time=0.002..0.002 rows=1 loops=1)
        Index Cond: (provider_repository_xid = 1)
Planning Time: 0.016 ms
Execution Time: 0.009 ms

The previous partial unique index on (organization_id, provider_repository_xid), and the provider_bound scope/uniqueness validator built on it, are both gone along with organization_id on this table — removed, not just re-scoped, since a GitHub repository can now sync to more than one project.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.