3847eb2036
This repo had no version control prior to this commit. The import is a
straight snapshot of the working tree at 2026-05-03; the deployed
binary on fihelvop01 was being rebuilt from this source via `make
build` + scp into place, with no upstream review path.
The snapshot already includes one in-flight fix made on 2026-05-03 to
internal/service/persona.go:GetSelfModel — the handler queried
`source` and `strength` columns plus an `is_active = true` filter on
persona.persona_commitments, none of which exist on that table (its
shape is session-bound commitments with `status`, `commitment_meta`,
etc.). The query returned a 500 every time SynapseHub bootstrapped a
persona's self-model, dropping the IdentityConstraints / Commitments /
ConscienceStandards layer from the assembled prompt. The patched
query reads existing columns only (commitment_text, commitment_type),
filters on `status='active'`, and synthesises Source="learned" /
Strength=1.0 to keep the SelfModel response shape stable for callers.
Verified live: `GET /api/v1/personas/70f7cfd9-.../self-model` now
returns 200 with `{identityConstraints:[],commitments:[],
conscienceStandards:[]}` instead of 500.
Future changes go through PRs against this repo — no more bin-only
deploys.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
414 lines
13 KiB
Go
414 lines
13 KiB
Go
package service
|
|
|
|
import (
|
|
"context"
|
|
"encoding/json"
|
|
"fmt"
|
|
"time"
|
|
|
|
"github.com/google/uuid"
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
"github.com/rs/zerolog"
|
|
|
|
"github.com/gosec/gsc-ops-api/pkg/types"
|
|
)
|
|
|
|
// DatabaseService handles tenant and user database operations
|
|
type DatabaseService struct {
|
|
pool *pgxpool.Pool
|
|
logger zerolog.Logger
|
|
}
|
|
|
|
// NewDatabaseService creates a new database service
|
|
func NewDatabaseService(pool *pgxpool.Pool, logger zerolog.Logger) *DatabaseService {
|
|
return &DatabaseService{
|
|
pool: pool,
|
|
logger: logger.With().Str("service", "database").Logger(),
|
|
}
|
|
}
|
|
|
|
// ListTenants lists tenants with optional filters
|
|
func (s *DatabaseService) ListTenants(ctx context.Context, params types.ListParams) ([]types.Tenant, int64, error) {
|
|
params = types.DefaultListParams(params)
|
|
|
|
countQuery := `SELECT COUNT(*) FROM admin.tenants WHERE 1=1`
|
|
listQuery := `SELECT id, customer_id, code, name, display_name, domain, logo_url, primary_color,
|
|
max_users, max_storage_gb, max_recording_hours, is_active, metadata, created_at, updated_at
|
|
FROM admin.tenants WHERE 1=1`
|
|
|
|
args := []interface{}{}
|
|
argIdx := 1
|
|
|
|
if params.Status != "" {
|
|
if params.Status == "active" {
|
|
countQuery += " AND is_active = true"
|
|
listQuery += " AND is_active = true"
|
|
} else if params.Status == "inactive" {
|
|
countQuery += " AND is_active = false"
|
|
listQuery += " AND is_active = false"
|
|
}
|
|
}
|
|
if params.Search != "" {
|
|
countQuery += fmt.Sprintf(" AND (name ILIKE $%d OR code ILIKE $%d OR domain ILIKE $%d)", argIdx, argIdx, argIdx)
|
|
listQuery += fmt.Sprintf(" AND (name ILIKE $%d OR code ILIKE $%d OR domain ILIKE $%d)", argIdx, argIdx, argIdx)
|
|
args = append(args, "%"+params.Search+"%")
|
|
argIdx++
|
|
}
|
|
|
|
var total int64
|
|
if err := s.pool.QueryRow(ctx, countQuery, args...).Scan(&total); err != nil {
|
|
return nil, 0, fmt.Errorf("count query failed: %w", err)
|
|
}
|
|
|
|
listQuery += fmt.Sprintf(" ORDER BY created_at DESC LIMIT $%d OFFSET $%d", argIdx, argIdx+1)
|
|
args = append(args, params.Limit, params.Offset)
|
|
|
|
rows, err := s.pool.Query(ctx, listQuery, args...)
|
|
if err != nil {
|
|
return nil, 0, fmt.Errorf("list query failed: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
tenants := make([]types.Tenant, 0)
|
|
for rows.Next() {
|
|
var t types.Tenant
|
|
var metadataJSON []byte
|
|
if err := rows.Scan(&t.ID, &t.CustomerID, &t.Code, &t.Name, &t.DisplayName, &t.Domain,
|
|
&t.LogoURL, &t.PrimaryColor, &t.MaxUsers, &t.MaxStorageGB, &t.MaxRecordingHours,
|
|
&t.IsActive, &metadataJSON, &t.CreatedAt, &t.UpdatedAt); err != nil {
|
|
return nil, 0, fmt.Errorf("scan failed: %w", err)
|
|
}
|
|
if len(metadataJSON) > 0 {
|
|
json.Unmarshal(metadataJSON, &t.Metadata)
|
|
}
|
|
tenants = append(tenants, t)
|
|
}
|
|
|
|
return tenants, total, nil
|
|
}
|
|
|
|
// GetTenant gets a tenant by ID
|
|
func (s *DatabaseService) GetTenant(ctx context.Context, id uuid.UUID) (*types.Tenant, error) {
|
|
var t types.Tenant
|
|
var metadataJSON []byte
|
|
err := s.pool.QueryRow(ctx,
|
|
`SELECT id, customer_id, code, name, display_name, domain, logo_url, primary_color,
|
|
max_users, max_storage_gb, max_recording_hours, is_active, metadata, created_at, updated_at
|
|
FROM admin.tenants WHERE id = $1`, id).
|
|
Scan(&t.ID, &t.CustomerID, &t.Code, &t.Name, &t.DisplayName, &t.Domain,
|
|
&t.LogoURL, &t.PrimaryColor, &t.MaxUsers, &t.MaxStorageGB, &t.MaxRecordingHours,
|
|
&t.IsActive, &metadataJSON, &t.CreatedAt, &t.UpdatedAt)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if len(metadataJSON) > 0 {
|
|
json.Unmarshal(metadataJSON, &t.Metadata)
|
|
}
|
|
return &t, nil
|
|
}
|
|
|
|
// CreateTenant creates a new tenant
|
|
func (s *DatabaseService) CreateTenant(ctx context.Context, req *types.TenantCreate) (*types.Tenant, error) {
|
|
id := uuid.New()
|
|
now := time.Now().UTC()
|
|
|
|
var metadataJSON []byte
|
|
if req.Metadata != nil {
|
|
var err error
|
|
metadataJSON, err = json.Marshal(req.Metadata)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to marshal metadata: %w", err)
|
|
}
|
|
}
|
|
|
|
_, err := s.pool.Exec(ctx,
|
|
`INSERT INTO admin.tenants (id, customer_id, code, name, display_name, domain, logo_url, primary_color,
|
|
max_users, max_storage_gb, max_recording_hours, is_active, metadata, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, true, $12, $13, $13)`,
|
|
id, req.CustomerID, req.Code, req.Name, nilIfEmpty(req.DisplayName), nilIfEmpty(req.Domain),
|
|
nilIfEmpty(req.LogoURL), nilIfEmpty(req.PrimaryColor),
|
|
req.MaxUsers, req.MaxStorageGB, req.MaxRecordingHours, metadataJSON, now)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("insert failed: %w", err)
|
|
}
|
|
|
|
return s.GetTenant(ctx, id)
|
|
}
|
|
|
|
// UpdateTenant updates a tenant
|
|
func (s *DatabaseService) UpdateTenant(ctx context.Context, id uuid.UUID, req *types.TenantUpdate) (*types.Tenant, error) {
|
|
setClauses := []string{}
|
|
args := []interface{}{}
|
|
argIdx := 1
|
|
|
|
if req.Name != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("name = $%d", argIdx))
|
|
args = append(args, *req.Name)
|
|
argIdx++
|
|
}
|
|
if req.DisplayName != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("display_name = $%d", argIdx))
|
|
args = append(args, *req.DisplayName)
|
|
argIdx++
|
|
}
|
|
if req.Domain != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("domain = $%d", argIdx))
|
|
args = append(args, *req.Domain)
|
|
argIdx++
|
|
}
|
|
if req.LogoURL != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("logo_url = $%d", argIdx))
|
|
args = append(args, *req.LogoURL)
|
|
argIdx++
|
|
}
|
|
if req.PrimaryColor != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("primary_color = $%d", argIdx))
|
|
args = append(args, *req.PrimaryColor)
|
|
argIdx++
|
|
}
|
|
if req.MaxUsers != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("max_users = $%d", argIdx))
|
|
args = append(args, *req.MaxUsers)
|
|
argIdx++
|
|
}
|
|
if req.MaxStorageGB != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("max_storage_gb = $%d", argIdx))
|
|
args = append(args, *req.MaxStorageGB)
|
|
argIdx++
|
|
}
|
|
if req.MaxRecordingHours != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("max_recording_hours = $%d", argIdx))
|
|
args = append(args, *req.MaxRecordingHours)
|
|
argIdx++
|
|
}
|
|
if req.IsActive != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("is_active = $%d", argIdx))
|
|
args = append(args, *req.IsActive)
|
|
argIdx++
|
|
}
|
|
if req.Metadata != nil {
|
|
metadataJSON, err := json.Marshal(req.Metadata)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to marshal metadata: %w", err)
|
|
}
|
|
setClauses = append(setClauses, fmt.Sprintf("metadata = $%d", argIdx))
|
|
args = append(args, metadataJSON)
|
|
argIdx++
|
|
}
|
|
|
|
if len(setClauses) == 0 {
|
|
return s.GetTenant(ctx, id)
|
|
}
|
|
|
|
setClauses = append(setClauses, fmt.Sprintf("updated_at = $%d", argIdx))
|
|
args = append(args, time.Now().UTC())
|
|
argIdx++
|
|
|
|
args = append(args, id)
|
|
query := fmt.Sprintf("UPDATE admin.tenants SET %s WHERE id = $%d",
|
|
join(setClauses, ", "), argIdx)
|
|
|
|
_, err := s.pool.Exec(ctx, query, args...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("update failed: %w", err)
|
|
}
|
|
|
|
return s.GetTenant(ctx, id)
|
|
}
|
|
|
|
// SoftDeleteTenant deactivates a tenant
|
|
func (s *DatabaseService) SoftDeleteTenant(ctx context.Context, id uuid.UUID) error {
|
|
_, err := s.pool.Exec(ctx,
|
|
`UPDATE admin.tenants SET is_active = false, updated_at = $1 WHERE id = $2`,
|
|
time.Now().UTC(), id)
|
|
return err
|
|
}
|
|
|
|
// ListUsers lists users with optional filters
|
|
func (s *DatabaseService) ListUsers(ctx context.Context, params types.ListParams) ([]types.DBUser, int64, error) {
|
|
params = types.DefaultListParams(params)
|
|
|
|
countQuery := `SELECT COUNT(*) FROM admin.users WHERE 1=1`
|
|
listQuery := `SELECT id, gscsid, first_name, last_name, display_name, email, timezone, locale, status,
|
|
last_login_at, last_activity_at, metadata, created_at, updated_at
|
|
FROM admin.users WHERE 1=1`
|
|
|
|
args := []interface{}{}
|
|
argIdx := 1
|
|
|
|
if params.Status != "" {
|
|
countQuery += fmt.Sprintf(" AND status = $%d", argIdx)
|
|
listQuery += fmt.Sprintf(" AND status = $%d", argIdx)
|
|
args = append(args, params.Status)
|
|
argIdx++
|
|
}
|
|
if params.Search != "" {
|
|
countQuery += fmt.Sprintf(" AND (gscsid ILIKE $%d OR display_name ILIKE $%d OR email ILIKE $%d)", argIdx, argIdx, argIdx)
|
|
listQuery += fmt.Sprintf(" AND (gscsid ILIKE $%d OR display_name ILIKE $%d OR email ILIKE $%d)", argIdx, argIdx, argIdx)
|
|
args = append(args, "%"+params.Search+"%")
|
|
argIdx++
|
|
}
|
|
|
|
var total int64
|
|
if err := s.pool.QueryRow(ctx, countQuery, args...).Scan(&total); err != nil {
|
|
return nil, 0, fmt.Errorf("count query failed: %w", err)
|
|
}
|
|
|
|
listQuery += fmt.Sprintf(" ORDER BY created_at DESC LIMIT $%d OFFSET $%d", argIdx, argIdx+1)
|
|
args = append(args, params.Limit, params.Offset)
|
|
|
|
rows, err := s.pool.Query(ctx, listQuery, args...)
|
|
if err != nil {
|
|
return nil, 0, fmt.Errorf("list query failed: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
users := make([]types.DBUser, 0)
|
|
for rows.Next() {
|
|
var u types.DBUser
|
|
var metadataJSON []byte
|
|
if err := rows.Scan(&u.ID, &u.GscSID, &u.FirstName, &u.LastName, &u.DisplayName, &u.Email, &u.Timezone, &u.Locale, &u.Status,
|
|
&u.LastLoginAt, &u.LastActivityAt, &metadataJSON, &u.CreatedAt, &u.UpdatedAt); err != nil {
|
|
return nil, 0, fmt.Errorf("scan failed: %w", err)
|
|
}
|
|
if len(metadataJSON) > 0 {
|
|
json.Unmarshal(metadataJSON, &u.Metadata)
|
|
}
|
|
users = append(users, u)
|
|
}
|
|
|
|
return users, total, nil
|
|
}
|
|
|
|
// GetUser gets a user by ID
|
|
func (s *DatabaseService) GetUser(ctx context.Context, id uuid.UUID) (*types.DBUser, error) {
|
|
var u types.DBUser
|
|
var metadataJSON []byte
|
|
err := s.pool.QueryRow(ctx,
|
|
`SELECT id, gscsid, first_name, last_name, display_name, email, timezone, locale, status,
|
|
last_login_at, last_activity_at, metadata, created_at, updated_at
|
|
FROM admin.users WHERE id = $1`, id).
|
|
Scan(&u.ID, &u.GscSID, &u.FirstName, &u.LastName, &u.DisplayName, &u.Email, &u.Timezone, &u.Locale, &u.Status,
|
|
&u.LastLoginAt, &u.LastActivityAt, &metadataJSON, &u.CreatedAt, &u.UpdatedAt)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if len(metadataJSON) > 0 {
|
|
json.Unmarshal(metadataJSON, &u.Metadata)
|
|
}
|
|
return &u, nil
|
|
}
|
|
|
|
// CreateUser creates a new user record
|
|
func (s *DatabaseService) CreateUser(ctx context.Context, req *types.DBUserCreate) (*types.DBUser, error) {
|
|
id := uuid.New()
|
|
now := time.Now().UTC()
|
|
|
|
var metadataJSON []byte
|
|
if req.Metadata != nil {
|
|
var err error
|
|
metadataJSON, err = json.Marshal(req.Metadata)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to marshal metadata: %w", err)
|
|
}
|
|
}
|
|
|
|
_, err := s.pool.Exec(ctx,
|
|
`INSERT INTO admin.users (id, gscsid, first_name, last_name, display_name, email, timezone, locale, status, metadata, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'active', $9, $10, $10)`,
|
|
id, req.GscSID, nilIfEmpty(req.FirstName), nilIfEmpty(req.LastName), nilIfEmpty(req.DisplayName), nilIfEmpty(req.Email), nilIfEmpty(req.Timezone), nilIfEmpty(req.Locale), metadataJSON, now)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("insert failed: %w", err)
|
|
}
|
|
|
|
return s.GetUser(ctx, id)
|
|
}
|
|
|
|
// UpdateUser updates a user record
|
|
func (s *DatabaseService) UpdateUser(ctx context.Context, id uuid.UUID, req *types.DBUserUpdate) (*types.DBUser, error) {
|
|
setClauses := []string{}
|
|
args := []interface{}{}
|
|
argIdx := 1
|
|
|
|
if req.Timezone != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("timezone = $%d", argIdx))
|
|
args = append(args, *req.Timezone)
|
|
argIdx++
|
|
}
|
|
if req.Locale != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("locale = $%d", argIdx))
|
|
args = append(args, *req.Locale)
|
|
argIdx++
|
|
}
|
|
if req.Status != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("status = $%d", argIdx))
|
|
args = append(args, *req.Status)
|
|
argIdx++
|
|
}
|
|
if req.LastLoginAt != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("last_login_at = $%d", argIdx))
|
|
args = append(args, *req.LastLoginAt)
|
|
argIdx++
|
|
}
|
|
if req.LastActivityAt != nil {
|
|
setClauses = append(setClauses, fmt.Sprintf("last_activity_at = $%d", argIdx))
|
|
args = append(args, *req.LastActivityAt)
|
|
argIdx++
|
|
}
|
|
if req.Metadata != nil {
|
|
metadataJSON, err := json.Marshal(req.Metadata)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to marshal metadata: %w", err)
|
|
}
|
|
setClauses = append(setClauses, fmt.Sprintf("metadata = $%d", argIdx))
|
|
args = append(args, metadataJSON)
|
|
argIdx++
|
|
}
|
|
|
|
if len(setClauses) == 0 {
|
|
return s.GetUser(ctx, id)
|
|
}
|
|
|
|
setClauses = append(setClauses, fmt.Sprintf("updated_at = $%d", argIdx))
|
|
args = append(args, time.Now().UTC())
|
|
argIdx++
|
|
|
|
args = append(args, id)
|
|
query := fmt.Sprintf("UPDATE admin.users SET %s WHERE id = $%d",
|
|
join(setClauses, ", "), argIdx)
|
|
|
|
_, err := s.pool.Exec(ctx, query, args...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("update failed: %w", err)
|
|
}
|
|
|
|
return s.GetUser(ctx, id)
|
|
}
|
|
|
|
// DeactivateUser deactivates a user
|
|
func (s *DatabaseService) DeactivateUser(ctx context.Context, id uuid.UUID) error {
|
|
now := time.Now().UTC()
|
|
_, err := s.pool.Exec(ctx,
|
|
`UPDATE admin.users SET status = 'inactive', updated_at = $1 WHERE id = $2`,
|
|
now, id)
|
|
return err
|
|
}
|
|
|
|
func nilIfEmpty(s string) *string {
|
|
if s == "" {
|
|
return nil
|
|
}
|
|
return &s
|
|
}
|
|
|
|
func join(strs []string, sep string) string {
|
|
result := ""
|
|
for i, s := range strs {
|
|
if i > 0 {
|
|
result += sep
|
|
}
|
|
result += s
|
|
}
|
|
return result
|
|
}
|