PROJECT - a tribute inventory system

The eight lessons of this module travelled from the first connection to a database as far as validation at the schema level. The project is where all of it must work together - and where you will see for the first time that decisions made about the schema come back to you a week later, while writing queries.

You will build a tribute inventory system for a Roman legion: who owns what, where it came from, and how it passes from hand to hand.

The domain

Five areas the system must cover:

1. Legionaries - registration with validation, ranks and privileges, the history of each one's operations.
2. The tribute catalogue - types (gold, silver, gems, artefacts), valuation, authenticity assessment, discovery history.
3. Maps - geographic coordinates with range validation, search directions, discovery status, a link to a particular tribute.
4. Transactions and transfers - safe transfer of ownership between legionaries, exchange, a full history of operations.
5. Legions and cohorts - assignment of legionaries, size limits, centurions.

Do not begin with code. Begin with a sheet of paper and write down what relates to what - the one decision in this project whose later revision costs a great deal.

Step 1 - schema and relations

The legionary entity shows three things at once: column types, indexes and constraints at the database level:

1@Entity('legionaries')
2@Check('tribute_count_non_negative', '"tributeCount" >= 0')
3@Index(['name', 'isActive'])
4export class Legionary {
5  @PrimaryGeneratedColumn()
6  id: number;
7
8  @Column({ length: 100, unique: true })
9  name: string;
10
11  @Column({ length: 50 })
12  rank: string;
13
14  @Column({ type: 'int', default: 0 })
15  tributeCount: number;
16
17  @Column({ type: 'decimal', precision: 15, scale: 2, default: 0 })
18  totalTributeValue: number;
19
20  @Column({ default: true })
21  isActive: boolean;
22
23  @CreateDateColumn()
24  joinedAt: Date;
25
26  @OneToMany(() => Tribute, (tribute) => tribute.owner)
27  tributes: Tribute[];
28
29  @ManyToOne(() => Cohort, (cohort) => cohort.legionaries)
30  cohort: Cohort;
31}

Three decisions in this code are worth understanding, because you will repeat them in every project.

A tribute's value is a decimal, not a float. A floating-point number cannot represent even 0.1 exactly; after a thousand transfers the totals will disagree by pennies, and where money is concerned that is the end of trust in the system. precision: 15, scale: 2 means fifteen significant digits, two of them after the point.

@Index(['name', 'isActive']) creates a composite index, useful when queries filter on both columns at once. Add indexes once you know what you will be asking - not "just in case", because each one slows writes down.

@Check is the last line of defence, written into the database itself. Validation in a DTO protects you from a bad HTTP request, but not from a migration script, a manual UPDATE, or a bug in your own service. A constraint in the database holds regardless of who is writing.

Choose relations by cardinality: @OneToMany and @ManyToOne are two sides of the same relation (a legionary has many tributes, a tribute has one owner), @OneToOne suits a map tied to a single tribute, and @ManyToMany suits exchanges with several participants.

Step 2 - the repository layer

Complex queries belong in a repository of your own:

1@Injectable()
2export class TributeRepository extends Repository<Tribute> {
3  async findValuableByLegionary(legionaryId: number, minValue: number) {
4    return this.createQueryBuilder('tribute')
5      .innerJoinAndSelect('tribute.owner', 'owner')
6      .where('owner.id = :legionaryId', { legionaryId })
7      .andWhere('tribute.value >= :minValue', { minValue })
8      .orderBy('tribute.value', 'DESC')
9      .getMany();
10  }
11}

The division of responsibility is simple: the repository knows how to ask the database something; the service knows when and why. When a createQueryBuilder appears in a service, it usually means the method has started doing two things at once.

Note the :legionaryId and :minValue parameters. This is not a matter of style - gluing a query together from strings opens the door to SQL injection. The Query Builder always passes values separately.

Step 3 - the transfer, that is, a transaction

Moving a tribute is the heart of this project, because it consists of operations that must all happen or none at all:

1async transferTribute(tributeId: number, fromId: number, toId: number) {
2  return this.dataSource.transaction(async (manager) => {
3    const tribute = await manager.findOne(Tribute, {
4      where: { id: tributeId, owner: { id: fromId } },
5      relations: ['owner'],
6    });
7
8    if (!tribute) {
9      throw new BadRequestException('Tribute not found or not owned by the sender');
10    }
11
12    tribute.owner = await manager.findOneOrFail(Legionary, { where: { id: toId } });
13    await manager.save(tribute);
14
15    await manager.decrement(Legionary, { id: fromId }, 'tributeCount', 1);
16    await manager.increment(Legionary, { id: toId }, 'tributeCount', 1);
17
18    return manager.save(Transaction, {
19      tribute,
20      fromLegionaryId: fromId,
21      toLegionaryId: toId,
22    });
23  });
24}

Without a transaction, a failure halfway through leaves the system in an impossible state: the tribute has a new owner, but the old one's counter was never decremented - or worse, the counter fell and rose while the owner stayed the same. Such discrepancies surface months later, and by then there is no telling which records are true.

And now the greatest trap of the whole project. Inside a transaction use only manager, never the injected repository. A call to this.tributeRepository.save(...) in that block reaches for a different connection - one that knows nothing of the transaction. The code will compile, the tests will most likely pass, and on rollback that one operation will be written anyway. The rule is simple: if you are inside transaction(async (manager) => ...), then manager is your only road to the database.

Step 4 - seeders

Seed data fills the database for development and testing:

1export class LegionarySeeder {
2  constructor(private dataSource: DataSource) {}
3
4  async run() {
5    const repo = this.dataSource.getRepository(Legionary);
6
7    const existing = await repo.count();
8    if (existing > 0) {
9      return;
10    }
11
12    await repo.save([
13      { name: 'Marcus Aurelius', rank: 'Centurion' },
14      { name: 'Gaius Julius', rank: 'Optio' },
15    ]);
16  }
17}

