[Bug]: SQL schema accepts versions, refs and checkpoints whose own commit or identity belongs to another project #411

What type of issue is this?

• Bug report
• Feature request

Prerequisites

• I have written a descriptive issue title
• I have verified that I am running the latest version of the SysML2.NET
• I have searched open and closed issues to ensure it has not already been reported

Description

References from an element to an identity in another project are deliberate (§4 of schema.generated.sql: data_identity has a bare-uuid key so cross-project references stay FK-able) and are out of scope. This issue covers rows that must belong to their own project.

No constraint ties a row's project_id to the project of the commit, branch or identity it references (schema.generated.sql:294-626, :1402-1441). Affected:

• element_version.commit_id, and element_version.project_id against the project of its own identity (identity_id), not of the identities it references
• commit_parent: a parent may belong to another project
• branch.head_commit_id, branch.base_commit_id and tag.tagged_commit_id
• branch_head and commit_checkpoint_registry
• project_usage: the PIM requires usedProject = usedProjectCommit.owningProject (constraint on ProjectUsage, ptc/25-02-29); a composite foreign key enforces it at no cost

Tables are partitioned by project_id, so a mismatched row is not an error: it silently disappears from the resolvers.

Expected: a row's own commit, branch and identity belong to the row's project, while references to elements of a used project stay accepted.

Fix:

• Add UNIQUE (project_id, id) on commit, branch and data_identity.
• Make these foreign keys composite on (project_id, …):
  • element_version (project_id, commit_id) → commit; element_version (project_id, identity_id, class_kind) → data_identity
  • derived_version (project_id, commit_id) → commit; derived_version (project_id, identity_id) → data_identity
  • branch (project_id, head_commit_id), branch (project_id, base_commit_id), tag (project_id, tagged_commit_id) → commit
  • branch_head (project_id, branch_id) → branch; branch_head (project_id, identity_id) → data_identity
  • commit_checkpoint (project_id, commit_id) and commit_checkpoint_registry (project_id, commit_id) → commit
  • project_usage (used_project_id, used_project_commit_id) → commit (project_id, id)
• Enforce same-project parents in commit_parent, which carries no project_id, through the existing insert trigger or an added column.
• Leave every reference column single-column (target_identity in the link tables, general, member_element, owning_relationship, …), so cross-project references keep working.
• Add rejection checks to schema.smoke.sql, plus one check that a reference to a used project's element is still accepted.

Steps to Reproduce

1. Create projects P and P2, and a commit C in P.
2. Insert a P2 commit with C as parent in commit_parent: accepted.
3. Insert an element_version with project_id = P2, an identity of P and commit_id = C: accepted.

System Configuration

• SysML2.NET version: PR [ADD] Sql schema #344 (feat/sql-schema-and-more, d5a90f2), SysML2.NET.CodeGenerator
• Environment (Operating system, version and so on): Windows 11, PostgreSQL 17 (Docker)
• .NET Framework version: .NET 10
• Additional information: none