mAi
0ac26fe0ee
Re-runnable Go script under scripts/seed-example-projects/ that wipes every paliad.projects row (FK CASCADE handles dependents) and seeds 18 realistic patent-litigation projects across 3 example clients: SIEMENS — UPC + LG cases incl. CCR (Widerklage) on EP3456789 BAYER — EPA Einspruch + BPatG Nichtigkeit on EP2222333 BEISPL — sparse DPMA demo on DE10987654 Every node carries the chain-code-driving fields (reference on client, opponent_code on litigation, patent_number on patent, proceeding_type_id on case), producing codes like SIEMENS.HUAW.789.INF.CFI and SIEMENS.HUAW.789.CCR.CFI via services.BuildProjectCode. One transaction wraps both wipe and seed; -dry-run rolls back so the script can be sanity-checked before commit. Reference tables (proceeding_types, deadline_rules, event_types, gerichte, checklists templates, firms) are untouched. Ran live against youpc Postgres 2026-05-25: 12 rows wiped, 18 seeded.
569 lines
16 KiB
Go
569 lines
16 KiB
Go
// Seed Example Projects (t-paliad-256 / m/paliad#87).
|
|
//
|
|
// Re-runnable test-data reset:
|
|
//
|
|
// 1. Wipes every row in paliad.projects (FK CASCADE handles the
|
|
// dependent rows: deadlines, appointments, parties, notes,
|
|
// project_events, project_teams, submission_drafts, approval_*,
|
|
// project_partner_units, user_pinned_projects, documents,
|
|
// user_calendar_bindings).
|
|
//
|
|
// 2. Inserts a small but realistic example tree (3 clients, 4
|
|
// litigations, 4 patents, 8 cases — 19 projects total) that
|
|
// exercises the auto-derived chain code: Client.Litigation.Patent.Case
|
|
// → e.g. SIEMENS.HUAW.789.INF.CFI.
|
|
//
|
|
// 3. Re-reads the projects and prints each row's chain code so the
|
|
// operator can eyeball the result without bouncing to SQL.
|
|
//
|
|
// Reference tables (proceeding_types, deadline_rules, event_types,
|
|
// gerichte, checklists templates, firms, profiles) are untouched.
|
|
//
|
|
// Run:
|
|
//
|
|
// DATABASE_URL='postgres://...' go run ./scripts/seed-example-projects
|
|
//
|
|
// One transaction wraps both wipe and seed so the DB is never in a
|
|
// half-wiped state. Re-running drops the previous example tree and
|
|
// reseeds fresh UUIDs — handy when project-code semantics change.
|
|
//
|
|
// Owner: m (matthias.siebels@hoganlovells.com). The script looks the
|
|
// auth user up by email so it works on any environment where that
|
|
// account exists; on a brand-new DB it falls back to NULL created_by.
|
|
package main
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"errors"
|
|
"flag"
|
|
"fmt"
|
|
"os"
|
|
"strings"
|
|
"text/tabwriter"
|
|
"time"
|
|
|
|
"github.com/google/uuid"
|
|
"github.com/jmoiron/sqlx"
|
|
_ "github.com/lib/pq"
|
|
|
|
"mgit.msbls.de/m/paliad/internal/services"
|
|
)
|
|
|
|
// ownerEmail is the auth.users email the seed assigns as created_by.
|
|
// Living in code (not a flag) because the example tree is m-owned by
|
|
// convention; flip if the example data ever needs a service-account
|
|
// owner.
|
|
const ownerEmail = "matthias.siebels@hoganlovells.com"
|
|
|
|
// Proceeding-type IDs used by the seed. Resolved by code (not pinned
|
|
// to integer IDs in source) to survive DB renumbering. Loaded once at
|
|
// startup; missing codes fail fast with a clear message.
|
|
var proceedingCodes = []string{
|
|
"upc.inf.cfi",
|
|
"upc.ccr.cfi",
|
|
"upc.apl.merits",
|
|
"de.inf.lg",
|
|
"epa.opp.opd",
|
|
"de.null.bpatg",
|
|
"dpma.opp.dpma",
|
|
}
|
|
|
|
func main() {
|
|
dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "Postgres DSN (defaults to $DATABASE_URL)")
|
|
dryRun := flag.Bool("dry-run", false, "print intended actions, roll back transaction")
|
|
flag.Parse()
|
|
|
|
if *dsn == "" {
|
|
fmt.Fprintln(os.Stderr, "seed-example-projects: DATABASE_URL not set and -dsn empty")
|
|
os.Exit(1)
|
|
}
|
|
|
|
db, err := sqlx.Connect("postgres", *dsn)
|
|
if err != nil {
|
|
fmt.Fprintln(os.Stderr, "connect:", err)
|
|
os.Exit(1)
|
|
}
|
|
defer db.Close()
|
|
|
|
ctx := context.Background()
|
|
if err := run(ctx, db, *dryRun); err != nil {
|
|
fmt.Fprintln(os.Stderr, "seed-example-projects:", err)
|
|
os.Exit(1)
|
|
}
|
|
}
|
|
|
|
func run(ctx context.Context, db *sqlx.DB, dryRun bool) error {
|
|
ownerID, err := lookupOwner(ctx, db, ownerEmail)
|
|
if err != nil {
|
|
return fmt.Errorf("lookup owner: %w", err)
|
|
}
|
|
if ownerID == uuid.Nil {
|
|
fmt.Printf("note: %s not found in auth.users — created_by will be NULL\n", ownerEmail)
|
|
} else {
|
|
fmt.Printf("owner resolved: %s = %s\n", ownerEmail, ownerID)
|
|
}
|
|
|
|
procIDs, err := lookupProceedingTypes(ctx, db, proceedingCodes)
|
|
if err != nil {
|
|
return fmt.Errorf("lookup proceeding_types: %w", err)
|
|
}
|
|
|
|
tx, err := db.BeginTxx(ctx, nil)
|
|
if err != nil {
|
|
return fmt.Errorf("begin tx: %w", err)
|
|
}
|
|
defer func() { _ = tx.Rollback() }() // no-op if Commit ran first
|
|
|
|
if err := wipe(ctx, tx); err != nil {
|
|
return fmt.Errorf("wipe: %w", err)
|
|
}
|
|
|
|
tree, err := seed(ctx, tx, ownerID, procIDs)
|
|
if err != nil {
|
|
return fmt.Errorf("seed: %w", err)
|
|
}
|
|
|
|
if dryRun {
|
|
fmt.Println("\n--- DRY RUN — rolling back ---")
|
|
return nil
|
|
}
|
|
|
|
if err := tx.Commit(); err != nil {
|
|
return fmt.Errorf("commit: %w", err)
|
|
}
|
|
fmt.Println("seed committed.")
|
|
|
|
if err := report(ctx, db, tree); err != nil {
|
|
return fmt.Errorf("report: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func lookupOwner(ctx context.Context, db *sqlx.DB, email string) (uuid.UUID, error) {
|
|
var id uuid.UUID
|
|
err := db.GetContext(ctx, &id, `SELECT id FROM auth.users WHERE email = $1`, email)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return uuid.Nil, nil
|
|
}
|
|
if err != nil {
|
|
return uuid.Nil, err
|
|
}
|
|
return id, nil
|
|
}
|
|
|
|
func lookupProceedingTypes(ctx context.Context, db *sqlx.DB, codes []string) (map[string]int, error) {
|
|
rows, err := db.QueryxContext(ctx,
|
|
`SELECT id, code FROM paliad.proceeding_types WHERE code = ANY($1)`,
|
|
pgTextArray(codes))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
out := make(map[string]int, len(codes))
|
|
for rows.Next() {
|
|
var id int
|
|
var code string
|
|
if err := rows.Scan(&id, &code); err != nil {
|
|
return nil, err
|
|
}
|
|
out[code] = id
|
|
}
|
|
for _, c := range codes {
|
|
if _, ok := out[c]; !ok {
|
|
return nil, fmt.Errorf("proceeding_types row missing for code=%q", c)
|
|
}
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// pgTextArray is the lib/pq array adapter, repackaged inline so the
|
|
// script doesn't need a separate util import.
|
|
func pgTextArray(xs []string) any {
|
|
type arr = []string
|
|
return arr(xs)
|
|
}
|
|
|
|
// wipe deletes every paliad.projects row. FK CASCADE handles the
|
|
// dependent tables (verified live 2026-05-25 against information_schema:
|
|
// appointments, approval_requests, approval_policies, deadlines,
|
|
// documents, notes, parties, project_events, project_partner_units,
|
|
// project_teams, submission_drafts, user_pinned_projects,
|
|
// user_calendar_bindings, checklist_shares all cascade; projects.
|
|
// counterclaim_of and checklist_instances SET NULL; policy_audit_log
|
|
// SET NULL).
|
|
//
|
|
// Reference tables (proceeding_types, deadline_rules, event_types,
|
|
// gerichte, checklists, firms, partner_units, profiles) are not
|
|
// referenced from this delete.
|
|
func wipe(ctx context.Context, tx *sqlx.Tx) error {
|
|
res, err := tx.ExecContext(ctx, `DELETE FROM paliad.projects`)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
n, _ := res.RowsAffected()
|
|
fmt.Printf("wiped: %d project rows (FK CASCADE handled dependents)\n", n)
|
|
return nil
|
|
}
|
|
|
|
// seededNode is one row of the seed result, kept so we can print the
|
|
// chain code after commit without re-querying for IDs.
|
|
type seededNode struct {
|
|
id uuid.UUID
|
|
title string
|
|
}
|
|
|
|
// seed inserts the example tree. Order matters because parent_id FKs
|
|
// must already exist — clients first, then litigations under them, then
|
|
// patents, then cases (with the CCR case referencing its sibling
|
|
// Klage case via counterclaim_of).
|
|
func seed(ctx context.Context, tx *sqlx.Tx, ownerID uuid.UUID, procIDs map[string]int) ([]seededNode, error) {
|
|
var nodes []seededNode
|
|
|
|
insertProject := func(p projectInsert) (uuid.UUID, error) {
|
|
id := uuid.New()
|
|
var createdBy any
|
|
if ownerID != uuid.Nil {
|
|
createdBy = ownerID
|
|
}
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO paliad.projects (
|
|
id, type, parent_id, title, reference, description, status,
|
|
created_by, industry, country, client_number, matter_number,
|
|
patent_number, filing_date, grant_date,
|
|
court, case_number, proceeding_type_id,
|
|
our_side, opponent_code, instance_level, counterclaim_of
|
|
) VALUES (
|
|
$1, $2, $3, $4, $5, $6, 'active',
|
|
$7, $8, $9, $10, $11,
|
|
$12, $13, $14,
|
|
$15, $16, $17,
|
|
$18, $19, $20, $21
|
|
)`,
|
|
id, p.Type, nullUUID(p.ParentID), p.Title, nullStr(p.Reference), nullStr(p.Description),
|
|
createdBy, nullStr(p.Industry), nullStr(p.Country), nullStr(p.ClientNumber), nullStr(p.MatterNumber),
|
|
nullStr(p.PatentNumber), nullDate(p.FilingDate), nullDate(p.GrantDate),
|
|
nullStr(p.Court), nullStr(p.CaseNumber), nullInt(p.ProceedingTypeID),
|
|
nullStr(p.OurSide), nullStr(p.OpponentCode), nullStr(p.InstanceLevel), nullUUID(p.CounterclaimOf),
|
|
)
|
|
if err != nil {
|
|
return uuid.Nil, fmt.Errorf("insert %s %q: %w", p.Type, p.Title, err)
|
|
}
|
|
nodes = append(nodes, seededNode{id: id, title: p.Title})
|
|
return id, nil
|
|
}
|
|
|
|
// --- Client 1: Siemens AG ----------------------------------------
|
|
siemens, err := insertProject(projectInsert{
|
|
Type: "client", Title: "Siemens AG", Reference: "SIEMENS",
|
|
Industry: "Telekommunikation / Industrieelektronik", Country: "DE",
|
|
Description: "Beispiel-Mandant — Telekommunikation & Halbleiter.",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
siemensHuawei, err := insertProject(projectInsert{
|
|
Type: "litigation", ParentID: siemens,
|
|
Title: "Siemens ./. Huawei Technologies", OpponentCode: "HUAW",
|
|
Description: "Patentstreit Mobilfunk-Standardpatent.", OurSide: "claimant",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
siemensHuaweiPatent, err := insertProject(projectInsert{
|
|
Type: "patent", ParentID: siemensHuawei,
|
|
Title: "EP3456789 — Funkkommunikationssystem mit Mehrfachantenne",
|
|
PatentNumber: "EP3456789",
|
|
FilingDate: "2018-03-12", GrantDate: "2022-11-09",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
upcInfCFI, err := insertProject(projectInsert{
|
|
Type: "case", ParentID: siemensHuaweiPatent,
|
|
Title: "UPC CFI München — Klage Siemens ./. Huawei (EP3456789)",
|
|
Court: "UPC Lokalkammer München",
|
|
CaseNumber: "UPC_CFI_123/2026",
|
|
ProceedingTypeID: procIDs["upc.inf.cfi"],
|
|
OurSide: "claimant",
|
|
InstanceLevel: "first",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = insertProject(projectInsert{
|
|
Type: "case", ParentID: siemensHuaweiPatent,
|
|
Title: "UPC CFI München — Widerklage Huawei ./. Siemens (EP3456789)",
|
|
Court: "UPC Lokalkammer München",
|
|
CaseNumber: "UPC_CFI_123/2026 (CCR)",
|
|
ProceedingTypeID: procIDs["upc.ccr.cfi"],
|
|
OurSide: "defendant", // we're respondent on the CCR
|
|
InstanceLevel: "first",
|
|
CounterclaimOf: upcInfCFI,
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = insertProject(projectInsert{
|
|
Type: "case", ParentID: siemensHuaweiPatent,
|
|
Title: "UPC Berufungsgericht — Berufung Huawei (EP3456789)",
|
|
Court: "UPC Court of Appeal",
|
|
CaseNumber: "UPC_CoA_45/2027",
|
|
ProceedingTypeID: procIDs["upc.apl.merits"],
|
|
OurSide: "respondent",
|
|
InstanceLevel: "appeal",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
siemensBosch, err := insertProject(projectInsert{
|
|
Type: "litigation", ParentID: siemens,
|
|
Title: "Siemens ./. Robert Bosch GmbH", OpponentCode: "BOSCH",
|
|
Description: "Sensorik / autonomes Fahren.", OurSide: "claimant",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
siemensBoschPatent, err := insertProject(projectInsert{
|
|
Type: "patent", ParentID: siemensBosch,
|
|
Title: "EP1111222 — Sensoreinrichtung für autonomes Fahren",
|
|
PatentNumber: "EP1111222",
|
|
FilingDate: "2017-06-21", GrantDate: "2021-08-04",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = insertProject(projectInsert{
|
|
Type: "case", ParentID: siemensBoschPatent,
|
|
Title: "LG München I — Klage Siemens ./. Bosch (EP1111222)",
|
|
Court: "Landgericht München I",
|
|
CaseNumber: "7 O 12345/26",
|
|
ProceedingTypeID: procIDs["de.inf.lg"],
|
|
OurSide: "claimant",
|
|
InstanceLevel: "first",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// --- Client 2: Bayer AG ------------------------------------------
|
|
bayer, err := insertProject(projectInsert{
|
|
Type: "client", Title: "Bayer AG", Reference: "BAYER",
|
|
Industry: "Pharma / Life Sciences", Country: "DE",
|
|
Description: "Beispiel-Mandant — pharmazeutische Wirkstoffe.",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
bayerNova, err := insertProject(projectInsert{
|
|
Type: "litigation", ParentID: bayer,
|
|
Title: "Bayer ./. Novartis Pharma", OpponentCode: "NOVA",
|
|
Description: "Wirkstoffverbindung X — Einspruch + Nichtigkeit.", OurSide: "claimant",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
bayerNovaPatent, err := insertProject(projectInsert{
|
|
Type: "patent", ParentID: bayerNova,
|
|
Title: "EP2222333 — Wirkstoffverbindung X",
|
|
PatentNumber: "EP2222333",
|
|
FilingDate: "2015-09-30", GrantDate: "2020-04-22",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = insertProject(projectInsert{
|
|
Type: "case", ParentID: bayerNovaPatent,
|
|
Title: "EPA Einspruch — Novartis ./. EP2222333",
|
|
Court: "Europäisches Patentamt — Einspruchsabteilung",
|
|
CaseNumber: "OPP-2026-0042",
|
|
ProceedingTypeID: procIDs["epa.opp.opd"],
|
|
OurSide: "respondent", // Bayer is patent owner defending the patent
|
|
InstanceLevel: "first",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = insertProject(projectInsert{
|
|
Type: "case", ParentID: bayerNovaPatent,
|
|
Title: "BPatG — Nichtigkeitsklage Novartis ./. EP2222333",
|
|
Court: "Bundespatentgericht",
|
|
CaseNumber: "5 Ni 12/26",
|
|
ProceedingTypeID: procIDs["de.null.bpatg"],
|
|
OurSide: "respondent",
|
|
InstanceLevel: "first",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// --- Client 3: Beispiel AG (intentionally sparse) ----------------
|
|
// Demonstrates the empty-segment skip in BuildProjectCode — the
|
|
// case row has a proceeding_type set so the tail is present, but
|
|
// no instance_level / our_side, and the patent's number is national
|
|
// (DE) so the last-3-digits segment shows DE-style behaviour.
|
|
beispiel, err := insertProject(projectInsert{
|
|
Type: "client", Title: "Beispiel AG", Reference: "BEISPL",
|
|
Industry: "Unspezifiziert", Country: "DE",
|
|
Description: "Sparse-Beispiel — zeigt, wie fehlende Segmente übersprungen werden.",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
beispielWtb, err := insertProject(projectInsert{
|
|
Type: "litigation", ParentID: beispiel,
|
|
Title: "Beispiel ./. Wettbewerber GmbH", OpponentCode: "WTB",
|
|
Description: "Demo-Litigation ohne große Detailtiefe.",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
beispielWtbPatent, err := insertProject(projectInsert{
|
|
Type: "patent", ParentID: beispielWtb,
|
|
Title: "DE10987654 — Demo-Erfindung",
|
|
PatentNumber: "DE10987654",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = insertProject(projectInsert{
|
|
Type: "case", ParentID: beispielWtbPatent,
|
|
Title: "DPMA Einspruch — Wettbewerber ./. DE10987654",
|
|
Court: "Deutsches Patent- und Markenamt",
|
|
CaseNumber: "DPMA-EIN-987/26",
|
|
ProceedingTypeID: procIDs["dpma.opp.dpma"],
|
|
OurSide: "respondent",
|
|
InstanceLevel: "first",
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
fmt.Printf("seeded: %d projects\n", len(nodes))
|
|
return nodes, nil
|
|
}
|
|
|
|
// projectInsert is the typed input for one insertProject call. Pointer
|
|
// fields are kept as plain strings here and converted via nullStr at
|
|
// bind time; keeps the call sites readable.
|
|
type projectInsert struct {
|
|
Type string
|
|
ParentID uuid.UUID
|
|
Title string
|
|
Reference string
|
|
Description string
|
|
Industry string
|
|
Country string
|
|
ClientNumber string
|
|
MatterNumber string
|
|
PatentNumber string
|
|
FilingDate string // YYYY-MM-DD
|
|
GrantDate string
|
|
Court string
|
|
CaseNumber string
|
|
ProceedingTypeID int
|
|
OurSide string
|
|
OpponentCode string
|
|
InstanceLevel string
|
|
CounterclaimOf uuid.UUID
|
|
}
|
|
|
|
func nullStr(s string) any {
|
|
if s == "" {
|
|
return nil
|
|
}
|
|
return s
|
|
}
|
|
|
|
func nullInt(i int) any {
|
|
if i == 0 {
|
|
return nil
|
|
}
|
|
return i
|
|
}
|
|
|
|
func nullUUID(u uuid.UUID) any {
|
|
if u == uuid.Nil {
|
|
return nil
|
|
}
|
|
return u
|
|
}
|
|
|
|
func nullDate(s string) any {
|
|
if s == "" {
|
|
return nil
|
|
}
|
|
t, err := time.Parse("2006-01-02", s)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
return t
|
|
}
|
|
|
|
// reportRow is one row of the post-seed report — only the fields the
|
|
// printout needs.
|
|
type reportRow struct {
|
|
ID uuid.UUID `db:"id"`
|
|
Type string `db:"type"`
|
|
Title string `db:"title"`
|
|
Path string `db:"path"`
|
|
}
|
|
|
|
// report prints the seeded tree with the auto-derived chain code for
|
|
// each row. Uses services.BuildProjectCode so the script verifies the
|
|
// same helper the live app uses (catches drift if the algorithm
|
|
// changes).
|
|
func report(ctx context.Context, db *sqlx.DB, _ []seededNode) error {
|
|
var rows []reportRow
|
|
err := db.SelectContext(ctx, &rows, `
|
|
SELECT id, type, title, path
|
|
FROM paliad.projects
|
|
ORDER BY path
|
|
`)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
fmt.Println("\nresulting chain codes:")
|
|
tw := tabwriter.NewWriter(os.Stdout, 0, 0, 2, ' ', 0)
|
|
fmt.Fprintln(tw, "TYPE\tTITLE\tCODE")
|
|
for _, r := range rows {
|
|
code, err := services.BuildProjectCode(ctx, db, r.ID)
|
|
if err != nil {
|
|
return fmt.Errorf("build code for %s: %w", r.ID, err)
|
|
}
|
|
indent := strings.Repeat(" ", pathDepth(r.Path)-1)
|
|
fmt.Fprintf(tw, "%s\t%s%s\t%s\n", r.Type, indent, r.Title, code)
|
|
}
|
|
return tw.Flush()
|
|
}
|
|
|
|
func pathDepth(p string) int {
|
|
if p == "" {
|
|
return 1
|
|
}
|
|
d := 1
|
|
for _, c := range p {
|
|
if c == '.' {
|
|
d++
|
|
}
|
|
}
|
|
return d
|
|
}
|