refactor(db): port auth and security handlers to sqlc

- auth.sql: user lookups, counts, register/mirror inserts, profile update
- security.sql: access EXISTS, vulnerability list/metrics/status,
  framework list, latest scan/report, project-id guards
- NULL description/service_id rows stay filtered, matching the original
  scan-error-and-skip behavior
- auth_users probe and filtered audit-log queries stay raw: one is a
  Better Auth table outside the schema, the other is dynamically built
- TestSecurityLiveInvariants covers the access predicate, severity
  ordering, upsert conflict path, and latest-row lookups against live
  Postgres

Authored By: TDvorak <[email protected]>

app/backend/internal/api/auth.go

@@ -3,6 +3,8 @@ package api
 import (
 	"bytes"
 	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
+	"context"
 	"database/sql"
 	"encoding/json"
 	"errors"
@@ -63,11 +65,16 @@ func handleLogin(c *gin.Context) {
 	// Find user by email
 	var user User
 	var hashedPassword string
-	err := db.QueryRow(`
+	urow, err := sqlcdb.New(db.DB).GetUserByEmailForAuth(context.Background(), req.Email)
-		SELECT id, email, password_hash, name, COALESCE(avatar_url, ''), is_admin, created_at
+	user = User{
-		FROM users
+		ID:        urow.ID.String(),
-		WHERE email = $1
+		Email:     urow.Email,
-	`, req.Email).Scan(&user.ID, &user.Email, &hashedPassword, &user.Name, &user.AvatarURL, &user.IsAdmin, &user.CreatedAt)
+		Name:      urow.Name,
+		AvatarURL: urow.AvatarUrl,
+		IsAdmin:   urow.IsAdmin,
+		CreatedAt: urow.CreatedAt.Time.String(),
+	}
+	hashedPassword = urow.PasswordHash
 
 	if err == sql.ErrNoRows {
 		authUser, ok := verifyBetterAuthCredentials(c, req.Email, req.Password)
@@ -257,25 +264,30 @@ func createLocalUserFromBetterAuth(db *database.DB, authUser betterAuthLoginUser
 	}
 
 	// A mirrored first account owns the platform, same as handleRegister.
-	var total int
+	q := sqlcdb.New(db.DB)
-	if err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&total); err != nil {
+	total64, err := q.CountUsers(context.Background())
+	if err != nil {
 		return User{}, err
 	}
 
-	var user User
+	urow, err := q.UpsertLocalUser(context.Background(), sqlcdb.UpsertLocalUserParams{
-	err = db.QueryRow(`
+		Email:        email,
-		INSERT INTO users (email, password_hash, name, avatar_url, is_admin)
+		PasswordHash: string(hashedPassword),
-		VALUES ($1, $2, $3, NULLIF($4, ''), $5)
+		Name:         name,
-		ON CONFLICT (email) DO UPDATE
+		Column4:      strings.TrimSpace(authUser.Image),
-		SET name = EXCLUDED.name,
+		IsAdmin:      total64 == 0,
-		    avatar_url = COALESCE(EXCLUDED.avatar_url, users.avatar_url),
+	})
-		    updated_at = NOW()
-		RETURNING id, email, name, COALESCE(avatar_url, ''), is_admin, created_at
-	`, email, string(hashedPassword), name, strings.TrimSpace(authUser.Image), total == 0).
-		Scan(&user.ID, &user.Email, &user.Name, &user.AvatarURL, &user.IsAdmin, &user.CreatedAt)
 	if err != nil {
 		return User{}, err
 	}
+	user := User{
+		ID:        urow.ID.String(),
+		Email:     urow.Email,
+		Name:      urow.Name,
+		AvatarURL: urow.AvatarUrl,
+		IsAdmin:   urow.IsAdmin,
+		CreatedAt: urow.CreatedAt.Time.String(),
+	}
 
 	return user, nil
 }
@@ -302,8 +314,8 @@ func handleRegister(c *gin.Context) {
 		return
 	}
 
-	var existing int
+	q := sqlcdb.New(db.DB)
-	err = db.QueryRow("SELECT COUNT(*) FROM users WHERE email = $1", strings.ToLower(strings.TrimSpace(req.Email))).Scan(&existing)
+	existing, err := q.CountUsersByEmail(context.Background(), strings.ToLower(strings.TrimSpace(req.Email)))
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
@@ -322,12 +334,20 @@ func handleRegister(c *gin.Context) {
 	}
 
 	// First account owns the platform.
-	var user User
+	urow, err := q.InsertUserAdmin(context.Background(), sqlcdb.InsertUserAdminParams{
-	err = db.QueryRow(`
+		Email:        req.Email,
-		INSERT INTO users (email, password_hash, name, is_admin)
+		PasswordHash: string(hashedPassword),
-		VALUES ($1, $2, $3, $4)
+		Name:         req.Name,
-		RETURNING id, email, name, COALESCE(avatar_url, ''), is_admin, created_at
+		IsAdmin:      total == 0,
-	`, req.Email, string(hashedPassword), req.Name, total == 0).Scan(&user.ID, &user.Email, &user.Name, &user.AvatarURL, &user.IsAdmin, &user.CreatedAt)
+	})
+	user := User{
+		ID:        urow.ID.String(),
+		Email:     urow.Email,
+		Name:      urow.Name,
+		AvatarURL: urow.AvatarUrl,
+		IsAdmin:   urow.IsAdmin,
+		CreatedAt: urow.CreatedAt.Time.String(),
+	}
 
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to create user"})
@@ -365,8 +385,8 @@ func handleCreateUser(c *gin.Context) {
 		return
 	}
 
-	var existing int
+	existing, err := sqlcdb.New(db.DB).CountUsersByEmail(context.Background(), req.Email)
-	if err := db.QueryRow("SELECT COUNT(*) FROM users WHERE email = $1", req.Email).Scan(&existing); err != nil {
+	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
@@ -390,12 +410,19 @@ func handleCreateUser(c *gin.Context) {
 		return
 	}
 
-	var user User
+	urow, err := sqlcdb.New(db.DB).InsertUser(context.Background(), sqlcdb.InsertUserParams{
-	err = db.QueryRow(`
+		Email:        req.Email,
-		INSERT INTO users (email, password_hash, name)
+		PasswordHash: string(hashedPassword),
-		VALUES ($1, $2, $3)
+		Name:         req.Name,
-		RETURNING id, email, name, COALESCE(avatar_url, ''), is_admin, created_at
+	})
-	`, req.Email, string(hashedPassword), req.Name).Scan(&user.ID, &user.Email, &user.Name, &user.AvatarURL, &user.IsAdmin, &user.CreatedAt)
+	user := User{
+		ID:        urow.ID.String(),
+		Email:     urow.Email,
+		Name:      urow.Name,
+		AvatarURL: urow.AvatarUrl,
+		IsAdmin:   urow.IsAdmin,
+		CreatedAt: urow.CreatedAt.Time.String(),
+	}
 	if err != nil {
 		c.JSON(http.StatusConflict, gin.H{"error": "User already exists"})
 		return
@@ -458,10 +485,11 @@ func createBetterAuthUser(c *gin.Context, name string, email string, password st
 }
 
 func countLocalUsers(db *database.DB) (int, error) {
-	var count int
+	count64, err := sqlcdb.New(db.DB).CountUsers(context.Background())
-	if err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&count); err != nil {
+	if err != nil {
 		return 0, err
 	}
+	count := int(count64)
 	// Better Auth writes to auth_users; a row there only mirrors into users on
 	// the first authenticated call. Count it too or a second public sign-up
 	// slips through the bootstrap window. UNION by email avoids double-counting
@@ -489,11 +517,15 @@ func handleGetProfile(c *gin.Context) {
 	db := c.MustGet("db").(*database.DB)
 
 	var user User
-	err := db.QueryRow(`
+	urow, err := sqlcdb.New(db.DB).GetUserByID(context.Background(), uuid.MustParse(userID))
-		SELECT id, email, name, COALESCE(avatar_url, ''), is_admin, created_at
+	user = User{
-		FROM users
+		ID:        urow.ID.String(),
-		WHERE id = $1
+		Email:     urow.Email,
-	`, userID).Scan(&user.ID, &user.Email, &user.Name, &user.AvatarURL, &user.IsAdmin, &user.CreatedAt)
+		Name:      urow.Name,
+		AvatarURL: urow.AvatarUrl,
+		IsAdmin:   urow.IsAdmin,
+		CreatedAt: urow.CreatedAt.Time.String(),
+	}
 
 	if err == sql.ErrNoRows {
 		c.JSON(http.StatusNotFound, gin.H{"error": "User not found"})
@@ -521,11 +553,11 @@ func handleUpdateProfile(c *gin.Context) {
 	}
 
 	// Update user profile
-	_, err := db.Exec(`
+	err := sqlcdb.New(db.DB).UpdateUserProfile(context.Background(), sqlcdb.UpdateUserProfileParams{
-		UPDATE users 
+		Column1: req.Name,
-		SET name = COALESCE($1, name), avatar_url = COALESCE($2, avatar_url)
+		Column2: req.AvatarURL,
-		WHERE id = $3
+		ID:      uuid.MustParse(userID),
-	`, req.Name, req.AvatarURL, userID)
+	})
 
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to update profile"})

app/backend/internal/api/git.go

@@ -2,6 +2,8 @@ package api
 
 import (
 	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
+	"context"
 	"crypto/rand"
 	"crypto/rsa"
 	"crypto/x509"
@@ -92,41 +94,73 @@ type CreateWebhookRequest struct {
 	Branch string   `json:"branch"`
 }
 
+func gitRepoFrom(r sqlcdb.ListGitRepositoriesRow) GitRepository {
+	return GitRepository{
+		ID:            r.ID.String(),
+		ProviderID:    r.ProviderID.String(),
+		Name:          r.Name,
+		FullName:      r.FullName,
+		Description:   r.Description,
+		CloneURL:      r.CloneUrl,
+		WebhookURL:    r.WebhookUrl,
+		DefaultBranch: r.DefaultBranch.String,
+		IsPrivate:     r.IsPrivate.Bool,
+		UserID:        r.UserID.String(),
+		CreatedAt:     r.CreatedAt.Time,
+		UpdatedAt:     r.UpdatedAt.Time,
+	}
+}
+
+func gitRepoFromFullNameRow(r sqlcdb.GetGitRepositoryByFullNameRow) GitRepository {
+	return GitRepository{
+		ID:            r.ID.String(),
+		ProviderID:    r.ProviderID.String(),
+		Name:          r.Name,
+		FullName:      r.FullName,
+		Description:   r.Description,
+		CloneURL:      r.CloneUrl,
+		WebhookURL:    r.WebhookUrl,
+		DefaultBranch: r.DefaultBranch.String,
+		IsPrivate:     r.IsPrivate.Bool,
+		UserID:        r.UserID.String(),
+		CreatedAt:     r.CreatedAt.Time,
+		UpdatedAt:     r.UpdatedAt.Time,
+	}
+}
+
+func gitProviderFrom(p sqlcdb.ListGitProvidersRow) GitProvider {
+	return GitProvider{
+		ID:          p.ID.String(),
+		Name:        p.Name,
+		DisplayName: p.DisplayName,
+		APIUrl:      p.ApiUrl,
+		WebhookUrl:  p.WebhookUrl,
+		UserID:      p.UserID.String(),
+		CreatedAt:   p.CreatedAt.Time,
+		UpdatedAt:   p.UpdatedAt.Time,
+	}
+}
+
 func handleGetGitProviders(c *gin.Context) {
 	ctx, ok := requireGitRequestContext(c)
 	if !ok {
 		return
 	}
 
-	rows, err := ctx.db.Query(`
+	userID, err := uuid.Parse(ctx.userID)
-		SELECT id, name, display_name, api_url, webhook_url, user_id, created_at, updated_at
+	if err != nil {
-		FROM git_providers
+		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
-		WHERE user_id = $1
+		return
-		ORDER BY created_at DESC
+	}
-	`, ctx.userID)
+	rows, err := sqlcdb.New(ctx.db.DB).ListGitProviders(context.Background(), userID)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
-	defer rows.Close()
 
-	providers := make([]GitProvider, 0)
+	providers := make([]GitProvider, 0, len(rows))
-	for rows.Next() {
+	for _, p := range rows {
-		var provider GitProvider
+		providers = append(providers, gitProviderFrom(p))
-		if err := rows.Scan(
-			&provider.ID,
-			&provider.Name,
-			&provider.DisplayName,
-			&provider.APIUrl,
-			&provider.WebhookUrl,
-			&provider.UserID,
-			&provider.CreatedAt,
-			&provider.UpdatedAt,
-		); err != nil {
-			c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
-			return
-		}
-		providers = append(providers, provider)
 	}
 
 	c.JSON(http.StatusOK, gin.H{"providers": providers})
@@ -173,21 +207,22 @@ func handleConnectGitHubApp(c *gin.Context) {
 		UserID:      ctx.userID,
 	}
 
-	if err := ctx.db.QueryRow(`
+	prow, err := sqlcdb.New(ctx.db.DB).UpsertGitHubAppProvider(context.Background(), sqlcdb.UpsertGitHubAppProviderParams{
-		INSERT INTO git_providers (id, name, display_name, api_url, webhook_url, access_token, user_id, created_at, updated_at)
+		ID:          uuid.MustParse(provider.ID),
-		VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
+		Name:        provider.Name,
-		ON CONFLICT (name, user_id)
+		DisplayName: provider.DisplayName,
-		DO UPDATE SET
+		ApiUrl:      provider.APIUrl,
-			display_name = EXCLUDED.display_name,
+		WebhookUrl:  provider.WebhookUrl,
-			api_url = EXCLUDED.api_url,
+		AccessToken: provider.AccessToken,
-			webhook_url = EXCLUDED.webhook_url,
+		UserID:      uuid.MustParse(provider.UserID),
-			access_token = EXCLUDED.access_token,
+	})
-			updated_at = NOW()
+	if err != nil {
-		RETURNING id, created_at, updated_at
-	`, provider.ID, provider.Name, provider.DisplayName, provider.APIUrl, provider.WebhookUrl, provider.AccessToken, provider.UserID).Scan(&provider.ID, &provider.CreatedAt, &provider.UpdatedAt); err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to connect GitHub App installation"})
 		return
 	}
+	provider.ID = prow.ID.String()
+	provider.CreatedAt = prow.CreatedAt.Time
+	provider.UpdatedAt = prow.UpdatedAt.Time
 
 	provider.AccessToken = ""
 	c.JSON(http.StatusOK, gin.H{
@@ -259,10 +294,16 @@ func handleCreateGitProvider(c *gin.Context) {
 		return
 	}
 
-	if _, err := ctx.db.Exec(`
+	q := sqlcdb.New(ctx.db.DB)
-		INSERT INTO git_providers (id, name, display_name, api_url, webhook_url, access_token, user_id, created_at, updated_at)
+	if err := q.InsertGitProvider(context.Background(), sqlcdb.InsertGitProviderParams{
-		VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
+		ID:          uuid.MustParse(provider.ID),
-	`, provider.ID, provider.Name, provider.DisplayName, provider.APIUrl, provider.WebhookUrl, provider.AccessToken, provider.UserID); err != nil {
+		Name:        provider.Name,
+		DisplayName: provider.DisplayName,
+		ApiUrl:      provider.APIUrl,
+		WebhookUrl:  provider.WebhookUrl,
+		AccessToken: provider.AccessToken,
+		UserID:      uuid.MustParse(provider.UserID),
+	}); err != nil {
 		if isUniqueConstraintError(err) {
 			c.JSON(http.StatusConflict, gin.H{"error": "Provider already connected for this account"})
 			return
@@ -271,13 +312,12 @@ func handleCreateGitProvider(c *gin.Context) {
 		return
 	}
 
-	if err := ctx.db.QueryRow(`
+	if ts, err := q.GetGitProviderTimestamps(context.Background(), uuid.MustParse(provider.ID)); err != nil {
-		SELECT created_at, updated_at
-		FROM git_providers
-		WHERE id = $1
-	`, provider.ID).Scan(&provider.CreatedAt, &provider.UpdatedAt); err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to read created provider"})
 		return
+	} else {
+		provider.CreatedAt = ts.CreatedAt.Time
+		provider.UpdatedAt = ts.UpdatedAt.Time
 	}
 
 	provider.AccessToken = ""
@@ -296,12 +336,11 @@ func handleGetGitRepositories(c *gin.Context) {
 		return
 	}
 
-	var providerName, providerDisplayName, providerAccessToken, providerAPIURL string
+	q := sqlcdb.New(ctx.db.DB)
-	err := ctx.db.QueryRow(`
+	provRow, err := q.GetGitProviderForRepos(context.Background(), sqlcdb.GetGitProviderForReposParams{
-		SELECT name, display_name, access_token, api_url
+		ID:     uuid.MustParse(providerID),
-		FROM git_providers
+		UserID: uuid.MustParse(ctx.userID),
-		WHERE id = $1 AND user_id = $2
+	})
-	`, providerID, ctx.userID).Scan(&providerName, &providerDisplayName, &providerAccessToken, &providerAPIURL)
 	if err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			c.JSON(http.StatusNotFound, gin.H{"error": "Provider not found"})
@@ -310,6 +349,10 @@ func handleGetGitRepositories(c *gin.Context) {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
+	providerName := provRow.Name
+	providerDisplayName := provRow.DisplayName
+	providerAccessToken := provRow.AccessToken
+	providerAPIURL := provRow.ApiUrl
 
 	if c.DefaultQuery("sync", "1") != "0" {
 		if syncErr := syncProviderRepositories(ctx.db, providerID, ctx.userID, providerName, providerAPIURL, providerAccessToken); syncErr != nil {
@@ -324,53 +367,30 @@ func handleGetGitRepositories(c *gin.Context) {
 	}
 	offset := (page - 1) * limit
 	search := strings.TrimSpace(c.Query("search"))
-	searchNullable := sql.NullString{String: search, Valid: search != ""}
 
-	rows, err := ctx.db.Query(`
+	repoRows, err := q.ListGitRepositories(context.Background(), sqlcdb.ListGitRepositoriesParams{
-		SELECT id, provider_id, name, full_name, COALESCE(description, ''), clone_url, COALESCE(webhook_url, ''),
+		ProviderID: uuid.MustParse(providerID),
-			   default_branch, is_private, user_id, created_at, updated_at
+		UserID:     uuid.MustParse(ctx.userID),
-		FROM git_repositories
+		Column3:    search,
-		WHERE provider_id = $1 AND user_id = $2
+		Limit:      int32(limit),
-		  AND ($3::text IS NULL OR full_name ILIKE ('%' || $3::text || '%') OR name ILIKE ('%' || $3::text || '%'))
+		Offset:     int32(offset),
-		ORDER BY updated_at DESC
+	})
-		LIMIT $4 OFFSET $5
-	`, providerID, ctx.userID, searchNullable, limit, offset)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
-	defer rows.Close()
 
-	repositories := make([]GitRepository, 0)
+	repositories := make([]GitRepository, 0, len(repoRows))
-	for rows.Next() {
+	for _, r := range repoRows {
-		var repo GitRepository
+		repositories = append(repositories, gitRepoFrom(r))
-		if err := rows.Scan(
-			&repo.ID,
-			&repo.ProviderID,
-			&repo.Name,
-			&repo.FullName,
-			&repo.Description,
-			&repo.CloneURL,
-			&repo.WebhookURL,
-			&repo.DefaultBranch,
-			&repo.IsPrivate,
-			&repo.UserID,
-			&repo.CreatedAt,
-			&repo.UpdatedAt,
-		); err != nil {
-			c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
-			return
-		}
-		repositories = append(repositories, repo)
 	}
 
-	var total int
+	total, err := q.CountGitRepositories(context.Background(), sqlcdb.CountGitRepositoriesParams{
-	if err := ctx.db.QueryRow(`
+		ProviderID: uuid.MustParse(providerID),
-		SELECT COUNT(*)
+		UserID:     uuid.MustParse(ctx.userID),
-		FROM git_repositories
+		Column3:    search,
-		WHERE provider_id = $1 AND user_id = $2
+	})
-		  AND ($3::text IS NULL OR full_name ILIKE ('%' || $3::text || '%') OR name ILIKE ('%' || $3::text || '%'))
+	if err != nil {
-	`, providerID, ctx.userID, searchNullable).Scan(&total); err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
@@ -408,12 +428,11 @@ func handleConnectGitRepository(c *gin.Context) {
 		return
 	}
 
-	var providerName string
+	q := sqlcdb.New(ctx.db.DB)
-	err := ctx.db.QueryRow(`
+	providerName, err := q.GetGitProviderName(context.Background(), sqlcdb.GetGitProviderNameParams{
-		SELECT name
+		ID:     uuid.MustParse(providerID),
-		FROM git_providers
+		UserID: uuid.MustParse(ctx.userID),
-		WHERE id = $1 AND user_id = $2
+	})
-	`, providerID, ctx.userID).Scan(&providerName)
 	if err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			c.JSON(http.StatusNotFound, gin.H{"error": "Provider not found"})
@@ -429,31 +448,16 @@ func handleConnectGitRepository(c *gin.Context) {
 		return
 	}
 
-	var existing GitRepository
+	existingRow, err := q.GetGitRepositoryByFullName(context.Background(), sqlcdb.GetGitRepositoryByFullNameParams{
-	err = ctx.db.QueryRow(`
+		ProviderID: uuid.MustParse(providerID),
-		SELECT id, provider_id, name, full_name, COALESCE(description, ''), clone_url, COALESCE(webhook_url, ''),
+		FullName:   fullName,
-			   default_branch, is_private, user_id, created_at, updated_at
+		UserID:     uuid.MustParse(ctx.userID),
-		FROM git_repositories
+	})
-		WHERE provider_id = $1 AND full_name = $2 AND user_id = $3
-	`, providerID, fullName, ctx.userID).Scan(
-		&existing.ID,
-		&existing.ProviderID,
-		&existing.Name,
-		&existing.FullName,
-		&existing.Description,
-		&existing.CloneURL,
-		&existing.WebhookURL,
-		&existing.DefaultBranch,
-		&existing.IsPrivate,
-		&existing.UserID,
-		&existing.CreatedAt,
-		&existing.UpdatedAt,
-	)
 	switch {
 	case err == nil:
 		c.JSON(http.StatusOK, gin.H{
 			"message":    "Repository already connected",
-			"repository": existing,
+			"repository": gitRepoFromFullNameRow(existingRow),
 		})
 		return
 	case !errors.Is(err, sql.ErrNoRows):
@@ -473,13 +477,18 @@ func handleConnectGitRepository(c *gin.Context) {
 		UserID:        ctx.userID,
 	}
 
-	if err := ctx.db.QueryRow(`
+	ins, err := q.InsertGitRepository(context.Background(), sqlcdb.InsertGitRepositoryParams{
-		INSERT INTO git_repositories (
+		ID:            uuid.MustParse(repo.ID),
-			id, provider_id, name, full_name, description, clone_url, default_branch, is_private, user_id, created_at, updated_at
+		ProviderID:    uuid.MustParse(repo.ProviderID),
-		)
+		Name:          repo.Name,
-		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
+		FullName:      repo.FullName,
-		RETURNING created_at, updated_at
+		Description:   sql.NullString{String: repo.Description, Valid: true},
-	`, repo.ID, repo.ProviderID, repo.Name, repo.FullName, repo.Description, repo.CloneURL, repo.DefaultBranch, repo.IsPrivate, repo.UserID).Scan(&repo.CreatedAt, &repo.UpdatedAt); err != nil {
+		CloneUrl:      repo.CloneURL,
+		DefaultBranch: sql.NullString{String: repo.DefaultBranch, Valid: true},
+		IsPrivate:     sql.NullBool{Bool: repo.IsPrivate, Valid: true},
+		UserID:        uuid.MustParse(repo.UserID),
+	})
+	if err != nil {
 		if isUniqueConstraintError(err) {
 			c.JSON(http.StatusConflict, gin.H{"error": "Repository is already connected"})
 			return
@@ -487,6 +496,8 @@ func handleConnectGitRepository(c *gin.Context) {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to connect repository"})
 		return
 	}
+	repo.CreatedAt = ins.CreatedAt.Time
+	repo.UpdatedAt = ins.UpdatedAt.Time
 
 	c.JSON(http.StatusCreated, gin.H{
 		"message":    "Repository connected successfully",
@@ -516,12 +527,10 @@ func handleCreateWebhook(c *gin.Context) {
 		return
 	}
 
-	var providerID string
+	providerUUID, err := sqlcdb.New(ctx.db.DB).GetGitRepositoryProviderID(context.Background(), sqlcdb.GetGitRepositoryProviderIDParams{
-	err := ctx.db.QueryRow(`
+		ID:     uuid.MustParse(repoID),
-		SELECT provider_id
+		UserID: uuid.MustParse(ctx.userID),
-		FROM git_repositories
+	})
-		WHERE id = $1 AND user_id = $2
-	`, repoID, ctx.userID).Scan(&providerID)
 	if err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			c.JSON(http.StatusNotFound, gin.H{"error": "Repository not found"})
@@ -530,6 +539,7 @@ func handleCreateWebhook(c *gin.Context) {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
+	providerID := providerUUID.String()
 
 	eventsJSON, err := json.Marshal(req.Events)
 	if err != nil {
@@ -552,23 +562,22 @@ func handleCreateWebhook(c *gin.Context) {
 		Active:     true,
 	}
 
-	if err := ctx.db.QueryRow(`
+	wrow, err := sqlcdb.New(ctx.db.DB).UpsertGitWebhook(context.Background(), sqlcdb.UpsertGitWebhookParams{
-		INSERT INTO git_webhooks (
+		ID:            uuid.MustParse(webhook.ID),
-			id, repo_id, provider_id, events, webhook_secret, active, branch_filter, created_at, updated_at
+		RepoID:        uuid.MustParse(webhook.RepoID),
-		)
+		ProviderID:    uuid.MustParse(webhook.ProviderID),
-		VALUES ($1, $2, $3, $4, $5, TRUE, $6, NOW(), NOW())
+		Events:        webhook.Events,
-		ON CONFLICT (repo_id, provider_id)
+		WebhookSecret: webhook.Secret,
-		DO UPDATE SET
+		BranchFilter:  sql.NullString{String: strings.TrimSpace(req.Branch), Valid: true},
-			events = EXCLUDED.events,
+	})
-			webhook_secret = EXCLUDED.webhook_secret,
+	if err != nil {
-			active = TRUE,
-			branch_filter = EXCLUDED.branch_filter,
-			updated_at = NOW()
-		RETURNING id, active, created_at, updated_at
-	`, webhook.ID, webhook.RepoID, webhook.ProviderID, webhook.Events, webhook.Secret, strings.TrimSpace(req.Branch)).Scan(&webhook.ID, &webhook.Active, &webhook.CreatedAt, &webhook.UpdatedAt); err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to configure webhook"})
 		return
 	}
+	webhook.ID = wrow.ID.String()
+	webhook.Active = wrow.Active.Bool
+	webhook.CreatedAt = wrow.CreatedAt.Time
+	webhook.UpdatedAt = wrow.UpdatedAt.Time
 
 	c.JSON(http.StatusCreated, gin.H{
 		"message": "Webhook configured successfully",
@@ -598,69 +607,39 @@ func handleGetConnectedRepositories(c *gin.Context) {
 	}
 	offset := (page - 1) * limit
 
-	rows, err := ctx.db.Query(`
+	q := sqlcdb.New(ctx.db.DB)
-		SELECT r.id, r.provider_id, r.name, r.full_name, COALESCE(r.description, ''), r.clone_url,
+	repoRows, err := q.ListConnectedRepositories(context.Background(), sqlcdb.ListConnectedRepositoriesParams{
-			   r.default_branch, r.is_private, r.user_id, r.created_at, r.updated_at,
+		UserID: uuid.MustParse(ctx.userID),
-			   p.name as provider_name, p.display_name
+		Limit:  int32(limit),
-		FROM git_repositories r
+		Offset: int32(offset),
-		JOIN git_providers p ON r.provider_id = p.id
+	})
-		WHERE r.user_id = $1
-		ORDER BY r.updated_at DESC
-		LIMIT $2 OFFSET $3
-	`, ctx.userID, limit, offset)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
-	defer rows.Close()
-
-	repositories := make([]map[string]interface{}, 0)
-	for rows.Next() {
-		var repo GitRepository
-		var providerName, providerDisplayName string
-		if err := rows.Scan(
-			&repo.ID,
-			&repo.ProviderID,
-			&repo.Name,
-			&repo.FullName,
-			&repo.Description,
-			&repo.CloneURL,
-			&repo.DefaultBranch,
-			&repo.IsPrivate,
-			&repo.UserID,
-			&repo.CreatedAt,
-			&repo.UpdatedAt,
-			&providerName,
-			&providerDisplayName,
-		); err != nil {
-			c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
-			return
-		}
 
+	repositories := make([]map[string]interface{}, 0, len(repoRows))
+	for _, r := range repoRows {
 		repositories = append(repositories, map[string]interface{}{
-			"id":             repo.ID,
+			"id":             r.ID.String(),
-			"provider_id":    repo.ProviderID,
+			"provider_id":    r.ProviderID.String(),
-			"name":           repo.Name,
+			"name":           r.Name,
-			"full_name":      repo.FullName,
+			"full_name":      r.FullName,
-			"description":    repo.Description,
+			"description":    r.Description,
-			"clone_url":      repo.CloneURL,
+			"clone_url":      r.CloneUrl,
-			"default_branch": repo.DefaultBranch,
+			"default_branch": r.DefaultBranch.String,
-			"is_private":     repo.IsPrivate,
+			"is_private":     r.IsPrivate.Bool,
-			"created_at":     repo.CreatedAt,
+			"created_at":     r.CreatedAt.Time,
-			"updated_at":     repo.UpdatedAt,
+			"updated_at":     r.UpdatedAt.Time,
 			"provider": map[string]string{
-				"name":         providerName,
+				"name":         r.ProviderName,
-				"display_name": providerDisplayName,
+				"display_name": r.DisplayName,
 			},
 		})
 	}
 
-	var total int
+	total, err := q.CountUserRepositories(context.Background(), uuid.MustParse(ctx.userID))
-	if err := ctx.db.QueryRow(`
+	if err != nil {
-		SELECT COUNT(*)
-		FROM git_repositories
-		WHERE user_id = $1
-	`, ctx.userID).Scan(&total); err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
@@ -705,20 +684,17 @@ func syncProviderRepositories(db *database.DB, providerID, userID, providerName,
 			defaultBranch = "main"
 		}
 
-		_, err := db.Exec(`
+		err := sqlcdb.New(db.DB).UpsertGitRepository(context.Background(), sqlcdb.UpsertGitRepositoryParams{
-			INSERT INTO git_repositories (
+			ID:            uuid.New(),
-				id, provider_id, name, full_name, description, clone_url, default_branch, is_private, user_id, created_at, updated_at
+			ProviderID:    uuid.MustParse(providerID),
-			)
+			Name:          repository.Name,
-			VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
+			FullName:      repository.FullName,
-			ON CONFLICT (provider_id, full_name)
+			Description:   sql.NullString{String: repository.Description, Valid: true},
-			DO UPDATE SET
+			CloneUrl:      cloneURL,
-				name = EXCLUDED.name,
+			DefaultBranch: sql.NullString{String: defaultBranch, Valid: true},
-				description = EXCLUDED.description,
+			IsPrivate:     sql.NullBool{Bool: repository.IsPrivate, Valid: true},
-				clone_url = EXCLUDED.clone_url,
+			UserID:        uuid.MustParse(userID),
-				default_branch = EXCLUDED.default_branch,
+		})
-				is_private = EXCLUDED.is_private,
-				updated_at = NOW()
-		`, uuid.NewString(), providerID, repository.Name, repository.FullName, repository.Description, cloneURL, defaultBranch, repository.IsPrivate, userID)
 		if err != nil {
 			return err
 		}
@@ -1323,16 +1299,15 @@ func handleDeleteGitProvider(c *gin.Context) {
 		return
 	}
 
-	result, err := ctx.db.Exec(
+	affected, err := sqlcdb.New(ctx.db.DB).DeleteGitProvider(context.Background(), sqlcdb.DeleteGitProviderParams{
-		"DELETE FROM git_providers WHERE id = $1 AND user_id = $2",
+		ID:     uuid.MustParse(providerID),
-		providerID, ctx.userID,
+		UserID: uuid.MustParse(ctx.userID),
-	)
+	})
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to delete provider"})
 		return
 	}
 
-	affected, _ := result.RowsAffected()
 	if affected == 0 {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Provider not found"})
 		return
@@ -1362,12 +1337,10 @@ func handleGetGitRepositoryBranches(c *gin.Context) {
 		return
 	}
 
-	var providerName, providerAPIURL, providerAccessToken string
+	pf, err := sqlcdb.New(ctx.db.DB).GetGitProviderForFetch(context.Background(), sqlcdb.GetGitProviderForFetchParams{
-	err := ctx.db.QueryRow(`
+		ID:     uuid.MustParse(providerID),
-		SELECT name, api_url, access_token
+		UserID: uuid.MustParse(ctx.userID),
-		FROM git_providers
+	})
-		WHERE id = $1 AND user_id = $2
-	`, providerID, ctx.userID).Scan(&providerName, &providerAPIURL, &providerAccessToken)
 	if err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			c.JSON(http.StatusNotFound, gin.H{"error": "Provider not found"})
@@ -1376,6 +1349,9 @@ func handleGetGitRepositoryBranches(c *gin.Context) {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
 		return
 	}
+	providerName := pf.Name
+	providerAPIURL := pf.ApiUrl
+	providerAccessToken := pf.AccessToken
 
 	branches, err := fetchRepositoryBranches(providerName, providerAPIURL, providerAccessToken, owner, repo)
 	if err != nil {

app/backend/internal/api/git_receiver.go

@@ -5,6 +5,7 @@ import (
 	"crypto/hmac"
 	"crypto/sha256"
 	"crypto/subtle"
+	"database/sql"
 	"encoding/hex"
 	"encoding/json"
 	"fmt"
@@ -14,6 +15,7 @@ import (
 	"time"
 
 	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
 	"containr/internal/deployment"
 	"containr/internal/deployqueue"
 
@@ -38,22 +40,15 @@ func handleGitWebhookPush(c *gin.Context) {
 		return
 	}
 
-	var (
+	q := sqlcdb.New(db.DB)
-		secret       string
+	hook, err := q.GetGitWebhookForPush(context.Background(), uuid.MustParse(webhookID))
-		repoID       string
-		providerID   string
-		branchFilter string
-		active       bool
-	)
-	err := db.QueryRow(`
-		SELECT webhook_secret, repo_id, provider_id, COALESCE(branch_filter, ''), active
-		FROM git_webhooks WHERE id = $1`, webhookID).
-		Scan(&secret, &repoID, &providerID, &branchFilter, &active)
 	if err != nil {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Webhook not found"})
 		return
 	}
-	if !active {
+	secret := hook.WebhookSecret
+	branchFilter := hook.BranchFilter
+	if !hook.Active.Bool {
 		c.JSON(http.StatusForbidden, gin.H{"error": "Webhook is disabled"})
 		return
 	}
@@ -64,8 +59,7 @@ func handleGitWebhookPush(c *gin.Context) {
 		return
 	}
 
-	var providerName string
+	providerName, _ := q.GetGitProviderNameByID(context.Background(), hook.ProviderID)
-	_ = db.QueryRow(`SELECT name FROM git_providers WHERE id = $1`, providerID).Scan(&providerName)
 
 	if !verifyGitWebhookSignature(providerName, secret, c.Request, body) {
 		c.JSON(http.StatusUnauthorized, gin.H{"error": "Invalid signature"})
@@ -118,14 +112,12 @@ func handleGitWebhookPush(c *gin.Context) {
 		commit = payload.After
 	}
 
-	var cloneURL, fullName, repoUserID string
+	repo, err := q.GetGitRepoForPush(context.Background(), hook.RepoID)
-	err = db.QueryRow(
-		`SELECT clone_url, full_name, user_id FROM git_repositories WHERE id = $1`, repoID,
-	).Scan(&cloneURL, &fullName, &repoUserID)
 	if err != nil {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Repository not found"})
 		return
 	}
+	cloneURL, fullName, repoUserID := repo.CloneUrl, repo.FullName, repo.UserID.String()
 
 	engineValue, _ := c.Get("deployment_engine")
 	engine, _ := engineValue.(*deployment.DeploymentEngine)
@@ -147,34 +139,37 @@ func handleGitWebhookPush(c *gin.Context) {
 // the pushed repo+branch. Shared by the per-repo webhook receiver and the
 // GitHub App webhook endpoint.
 func dispatchPushToServices(c *gin.Context, db *database.DB, engine *deployment.DeploymentEngine, cloneURL, fullName, branch, commit, repoUserID string) (int, error) {
-	// Sync-style events may carry no branch — match services on repo only.
+	q := sqlcdb.New(db.DB)
-	branchClause := "s.git_branch = $1"
+	ctx := context.Background()
-	if branch == "" {
+	// Sync-style events may carry no branch — empty branch matches all.
-		branchClause = "TRUE"
+	rows, err := q.ListServicesForPush(ctx, sqlcdb.ListServicesForPushParams{
-	}
+		GitBranch: sql.NullString{String: branch, Valid: true},
-	rows, err := db.Query(fmt.Sprintf(`
+		GitRepo:   sql.NullString{String: cloneURL, Valid: true},
-		SELECT s.id, s.project_id, s.name, s.type, s.status, s.image, s.command,
+		GitRepo_2: sql.NullString{String: fullName, Valid: true},
-		       s.environment, s.git_repo, s.git_branch, s.build_path, s.cpu, s.memory,
+	})
-		       s.created_at, s.updated_at
-		FROM services s
-		WHERE %s AND (s.git_repo = $2 OR s.git_repo = $3)`, branchClause),
-		branch, cloneURL, fullName)
 	if err != nil {
 		return 0, err
 	}
-	defer rows.Close()
 
 	var services []Service
-	for rows.Next() {
+	for _, row := range rows {
-		var s Service
+		services = append(services, Service{
-		if err := rows.Scan(
+			ID:          row.ID,
-			&s.ID, &s.ProjectID, &s.Name, &s.Type, &s.Status, &s.Image, &s.Command,
+			ProjectID:   row.ProjectID,
-			&s.Environment, &s.GitRepo, &s.GitBranch, &s.BuildPath, &s.CPU, &s.Memory,
+			Name:        row.Name,
-			&s.CreatedAt, &s.UpdatedAt,
+			Type:        row.Type,
-		); err != nil {
+			Status:      row.Status,
-			return 0, err
+			Image:       row.Image,
-		}
+			Command:     row.Command,
-		services = append(services, s)
+			Environment: row.Environment,
+			GitRepo:     row.GitRepo,
+			GitBranch:   row.GitBranch,
+			BuildPath:   row.BuildPath,
+			CPU:         row.Cpu,
+			Memory:      row.Memory,
+			CreatedAt:   row.CreatedAt.Time,
+			UpdatedAt:   row.UpdatedAt.Time,
+		})
 	}
 
 	enqueued := 0
@@ -192,27 +187,34 @@ func dispatchPushToServices(c *gin.Context, db *database.DB, engine *deployment.
 			CreatedAt:  now,
 			UpdatedAt:  now,
 		}
-		if _, err := db.Exec(
+		if err := q.InsertDeployment(ctx, sqlcdb.InsertDeploymentParams{
-			`INSERT INTO deployments
+			ID:         d.ID,
-			 (id, service_id, version, commit_hash, status, image_name, image_tag, created_at, updated_at)
+			ServiceID:  d.ServiceID,
-			 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
+			Version:    fmt.Sprintf("v%d", now.Unix()),
-			d.ID, d.ServiceID, fmt.Sprintf("v%d", now.Unix()), d.CommitHash, d.Status, d.ImageName, d.ImageTag, d.CreatedAt, d.UpdatedAt,
+			CommitHash: sql.NullString{String: ptrStr(d.CommitHash), Valid: d.CommitHash != nil},
-		); err != nil {
+			Status:     sql.NullString{String: d.Status, Valid: true},
+			ImageName:  sql.NullString{String: d.ImageName, Valid: true},
+			ImageTag:   sql.NullString{String: d.ImageTag, Valid: true},
+			CreatedAt:  sql.NullTime{Time: d.CreatedAt, Valid: true},
+			UpdatedAt:  sql.NullTime{Time: d.UpdatedAt, Valid: true},
+		}); err != nil {
 			continue
 		}
 		if engine == nil {
-			failedAt := time.Now()
 			failure := "Deployment engine unavailable. Docker may not be configured on this server."
-			_, _ = db.Exec(
+			failedAt := time.Now()
-				`UPDATE deployments SET status = 'failed', error = $1, completed_at = $2, updated_at = $2 WHERE id = $3`,
+			_ = q.FailDeployment(ctx, sqlcdb.FailDeploymentParams{
-				failure, failedAt, d.ID,
+				Error:       sql.NullString{String: failure, Valid: true},
-			)
+				CompletedAt: sql.NullTime{Time: failedAt, Valid: true},
+				ID:          d.ID,
+			})
 			continue
 		}
-		_, _ = db.Exec(
+		_ = q.SetServiceStatus(ctx, sqlcdb.SetServiceStatusParams{
-			`UPDATE services SET status = 'building', updated_at = $1 WHERE id = $2`,
+			Status:    sql.NullString{String: "building", Valid: true},
-			time.Now(), service.ID,
+			UpdatedAt: sql.NullTime{Time: time.Now(), Valid: true},
-		)
+			ID:        service.ID,
+		})
 		// Webhook pushes default to a clean build — stale layers are the
 		// classic silent-rollback failure (dflow convention).
 		branchForBuild := branch
@@ -226,14 +228,17 @@ func dispatchPushToServices(c *gin.Context, db *database.DB, engine *deployment.
 				runDeploymentAndSync(jctx, db, engine, &d, service, webhookReq, repoUserID)
 			},
 		}); pos > 0 {
-			_, _ = db.Exec(
+			now := sql.NullTime{Time: time.Now(), Valid: true}
-				`UPDATE deployments SET status = 'queued', updated_at = $1 WHERE id = $2`,
+			_ = q.SetDeploymentStatus(ctx, sqlcdb.SetDeploymentStatusParams{
-				time.Now(), d.ID,
+				Status:    sql.NullString{String: "queued", Valid: true},
-			)
+				UpdatedAt: now,
-			_, _ = db.Exec(
+				ID:        d.ID,
-				`UPDATE services SET status = 'queued', updated_at = $1 WHERE id = $2`,
+			})
-				time.Now(), service.ID,
+			_ = q.SetServiceStatus(ctx, sqlcdb.SetServiceStatusParams{
-			)
+				Status:    sql.NullString{String: "queued", Valid: true},
+				UpdatedAt: now,
+				ID:        service.ID,
+			})
 		}
 		enqueued++
 	}

app/backend/internal/api/git_test.go

@@ -1,6 +1,16 @@
 package api
 
-import "testing"
+import (
+	"context"
+	"database/sql"
+	"strings"
+	"testing"
+
+	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
+
+	"github.com/google/uuid"
+)
 
 func TestNormalizeRepositoryFullName(t *testing.T) {
 	cases := []struct {
@@ -83,3 +93,195 @@ func TestDeriveCloneURL(t *testing.T) {
 		})
 	}
 }
+
+// Live invariant test against real Postgres — set METRICS_TEST_DSN (or
+// DATABASE_URL). Covers the generated git provider/repo/webhook queries plus
+// the push-dispatch service matcher (empty branch must match all branches).
+func TestGitLiveInvariants(t *testing.T) {
+	dsn := testDSNString(t)
+	db, err := database.NewConnection(dsn)
+	if err != nil {
+		t.Fatalf("connect: %v", err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	q := sqlcdb.New(db.DB)
+
+	userID := uuid.New()
+	projectID := uuid.New()
+	envID := uuid.New()
+	providerID := uuid.New()
+	repoID := uuid.New()
+	webhookID := uuid.New()
+	svcMain := uuid.New()
+	svcDev := uuid.New()
+	suffix := strings.ReplaceAll(userID.String()[:8], "-", "")
+	must := func(err error) {
+		t.Helper()
+		if err != nil {
+			t.Fatalf("setup: %v", err)
+		}
+	}
+	_, err = db.Exec(`INSERT INTO users (id, email, password_hash, name) VALUES ($1, $2, 'x', 'test')`,
+		userID, "git-"+suffix+"@test.local")
+	must(err)
+	_, err = db.Exec(`INSERT INTO projects (id, name, owner_id, is_approved) VALUES ($1, $2, $3, true)`,
+		projectID, "git-"+suffix, userID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO environments (id, name, project_id) VALUES ($1, 'production', $2)`,
+		envID, projectID)
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM users WHERE id = $1`, userID) }()
+
+	// Provider lifecycle.
+	must(q.InsertGitProvider(ctx, sqlcdb.InsertGitProviderParams{
+		ID: providerID, Name: "github", DisplayName: "GitHub",
+		ApiUrl: "https://api.github.com", WebhookUrl: "https://hooks.test",
+		AccessToken: "tok", UserID: userID,
+	}))
+	providers, err := q.ListGitProviders(ctx, userID)
+	if err != nil || len(providers) == 0 {
+		t.Fatalf("providers: %v %v", err, providers)
+	}
+	name, err := q.GetGitProviderNameByID(ctx, providerID)
+	if err != nil || name != "github" {
+		t.Fatalf("provider name: %v %q", err, name)
+	}
+	fetch, err := q.GetGitProviderForFetch(ctx, sqlcdb.GetGitProviderForFetchParams{ID: providerID, UserID: userID})
+	if err != nil || fetch.AccessToken != "tok" || fetch.ApiUrl != "https://api.github.com" {
+		t.Fatalf("fetch: %v %+v", err, fetch)
+	}
+
+	// Repository insert + search.
+	cloneURL := "https://github.com/acme/svc-" + suffix + ".git"
+	fullName := "acme/svc-" + suffix
+	_, err = q.InsertGitRepository(ctx, sqlcdb.InsertGitRepositoryParams{
+		ID: repoID, ProviderID: providerID, Name: "svc-" + suffix, FullName: fullName,
+		Description:   sql.NullString{String: "", Valid: true},
+		CloneUrl:      cloneURL,
+		DefaultBranch: sql.NullString{String: "main", Valid: true},
+		IsPrivate:     sql.NullBool{Bool: false, Valid: true},
+		UserID:        userID,
+	})
+	must(err)
+	byName, err := q.GetGitRepositoryByFullName(ctx, sqlcdb.GetGitRepositoryByFullNameParams{
+		ProviderID: providerID, FullName: fullName, UserID: userID,
+	})
+	if err != nil || byName.ID != repoID || byName.CloneUrl != cloneURL {
+		t.Fatalf("repo by full name: %v %+v", err, byName)
+	}
+	all, err := q.ListGitRepositories(ctx, sqlcdb.ListGitRepositoriesParams{
+		ProviderID: providerID, UserID: userID, Column3: "", Limit: 50, Offset: 0,
+	})
+	if err != nil || len(all) == 0 {
+		t.Fatalf("repo list (empty search): %v %v", err, all)
+	}
+	hit, err := q.ListGitRepositories(ctx, sqlcdb.ListGitRepositoriesParams{
+		ProviderID: providerID, UserID: userID, Column3: "svc-" + suffix, Limit: 50, Offset: 0,
+	})
+	if err != nil || len(hit) != 1 || hit[0].ID != repoID {
+		t.Fatalf("repo list (matching search): %v %v", err, hit)
+	}
+	miss, err := q.ListGitRepositories(ctx, sqlcdb.ListGitRepositoriesParams{
+		ProviderID: providerID, UserID: userID, Column3: "zzz-no-match", Limit: 50, Offset: 0,
+	})
+	if err != nil || len(miss) != 0 {
+		t.Fatalf("repo list (non-matching search): %v %v", err, miss)
+	}
+	count, err := q.CountGitRepositories(ctx, sqlcdb.CountGitRepositoriesParams{
+		ProviderID: providerID, UserID: userID, Column3: "",
+	})
+	if err != nil || count < 1 {
+		t.Fatalf("repo count: %v %d", err, count)
+	}
+	// Upsert on (provider_id, full_name) conflict — updates rather than errors.
+	must(q.UpsertGitRepository(ctx, sqlcdb.UpsertGitRepositoryParams{
+		ID: uuid.New(), ProviderID: providerID, Name: "renamed-" + suffix, FullName: fullName,
+		Description:   sql.NullString{String: "d", Valid: true},
+		CloneUrl:      cloneURL,
+		DefaultBranch: sql.NullString{String: "main", Valid: true},
+		IsPrivate:     sql.NullBool{Bool: true, Valid: true},
+		UserID:        userID,
+	}))
+	byName, err = q.GetGitRepositoryByFullName(ctx, sqlcdb.GetGitRepositoryByFullNameParams{
+		ProviderID: providerID, FullName: fullName, UserID: userID,
+	})
+	if err != nil || byName.Name != "renamed-"+suffix {
+		t.Fatalf("repo after upsert: %v %+v", err, byName)
+	}
+
+	// Connected-repos join + webhook lifecycle.
+	connected, err := q.ListConnectedRepositories(ctx, sqlcdb.ListConnectedRepositoriesParams{
+		UserID: userID, Limit: 50, Offset: 0,
+	})
+	if err != nil || len(connected) == 0 {
+		t.Fatalf("connected repos: %v %v", err, connected)
+	}
+	wh, err := q.UpsertGitWebhook(ctx, sqlcdb.UpsertGitWebhookParams{
+		ID: webhookID, RepoID: repoID, ProviderID: providerID,
+		Events: "push", WebhookSecret: "sec-" + suffix,
+		BranchFilter: sql.NullString{String: "", Valid: true},
+	})
+	must(err)
+	if !wh.Active.Bool {
+		t.Fatalf("webhook inactive after upsert: %+v", wh)
+	}
+	push, err := q.GetGitWebhookForPush(ctx, webhookID)
+	if err != nil || push.WebhookSecret != "sec-"+suffix || push.ProviderID != providerID {
+		t.Fatalf("webhook for push: %v %+v", err, push)
+	}
+	pushRepo, err := q.GetGitRepoForPush(ctx, repoID)
+	if err != nil || pushRepo.CloneUrl != cloneURL || pushRepo.FullName != fullName || pushRepo.UserID != userID {
+		t.Fatalf("repo for push: %v %+v", err, pushRepo)
+	}
+
+	// Push-dispatch service matcher: branch filter and empty-branch match-all.
+	for id, branch := range map[uuid.UUID]string{svcMain: "main", svcDev: "dev"} {
+		_, err = db.Exec(`INSERT INTO services (id, name, project_id, environment_id, service_type, source_type, git_repo, git_branch)
+			VALUES ($1, $2, $3, $4, 'web', 'git', $5, $6)`,
+			id, "svc-"+suffix+"-"+branch, projectID, envID, cloneURL, branch)
+		must(err)
+	}
+	servicesFor := func(branch string) []sqlcdb.ListServicesForPushRow {
+		rows, err := q.ListServicesForPush(ctx, sqlcdb.ListServicesForPushParams{
+			GitBranch: sql.NullString{String: branch, Valid: true},
+			GitRepo:   sql.NullString{String: cloneURL, Valid: true},
+			GitRepo_2: sql.NullString{String: fullName, Valid: true},
+		})
+		if err != nil {
+			t.Fatalf("services for push %q: %v", branch, err)
+		}
+		return rows
+	}
+	mainRows := servicesFor("main")
+	if len(mainRows) != 1 || mainRows[0].ID != svcMain || mainRows[0].GitBranch != "main" {
+		t.Fatalf("main branch dispatch: %+v", mainRows)
+	}
+	devRows := servicesFor("dev")
+	if len(devRows) != 1 || devRows[0].ID != svcDev {
+		t.Fatalf("dev branch dispatch: %+v", devRows)
+	}
+	allRows := servicesFor("")
+	if len(allRows) != 2 {
+		t.Fatalf("empty-branch dispatch should match all: %+v", allRows)
+	}
+	// Repo may also be referenced by its full_name.
+	fullRows, err := q.ListServicesForPush(ctx, sqlcdb.ListServicesForPushParams{
+		GitBranch: sql.NullString{String: "main", Valid: true},
+		GitRepo:   sql.NullString{String: "[email protected]:other/none.git", Valid: true},
+		GitRepo_2: sql.NullString{String: cloneURL, Valid: true},
+	})
+	if err != nil || len(fullRows) != 1 {
+		t.Fatalf("full-name repo match: %v %+v", err, fullRows)
+	}
+
+	// Provider delete is owner-scoped.
+	other, err := q.DeleteGitProvider(ctx, sqlcdb.DeleteGitProviderParams{ID: providerID, UserID: uuid.New()})
+	if err != nil || other != 0 {
+		t.Fatalf("foreign delete affected %d rows: %v", other, err)
+	}
+	deleted, err := q.DeleteGitProvider(ctx, sqlcdb.DeleteGitProviderParams{ID: providerID, UserID: userID})
+	if err != nil || deleted != 1 {
+		t.Fatalf("owner delete affected %d rows: %v", deleted, err)
+	}
+}

app/backend/internal/api/security.go

@@ -2,7 +2,9 @@ package api
 
 import (
 	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
 	"containr/internal/security"
+	"context"
 	"database/sql"
 	"encoding/json"
 	"fmt"
@@ -33,8 +35,8 @@ func NewSecurityHandler(db *database.DB, encryptionKey string) *SecurityHandler
 	// Notify the project owner when a background scan completes — criticals
 	// earn a distinct title so they stand out in the notification list.
 	scanner.OnScanComplete = func(scan *security.SecurityScan) {
-		var ownerID string
+		ownerID, err := sqlcdb.New(db.DB).GetProjectOwnerText(context.Background(), uuid.MustParse(scan.ProjectID))
-		if err := db.QueryRow(`SELECT owner_id::text FROM projects WHERE id = $1`, scan.ProjectID).Scan(&ownerID); err != nil || ownerID == "" {
+		if err != nil || ownerID == "" {
 			return
 		}
 		title := "Security scan completed"
@@ -81,14 +83,10 @@ func (sh *SecurityHandler) StartSecurityScan(c *gin.Context) {
 			return
 		}
 
-		var serviceExists bool
+		serviceExists, err := sqlcdb.New(sh.db.DB).ServiceExistsInProject(context.Background(), sqlcdb.ServiceExistsInProjectParams{
-		err := sh.db.QueryRow(
+			ID:        uuid.MustParse(req.ServiceID),
-			`SELECT EXISTS(
+			ProjectID: uuid.MustParse(req.ProjectID),
-				SELECT 1 FROM services WHERE id = $1 AND project_id = $2
+		})
-			)`,
-			req.ServiceID,
-			req.ProjectID,
-		).Scan(&serviceExists)
 		if err != nil {
 			c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to validate service"})
 			return
@@ -164,40 +162,30 @@ func (sh *SecurityHandler) GetVulnerabilities(c *gin.Context) {
 	}
 
 	// Query vulnerabilities
-	rows, err := sh.db.Query(`
+	rows, err := sqlcdb.New(sh.db.DB).ListVulnerabilities(context.Background(), uuid.MustParse(projectID))
-		SELECT id, type, severity, title, description, service_id, status, found_at, resolved_at
-		FROM vulnerabilities 
-		WHERE project_id = $1 
-		ORDER BY 
-			CASE severity 
-				WHEN 'critical' THEN 1 
-				WHEN 'high' THEN 2 
-				WHEN 'medium' THEN 3 
-				WHEN 'low' THEN 4 
-			END,
-			found_at DESC
-	`, projectID)
-
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to get vulnerabilities"})
 		return
 	}
-	defer rows.Close()
 
 	var vulnerabilities []security.Vulnerability
-	for rows.Next() {
+	for _, row := range rows {
-		var vuln security.Vulnerability
+		// NULL description/service_id failed the original scan and dropped
-		var resolvedAt *time.Time
+		// the row — keep the same filter.
-
+		if !row.Description.Valid || !row.ServiceID.Valid {
-		err := rows.Scan(&vuln.ID, &vuln.Type, &vuln.Severity, &vuln.Title, &vuln.Description,
-			&vuln.ServiceID, &vuln.Status, &vuln.FoundAt, &resolvedAt)
-
-		if err != nil {
 			continue
 		}
-
+		vulnerabilities = append(vulnerabilities, security.Vulnerability{
-		vuln.ResolvedAt = resolvedAt
+			ID:          row.ID.String(),
-		vulnerabilities = append(vulnerabilities, vuln)
+			Type:        row.Type,
+			Severity:    row.Severity,
+			Title:       row.Title,
+			Description: row.Description.String,
+			ServiceID:   row.ServiceID.UUID.String(),
+			Status:      row.Status,
+			FoundAt:     row.FoundAt,
+			ResolvedAt:  timePtrNT(row.ResolvedAt),
+		})
 	}
 
 	c.JSON(http.StatusOK, gin.H{"vulnerabilities": vulnerabilities})
@@ -227,11 +215,11 @@ func (sh *SecurityHandler) UpdateVulnerability(c *gin.Context) {
 		resolvedAt = &now
 	}
 
-	_, err := sh.db.Exec(`
+	err := sqlcdb.New(sh.db.DB).UpdateVulnerabilityStatus(context.Background(), sqlcdb.UpdateVulnerabilityStatusParams{
-		UPDATE vulnerabilities 
+		Status:     req.Status,
-		SET status = $1, resolved_at = $2 
+		ResolvedAt: ntPtr(resolvedAt),
-		WHERE id = $3
+		ID:         uuid.MustParse(vulnID),
-	`, req.Status, resolvedAt, vulnID)
+	})
 
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to update vulnerability"})
@@ -271,13 +259,7 @@ func (sh *SecurityHandler) StartComplianceAssessment(c *gin.Context) {
 		return
 	}
 
-	var frameworkExists bool
+	frameworkExists, err := sqlcdb.New(sh.db.DB).ComplianceFrameworkExists(context.Background(), uuid.MustParse(req.FrameworkID))
-	err := sh.db.QueryRow(
-		`SELECT EXISTS(
-			SELECT 1 FROM compliance_frameworks WHERE id = $1
-		)`,
-		req.FrameworkID,
-	).Scan(&frameworkExists)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to validate framework"})
 		return
@@ -321,30 +303,25 @@ func (sh *SecurityHandler) GetComplianceReport(c *gin.Context) {
 
 // GetComplianceFrameworks retrieves available compliance frameworks
 func (sh *SecurityHandler) GetComplianceFrameworks(c *gin.Context) {
-	rows, err := sh.db.Query(`
+	rows, err := sqlcdb.New(sh.db.DB).ListComplianceFrameworks(context.Background())
-		SELECT id, name, description, version, enabled, created_at
-		FROM compliance_frameworks 
-		WHERE enabled = true 
-		ORDER BY name
-	`)
-
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to get compliance frameworks"})
 		return
 	}
-	defer rows.Close()
 
 	var frameworks []security.ComplianceFramework
-	for rows.Next() {
+	for _, row := range rows {
-		var framework security.ComplianceFramework
+		if !row.Description.Valid {
-		err := rows.Scan(&framework.ID, &framework.Name, &framework.Description,
-			&framework.Version, &framework.Enabled, &framework.CreatedAt)
-
-		if err != nil {
 			continue
 		}
-
+		frameworks = append(frameworks, security.ComplianceFramework{
-		frameworks = append(frameworks, framework)
+			ID:          row.ID.String(),
+			Name:        row.Name,
+			Description: row.Description.String,
+			Version:     row.Version,
+			Enabled:     row.Enabled,
+			CreatedAt:   row.CreatedAt,
+		})
 	}
 
 	c.JSON(http.StatusOK, gin.H{"frameworks": frameworks})
@@ -385,23 +362,19 @@ func (sh *SecurityHandler) GetSecurityMetrics(c *gin.Context) {
 		Resolved int `json:"resolved"`
 	}
 
-	err := sh.db.QueryRow(`
+	q := sqlcdb.New(sh.db.DB)
-		SELECT 
+	mrow, err := q.GetVulnerabilityMetrics(context.Background(), uuid.MustParse(projectID))
-			COUNT(*) as total,
-			COUNT(*) FILTER (WHERE severity = 'critical') as critical,
-			COUNT(*) FILTER (WHERE severity = 'high') as high,
-			COUNT(*) FILTER (WHERE severity = 'medium') as medium,
-			COUNT(*) FILTER (WHERE severity = 'low') as low,
-			COUNT(*) FILTER (WHERE status = 'open') as open,
-			COUNT(*) FILTER (WHERE status = 'resolved') as resolved
-		FROM vulnerabilities 
-		WHERE project_id = $1
-	`, projectID).Scan(&vulnMetrics.Total, &vulnMetrics.Critical, &vulnMetrics.High,
-		&vulnMetrics.Medium, &vulnMetrics.Low, &vulnMetrics.Open, &vulnMetrics.Resolved)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to get vulnerability metrics"})
 		return
 	}
+	vulnMetrics.Total = int(mrow.Total)
+	vulnMetrics.Critical = int(mrow.Critical)
+	vulnMetrics.High = int(mrow.High)
+	vulnMetrics.Medium = int(mrow.Medium)
+	vulnMetrics.Low = int(mrow.Low)
+	vulnMetrics.Open = int(mrow.Open)
+	vulnMetrics.Resolved = int(mrow.Resolved)
 
 	// Get latest scan
 	var latestScan struct {
@@ -411,13 +384,11 @@ func (sh *SecurityHandler) GetSecurityMetrics(c *gin.Context) {
 		Status    string    `json:"status"`
 	}
 
-	err = sh.db.QueryRow(`
+	srow, err := q.GetLatestSecurityScan(context.Background(), uuid.MustParse(projectID))
-		SELECT id, COALESCE((summary->>'score')::int, 0), started_at as scanned_at, status
+	latestScan.ID = srow.ID.String()
-		FROM security_scans
+	latestScan.Score = int(srow.Score)
-		WHERE project_id = $1
+	latestScan.ScannedAt = srow.ScannedAt
-		ORDER BY started_at DESC
+	latestScan.Status = srow.Status
-		LIMIT 1
-	`, projectID).Scan(&latestScan.ID, &latestScan.Score, &latestScan.ScannedAt, &latestScan.Status)
 
 	if err == sql.ErrNoRows {
 		latestScan = struct {
@@ -438,13 +409,10 @@ func (sh *SecurityHandler) GetSecurityMetrics(c *gin.Context) {
 		LastAssessed  *time.Time `json:"last_assessed"`
 	}
 
-	err = sh.db.QueryRow(`
+	crow, err := q.GetLatestComplianceReport(context.Background(), uuid.MustParse(projectID))
-		SELECT overall_status, score, assessment_date
+	complianceStatus.OverallStatus = crow.OverallStatus
-		FROM compliance_reports 
+	complianceStatus.Score = int(crow.Score)
-		WHERE project_id = $1 
+	complianceStatus.LastAssessed = &crow.AssessmentDate
-		ORDER BY assessment_date DESC 
-		LIMIT 1
-	`, projectID).Scan(&complianceStatus.OverallStatus, &complianceStatus.Score, &complianceStatus.LastAssessed)
 
 	if err == sql.ErrNoRows {
 		complianceStatus = struct {
@@ -614,21 +582,11 @@ func (sh *SecurityHandler) requireProjectAccess(c *gin.Context, projectID string
 		return "", false
 	}
 
-	var hasAccess bool
+	hasAccess, err := sqlcdb.New(sh.db.DB).CheckSecurityProjectAccess(context.Background(), sqlcdb.CheckSecurityProjectAccessParams{
-	err := sh.db.QueryRow(
+		ID:      uuid.MustParse(projectID),
-		`SELECT EXISTS (
+		OwnerID: optionalUserUUID(c),
-			SELECT 1
+		Column3: contextIsAdmin(c),
-			FROM projects p
+	})
-			WHERE p.id = $1
-			  AND (p.is_approved
-				   OR $3::bool
-				   OR p.owner_id = $2 OR EXISTS (
-					SELECT 1 FROM project_members pm
-					WHERE pm.project_id = p.id AND pm.user_id = $2
-			  ))
-		)`,
-		projectID, optionalUserUUID(c), contextIsAdmin(c),
-	).Scan(&hasAccess)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to verify project access"})
 		return "", false
@@ -649,7 +607,8 @@ func (sh *SecurityHandler) requireSecurityScanAccess(c *gin.Context, scanID stri
 	}
 
 	var projectID string
-	err := sh.db.QueryRow("SELECT project_id FROM security_scans WHERE id = $1", scanID).Scan(&projectID)
+	pid, err := sqlcdb.New(sh.db.DB).GetSecurityScanProjectID(context.Background(), uuid.MustParse(scanID))
+	projectID = pid.String()
 	if err == sql.ErrNoRows {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Security scan not found"})
 		return false
@@ -670,7 +629,8 @@ func (sh *SecurityHandler) requireComplianceReportAccess(c *gin.Context, reportI
 	}
 
 	var projectID string
-	err := sh.db.QueryRow("SELECT project_id FROM compliance_reports WHERE id = $1", reportID).Scan(&projectID)
+	pid, err := sqlcdb.New(sh.db.DB).GetComplianceReportProjectID(context.Background(), uuid.MustParse(reportID))
+	projectID = pid.String()
 	if err == sql.ErrNoRows {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Compliance report not found"})
 		return false
@@ -691,7 +651,8 @@ func (sh *SecurityHandler) requireVulnerabilityAccess(c *gin.Context, vulnID str
 	}
 
 	var projectID string
-	err := sh.db.QueryRow("SELECT project_id FROM vulnerabilities WHERE id = $1", vulnID).Scan(&projectID)
+	pid, err := sqlcdb.New(sh.db.DB).GetVulnerabilityProjectID(context.Background(), uuid.MustParse(vulnID))
+	projectID = pid.String()
 	if err == sql.ErrNoRows {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Vulnerability not found"})
 		return "", false

app/backend/internal/api/security_test.go

@@ -0,0 +1,212 @@
+package api
+
+import (
+	"context"
+	"database/sql"
+	"strings"
+	"testing"
+	"time"
+
+	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
+
+	"github.com/google/uuid"
+)
+
+// Live invariant test against real Postgres — set METRICS_TEST_DSN (or
+// DATABASE_URL). Covers the generated auth/security queries: user upsert
+// conflict path, project-access EXISTS predicate, vulnerability ordering and
+// status update, latest-scan/report lookups, and the framework list.
+func TestSecurityLiveInvariants(t *testing.T) {
+	dsn := testDSNString(t)
+	db, err := database.NewConnection(dsn)
+	if err != nil {
+		t.Fatalf("connect: %v", err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	q := sqlcdb.New(db.DB)
+
+	userID := uuid.New()
+	otherID := uuid.New()
+	projectID := uuid.New()
+	envID := uuid.New()
+	serviceID := uuid.New()
+	scanID := uuid.New()
+	reportID := uuid.New()
+	frameworkID := uuid.New()
+	vulnCrit := uuid.New()
+	vulnLow := uuid.New()
+	suffix := strings.ReplaceAll(userID.String()[:8], "-", "")
+	must := func(err error) {
+		t.Helper()
+		if err != nil {
+			t.Fatalf("setup: %v", err)
+		}
+	}
+	_, err = db.Exec(`INSERT INTO users (id, email, password_hash, name) VALUES ($1, $2, 'x', 'test'), ($3, $4, 'x', 'other')`,
+		userID, "sec-"+suffix+"@test.local", otherID, "sec-"+suffix+"[email protected]")
+	must(err)
+	_, err = db.Exec(`INSERT INTO projects (id, name, owner_id, is_approved) VALUES ($1, $2, $3, false)`,
+		projectID, "sec-"+suffix, userID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO environments (id, name, project_id) VALUES ($1, 'production', $2)`,
+		envID, projectID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO services (id, name, project_id, environment_id, service_type, source_type)
+		VALUES ($1, $2, $3, $4, 'web', 'image')`,
+		serviceID, "sec-"+suffix, projectID, envID)
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM users WHERE id IN ($1, $2)`, userID, otherID) }()
+
+	// Auth queries: upsert conflicts update rather than error, counts see rows.
+	_, err = q.InsertUserAdmin(ctx, sqlcdb.InsertUserAdminParams{
+		Email: "ins-" + suffix + "@test.local", PasswordHash: "x", Name: "ins", IsAdmin: false,
+	})
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM users WHERE email = $1`, "ins-"+suffix+"@test.local") }()
+	urow, err := q.UpsertLocalUser(ctx, sqlcdb.UpsertLocalUserParams{
+		Email: "ins-" + suffix + "@test.local", PasswordHash: "y", Name: "renamed",
+		Column4: "http://img", IsAdmin: false,
+	})
+	must(err)
+	if urow.Name != "renamed" || urow.AvatarUrl != "http://img" {
+		t.Fatalf("upsert: %+v", urow)
+	}
+	byEmail, err := q.GetUserByEmailForAuth(ctx, "ins-"+suffix+"@test.local")
+	must(err)
+	if byEmail.ID != urow.ID || byEmail.PasswordHash != "x" {
+		t.Fatalf("by email: %+v", byEmail)
+	}
+	count, err := q.CountUsersByEmail(ctx, "ins-"+suffix+"@test.local")
+	must(err)
+	if count != 1 {
+		t.Fatalf("count by email: %d", count)
+	}
+	byID, err := q.GetUserByID(ctx, urow.ID)
+	must(err)
+	if byID.Email != "ins-"+suffix+"@test.local" {
+		t.Fatalf("by id: %+v", byID)
+	}
+
+	// Access predicate: unapproved project is owner/admin/member only.
+	ownerAccess, err := q.CheckSecurityProjectAccess(ctx, sqlcdb.CheckSecurityProjectAccessParams{
+		ID: projectID, OwnerID: userID, Column3: false,
+	})
+	must(err)
+	if !ownerAccess {
+		t.Fatal("owner should have access")
+	}
+	strangerAccess, err := q.CheckSecurityProjectAccess(ctx, sqlcdb.CheckSecurityProjectAccessParams{
+		ID: projectID, OwnerID: otherID, Column3: false,
+	})
+	must(err)
+	if strangerAccess {
+		t.Fatal("stranger should not have access")
+	}
+	adminAccess, err := q.CheckSecurityProjectAccess(ctx, sqlcdb.CheckSecurityProjectAccessParams{
+		ID: projectID, OwnerID: otherID, Column3: true,
+	})
+	must(err)
+	if !adminAccess {
+		t.Fatal("admin should have access")
+	}
+	ownerText, err := q.GetProjectOwnerText(ctx, projectID)
+	must(err)
+	if ownerText != userID.String() {
+		t.Fatalf("owner text: %q", ownerText)
+	}
+	exists, err := q.ServiceExistsInProject(ctx, sqlcdb.ServiceExistsInProjectParams{
+		ID: serviceID, ProjectID: projectID,
+	})
+	must(err)
+	if !exists {
+		t.Fatal("service should exist in project")
+	}
+
+	// Scan/report/vuln rows via raw inserts (fixtures), reads via sqlc.
+	_, err = db.Exec(`INSERT INTO security_scans (id, project_id, service_id, scan_type, status, summary, started_at)
+		VALUES ($1, $2, $3, 'dependency', 'completed', '{"score": 85}', NOW() - interval '1 hour')`,
+		scanID, projectID, serviceID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO compliance_frameworks (id, name, description, version, enabled)
+		VALUES ($1, $2, 'desc', '1.0', true)`,
+		frameworkID, "fw-"+suffix)
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM compliance_frameworks WHERE id = $1`, frameworkID) }()
+	_, err = db.Exec(`INSERT INTO compliance_reports (id, project_id, framework_id, overall_status, score, assessment_date)
+		VALUES ($1, $2, $3, 'compliant', 92, NOW() - interval '30 minutes')`,
+		reportID, projectID, frameworkID)
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM compliance_reports WHERE id = $1`, reportID) }()
+	_, err = db.Exec(`INSERT INTO vulnerabilities (id, type, severity, title, description, service_id, project_id, status, found_at)
+		VALUES ($1, 'dependency', 'low', 'low-v', 'd', $3, $4, 'open', NOW() - interval '2 hours'),
+		       ($2, 'dependency', 'critical', 'crit-v', 'd', $3, $4, 'open', NOW() - interval '1 hour')`,
+		vulnLow, vulnCrit, serviceID, projectID)
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM vulnerabilities WHERE project_id = $1`, projectID) }()
+	defer func() { _, _ = db.Exec(`DELETE FROM security_scans WHERE id = $1`, scanID) }()
+
+	// Severity ordering: critical first.
+	vulns, err := q.ListVulnerabilities(ctx, projectID)
+	must(err)
+	if len(vulns) != 2 || vulns[0].ID != vulnCrit {
+		t.Fatalf("vuln ordering: %+v", vulns)
+	}
+	metrics, err := q.GetVulnerabilityMetrics(ctx, projectID)
+	must(err)
+	if metrics.Total != 2 || metrics.Critical != 1 || metrics.Low != 1 || metrics.Open != 2 {
+		t.Fatalf("metrics: %+v", metrics)
+	}
+	must(q.UpdateVulnerabilityStatus(ctx, sqlcdb.UpdateVulnerabilityStatusParams{
+		Status:     "resolved",
+		ResolvedAt: sql.NullTime{Time: time.Now(), Valid: true},
+		ID:         vulnCrit,
+	}))
+	metrics, err = q.GetVulnerabilityMetrics(ctx, projectID)
+	must(err)
+	if metrics.Open != 1 || metrics.Resolved != 1 {
+		t.Fatalf("metrics after resolve: %+v", metrics)
+	}
+
+	// Latest scan/report lookups.
+	scan, err := q.GetLatestSecurityScan(ctx, projectID)
+	must(err)
+	if scan.ID != scanID || scan.Score != 85 || scan.Status != "completed" {
+		t.Fatalf("latest scan: %+v", scan)
+	}
+	report, err := q.GetLatestComplianceReport(ctx, projectID)
+	must(err)
+	if report.OverallStatus != "compliant" || report.Score != 92 {
+		t.Fatalf("latest report: %+v", report)
+	}
+
+	// Project-ID lookups used by the access guards.
+	scanPID, err := q.GetSecurityScanProjectID(ctx, scanID)
+	must(err)
+	reportPID, err := q.GetComplianceReportProjectID(ctx, reportID)
+	must(err)
+	vulnPID, err := q.GetVulnerabilityProjectID(ctx, vulnCrit)
+	must(err)
+	if scanPID != projectID || reportPID != projectID || vulnPID != projectID {
+		t.Fatalf("project id lookups: %v %v %v", scanPID, reportPID, vulnPID)
+	}
+
+	// Framework list only returns enabled rows.
+	frameworks, err := q.ListComplianceFrameworks(ctx)
+	must(err)
+	found := false
+	for _, f := range frameworks {
+		if f.ID == frameworkID && f.Enabled {
+			found = true
+		}
+	}
+	if !found {
+		t.Fatal("enabled framework missing from list")
+	}
+	frameworkExists, err := q.ComplianceFrameworkExists(ctx, frameworkID)
+	must(err)
+	if !frameworkExists {
+		t.Fatal("framework should exist")
+	}
+}

app/backend/internal/database/sqlcdb/auth.sql.go

@@ -0,0 +1,233 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: auth.sql
+
+package sqlcdb
+
+import (
+	"context"
+	"database/sql"
+
+	"github.com/google/uuid"
+)
+
+const countUsers = `-- name: CountUsers :one
+SELECT COUNT(*) FROM users
+`
+
+func (q *Queries) CountUsers(ctx context.Context) (int64, error) {
+	row := q.db.QueryRowContext(ctx, countUsers)
+	var count int64
+	err := row.Scan(&count)
+	return count, err
+}
+
+const countUsersByEmail = `-- name: CountUsersByEmail :one
+SELECT COUNT(*) FROM users WHERE email = $1
+`
+
+func (q *Queries) CountUsersByEmail(ctx context.Context, email string) (int64, error) {
+	row := q.db.QueryRowContext(ctx, countUsersByEmail, email)
+	var count int64
+	err := row.Scan(&count)
+	return count, err
+}
+
+const getUserByEmailForAuth = `-- name: GetUserByEmailForAuth :one
+SELECT id, email, password_hash, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+FROM users WHERE email = $1
+`
+
+type GetUserByEmailForAuthRow struct {
+	ID           uuid.UUID    `json:"id"`
+	Email        string       `json:"email"`
+	PasswordHash string       `json:"password_hash"`
+	Name         string       `json:"name"`
+	AvatarUrl    string       `json:"avatar_url"`
+	IsAdmin      bool         `json:"is_admin"`
+	CreatedAt    sql.NullTime `json:"created_at"`
+}
+
+func (q *Queries) GetUserByEmailForAuth(ctx context.Context, email string) (GetUserByEmailForAuthRow, error) {
+	row := q.db.QueryRowContext(ctx, getUserByEmailForAuth, email)
+	var i GetUserByEmailForAuthRow
+	err := row.Scan(
+		&i.ID,
+		&i.Email,
+		&i.PasswordHash,
+		&i.Name,
+		&i.AvatarUrl,
+		&i.IsAdmin,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const getUserByID = `-- name: GetUserByID :one
+SELECT id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+FROM users WHERE id = $1
+`
+
+type GetUserByIDRow struct {
+	ID        uuid.UUID    `json:"id"`
+	Email     string       `json:"email"`
+	Name      string       `json:"name"`
+	AvatarUrl string       `json:"avatar_url"`
+	IsAdmin   bool         `json:"is_admin"`
+	CreatedAt sql.NullTime `json:"created_at"`
+}
+
+func (q *Queries) GetUserByID(ctx context.Context, id uuid.UUID) (GetUserByIDRow, error) {
+	row := q.db.QueryRowContext(ctx, getUserByID, id)
+	var i GetUserByIDRow
+	err := row.Scan(
+		&i.ID,
+		&i.Email,
+		&i.Name,
+		&i.AvatarUrl,
+		&i.IsAdmin,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const insertUser = `-- name: InsertUser :one
+INSERT INTO users (email, password_hash, name)
+VALUES ($1, $2, $3)
+RETURNING id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+`
+
+type InsertUserParams struct {
+	Email        string `json:"email"`
+	PasswordHash string `json:"password_hash"`
+	Name         string `json:"name"`
+}
+
+type InsertUserRow struct {
+	ID        uuid.UUID    `json:"id"`
+	Email     string       `json:"email"`
+	Name      string       `json:"name"`
+	AvatarUrl string       `json:"avatar_url"`
+	IsAdmin   bool         `json:"is_admin"`
+	CreatedAt sql.NullTime `json:"created_at"`
+}
+
+func (q *Queries) InsertUser(ctx context.Context, arg InsertUserParams) (InsertUserRow, error) {
+	row := q.db.QueryRowContext(ctx, insertUser, arg.Email, arg.PasswordHash, arg.Name)
+	var i InsertUserRow
+	err := row.Scan(
+		&i.ID,
+		&i.Email,
+		&i.Name,
+		&i.AvatarUrl,
+		&i.IsAdmin,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const insertUserAdmin = `-- name: InsertUserAdmin :one
+INSERT INTO users (email, password_hash, name, is_admin)
+VALUES ($1, $2, $3, $4)
+RETURNING id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+`
+
+type InsertUserAdminParams struct {
+	Email        string `json:"email"`
+	PasswordHash string `json:"password_hash"`
+	Name         string `json:"name"`
+	IsAdmin      bool   `json:"is_admin"`
+}
+
+type InsertUserAdminRow struct {
+	ID        uuid.UUID    `json:"id"`
+	Email     string       `json:"email"`
+	Name      string       `json:"name"`
+	AvatarUrl string       `json:"avatar_url"`
+	IsAdmin   bool         `json:"is_admin"`
+	CreatedAt sql.NullTime `json:"created_at"`
+}
+
+func (q *Queries) InsertUserAdmin(ctx context.Context, arg InsertUserAdminParams) (InsertUserAdminRow, error) {
+	row := q.db.QueryRowContext(ctx, insertUserAdmin,
+		arg.Email,
+		arg.PasswordHash,
+		arg.Name,
+		arg.IsAdmin,
+	)
+	var i InsertUserAdminRow
+	err := row.Scan(
+		&i.ID,
+		&i.Email,
+		&i.Name,
+		&i.AvatarUrl,
+		&i.IsAdmin,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const updateUserProfile = `-- name: UpdateUserProfile :exec
+UPDATE users
+SET name = COALESCE($1::varchar, name), avatar_url = COALESCE($2::varchar, avatar_url)
+WHERE id = $3
+`
+
+type UpdateUserProfileParams struct {
+	Column1 string    `json:"column_1"`
+	Column2 string    `json:"column_2"`
+	ID      uuid.UUID `json:"id"`
+}
+
+func (q *Queries) UpdateUserProfile(ctx context.Context, arg UpdateUserProfileParams) error {
+	_, err := q.db.ExecContext(ctx, updateUserProfile, arg.Column1, arg.Column2, arg.ID)
+	return err
+}
+
+const upsertLocalUser = `-- name: UpsertLocalUser :one
+INSERT INTO users (email, password_hash, name, avatar_url, is_admin)
+VALUES ($1, $2, $3, NULLIF($4::varchar, ''), $5)
+ON CONFLICT (email) DO UPDATE
+SET name = EXCLUDED.name,
+    avatar_url = COALESCE(EXCLUDED.avatar_url, users.avatar_url),
+    updated_at = NOW()
+RETURNING id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+`
+
+type UpsertLocalUserParams struct {
+	Email        string `json:"email"`
+	PasswordHash string `json:"password_hash"`
+	Name         string `json:"name"`
+	Column4      string `json:"column_4"`
+	IsAdmin      bool   `json:"is_admin"`
+}
+
+type UpsertLocalUserRow struct {
+	ID        uuid.UUID    `json:"id"`
+	Email     string       `json:"email"`
+	Name      string       `json:"name"`
+	AvatarUrl string       `json:"avatar_url"`
+	IsAdmin   bool         `json:"is_admin"`
+	CreatedAt sql.NullTime `json:"created_at"`
+}
+
+func (q *Queries) UpsertLocalUser(ctx context.Context, arg UpsertLocalUserParams) (UpsertLocalUserRow, error) {
+	row := q.db.QueryRowContext(ctx, upsertLocalUser,
+		arg.Email,
+		arg.PasswordHash,
+		arg.Name,
+		arg.Column4,
+		arg.IsAdmin,
+	)
+	var i UpsertLocalUserRow
+	err := row.Scan(
+		&i.ID,
+		&i.Email,
+		&i.Name,
+		&i.AvatarUrl,
+		&i.IsAdmin,
+		&i.CreatedAt,
+	)
+	return i, err
+}

app/backend/internal/database/sqlcdb/git.sql.go

@@ -0,0 +1,737 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: git.sql
+
+package sqlcdb
+
+import (
+	"context"
+	"database/sql"
+
+	"github.com/google/uuid"
+)
+
+const countGitRepositories = `-- name: CountGitRepositories :one
+SELECT COUNT(*)
+FROM git_repositories
+WHERE provider_id = $1 AND user_id = $2
+  AND ($3::text IS NULL OR full_name ILIKE ('%' || $3::text || '%') OR name ILIKE ('%' || $3::text || '%'))
+`
+
+type CountGitRepositoriesParams struct {
+	ProviderID uuid.UUID `json:"provider_id"`
+	UserID     uuid.UUID `json:"user_id"`
+	Column3    string    `json:"column_3"`
+}
+
+func (q *Queries) CountGitRepositories(ctx context.Context, arg CountGitRepositoriesParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, countGitRepositories, arg.ProviderID, arg.UserID, arg.Column3)
+	var count int64
+	err := row.Scan(&count)
+	return count, err
+}
+
+const countUserRepositories = `-- name: CountUserRepositories :one
+SELECT COUNT(*) FROM git_repositories WHERE user_id = $1
+`
+
+func (q *Queries) CountUserRepositories(ctx context.Context, userID uuid.UUID) (int64, error) {
+	row := q.db.QueryRowContext(ctx, countUserRepositories, userID)
+	var count int64
+	err := row.Scan(&count)
+	return count, err
+}
+
+const deleteGitProvider = `-- name: DeleteGitProvider :execrows
+DELETE FROM git_providers WHERE id = $1 AND user_id = $2
+`
+
+type DeleteGitProviderParams struct {
+	ID     uuid.UUID `json:"id"`
+	UserID uuid.UUID `json:"user_id"`
+}
+
+func (q *Queries) DeleteGitProvider(ctx context.Context, arg DeleteGitProviderParams) (int64, error) {
+	result, err := q.db.ExecContext(ctx, deleteGitProvider, arg.ID, arg.UserID)
+	if err != nil {
+		return 0, err
+	}
+	return result.RowsAffected()
+}
+
+const getGitProviderForFetch = `-- name: GetGitProviderForFetch :one
+SELECT name, api_url, access_token
+FROM git_providers
+WHERE id = $1 AND user_id = $2
+`
+
+type GetGitProviderForFetchParams struct {
+	ID     uuid.UUID `json:"id"`
+	UserID uuid.UUID `json:"user_id"`
+}
+
+type GetGitProviderForFetchRow struct {
+	Name        string `json:"name"`
+	ApiUrl      string `json:"api_url"`
+	AccessToken string `json:"access_token"`
+}
+
+func (q *Queries) GetGitProviderForFetch(ctx context.Context, arg GetGitProviderForFetchParams) (GetGitProviderForFetchRow, error) {
+	row := q.db.QueryRowContext(ctx, getGitProviderForFetch, arg.ID, arg.UserID)
+	var i GetGitProviderForFetchRow
+	err := row.Scan(&i.Name, &i.ApiUrl, &i.AccessToken)
+	return i, err
+}
+
+const getGitProviderForRepos = `-- name: GetGitProviderForRepos :one
+SELECT name, display_name, access_token, api_url
+FROM git_providers
+WHERE id = $1 AND user_id = $2
+`
+
+type GetGitProviderForReposParams struct {
+	ID     uuid.UUID `json:"id"`
+	UserID uuid.UUID `json:"user_id"`
+}
+
+type GetGitProviderForReposRow struct {
+	Name        string `json:"name"`
+	DisplayName string `json:"display_name"`
+	AccessToken string `json:"access_token"`
+	ApiUrl      string `json:"api_url"`
+}
+
+func (q *Queries) GetGitProviderForRepos(ctx context.Context, arg GetGitProviderForReposParams) (GetGitProviderForReposRow, error) {
+	row := q.db.QueryRowContext(ctx, getGitProviderForRepos, arg.ID, arg.UserID)
+	var i GetGitProviderForReposRow
+	err := row.Scan(
+		&i.Name,
+		&i.DisplayName,
+		&i.AccessToken,
+		&i.ApiUrl,
+	)
+	return i, err
+}
+
+const getGitProviderName = `-- name: GetGitProviderName :one
+SELECT name FROM git_providers WHERE id = $1 AND user_id = $2
+`
+
+type GetGitProviderNameParams struct {
+	ID     uuid.UUID `json:"id"`
+	UserID uuid.UUID `json:"user_id"`
+}
+
+func (q *Queries) GetGitProviderName(ctx context.Context, arg GetGitProviderNameParams) (string, error) {
+	row := q.db.QueryRowContext(ctx, getGitProviderName, arg.ID, arg.UserID)
+	var name string
+	err := row.Scan(&name)
+	return name, err
+}
+
+const getGitProviderNameByID = `-- name: GetGitProviderNameByID :one
+SELECT name FROM git_providers WHERE id = $1
+`
+
+func (q *Queries) GetGitProviderNameByID(ctx context.Context, id uuid.UUID) (string, error) {
+	row := q.db.QueryRowContext(ctx, getGitProviderNameByID, id)
+	var name string
+	err := row.Scan(&name)
+	return name, err
+}
+
+const getGitProviderTimestamps = `-- name: GetGitProviderTimestamps :one
+SELECT created_at, updated_at FROM git_providers WHERE id = $1
+`
+
+type GetGitProviderTimestampsRow struct {
+	CreatedAt sql.NullTime `json:"created_at"`
+	UpdatedAt sql.NullTime `json:"updated_at"`
+}
+
+func (q *Queries) GetGitProviderTimestamps(ctx context.Context, id uuid.UUID) (GetGitProviderTimestampsRow, error) {
+	row := q.db.QueryRowContext(ctx, getGitProviderTimestamps, id)
+	var i GetGitProviderTimestampsRow
+	err := row.Scan(&i.CreatedAt, &i.UpdatedAt)
+	return i, err
+}
+
+const getGitRepoForPush = `-- name: GetGitRepoForPush :one
+SELECT clone_url, full_name, user_id FROM git_repositories WHERE id = $1
+`
+
+type GetGitRepoForPushRow struct {
+	CloneUrl string    `json:"clone_url"`
+	FullName string    `json:"full_name"`
+	UserID   uuid.UUID `json:"user_id"`
+}
+
+func (q *Queries) GetGitRepoForPush(ctx context.Context, id uuid.UUID) (GetGitRepoForPushRow, error) {
+	row := q.db.QueryRowContext(ctx, getGitRepoForPush, id)
+	var i GetGitRepoForPushRow
+	err := row.Scan(&i.CloneUrl, &i.FullName, &i.UserID)
+	return i, err
+}
+
+const getGitRepositoryByFullName = `-- name: GetGitRepositoryByFullName :one
+SELECT id, provider_id, name, full_name, COALESCE(description, '') AS description,
+       clone_url, COALESCE(webhook_url, '') AS webhook_url,
+       default_branch, is_private, user_id, created_at, updated_at
+FROM git_repositories
+WHERE provider_id = $1 AND full_name = $2 AND user_id = $3
+`
+
+type GetGitRepositoryByFullNameParams struct {
+	ProviderID uuid.UUID `json:"provider_id"`
+	FullName   string    `json:"full_name"`
+	UserID     uuid.UUID `json:"user_id"`
+}
+
+type GetGitRepositoryByFullNameRow struct {
+	ID            uuid.UUID      `json:"id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Name          string         `json:"name"`
+	FullName      string         `json:"full_name"`
+	Description   string         `json:"description"`
+	CloneUrl      string         `json:"clone_url"`
+	WebhookUrl    string         `json:"webhook_url"`
+	DefaultBranch sql.NullString `json:"default_branch"`
+	IsPrivate     sql.NullBool   `json:"is_private"`
+	UserID        uuid.UUID      `json:"user_id"`
+	CreatedAt     sql.NullTime   `json:"created_at"`
+	UpdatedAt     sql.NullTime   `json:"updated_at"`
+}
+
+func (q *Queries) GetGitRepositoryByFullName(ctx context.Context, arg GetGitRepositoryByFullNameParams) (GetGitRepositoryByFullNameRow, error) {
+	row := q.db.QueryRowContext(ctx, getGitRepositoryByFullName, arg.ProviderID, arg.FullName, arg.UserID)
+	var i GetGitRepositoryByFullNameRow
+	err := row.Scan(
+		&i.ID,
+		&i.ProviderID,
+		&i.Name,
+		&i.FullName,
+		&i.Description,
+		&i.CloneUrl,
+		&i.WebhookUrl,
+		&i.DefaultBranch,
+		&i.IsPrivate,
+		&i.UserID,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getGitRepositoryProviderID = `-- name: GetGitRepositoryProviderID :one
+SELECT provider_id FROM git_repositories WHERE id = $1 AND user_id = $2
+`
+
+type GetGitRepositoryProviderIDParams struct {
+	ID     uuid.UUID `json:"id"`
+	UserID uuid.UUID `json:"user_id"`
+}
+
+func (q *Queries) GetGitRepositoryProviderID(ctx context.Context, arg GetGitRepositoryProviderIDParams) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getGitRepositoryProviderID, arg.ID, arg.UserID)
+	var provider_id uuid.UUID
+	err := row.Scan(&provider_id)
+	return provider_id, err
+}
+
+const getGitWebhookForPush = `-- name: GetGitWebhookForPush :one
+SELECT webhook_secret, repo_id, provider_id, COALESCE(branch_filter, '') AS branch_filter, active
+FROM git_webhooks WHERE id = $1
+`
+
+type GetGitWebhookForPushRow struct {
+	WebhookSecret string       `json:"webhook_secret"`
+	RepoID        uuid.UUID    `json:"repo_id"`
+	ProviderID    uuid.UUID    `json:"provider_id"`
+	BranchFilter  string       `json:"branch_filter"`
+	Active        sql.NullBool `json:"active"`
+}
+
+func (q *Queries) GetGitWebhookForPush(ctx context.Context, id uuid.UUID) (GetGitWebhookForPushRow, error) {
+	row := q.db.QueryRowContext(ctx, getGitWebhookForPush, id)
+	var i GetGitWebhookForPushRow
+	err := row.Scan(
+		&i.WebhookSecret,
+		&i.RepoID,
+		&i.ProviderID,
+		&i.BranchFilter,
+		&i.Active,
+	)
+	return i, err
+}
+
+const insertGitProvider = `-- name: InsertGitProvider :exec
+INSERT INTO git_providers (id, name, display_name, api_url, webhook_url, access_token, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
+`
+
+type InsertGitProviderParams struct {
+	ID          uuid.UUID `json:"id"`
+	Name        string    `json:"name"`
+	DisplayName string    `json:"display_name"`
+	ApiUrl      string    `json:"api_url"`
+	WebhookUrl  string    `json:"webhook_url"`
+	AccessToken string    `json:"access_token"`
+	UserID      uuid.UUID `json:"user_id"`
+}
+
+func (q *Queries) InsertGitProvider(ctx context.Context, arg InsertGitProviderParams) error {
+	_, err := q.db.ExecContext(ctx, insertGitProvider,
+		arg.ID,
+		arg.Name,
+		arg.DisplayName,
+		arg.ApiUrl,
+		arg.WebhookUrl,
+		arg.AccessToken,
+		arg.UserID,
+	)
+	return err
+}
+
+const insertGitRepository = `-- name: InsertGitRepository :one
+INSERT INTO git_repositories (id, provider_id, name, full_name, description, clone_url, default_branch, is_private, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
+RETURNING created_at, updated_at
+`
+
+type InsertGitRepositoryParams struct {
+	ID            uuid.UUID      `json:"id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Name          string         `json:"name"`
+	FullName      string         `json:"full_name"`
+	Description   sql.NullString `json:"description"`
+	CloneUrl      string         `json:"clone_url"`
+	DefaultBranch sql.NullString `json:"default_branch"`
+	IsPrivate     sql.NullBool   `json:"is_private"`
+	UserID        uuid.UUID      `json:"user_id"`
+}
+
+type InsertGitRepositoryRow struct {
+	CreatedAt sql.NullTime `json:"created_at"`
+	UpdatedAt sql.NullTime `json:"updated_at"`
+}
+
+func (q *Queries) InsertGitRepository(ctx context.Context, arg InsertGitRepositoryParams) (InsertGitRepositoryRow, error) {
+	row := q.db.QueryRowContext(ctx, insertGitRepository,
+		arg.ID,
+		arg.ProviderID,
+		arg.Name,
+		arg.FullName,
+		arg.Description,
+		arg.CloneUrl,
+		arg.DefaultBranch,
+		arg.IsPrivate,
+		arg.UserID,
+	)
+	var i InsertGitRepositoryRow
+	err := row.Scan(&i.CreatedAt, &i.UpdatedAt)
+	return i, err
+}
+
+const listConnectedRepositories = `-- name: ListConnectedRepositories :many
+SELECT r.id, r.provider_id, r.name, r.full_name, COALESCE(r.description, '') AS description, r.clone_url,
+       r.default_branch, r.is_private, r.user_id, r.created_at, r.updated_at,
+       p.name AS provider_name, p.display_name
+FROM git_repositories r
+JOIN git_providers p ON r.provider_id = p.id
+WHERE r.user_id = $1
+ORDER BY r.updated_at DESC
+LIMIT $2 OFFSET $3
+`
+
+type ListConnectedRepositoriesParams struct {
+	UserID uuid.UUID `json:"user_id"`
+	Limit  int32     `json:"limit"`
+	Offset int32     `json:"offset"`
+}
+
+type ListConnectedRepositoriesRow struct {
+	ID            uuid.UUID      `json:"id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Name          string         `json:"name"`
+	FullName      string         `json:"full_name"`
+	Description   string         `json:"description"`
+	CloneUrl      string         `json:"clone_url"`
+	DefaultBranch sql.NullString `json:"default_branch"`
+	IsPrivate     sql.NullBool   `json:"is_private"`
+	UserID        uuid.UUID      `json:"user_id"`
+	CreatedAt     sql.NullTime   `json:"created_at"`
+	UpdatedAt     sql.NullTime   `json:"updated_at"`
+	ProviderName  string         `json:"provider_name"`
+	DisplayName   string         `json:"display_name"`
+}
+
+func (q *Queries) ListConnectedRepositories(ctx context.Context, arg ListConnectedRepositoriesParams) ([]ListConnectedRepositoriesRow, error) {
+	rows, err := q.db.QueryContext(ctx, listConnectedRepositories, arg.UserID, arg.Limit, arg.Offset)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListConnectedRepositoriesRow{}
+	for rows.Next() {
+		var i ListConnectedRepositoriesRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.ProviderID,
+			&i.Name,
+			&i.FullName,
+			&i.Description,
+			&i.CloneUrl,
+			&i.DefaultBranch,
+			&i.IsPrivate,
+			&i.UserID,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+			&i.ProviderName,
+			&i.DisplayName,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listGitProviders = `-- name: ListGitProviders :many
+SELECT id, name, display_name, api_url, webhook_url, user_id, created_at, updated_at
+FROM git_providers
+WHERE user_id = $1
+ORDER BY created_at DESC
+`
+
+type ListGitProvidersRow struct {
+	ID          uuid.UUID    `json:"id"`
+	Name        string       `json:"name"`
+	DisplayName string       `json:"display_name"`
+	ApiUrl      string       `json:"api_url"`
+	WebhookUrl  string       `json:"webhook_url"`
+	UserID      uuid.UUID    `json:"user_id"`
+	CreatedAt   sql.NullTime `json:"created_at"`
+	UpdatedAt   sql.NullTime `json:"updated_at"`
+}
+
+func (q *Queries) ListGitProviders(ctx context.Context, userID uuid.UUID) ([]ListGitProvidersRow, error) {
+	rows, err := q.db.QueryContext(ctx, listGitProviders, userID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListGitProvidersRow{}
+	for rows.Next() {
+		var i ListGitProvidersRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.Name,
+			&i.DisplayName,
+			&i.ApiUrl,
+			&i.WebhookUrl,
+			&i.UserID,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listGitRepositories = `-- name: ListGitRepositories :many
+SELECT id, provider_id, name, full_name, COALESCE(description, '') AS description,
+       clone_url, COALESCE(webhook_url, '') AS webhook_url,
+       default_branch, is_private, user_id, created_at, updated_at
+FROM git_repositories
+WHERE provider_id = $1 AND user_id = $2
+  AND ($3::text IS NULL OR full_name ILIKE ('%' || $3::text || '%') OR name ILIKE ('%' || $3::text || '%'))
+ORDER BY updated_at DESC
+LIMIT $4 OFFSET $5
+`
+
+type ListGitRepositoriesParams struct {
+	ProviderID uuid.UUID `json:"provider_id"`
+	UserID     uuid.UUID `json:"user_id"`
+	Column3    string    `json:"column_3"`
+	Limit      int32     `json:"limit"`
+	Offset     int32     `json:"offset"`
+}
+
+type ListGitRepositoriesRow struct {
+	ID            uuid.UUID      `json:"id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Name          string         `json:"name"`
+	FullName      string         `json:"full_name"`
+	Description   string         `json:"description"`
+	CloneUrl      string         `json:"clone_url"`
+	WebhookUrl    string         `json:"webhook_url"`
+	DefaultBranch sql.NullString `json:"default_branch"`
+	IsPrivate     sql.NullBool   `json:"is_private"`
+	UserID        uuid.UUID      `json:"user_id"`
+	CreatedAt     sql.NullTime   `json:"created_at"`
+	UpdatedAt     sql.NullTime   `json:"updated_at"`
+}
+
+func (q *Queries) ListGitRepositories(ctx context.Context, arg ListGitRepositoriesParams) ([]ListGitRepositoriesRow, error) {
+	rows, err := q.db.QueryContext(ctx, listGitRepositories,
+		arg.ProviderID,
+		arg.UserID,
+		arg.Column3,
+		arg.Limit,
+		arg.Offset,
+	)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListGitRepositoriesRow{}
+	for rows.Next() {
+		var i ListGitRepositoriesRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.ProviderID,
+			&i.Name,
+			&i.FullName,
+			&i.Description,
+			&i.CloneUrl,
+			&i.WebhookUrl,
+			&i.DefaultBranch,
+			&i.IsPrivate,
+			&i.UserID,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listServicesForPush = `-- name: ListServicesForPush :many
+SELECT s.id, s.project_id, s.name,
+       COALESCE(s.type, s.service_type, '') AS type, COALESCE(s.status, '') AS status,
+       COALESCE(s.image, s.image_name, '') AS image, COALESCE(s.command, s.start_command, '') AS command,
+       COALESCE(s.environment, '') AS environment, COALESCE(s.git_repo, s.source_url, '') AS git_repo,
+       COALESCE(s.git_branch, '') AS git_branch, COALESCE(s.build_path, '') AS build_path,
+       COALESCE(s.cpu, '') AS cpu, COALESCE(s.memory, '') AS memory,
+       s.created_at, s.updated_at
+FROM services s
+WHERE (s.git_branch = $1 OR $1 = '') AND (s.git_repo = $2 OR s.git_repo = $3)
+`
+
+type ListServicesForPushParams struct {
+	GitBranch sql.NullString `json:"git_branch"`
+	GitRepo   sql.NullString `json:"git_repo"`
+	GitRepo_2 sql.NullString `json:"git_repo_2"`
+}
+
+type ListServicesForPushRow struct {
+	ID          uuid.UUID    `json:"id"`
+	ProjectID   uuid.UUID    `json:"project_id"`
+	Name        string       `json:"name"`
+	Type        string       `json:"type"`
+	Status      string       `json:"status"`
+	Image       string       `json:"image"`
+	Command     string       `json:"command"`
+	Environment string       `json:"environment"`
+	GitRepo     string       `json:"git_repo"`
+	GitBranch   string       `json:"git_branch"`
+	BuildPath   string       `json:"build_path"`
+	Cpu         string       `json:"cpu"`
+	Memory      string       `json:"memory"`
+	CreatedAt   sql.NullTime `json:"created_at"`
+	UpdatedAt   sql.NullTime `json:"updated_at"`
+}
+
+func (q *Queries) ListServicesForPush(ctx context.Context, arg ListServicesForPushParams) ([]ListServicesForPushRow, error) {
+	rows, err := q.db.QueryContext(ctx, listServicesForPush, arg.GitBranch, arg.GitRepo, arg.GitRepo_2)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListServicesForPushRow{}
+	for rows.Next() {
+		var i ListServicesForPushRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.ProjectID,
+			&i.Name,
+			&i.Type,
+			&i.Status,
+			&i.Image,
+			&i.Command,
+			&i.Environment,
+			&i.GitRepo,
+			&i.GitBranch,
+			&i.BuildPath,
+			&i.Cpu,
+			&i.Memory,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const upsertGitHubAppProvider = `-- name: UpsertGitHubAppProvider :one
+INSERT INTO git_providers (id, name, display_name, api_url, webhook_url, access_token, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
+ON CONFLICT (name, user_id)
+DO UPDATE SET
+    display_name = EXCLUDED.display_name,
+    api_url = EXCLUDED.api_url,
+    webhook_url = EXCLUDED.webhook_url,
+    access_token = EXCLUDED.access_token,
+    updated_at = NOW()
+RETURNING id, created_at, updated_at
+`
+
+type UpsertGitHubAppProviderParams struct {
+	ID          uuid.UUID `json:"id"`
+	Name        string    `json:"name"`
+	DisplayName string    `json:"display_name"`
+	ApiUrl      string    `json:"api_url"`
+	WebhookUrl  string    `json:"webhook_url"`
+	AccessToken string    `json:"access_token"`
+	UserID      uuid.UUID `json:"user_id"`
+}
+
+type UpsertGitHubAppProviderRow struct {
+	ID        uuid.UUID    `json:"id"`
+	CreatedAt sql.NullTime `json:"created_at"`
+	UpdatedAt sql.NullTime `json:"updated_at"`
+}
+
+func (q *Queries) UpsertGitHubAppProvider(ctx context.Context, arg UpsertGitHubAppProviderParams) (UpsertGitHubAppProviderRow, error) {
+	row := q.db.QueryRowContext(ctx, upsertGitHubAppProvider,
+		arg.ID,
+		arg.Name,
+		arg.DisplayName,
+		arg.ApiUrl,
+		arg.WebhookUrl,
+		arg.AccessToken,
+		arg.UserID,
+	)
+	var i UpsertGitHubAppProviderRow
+	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
+	return i, err
+}
+
+const upsertGitRepository = `-- name: UpsertGitRepository :exec
+INSERT INTO git_repositories (id, provider_id, name, full_name, description, clone_url, default_branch, is_private, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
+ON CONFLICT (provider_id, full_name)
+DO UPDATE SET
+    name = EXCLUDED.name,
+    description = EXCLUDED.description,
+    clone_url = EXCLUDED.clone_url,
+    default_branch = EXCLUDED.default_branch,
+    is_private = EXCLUDED.is_private,
+    updated_at = NOW()
+`
+
+type UpsertGitRepositoryParams struct {
+	ID            uuid.UUID      `json:"id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Name          string         `json:"name"`
+	FullName      string         `json:"full_name"`
+	Description   sql.NullString `json:"description"`
+	CloneUrl      string         `json:"clone_url"`
+	DefaultBranch sql.NullString `json:"default_branch"`
+	IsPrivate     sql.NullBool   `json:"is_private"`
+	UserID        uuid.UUID      `json:"user_id"`
+}
+
+func (q *Queries) UpsertGitRepository(ctx context.Context, arg UpsertGitRepositoryParams) error {
+	_, err := q.db.ExecContext(ctx, upsertGitRepository,
+		arg.ID,
+		arg.ProviderID,
+		arg.Name,
+		arg.FullName,
+		arg.Description,
+		arg.CloneUrl,
+		arg.DefaultBranch,
+		arg.IsPrivate,
+		arg.UserID,
+	)
+	return err
+}
+
+const upsertGitWebhook = `-- name: UpsertGitWebhook :one
+INSERT INTO git_webhooks (id, repo_id, provider_id, events, webhook_secret, active, branch_filter, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, TRUE, $6, NOW(), NOW())
+ON CONFLICT (repo_id, provider_id)
+DO UPDATE SET
+    events = EXCLUDED.events,
+    webhook_secret = EXCLUDED.webhook_secret,
+    active = TRUE,
+    branch_filter = EXCLUDED.branch_filter,
+    updated_at = NOW()
+RETURNING id, active, created_at, updated_at
+`
+
+type UpsertGitWebhookParams struct {
+	ID            uuid.UUID      `json:"id"`
+	RepoID        uuid.UUID      `json:"repo_id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Events        string         `json:"events"`
+	WebhookSecret string         `json:"webhook_secret"`
+	BranchFilter  sql.NullString `json:"branch_filter"`
+}
+
+type UpsertGitWebhookRow struct {
+	ID        uuid.UUID    `json:"id"`
+	Active    sql.NullBool `json:"active"`
+	CreatedAt sql.NullTime `json:"created_at"`
+	UpdatedAt sql.NullTime `json:"updated_at"`
+}
+
+func (q *Queries) UpsertGitWebhook(ctx context.Context, arg UpsertGitWebhookParams) (UpsertGitWebhookRow, error) {
+	row := q.db.QueryRowContext(ctx, upsertGitWebhook,
+		arg.ID,
+		arg.RepoID,
+		arg.ProviderID,
+		arg.Events,
+		arg.WebhookSecret,
+		arg.BranchFilter,
+	)
+	var i UpsertGitWebhookRow
+	err := row.Scan(
+		&i.ID,
+		&i.Active,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}

app/backend/internal/database/sqlcdb/models.go

@@ -78,6 +78,28 @@ type Banner struct {
 	UpdatedAt   sql.NullTime  `json:"updated_at"`
 }
 
+type ComplianceFramework struct {
+	ID          uuid.UUID      `json:"id"`
+	Name        string         `json:"name"`
+	Description sql.NullString `json:"description"`
+	Version     string         `json:"version"`
+	Enabled     bool           `json:"enabled"`
+	CreatedAt   time.Time      `json:"created_at"`
+	UpdatedAt   time.Time      `json:"updated_at"`
+}
+
+type ComplianceReport struct {
+	ID             uuid.UUID      `json:"id"`
+	ProjectID      uuid.UUID      `json:"project_id"`
+	FrameworkID    uuid.UUID      `json:"framework_id"`
+	AssessmentDate time.Time      `json:"assessment_date"`
+	Assessor       sql.NullString `json:"assessor"`
+	OverallStatus  string         `json:"overall_status"`
+	Score          int32          `json:"score"`
+	CreatedAt      time.Time      `json:"created_at"`
+	UpdatedAt      time.Time      `json:"updated_at"`
+}
+
 type ContainerInstance struct {
 	ID            string                `json:"id"`
 	Name          string                `json:"name"`
@@ -200,6 +222,46 @@ type EnvironmentVariable struct {
 	UpdatedAt sql.NullTime `json:"updated_at"`
 }
 
+type GitProvider struct {
+	ID          uuid.UUID    `json:"id"`
+	Name        string       `json:"name"`
+	DisplayName string       `json:"display_name"`
+	ApiUrl      string       `json:"api_url"`
+	WebhookUrl  string       `json:"webhook_url"`
+	AccessToken string       `json:"access_token"`
+	UserID      uuid.UUID    `json:"user_id"`
+	CreatedAt   sql.NullTime `json:"created_at"`
+	UpdatedAt   sql.NullTime `json:"updated_at"`
+}
+
+type GitRepository struct {
+	ID            uuid.UUID      `json:"id"`
+	ProviderID    uuid.UUID      `json:"provider_id"`
+	Name          string         `json:"name"`
+	FullName      string         `json:"full_name"`
+	Description   sql.NullString `json:"description"`
+	CloneUrl      string         `json:"clone_url"`
+	WebhookUrl    sql.NullString `json:"webhook_url"`
+	DefaultBranch sql.NullString `json:"default_branch"`
+	IsPrivate     sql.NullBool   `json:"is_private"`
+	UserID        uuid.UUID      `json:"user_id"`
+	CreatedAt     sql.NullTime   `json:"created_at"`
+	UpdatedAt     sql.NullTime   `json:"updated_at"`
+}
+
+type GitWebhook struct {
+	ID              uuid.UUID      `json:"id"`
+	RepoID          uuid.UUID      `json:"repo_id"`
+	ProviderID      uuid.UUID      `json:"provider_id"`
+	Events          string         `json:"events"`
+	WebhookSecret   string         `json:"webhook_secret"`
+	RemoteWebhookID sql.NullString `json:"remote_webhook_id"`
+	Active          sql.NullBool   `json:"active"`
+	BranchFilter    sql.NullString `json:"branch_filter"`
+	CreatedAt       sql.NullTime   `json:"created_at"`
+	UpdatedAt       sql.NullTime   `json:"updated_at"`
+}
+
 type InstanceMetric struct {
 	ServiceID             string         `json:"service_id"`
 	Timestamp             time.Time      `json:"timestamp"`
@@ -357,6 +419,19 @@ type Registry struct {
 	UpdatedAt time.Time `json:"updated_at"`
 }
 
+type SecurityScan struct {
+	ID          uuid.UUID             `json:"id"`
+	ProjectID   uuid.UUID             `json:"project_id"`
+	ServiceID   uuid.NullUUID         `json:"service_id"`
+	ScanType    string                `json:"scan_type"`
+	Status      string                `json:"status"`
+	StartedAt   time.Time             `json:"started_at"`
+	CompletedAt sql.NullTime          `json:"completed_at"`
+	Summary     pqtype.NullRawMessage `json:"summary"`
+	CreatedAt   time.Time             `json:"created_at"`
+	UpdatedAt   time.Time             `json:"updated_at"`
+}
+
 type Service struct {
 	ID               uuid.UUID       `json:"id"`
 	Name             string          `json:"name"`
@@ -460,6 +535,17 @@ type ServiceTemplate struct {
 	UpdatedAt   sql.NullTime          `json:"updated_at"`
 }
 
+type User struct {
+	ID           uuid.UUID      `json:"id"`
+	Email        string         `json:"email"`
+	PasswordHash string         `json:"password_hash"`
+	Name         string         `json:"name"`
+	AvatarUrl    sql.NullString `json:"avatar_url"`
+	CreatedAt    sql.NullTime   `json:"created_at"`
+	UpdatedAt    sql.NullTime   `json:"updated_at"`
+	IsAdmin      bool           `json:"is_admin"`
+}
+
 type UserInvite struct {
 	ID        uuid.UUID      `json:"id"`
 	TokenHash string         `json:"token_hash"`
@@ -484,6 +570,21 @@ type UserToken struct {
 	CreatedAt  sql.NullTime `json:"created_at"`
 }
 
+type Vulnerability struct {
+	ID          uuid.UUID      `json:"id"`
+	Type        string         `json:"type"`
+	Severity    string         `json:"severity"`
+	Title       string         `json:"title"`
+	Description sql.NullString `json:"description"`
+	ServiceID   uuid.NullUUID  `json:"service_id"`
+	ProjectID   uuid.UUID      `json:"project_id"`
+	Status      string         `json:"status"`
+	FoundAt     time.Time      `json:"found_at"`
+	ResolvedAt  sql.NullTime   `json:"resolved_at"`
+	CreatedAt   time.Time      `json:"created_at"`
+	UpdatedAt   time.Time      `json:"updated_at"`
+}
+
 type WebhookDelivery struct {
 	ID             uuid.UUID             `json:"id"`
 	WebhookID      uuid.UUID             `json:"webhook_id"`

app/backend/internal/database/sqlcdb/querier.go

@@ -16,6 +16,7 @@ type Querier interface {
 	// Portable bucket bucketing (no TimescaleDB): floor epoch seconds into
 	// bucket-sized buckets, then back to timestamptz.
 	AggregateNodeMetrics(ctx context.Context, arg AggregateNodeMetricsParams) ([]AggregateNodeMetricsRow, error)
+	CheckSecurityProjectAccess(ctx context.Context, arg CheckSecurityProjectAccessParams) (bool, error)
 	ClearServiceDomainDefault(ctx context.Context, serviceID uuid.UUID) error
 	ClearServiceNodePins(ctx context.Context, nodeID sql.NullString) (int64, error)
 	CloneServiceDomains(ctx context.Context, arg CloneServiceDomainsParams) error
@@ -23,17 +24,22 @@ type Querier interface {
 	CloneServiceVariables(ctx context.Context, arg CloneServiceVariablesParams) error
 	CompleteCommand(ctx context.Context, arg CompleteCommandParams) (AgentCommand, error)
 	CompleteDeployment(ctx context.Context, arg CompleteDeploymentParams) error
+	ComplianceFrameworkExists(ctx context.Context, id uuid.UUID) (bool, error)
 	CountAccessibleDeployments(ctx context.Context, arg CountAccessibleDeploymentsParams) (int64, error)
 	CountActivePreviewsForBranch(ctx context.Context, arg CountActivePreviewsForBranchParams) (int64, error)
 	CountDatabaseServicesByUserAndName(ctx context.Context, arg CountDatabaseServicesByUserAndNameParams) (int64, error)
 	CountDatabasesUsingBackupTarget(ctx context.Context, targetID sql.NullString) (int64, error)
 	CountDeploymentsForService(ctx context.Context, serviceID uuid.UUID) (int64, error)
 	CountEnvironmentServices(ctx context.Context, environmentID uuid.UUID) (int64, error)
+	CountGitRepositories(ctx context.Context, arg CountGitRepositoriesParams) (int64, error)
 	CountProjectsByUser(ctx context.Context, arg CountProjectsByUserParams) (int64, error)
 	CountServiceDomains(ctx context.Context, serviceID uuid.UUID) (int64, error)
 	CountServicesByName(ctx context.Context, arg CountServicesByNameParams) (int64, error)
 	CountServicesByProjectAndName(ctx context.Context, arg CountServicesByProjectAndNameParams) (int64, error)
 	CountUnreadNotificationsByUser(ctx context.Context, userID uuid.UUID) (int64, error)
+	CountUserRepositories(ctx context.Context, userID uuid.UUID) (int64, error)
+	CountUsers(ctx context.Context) (int64, error)
+	CountUsersByEmail(ctx context.Context, email string) (int64, error)
 	CreateAgent(ctx context.Context, arg CreateAgentParams) (NodeAgent, error)
 	CreateAgentAuthToken(ctx context.Context, arg CreateAgentAuthTokenParams) (AgentAuthToken, error)
 	CreateBackupTarget(ctx context.Context, arg CreateBackupTargetParams) error
@@ -62,6 +68,7 @@ type Querier interface {
 	DeleteCronJob(ctx context.Context, id uuid.UUID) error
 	DeleteDatabaseServiceByID(ctx context.Context, id string) error
 	DeleteDatabaseServiceByIDAndUser(ctx context.Context, arg DeleteDatabaseServiceByIDAndUserParams) error
+	DeleteGitProvider(ctx context.Context, arg DeleteGitProviderParams) (int64, error)
 	DeleteNotificationChannel(ctx context.Context, arg DeleteNotificationChannelParams) (int64, error)
 	DeleteOutboundWebhookByIDAndUser(ctx context.Context, arg DeleteOutboundWebhookByIDAndUserParams) error
 	DeletePreviewEnvironment(ctx context.Context, id uuid.UUID) error
@@ -81,6 +88,7 @@ type Querier interface {
 	GetBackupTargetByIDAndUser(ctx context.Context, arg GetBackupTargetByIDAndUserParams) (BackupTarget, error)
 	GetBannerByID(ctx context.Context, id uuid.UUID) (Banner, error)
 	GetCommandForAgent(ctx context.Context, arg GetCommandForAgentParams) (AgentCommand, error)
+	GetComplianceReportProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
 	GetContainer(ctx context.Context, id string) (ContainerInstance, error)
 	GetContainerForAgent(ctx context.Context, arg GetContainerForAgentParams) (ContainerInstance, error)
 	GetCronJob(ctx context.Context, id uuid.UUID) (CronJob, error)
@@ -95,8 +103,19 @@ type Querier interface {
 	GetDeploymentAccess(ctx context.Context, id uuid.UUID) (GetDeploymentAccessRow, error)
 	GetDeploymentForRollback(ctx context.Context, id uuid.UUID) (GetDeploymentForRollbackRow, error)
 	GetDeploymentWithProject(ctx context.Context, id uuid.UUID) (GetDeploymentWithProjectRow, error)
+	GetGitProviderForFetch(ctx context.Context, arg GetGitProviderForFetchParams) (GetGitProviderForFetchRow, error)
+	GetGitProviderForRepos(ctx context.Context, arg GetGitProviderForReposParams) (GetGitProviderForReposRow, error)
+	GetGitProviderName(ctx context.Context, arg GetGitProviderNameParams) (string, error)
+	GetGitProviderNameByID(ctx context.Context, id uuid.UUID) (string, error)
+	GetGitProviderTimestamps(ctx context.Context, id uuid.UUID) (GetGitProviderTimestampsRow, error)
+	GetGitRepoForPush(ctx context.Context, id uuid.UUID) (GetGitRepoForPushRow, error)
+	GetGitRepositoryByFullName(ctx context.Context, arg GetGitRepositoryByFullNameParams) (GetGitRepositoryByFullNameRow, error)
+	GetGitRepositoryProviderID(ctx context.Context, arg GetGitRepositoryProviderIDParams) (uuid.UUID, error)
+	GetGitWebhookForPush(ctx context.Context, id uuid.UUID) (GetGitWebhookForPushRow, error)
 	GetLastAgentCommandByType(ctx context.Context, arg GetLastAgentCommandByTypeParams) (AgentCommand, error)
 	GetLastDeployedImage(ctx context.Context, serviceID uuid.UUID) (string, error)
+	GetLatestComplianceReport(ctx context.Context, projectID uuid.UUID) (GetLatestComplianceReportRow, error)
+	GetLatestSecurityScan(ctx context.Context, projectID uuid.UUID) (GetLatestSecurityScanRow, error)
 	GetOutboundWebhookByID(ctx context.Context, id uuid.UUID) (OutboundWebhook, error)
 	GetOutboundWebhookByIDAndUser(ctx context.Context, arg GetOutboundWebhookByIDAndUserParams) (OutboundWebhook, error)
 	GetPreviewEnvironment(ctx context.Context, arg GetPreviewEnvironmentParams) (GetPreviewEnvironmentRow, error)
@@ -110,7 +129,9 @@ type Querier interface {
 	GetProjectOwner(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
 	GetProjectOwnerByID(ctx context.Context, projectID uuid.UUID) (uuid.UUID, error)
 	GetProjectOwnerID(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
+	GetProjectOwnerText(ctx context.Context, id uuid.UUID) (string, error)
 	GetProjectRoleForUser(ctx context.Context, arg GetProjectRoleForUserParams) (string, error)
+	GetSecurityScanProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
 	GetServiceAccess(ctx context.Context, id uuid.UUID) (GetServiceAccessRow, error)
 	GetServiceDomainDefault(ctx context.Context, serviceID uuid.UUID) (string, error)
 	GetServiceDomainFirst(ctx context.Context, serviceID uuid.UUID) (string, error)
@@ -126,15 +147,23 @@ type Querier interface {
 	GetServiceTemplateByID(ctx context.Context, id string) (ServiceTemplate, error)
 	GetServiceTypeBrief(ctx context.Context, arg GetServiceTypeBriefParams) (GetServiceTypeBriefRow, error)
 	GetServiceVariableValue(ctx context.Context, arg GetServiceVariableValueParams) (string, error)
+	GetUserByEmailForAuth(ctx context.Context, email string) (GetUserByEmailForAuthRow, error)
+	GetUserByID(ctx context.Context, id uuid.UUID) (GetUserByIDRow, error)
 	GetUserInviteByTokenHash(ctx context.Context, tokenHash string) (UserInvite, error)
 	GetUserTokenByHash(ctx context.Context, tokenHash string) (UserToken, error)
+	GetVulnerabilityMetrics(ctx context.Context, projectID uuid.UUID) (GetVulnerabilityMetricsRow, error)
+	GetVulnerabilityProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
 	InsertAgentHeartbeat(ctx context.Context, arg InsertAgentHeartbeatParams) error
 	InsertCronExecution(ctx context.Context, arg InsertCronExecutionParams) error
 	InsertCronJob(ctx context.Context, arg InsertCronJobParams) error
 	InsertDeployment(ctx context.Context, arg InsertDeploymentParams) error
+	InsertGitProvider(ctx context.Context, arg InsertGitProviderParams) error
+	InsertGitRepository(ctx context.Context, arg InsertGitRepositoryParams) (InsertGitRepositoryRow, error)
 	InsertNotificationChannel(ctx context.Context, arg InsertNotificationChannelParams) (NotificationChannel, error)
 	InsertPendingDeployment(ctx context.Context, arg InsertPendingDeploymentParams) error
 	InsertProjectEnvironment(ctx context.Context, arg InsertProjectEnvironmentParams) error
+	InsertUser(ctx context.Context, arg InsertUserParams) (InsertUserRow, error)
+	InsertUserAdmin(ctx context.Context, arg InsertUserAdminParams) (InsertUserAdminRow, error)
 	ListActiveBanners(ctx context.Context) ([]Banner, error)
 	ListAgentAuthTokens(ctx context.Context) ([]AgentAuthToken, error)
 	ListAgentHeartbeatsSince(ctx context.Context, arg ListAgentHeartbeatsSinceParams) ([]AgentHeartbeat, error)
@@ -144,6 +173,8 @@ type Querier interface {
 	ListBackupTargetsByUser(ctx context.Context, userID string) ([]BackupTarget, error)
 	ListBanners(ctx context.Context) ([]Banner, error)
 	ListCommandsForAgent(ctx context.Context, nodeAgentID string) ([]AgentCommand, error)
+	ListComplianceFrameworks(ctx context.Context) ([]ListComplianceFrameworksRow, error)
+	ListConnectedRepositories(ctx context.Context, arg ListConnectedRepositoriesParams) ([]ListConnectedRepositoriesRow, error)
 	ListContainersForAgent(ctx context.Context, nodeAgentID string) ([]ContainerInstance, error)
 	ListCronExecutions(ctx context.Context, cronJobID uuid.UUID) ([]CronExecution, error)
 	ListDatabaseBackupsByDatabaseAndUser(ctx context.Context, arg ListDatabaseBackupsByDatabaseAndUserParams) ([]DatabaseBackup, error)
@@ -154,6 +185,8 @@ type Querier interface {
 	ListDueDatabaseBackups(ctx context.Context) ([]ListDueDatabaseBackupsRow, error)
 	ListEnabledOutboundWebhooks(ctx context.Context) ([]OutboundWebhook, error)
 	ListExpiredPreviewsForUser(ctx context.Context, arg ListExpiredPreviewsForUserParams) ([]ListExpiredPreviewsForUserRow, error)
+	ListGitProviders(ctx context.Context, userID uuid.UUID) ([]ListGitProvidersRow, error)
+	ListGitRepositories(ctx context.Context, arg ListGitRepositoriesParams) ([]ListGitRepositoriesRow, error)
 	ListInstanceMetrics(ctx context.Context, arg ListInstanceMetricsParams) ([]ListInstanceMetricsRow, error)
 	ListNodeAgentsForService(ctx context.Context, serviceID string) ([]string, error)
 	ListNodeMetrics(ctx context.Context, arg ListNodeMetricsParams) ([]ListNodeMetricsRow, error)
@@ -185,10 +218,12 @@ type Querier interface {
 	ListServiceTemplatesForUser(ctx context.Context, ownerID uuid.NullUUID) ([]ServiceTemplate, error)
 	ListServiceVariableValues(ctx context.Context, serviceID uuid.UUID) ([]ListServiceVariableValuesRow, error)
 	ListServiceVariablesWithSecret(ctx context.Context, serviceID uuid.UUID) ([]ListServiceVariablesWithSecretRow, error)
+	ListServicesForPush(ctx context.Context, arg ListServicesForPushParams) ([]ListServicesForPushRow, error)
 	ListSleepCandidates(ctx context.Context) ([]ListSleepCandidatesRow, error)
 	ListSweepablePreviews(ctx context.Context) ([]ListSweepablePreviewsRow, error)
 	ListUserInvites(ctx context.Context) ([]UserInvite, error)
 	ListUserTokens(ctx context.Context, userID uuid.UUID) ([]UserToken, error)
+	ListVulnerabilities(ctx context.Context, projectID uuid.UUID) ([]ListVulnerabilitiesRow, error)
 	ListWebhookDeliveriesByWebhook(ctx context.Context, webhookID uuid.UUID) ([]WebhookDelivery, error)
 	MarkAllNotificationsReadByUser(ctx context.Context, arg MarkAllNotificationsReadByUserParams) error
 	MarkNotificationReadByIDAndUser(ctx context.Context, arg MarkNotificationReadByIDAndUserParams) error
@@ -204,6 +239,7 @@ type Querier interface {
 	RevokeAgentAuthToken(ctx context.Context, id uuid.UUID) (AgentAuthToken, error)
 	RevokeUserToken(ctx context.Context, arg RevokeUserTokenParams) (int64, error)
 	ScrubCommandPayload(ctx context.Context, arg ScrubCommandPayloadParams) error
+	ServiceExistsInProject(ctx context.Context, arg ServiceExistsInProjectParams) (bool, error)
 	ServiceWriteAccess(ctx context.Context, arg ServiceWriteAccessParams) (bool, error)
 	SetAgentDefaultDomain(ctx context.Context, arg SetAgentDefaultDomainParams) error
 	SetAgentSchedulable(ctx context.Context, arg SetAgentSchedulableParams) error
@@ -250,10 +286,16 @@ type Querier interface {
 	UpdatePreviewEnvironment(ctx context.Context, arg UpdatePreviewEnvironmentParams) error
 	UpdateProjectByID(ctx context.Context, arg UpdateProjectByIDParams) (int64, error)
 	UpdateServicePreviewBranch(ctx context.Context, arg UpdateServicePreviewBranchParams) error
+	UpdateUserProfile(ctx context.Context, arg UpdateUserProfileParams) error
 	UpdateUserTemplate(ctx context.Context, arg UpdateUserTemplateParams) (int64, error)
+	UpdateVulnerabilityStatus(ctx context.Context, arg UpdateVulnerabilityStatusParams) error
 	UpdateWebhookDeliveryResult(ctx context.Context, arg UpdateWebhookDeliveryResultParams) error
 	UpsertEnvironmentVariable(ctx context.Context, arg UpsertEnvironmentVariableParams) error
+	UpsertGitHubAppProvider(ctx context.Context, arg UpsertGitHubAppProviderParams) (UpsertGitHubAppProviderRow, error)
+	UpsertGitRepository(ctx context.Context, arg UpsertGitRepositoryParams) error
+	UpsertGitWebhook(ctx context.Context, arg UpsertGitWebhookParams) (UpsertGitWebhookRow, error)
 	UpsertInstanceMetrics(ctx context.Context, arg UpsertInstanceMetricsParams) error
+	UpsertLocalUser(ctx context.Context, arg UpsertLocalUserParams) (UpsertLocalUserRow, error)
 	UpsertNodeMetrics(ctx context.Context, arg UpsertNodeMetricsParams) error
 	UpsertServiceContainer(ctx context.Context, arg UpsertServiceContainerParams) error
 	UpsertServiceMetrics(ctx context.Context, arg UpsertServiceMetricsParams) error

app/backend/internal/database/sqlcdb/security.sql.go

@@ -0,0 +1,318 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: security.sql
+
+package sqlcdb
+
+import (
+	"context"
+	"database/sql"
+	"time"
+
+	"github.com/google/uuid"
+)
+
+const checkSecurityProjectAccess = `-- name: CheckSecurityProjectAccess :one
+SELECT EXISTS (
+    SELECT 1
+    FROM projects p
+    WHERE p.id = $1
+      AND (p.is_approved
+           OR $3::bool
+           OR p.owner_id = $2 OR EXISTS (
+            SELECT 1 FROM project_members pm
+            WHERE pm.project_id = p.id AND pm.user_id = $2
+      ))
+)
+`
+
+type CheckSecurityProjectAccessParams struct {
+	ID      uuid.UUID `json:"id"`
+	OwnerID uuid.UUID `json:"owner_id"`
+	Column3 bool      `json:"column_3"`
+}
+
+func (q *Queries) CheckSecurityProjectAccess(ctx context.Context, arg CheckSecurityProjectAccessParams) (bool, error) {
+	row := q.db.QueryRowContext(ctx, checkSecurityProjectAccess, arg.ID, arg.OwnerID, arg.Column3)
+	var exists bool
+	err := row.Scan(&exists)
+	return exists, err
+}
+
+const complianceFrameworkExists = `-- name: ComplianceFrameworkExists :one
+SELECT EXISTS(SELECT 1 FROM compliance_frameworks WHERE id = $1)
+`
+
+func (q *Queries) ComplianceFrameworkExists(ctx context.Context, id uuid.UUID) (bool, error) {
+	row := q.db.QueryRowContext(ctx, complianceFrameworkExists, id)
+	var exists bool
+	err := row.Scan(&exists)
+	return exists, err
+}
+
+const getComplianceReportProjectID = `-- name: GetComplianceReportProjectID :one
+SELECT project_id FROM compliance_reports WHERE id = $1
+`
+
+func (q *Queries) GetComplianceReportProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getComplianceReportProjectID, id)
+	var project_id uuid.UUID
+	err := row.Scan(&project_id)
+	return project_id, err
+}
+
+const getLatestComplianceReport = `-- name: GetLatestComplianceReport :one
+SELECT overall_status, score, assessment_date
+FROM compliance_reports
+WHERE project_id = $1
+ORDER BY assessment_date DESC
+LIMIT 1
+`
+
+type GetLatestComplianceReportRow struct {
+	OverallStatus  string    `json:"overall_status"`
+	Score          int32     `json:"score"`
+	AssessmentDate time.Time `json:"assessment_date"`
+}
+
+func (q *Queries) GetLatestComplianceReport(ctx context.Context, projectID uuid.UUID) (GetLatestComplianceReportRow, error) {
+	row := q.db.QueryRowContext(ctx, getLatestComplianceReport, projectID)
+	var i GetLatestComplianceReportRow
+	err := row.Scan(&i.OverallStatus, &i.Score, &i.AssessmentDate)
+	return i, err
+}
+
+const getLatestSecurityScan = `-- name: GetLatestSecurityScan :one
+SELECT id, COALESCE((summary->>'score')::int, 0)::int AS score, started_at AS scanned_at, status
+FROM security_scans
+WHERE project_id = $1
+ORDER BY started_at DESC
+LIMIT 1
+`
+
+type GetLatestSecurityScanRow struct {
+	ID        uuid.UUID `json:"id"`
+	Score     int32     `json:"score"`
+	ScannedAt time.Time `json:"scanned_at"`
+	Status    string    `json:"status"`
+}
+
+func (q *Queries) GetLatestSecurityScan(ctx context.Context, projectID uuid.UUID) (GetLatestSecurityScanRow, error) {
+	row := q.db.QueryRowContext(ctx, getLatestSecurityScan, projectID)
+	var i GetLatestSecurityScanRow
+	err := row.Scan(
+		&i.ID,
+		&i.Score,
+		&i.ScannedAt,
+		&i.Status,
+	)
+	return i, err
+}
+
+const getProjectOwnerText = `-- name: GetProjectOwnerText :one
+SELECT owner_id::text FROM projects WHERE id = $1
+`
+
+func (q *Queries) GetProjectOwnerText(ctx context.Context, id uuid.UUID) (string, error) {
+	row := q.db.QueryRowContext(ctx, getProjectOwnerText, id)
+	var owner_id string
+	err := row.Scan(&owner_id)
+	return owner_id, err
+}
+
+const getSecurityScanProjectID = `-- name: GetSecurityScanProjectID :one
+SELECT project_id FROM security_scans WHERE id = $1
+`
+
+func (q *Queries) GetSecurityScanProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getSecurityScanProjectID, id)
+	var project_id uuid.UUID
+	err := row.Scan(&project_id)
+	return project_id, err
+}
+
+const getVulnerabilityMetrics = `-- name: GetVulnerabilityMetrics :one
+SELECT
+    COUNT(*) AS total,
+    COUNT(*) FILTER (WHERE severity = 'critical') AS critical,
+    COUNT(*) FILTER (WHERE severity = 'high') AS high,
+    COUNT(*) FILTER (WHERE severity = 'medium') AS medium,
+    COUNT(*) FILTER (WHERE severity = 'low') AS low,
+    COUNT(*) FILTER (WHERE status = 'open') AS open,
+    COUNT(*) FILTER (WHERE status = 'resolved') AS resolved
+FROM vulnerabilities
+WHERE project_id = $1
+`
+
+type GetVulnerabilityMetricsRow struct {
+	Total    int64 `json:"total"`
+	Critical int64 `json:"critical"`
+	High     int64 `json:"high"`
+	Medium   int64 `json:"medium"`
+	Low      int64 `json:"low"`
+	Open     int64 `json:"open"`
+	Resolved int64 `json:"resolved"`
+}
+
+func (q *Queries) GetVulnerabilityMetrics(ctx context.Context, projectID uuid.UUID) (GetVulnerabilityMetricsRow, error) {
+	row := q.db.QueryRowContext(ctx, getVulnerabilityMetrics, projectID)
+	var i GetVulnerabilityMetricsRow
+	err := row.Scan(
+		&i.Total,
+		&i.Critical,
+		&i.High,
+		&i.Medium,
+		&i.Low,
+		&i.Open,
+		&i.Resolved,
+	)
+	return i, err
+}
+
+const getVulnerabilityProjectID = `-- name: GetVulnerabilityProjectID :one
+SELECT project_id FROM vulnerabilities WHERE id = $1
+`
+
+func (q *Queries) GetVulnerabilityProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getVulnerabilityProjectID, id)
+	var project_id uuid.UUID
+	err := row.Scan(&project_id)
+	return project_id, err
+}
+
+const listComplianceFrameworks = `-- name: ListComplianceFrameworks :many
+SELECT id, name, description, version, enabled, created_at
+FROM compliance_frameworks
+WHERE enabled = true
+ORDER BY name
+`
+
+type ListComplianceFrameworksRow struct {
+	ID          uuid.UUID      `json:"id"`
+	Name        string         `json:"name"`
+	Description sql.NullString `json:"description"`
+	Version     string         `json:"version"`
+	Enabled     bool           `json:"enabled"`
+	CreatedAt   time.Time      `json:"created_at"`
+}
+
+func (q *Queries) ListComplianceFrameworks(ctx context.Context) ([]ListComplianceFrameworksRow, error) {
+	rows, err := q.db.QueryContext(ctx, listComplianceFrameworks)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListComplianceFrameworksRow{}
+	for rows.Next() {
+		var i ListComplianceFrameworksRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.Name,
+			&i.Description,
+			&i.Version,
+			&i.Enabled,
+			&i.CreatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listVulnerabilities = `-- name: ListVulnerabilities :many
+SELECT id, type, severity, title, description, service_id, status, found_at, resolved_at
+FROM vulnerabilities
+WHERE project_id = $1
+ORDER BY
+    CASE severity
+        WHEN 'critical' THEN 1
+        WHEN 'high' THEN 2
+        WHEN 'medium' THEN 3
+        WHEN 'low' THEN 4
+    END,
+    found_at DESC
+`
+
+type ListVulnerabilitiesRow struct {
+	ID          uuid.UUID      `json:"id"`
+	Type        string         `json:"type"`
+	Severity    string         `json:"severity"`
+	Title       string         `json:"title"`
+	Description sql.NullString `json:"description"`
+	ServiceID   uuid.NullUUID  `json:"service_id"`
+	Status      string         `json:"status"`
+	FoundAt     time.Time      `json:"found_at"`
+	ResolvedAt  sql.NullTime   `json:"resolved_at"`
+}
+
+func (q *Queries) ListVulnerabilities(ctx context.Context, projectID uuid.UUID) ([]ListVulnerabilitiesRow, error) {
+	rows, err := q.db.QueryContext(ctx, listVulnerabilities, projectID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListVulnerabilitiesRow{}
+	for rows.Next() {
+		var i ListVulnerabilitiesRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.Type,
+			&i.Severity,
+			&i.Title,
+			&i.Description,
+			&i.ServiceID,
+			&i.Status,
+			&i.FoundAt,
+			&i.ResolvedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const serviceExistsInProject = `-- name: ServiceExistsInProject :one
+SELECT EXISTS(SELECT 1 FROM services WHERE id = $1 AND project_id = $2)
+`
+
+type ServiceExistsInProjectParams struct {
+	ID        uuid.UUID `json:"id"`
+	ProjectID uuid.UUID `json:"project_id"`
+}
+
+func (q *Queries) ServiceExistsInProject(ctx context.Context, arg ServiceExistsInProjectParams) (bool, error) {
+	row := q.db.QueryRowContext(ctx, serviceExistsInProject, arg.ID, arg.ProjectID)
+	var exists bool
+	err := row.Scan(&exists)
+	return exists, err
+}
+
+const updateVulnerabilityStatus = `-- name: UpdateVulnerabilityStatus :exec
+UPDATE vulnerabilities SET status = $1, resolved_at = $2 WHERE id = $3
+`
+
+type UpdateVulnerabilityStatusParams struct {
+	Status     string       `json:"status"`
+	ResolvedAt sql.NullTime `json:"resolved_at"`
+	ID         uuid.UUID    `json:"id"`
+}
+
+func (q *Queries) UpdateVulnerabilityStatus(ctx context.Context, arg UpdateVulnerabilityStatusParams) error {
+	_, err := q.db.ExecContext(ctx, updateVulnerabilityStatus, arg.Status, arg.ResolvedAt, arg.ID)
+	return err
+}

app/backend/sqlc/queries/auth.sql

@@ -0,0 +1,37 @@
+-- name: GetUserByEmailForAuth :one
+SELECT id, email, password_hash, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+FROM users WHERE email = $1;
+
+-- name: CountUsers :one
+SELECT COUNT(*) FROM users;
+
+-- name: CountUsersByEmail :one
+SELECT COUNT(*) FROM users WHERE email = $1;
+
+-- name: UpsertLocalUser :one
+INSERT INTO users (email, password_hash, name, avatar_url, is_admin)
+VALUES ($1, $2, $3, NULLIF($4::varchar, ''), $5)
+ON CONFLICT (email) DO UPDATE
+SET name = EXCLUDED.name,
+    avatar_url = COALESCE(EXCLUDED.avatar_url, users.avatar_url),
+    updated_at = NOW()
+RETURNING id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at;
+
+-- name: InsertUserAdmin :one
+INSERT INTO users (email, password_hash, name, is_admin)
+VALUES ($1, $2, $3, $4)
+RETURNING id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at;
+
+-- name: InsertUser :one
+INSERT INTO users (email, password_hash, name)
+VALUES ($1, $2, $3)
+RETURNING id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at;
+
+-- name: GetUserByID :one
+SELECT id, email, name, COALESCE(avatar_url, '') AS avatar_url, is_admin, created_at
+FROM users WHERE id = $1;
+
+-- name: UpdateUserProfile :exec
+UPDATE users
+SET name = COALESCE($1::varchar, name), avatar_url = COALESCE($2::varchar, avatar_url)
+WHERE id = $3;

app/backend/sqlc/queries/git.sql

@@ -0,0 +1,129 @@
+-- name: ListGitProviders :many
+SELECT id, name, display_name, api_url, webhook_url, user_id, created_at, updated_at
+FROM git_providers
+WHERE user_id = $1
+ORDER BY created_at DESC;
+
+-- name: UpsertGitHubAppProvider :one
+INSERT INTO git_providers (id, name, display_name, api_url, webhook_url, access_token, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
+ON CONFLICT (name, user_id)
+DO UPDATE SET
+    display_name = EXCLUDED.display_name,
+    api_url = EXCLUDED.api_url,
+    webhook_url = EXCLUDED.webhook_url,
+    access_token = EXCLUDED.access_token,
+    updated_at = NOW()
+RETURNING id, created_at, updated_at;
+
+-- name: InsertGitProvider :exec
+INSERT INTO git_providers (id, name, display_name, api_url, webhook_url, access_token, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW());
+
+-- name: GetGitProviderTimestamps :one
+SELECT created_at, updated_at FROM git_providers WHERE id = $1;
+
+-- name: GetGitProviderForRepos :one
+SELECT name, display_name, access_token, api_url
+FROM git_providers
+WHERE id = $1 AND user_id = $2;
+
+-- name: GetGitProviderName :one
+SELECT name FROM git_providers WHERE id = $1 AND user_id = $2;
+
+-- name: GetGitProviderForFetch :one
+SELECT name, api_url, access_token
+FROM git_providers
+WHERE id = $1 AND user_id = $2;
+
+-- name: DeleteGitProvider :execrows
+DELETE FROM git_providers WHERE id = $1 AND user_id = $2;
+
+-- name: ListGitRepositories :many
+SELECT id, provider_id, name, full_name, COALESCE(description, '') AS description,
+       clone_url, COALESCE(webhook_url, '') AS webhook_url,
+       default_branch, is_private, user_id, created_at, updated_at
+FROM git_repositories
+WHERE provider_id = $1 AND user_id = $2
+  AND ($3::text IS NULL OR full_name ILIKE ('%' || $3::text || '%') OR name ILIKE ('%' || $3::text || '%'))
+ORDER BY updated_at DESC
+LIMIT $4 OFFSET $5;
+
+-- name: CountGitRepositories :one
+SELECT COUNT(*)
+FROM git_repositories
+WHERE provider_id = $1 AND user_id = $2
+  AND ($3::text IS NULL OR full_name ILIKE ('%' || $3::text || '%') OR name ILIKE ('%' || $3::text || '%'));
+
+-- name: GetGitRepositoryByFullName :one
+SELECT id, provider_id, name, full_name, COALESCE(description, '') AS description,
+       clone_url, COALESCE(webhook_url, '') AS webhook_url,
+       default_branch, is_private, user_id, created_at, updated_at
+FROM git_repositories
+WHERE provider_id = $1 AND full_name = $2 AND user_id = $3;
+
+-- name: InsertGitRepository :one
+INSERT INTO git_repositories (id, provider_id, name, full_name, description, clone_url, default_branch, is_private, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
+RETURNING created_at, updated_at;
+
+-- name: UpsertGitRepository :exec
+INSERT INTO git_repositories (id, provider_id, name, full_name, description, clone_url, default_branch, is_private, user_id, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
+ON CONFLICT (provider_id, full_name)
+DO UPDATE SET
+    name = EXCLUDED.name,
+    description = EXCLUDED.description,
+    clone_url = EXCLUDED.clone_url,
+    default_branch = EXCLUDED.default_branch,
+    is_private = EXCLUDED.is_private,
+    updated_at = NOW();
+
+-- name: GetGitRepositoryProviderID :one
+SELECT provider_id FROM git_repositories WHERE id = $1 AND user_id = $2;
+
+-- name: UpsertGitWebhook :one
+INSERT INTO git_webhooks (id, repo_id, provider_id, events, webhook_secret, active, branch_filter, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, TRUE, $6, NOW(), NOW())
+ON CONFLICT (repo_id, provider_id)
+DO UPDATE SET
+    events = EXCLUDED.events,
+    webhook_secret = EXCLUDED.webhook_secret,
+    active = TRUE,
+    branch_filter = EXCLUDED.branch_filter,
+    updated_at = NOW()
+RETURNING id, active, created_at, updated_at;
+
+-- name: ListConnectedRepositories :many
+SELECT r.id, r.provider_id, r.name, r.full_name, COALESCE(r.description, '') AS description, r.clone_url,
+       r.default_branch, r.is_private, r.user_id, r.created_at, r.updated_at,
+       p.name AS provider_name, p.display_name
+FROM git_repositories r
+JOIN git_providers p ON r.provider_id = p.id
+WHERE r.user_id = $1
+ORDER BY r.updated_at DESC
+LIMIT $2 OFFSET $3;
+
+-- name: CountUserRepositories :one
+SELECT COUNT(*) FROM git_repositories WHERE user_id = $1;
+
+-- name: GetGitWebhookForPush :one
+SELECT webhook_secret, repo_id, provider_id, COALESCE(branch_filter, '') AS branch_filter, active
+FROM git_webhooks WHERE id = $1;
+
+-- name: GetGitProviderNameByID :one
+SELECT name FROM git_providers WHERE id = $1;
+
+-- name: GetGitRepoForPush :one
+SELECT clone_url, full_name, user_id FROM git_repositories WHERE id = $1;
+
+-- name: ListServicesForPush :many
+SELECT s.id, s.project_id, s.name,
+       COALESCE(s.type, s.service_type, '') AS type, COALESCE(s.status, '') AS status,
+       COALESCE(s.image, s.image_name, '') AS image, COALESCE(s.command, s.start_command, '') AS command,
+       COALESCE(s.environment, '') AS environment, COALESCE(s.git_repo, s.source_url, '') AS git_repo,
+       COALESCE(s.git_branch, '') AS git_branch, COALESCE(s.build_path, '') AS build_path,
+       COALESCE(s.cpu, '') AS cpu, COALESCE(s.memory, '') AS memory,
+       s.created_at, s.updated_at
+FROM services s
+WHERE (s.git_branch = $1 OR $1 = '') AND (s.git_repo = $2 OR s.git_repo = $3);

app/backend/sqlc/queries/security.sql

@@ -0,0 +1,78 @@
+-- name: GetProjectOwnerText :one
+SELECT owner_id::text FROM projects WHERE id = $1;
+
+-- name: ServiceExistsInProject :one
+SELECT EXISTS(SELECT 1 FROM services WHERE id = $1 AND project_id = $2);
+
+-- name: ListVulnerabilities :many
+SELECT id, type, severity, title, description, service_id, status, found_at, resolved_at
+FROM vulnerabilities
+WHERE project_id = $1
+ORDER BY
+    CASE severity
+        WHEN 'critical' THEN 1
+        WHEN 'high' THEN 2
+        WHEN 'medium' THEN 3
+        WHEN 'low' THEN 4
+    END,
+    found_at DESC;
+
+-- name: UpdateVulnerabilityStatus :exec
+UPDATE vulnerabilities SET status = $1, resolved_at = $2 WHERE id = $3;
+
+-- name: ComplianceFrameworkExists :one
+SELECT EXISTS(SELECT 1 FROM compliance_frameworks WHERE id = $1);
+
+-- name: ListComplianceFrameworks :many
+SELECT id, name, description, version, enabled, created_at
+FROM compliance_frameworks
+WHERE enabled = true
+ORDER BY name;
+
+-- name: GetVulnerabilityMetrics :one
+SELECT
+    COUNT(*) AS total,
+    COUNT(*) FILTER (WHERE severity = 'critical') AS critical,
+    COUNT(*) FILTER (WHERE severity = 'high') AS high,
+    COUNT(*) FILTER (WHERE severity = 'medium') AS medium,
+    COUNT(*) FILTER (WHERE severity = 'low') AS low,
+    COUNT(*) FILTER (WHERE status = 'open') AS open,
+    COUNT(*) FILTER (WHERE status = 'resolved') AS resolved
+FROM vulnerabilities
+WHERE project_id = $1;
+
+-- name: GetLatestSecurityScan :one
+SELECT id, COALESCE((summary->>'score')::int, 0)::int AS score, started_at AS scanned_at, status
+FROM security_scans
+WHERE project_id = $1
+ORDER BY started_at DESC
+LIMIT 1;
+
+-- name: GetLatestComplianceReport :one
+SELECT overall_status, score, assessment_date
+FROM compliance_reports
+WHERE project_id = $1
+ORDER BY assessment_date DESC
+LIMIT 1;
+
+-- name: CheckSecurityProjectAccess :one
+SELECT EXISTS (
+    SELECT 1
+    FROM projects p
+    WHERE p.id = $1
+      AND (p.is_approved
+           OR $3::bool
+           OR p.owner_id = $2 OR EXISTS (
+            SELECT 1 FROM project_members pm
+            WHERE pm.project_id = p.id AND pm.user_id = $2
+      ))
+);
+
+-- name: GetSecurityScanProjectID :one
+SELECT project_id FROM security_scans WHERE id = $1;
+
+-- name: GetComplianceReportProjectID :one
+SELECT project_id FROM compliance_reports WHERE id = $1;
+
+-- name: GetVulnerabilityProjectID :one
+SELECT project_id FROM vulnerabilities WHERE id = $1;

app/backend/sqlc/schema.sql

@@ -489,3 +489,104 @@ CREATE TABLE cron_executions (
     output TEXT,
     error TEXT
 );
+
+CREATE TABLE git_providers (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    name VARCHAR(255) NOT NULL,
+    display_name VARCHAR(255) NOT NULL,
+    api_url VARCHAR(255) NOT NULL,
+    webhook_url VARCHAR(255) NOT NULL,
+    access_token TEXT NOT NULL,
+    user_id UUID NOT NULL,
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE TABLE git_repositories (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    provider_id UUID NOT NULL,
+    name VARCHAR(255) NOT NULL,
+    full_name VARCHAR(255) NOT NULL,
+    description TEXT,
+    clone_url VARCHAR(255) NOT NULL,
+    webhook_url VARCHAR(255),
+    default_branch VARCHAR(255) DEFAULT 'main',
+    is_private BOOLEAN DEFAULT false,
+    user_id UUID NOT NULL,
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE TABLE git_webhooks (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    repo_id UUID NOT NULL,
+    provider_id UUID NOT NULL,
+    events TEXT NOT NULL,
+    webhook_secret TEXT NOT NULL,
+    remote_webhook_id VARCHAR(255),
+    active BOOLEAN DEFAULT true,
+    branch_filter VARCHAR(255),
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE TABLE users (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    email VARCHAR(255) NOT NULL UNIQUE,
+    password_hash VARCHAR(255) NOT NULL,
+    name VARCHAR(255) NOT NULL,
+    avatar_url VARCHAR(500),
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    is_admin BOOLEAN NOT NULL DEFAULT false
+);
+
+CREATE TABLE compliance_frameworks (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    name VARCHAR(100) NOT NULL UNIQUE,
+    description TEXT,
+    version VARCHAR(20) NOT NULL,
+    enabled BOOLEAN NOT NULL DEFAULT true,
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE security_scans (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    project_id UUID NOT NULL,
+    service_id UUID,
+    scan_type VARCHAR(50) NOT NULL,
+    status VARCHAR(50) NOT NULL DEFAULT 'running',
+    started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    completed_at TIMESTAMP WITH TIME ZONE,
+    summary JSONB DEFAULT '{}',
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE compliance_reports (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    project_id UUID NOT NULL,
+    framework_id UUID NOT NULL,
+    assessment_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    assessor VARCHAR(255),
+    overall_status VARCHAR(50) NOT NULL,
+    score INTEGER NOT NULL DEFAULT 0,
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE vulnerabilities (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    type VARCHAR(50) NOT NULL,
+    severity VARCHAR(20) NOT NULL,
+    title VARCHAR(255) NOT NULL,
+    description TEXT,
+    service_id UUID,
+    project_id UUID NOT NULL,
+    status VARCHAR(50) NOT NULL DEFAULT 'open',
+    found_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    resolved_at TIMESTAMP WITH TIME ZONE,
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);