One requirement separates a good seeder from a troublesome one: it must be runnable twice. A count() check before writing, or an upsert, means a second call duplicates nothing. Without that, every environment restart multiplies the legionaries, and tests begin to depend on how many times somebody ran the seeder earlier.

Order matters too: first the independent entities (legions, cohorts), then those that refer to them (legionaries), and last the tributes and transactions. The reverse order ends in a foreign key error.

Validation at three levels

The system has three places where data can be rejected, and each catches something different:

• DTOs with class-validator - the shape of the HTTP request. Here you reject a missing name or a negative value before anything touches the database.
• The entity - unique, nullable: false, @Check. It holds no matter which way the data arrived.
• The service - domain rules the database cannot express: "a tribute cannot be transferred to oneself", "a cohort holds a hundred men".

Duplicating a rule across two levels is not a mistake - it is cheap insurance.

Assessment criteria

The project is finished when it meets six conditions:

1. Entities with relations - all four kinds where they fit, with indexes on the columns you genuinely filter by.
2. Migrations - the schema comes from migrations, not from synchronize: true.
3. Repositories with at least two queries written in the Query Builder.
4. A transfer inside a transaction, with rollback proved by a test.
5. Seeders that are idempotent and run in the right order.
6. Validation at the three levels described above.

Before you submit, run one test: break a transfer halfway through - give a recipient that does not exist - and check both legionaries' counters in the database. If either changed, somewhere in your code an injected repository stands where manager should be.

Send the link to your repository when you are done.

1// PROJECT: Roman Tribute Inventory System
2import {
3  Module, Controller, Injectable, Get, Post, Put, Delete,
4  Body, Param, Query,
5} from '@nestjs/common';
6
7console.log("=== PROJECT: Tribute Inventory System ===");
8
9// ===========================================
10// 1. Project entities
11// ===========================================
12
13// Entity: Tribute
14// id, name, province, amount, type, collectedBy, verified, createdAt
15
16// Entity: Province
17// id, name, governor, population, totalTribute
18
19// Entity: Collector (ManyToOne -> Province)
20// id, name, rank, assignedProvince
21
22// Relation: Province OneToMany -> Tribute
23// Relation: Collector ManyToOne -> Province
24
25// ===========================================
26// 2. Service with repository and QueryBuilder
27// ===========================================
28
29@Injectable()
30export class TributeInventoryService {
31  private tributes = [
32    { id: 1, name: 'Aurum Galliae', province: 'Gallia', amount: 50000, type: 'gold', verified: true },
33    { id: 2, name: 'Argentum Hispaniae', province: 'Hispania', amount: 30000, type: 'silver', verified: false },
34    { id: 3, name: 'Merces Britanniae', province: 'Britannia', amount: 15000, type: 'goods', verified: true },
35    { id: 4, name: 'Aurum Aegypti', province: 'Aegyptus', amount: 80000, type: 'gold', verified: true },
36  ];
37
38  // CRUD
39  findAll(filters?: { province?: string; type?: string; verified?: boolean }) {
40    let result = [...this.tributes];
41    if (filters?.province) result = result.filter(t => t.province === filters.province);
42    if (filters?.type) result = result.filter(t => t.type === filters.type);
43    if (filters?.verified !== undefined) result = result.filter(t => t.verified === filters.verified);
44    return result;
45  }
46
47  findById(id: number) {
48    return this.tributes.find(t => t.id === id);
49  }
50
51  create(data: any) {
52    const tribute = { id: this.tributes.length + 1, verified: false, ...data };
53    this.tributes.push(tribute);
54    return tribute;
55  }
56
57  // Transaction - transfer between provinces
58  async transfer(fromId: number, toProvince: string) {
59    const tribute = this.findById(fromId);
60    if (tribute) {
61      const oldProvince = tribute.province;
62      tribute.province = toProvince;
63      console.log('Transfer: ' + tribute.name + ' from ' + oldProvince + ' to ' + toProvince);
64    }
65    return tribute;
66  }
67
68  // Statistics (aggregation)
69  getStatistics() {
70    const total = this.tributes.reduce((sum, t) => sum + t.amount, 0);
71    const byProvince = this.tributes.reduce((acc, t) => {
72      acc[t.province] = (acc[t.province] || 0) + t.amount;
73      return acc;
74    }, {} as Record<string, number>);
75
76    return { total, byProvince, count: this.tributes.length };
77  }
78}
79
80// ===========================================
81// 3. Controller with a full API
82// ===========================================
83
84@Controller('inventory')
85export class InventoryController {
86  constructor(private service: TributeInventoryService) {}
87
88  @Get()
89  findAll(@Query('province') province?: string, @Query('type') type?: string) {
90    return this.service.findAll({ province, type });
91  }
92
93  @Get('stats')
94  getStats() { return this.service.getStatistics(); }
95
96  @Get(':id')
97  findOne(@Param('id') id: string) { return this.service.findById(+id); }
98
99  @Post()
100  create(@Body() data: any) { return this.service.create(data); }
101}
102
103@Module({
104  controllers: [InventoryController],
105  providers: [TributeInventoryService],
106})
107export class InventoryModule {}
108
109console.log("\n=== PROJECT STRUCTURE ===");
110console.log("Entities with relations (Province, Tribute, Collector)");
111console.log("Repository Pattern with CRUD");
112console.log("QueryBuilder for advanced queries");
113console.log("Transactions for safe transfers");
114console.log("Seeders with initial data");
115console.log("Validation at multiple levels (DTO + DB constraints)");
116