"How much did the protein run use" is a question about a piece of work, not about c003-15. A tenant asks for clusters over months; individually each one is an address, some hardware and a lease, and nothing joined them up. A project groups them, and is optional throughout. Somebody who wants one cluster for an afternoon should not have to invent a project to get it, and requiring one would mean every tenant carrying a "default" that means nothing. The form does not even ask when there are none: a field whose only option is "none" is a question with one answer. The cost side needed no new columns. The cluster record already said when it was created and destroyed, and cluster_vm said how many cores and how much memory it held; what was missing was anything that read them. cluster_usage is that, and it is a view rather than a table because a derived number stored is a number that can disagree with what it was derived from. It works retroactively - every cluster this estate has ever built now has a line. Core-hours are reserved, not executed, and the view says so. That is the honest unit for an on-demand cluster: the hardware was yours and nobody else could have it, whether or not a job ran. What was actually executed is a Slurm accounting question and there is no slurmdbd. The project is attached inside the allocating transaction, for the same reason the placement and the applications are, and it is checked there to be the tenant's own and still open - a cluster filed against somebody else's project leaks what they are working on. Closing keeps history and stops it being offered. There is no delete: the reference from cluster is RESTRICT, because a project removed from under its clusters leaves their cost attributed to nothing, and afterwards is when the question gets asked. A release now writes manifest.json beside home.tgz: the nodes, the wall time, the core-hours. The database knows all of it and outlives the cluster, but the tarball can be moved off this host and the description travels with it. Not fatal if it fails - the data is already safe by then, and holding hardware because a note could not be written is the wrong trade. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
200 lines
6.6 KiB
Go
200 lines
6.6 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"time"
|
|
)
|
|
|
|
// Projects group a tenant's clusters over time.
|
|
//
|
|
// Individually a cluster is an address, some hardware and a lease.
|
|
// Together they are a piece of work somebody is accountable for, and
|
|
// nothing recorded that: "how much did the protein run use" is a question
|
|
// about a project, not about c003-15.
|
|
//
|
|
// Optional throughout. Somebody who wants one cluster for an afternoon
|
|
// should not have to invent a project to get it.
|
|
|
|
type Project struct {
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
TenantName string `json:"tenant_name"`
|
|
Name string `json:"name"`
|
|
Description string `json:"description,omitempty"`
|
|
CreatedAt time.Time `json:"created_at"`
|
|
CreatedBy string `json:"created_by"`
|
|
ClosedAt *time.Time `json:"closed_at,omitempty"`
|
|
|
|
// Clusters and CoreHours are what the project is for: a count and a
|
|
// cost, joined in rather than looked up per row, because every screen
|
|
// that lists projects wants both.
|
|
Clusters int `json:"clusters"`
|
|
CoreHours float64 `json:"core_hours"`
|
|
}
|
|
|
|
func (p Project) Open() bool { return p.ClosedAt == nil }
|
|
|
|
const projectColumns = `p.id, p.tenant_id, t.name, p.name,
|
|
coalesce(p.description, ''), p.created_at, p.created_by, p.closed_at`
|
|
|
|
// CreateProject records one, for a tenant that must already exist.
|
|
//
|
|
// EnsureTenant rather than a plain lookup: a tenant is created the first
|
|
// time it is seen, and somebody filing a project before their first
|
|
// cluster is exactly that moment.
|
|
func (s *Store) CreateProject(ctx context.Context, tenant, owner, name, description, by string) (*Project, error) {
|
|
tenantID, err := s.EnsureTenant(ctx, tenant, owner)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
var p Project
|
|
err = s.pool.QueryRow(ctx, `
|
|
WITH inserted AS (
|
|
INSERT INTO project (tenant_id, name, description, created_by)
|
|
VALUES ($1, $2, nullif($3, ''), $4)
|
|
RETURNING *
|
|
)
|
|
SELECT `+projectColumns+`
|
|
FROM inserted p JOIN tenant t ON t.id = p.tenant_id`,
|
|
tenantID, name, description, by).Scan(
|
|
&p.ID, &p.TenantID, &p.TenantName, &p.Name,
|
|
&p.Description, &p.CreatedAt, &p.CreatedBy, &p.ClosedAt)
|
|
if err != nil {
|
|
if isUniqueViolationOn(err, "project_name_unique") {
|
|
return nil, fmt.Errorf("%s already has a project called %q", tenant, name)
|
|
}
|
|
return nil, err
|
|
}
|
|
return &p, nil
|
|
}
|
|
|
|
// Projects lists them with what they have cost so far.
|
|
//
|
|
// tenant empty means every project, which is the administrator's view. A
|
|
// tenant sees their own and nothing else - a project name is a statement
|
|
// about what somebody is working on.
|
|
func (s *Store) Projects(ctx context.Context, tenant string) ([]Project, error) {
|
|
rows, err := s.pool.Query(ctx, `
|
|
SELECT `+projectColumns+`,
|
|
coalesce(u.clusters, 0), coalesce(u.core_hours, 0)
|
|
FROM project p
|
|
JOIN tenant t ON t.id = p.tenant_id
|
|
LEFT JOIN LATERAL (
|
|
SELECT count(*)::int AS clusters, sum(core_hours) AS core_hours
|
|
FROM cluster_usage WHERE project_id = p.id
|
|
) u ON true
|
|
WHERE $1 = '' OR t.name = $1
|
|
ORDER BY p.closed_at IS NOT NULL, p.created_at DESC`, tenant)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var out []Project
|
|
for rows.Next() {
|
|
var p Project
|
|
if err := rows.Scan(&p.ID, &p.TenantID, &p.TenantName, &p.Name,
|
|
&p.Description, &p.CreatedAt, &p.CreatedBy, &p.ClosedAt,
|
|
&p.Clusters, &p.CoreHours); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, p)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// CloseProject stops it being offered for new clusters and keeps
|
|
// everything it already holds. Deleting is deliberately not offered: the
|
|
// reference from cluster is RESTRICT, so a project with history cannot go
|
|
// anyway, and one without history costs nothing to leave closed.
|
|
func (s *Store) CloseProject(ctx context.Context, id int64, tenant string) error {
|
|
tag, err := s.pool.Exec(ctx, `
|
|
UPDATE project p SET closed_at = now()
|
|
FROM tenant t
|
|
WHERE p.tenant_id = t.id AND p.id = $1 AND p.closed_at IS NULL
|
|
AND ($2 = '' OR t.name = $2)`, id, tenant)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if tag.RowsAffected() == 0 {
|
|
return fmt.Errorf("project %d: not found, not yours, or already closed", id)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// Usage is what each cluster held and for how long, newest first.
|
|
//
|
|
// It reads the view rather than recomputing: a derived number in two
|
|
// places is a number that can disagree with itself. Released clusters are
|
|
// included, and are most of the point - the record outlives the machines
|
|
// so that somebody can ask afterwards.
|
|
type Usage struct {
|
|
SerialID int64 `json:"serial_id"`
|
|
AccountingName string `json:"accounting_name"`
|
|
TenantName string `json:"tenant_name"`
|
|
ProjectID *int64 `json:"project_id,omitempty"`
|
|
ProjectName string `json:"project_name,omitempty"`
|
|
Site string `json:"site"`
|
|
State string `json:"state"`
|
|
CreatedAt time.Time `json:"created_at"`
|
|
DestroyedAt *time.Time `json:"destroyed_at,omitempty"`
|
|
Wall time.Duration
|
|
VMs int `json:"vms"`
|
|
Cores int `json:"cores"`
|
|
MemoryGB int `json:"memory_gb"`
|
|
Devices int `json:"devices"`
|
|
CoreHours float64 `json:"core_hours"`
|
|
GBHours float64 `json:"gb_hours"`
|
|
}
|
|
|
|
func (s *Store) Usage(ctx context.Context, tenant string, limit int) ([]Usage, error) {
|
|
if limit <= 0 || limit > 500 {
|
|
limit = 200
|
|
}
|
|
rows, err := s.pool.Query(ctx, `
|
|
SELECT serial_id, accounting_name, tenant_name, project_id,
|
|
coalesce(project_name, ''), site, state, created_at, destroyed_at,
|
|
extract(epoch from wall)::bigint,
|
|
vms, cores, memory_gb, devices, core_hours, gb_hours
|
|
FROM cluster_usage
|
|
WHERE $1 = '' OR tenant_name = $1
|
|
ORDER BY created_at DESC
|
|
LIMIT $2`, tenant, limit)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var out []Usage
|
|
for rows.Next() {
|
|
var u Usage
|
|
var secs int64
|
|
if err := rows.Scan(&u.SerialID, &u.AccountingName, &u.TenantName,
|
|
&u.ProjectID, &u.ProjectName, &u.Site, &u.State,
|
|
&u.CreatedAt, &u.DestroyedAt, &secs,
|
|
&u.VMs, &u.Cores, &u.MemoryGB, &u.Devices,
|
|
&u.CoreHours, &u.GBHours); err != nil {
|
|
return nil, err
|
|
}
|
|
u.Wall = time.Duration(secs) * time.Second
|
|
out = append(out, u)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// UsageFor is one cluster's line, which is what a release writes beside
|
|
// the archive so the tarball can say what it was without a database.
|
|
func (s *Store) UsageFor(ctx context.Context, serialID int64) (*Usage, error) {
|
|
all, err := s.Usage(ctx, "", 500)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for i := range all {
|
|
if all[i].SerialID == serialID {
|
|
return &all[i], nil
|
|
}
|
|
}
|
|
return nil, fmt.Errorf("no usage recorded for cluster %d", serialID)
|
|
}
|