General Investment endpoints, tables and keys

Two surfaces. Everything under /api/finance/investment is scoped to the calling user and carries no permission — the purchase route is gated by the feature switch and a KYC feature instead. Everything under /api/admin/finance/investment carries an explicit permission, and there are twenty-four of them.

This is the general investment product in core. AI Investments, Forex and Staking are separate products with separate tables, routes, crons and permission keys; nothing on this page applies to them.

Conventions that will catch you out

amount, profit and roiPercentage are DOUBLE columns, so they arrive as JSON numbers rather than the strings most money columns on this platform return. They are still money and double arithmetic is still inexact — do not compare two computed payouts for equality.

transaction.referenceId carries a platform-wide UNIQUE index (transactionReferenceIdKey). Every leg of one investment's life therefore has to use a different reference, and one of them does not — see Transactions and references below.

An investment row has no currency column. The denomination belongs to its plan (investment_plan.currency), so any sum across investments must join the plan and convert.

User endpoints

GET /api/finance/investment

?type= is required and is general or forex. A bad type is not always a clean 400 — which error you get depends on which of the two modes below the request lands in.

The route has two modes, and the switch between them is surprising:

• With type and no page it returns the caller's ACTIVE investments as a bare array, each with its duration attached, and throws 404 No active investments found when there are none. An empty portfolio is a 404, not an empty list. This branch is taken before the type is validated, and its own switch has no default, so an unrecognised type leaves the model unset and the query throws — a 500, not a 400. Treat a 500 here as a typo in type.
• With a page parameter it runs the standard filtered query: scoped to userId, sorted by createdAt, with plan trimmed to id, title, image and currency, and duration to id, duration and timeframe. The plan's rate is not in that projection, which is why the portfolio screen also loads the plan catalogue. This is the only path that validates the type, so 400 Invalid investment type comes from here — and from a request with no type at all, which falls through to the same check.

POST /api/finance/investment

{ "type": "general", "planId": "…", "durationId": "…", "amount": 1000 }

All four are required. A "forex" purchase through this same endpoint creates a forexInvestment row that the Forex addon's cron settles.

Refusals, in the order they are checked:

The response is a bare { "message": "Investment created successfully" } — the row is not returned. Re-read the list to get it.

GET and DELETE /api/finance/investment/{id}

Both require ?type=. Both scope the lookup to where: { id, userId }, so a row that belongs to somebody else answers the same 404 Investment not found as a row that does not exist.

DELETE refuses anything that is not ACTIVE with 400 Only active investments can be cancelled, credits the full principal back as a REFUND, annotates the original debit's metadata with cancelled, cancelledAt and refundTransactionId, then soft-deletes the investment row. No ROI is paid and no fee is taken. It returns { "message": "Investment cancelled and the principal returned to your wallet" }.

The three public reads

plan, plan/{id} and stats all declare requiresAuth: false. Treat everything they return as public.

The plan projection served to customers is id, title, description, image, minAmount, maxAmount, profitPercentage, currency, walletType, trending, defaultResult, plus each attached duration's id, duration and timeframe. name, invested, minProfit, maxProfit and defaultProfit are deliberately withheld.

stats returns activeInvestors, totalInvested, averageReturn, totalPlans and maxProfitPercentage. totalInvested is SUM(amount) across every investment regardless of its plan's currency, so it adds unlike currencies together; nothing in the product renders it, and neither should you.

Admin endpoints

Twenty-four routes across three objects. None of the fifteen permission keys is granted to any role by default.

Investments (the history desk)

The two status doors accept ACTIVE, COMPLETED, CANCELLED or REJECTED. The edit door writes userId, planId, durationId, amount, profit, result, status and endDate; the create door writes the same eight.

Plans

Create and update both accept name, title, description, image, minProfit, maxProfit, minAmount, maxAmount, invested, profitPercentage, status, defaultProfit, defaultResult, trending, currency, walletType and a durations array of duration ids, which is written to the join table.

plan/options returns { id, name } where name is the plan's title, not its name column, and only for plans with status true.

Durations

Durations have no status column, so there is no status endpoint. There is also no dependency guard: unlike the AI Investments addon, these routes do not refuse a delete while investments reference the row.

Delete semantics

Every delete route above takes the platform's standard query parameters:

Permission keys

access.* is what the menu entry and the page gate on; view.* is what the table's fetch gates on. Grant them together or the screen opens empty and never loads a row, with no error. See Roles and permissions.

Tables

investment

Paranoid — filter on deletedAt IS NULL unless you want cancelled positions.

There is a non-unique index on (userId, planId, status). It used to be unique, which blocked re-investing after a cancellation because MySQL ignores a partial WHERE on a unique index. The "one ACTIVE per plan" rule is now enforced in the application, inside the same transaction that creates the row.

investment_plan

Paranoid. name is UNIQUE.

investment_duration

Not paranoid and carries no timestamps. id (UUID), duration (INT, not null) and timeframe (ENUM HOUR, DAY, WEEK, MONTH). No unique index across the pair, so duplicate 3 MONTH rows are possible.

investment_plan_duration

The join. id, planId, durationId, no timestamps, both foreign keys ON DELETE CASCADE. The shipped schema carries UNIQUE (planId, durationId), so a duration can be attached to a plan only once. A row here is what makes a term selectable on a plan.

Transactions and references

A forex purchase through the same route uses FOREX_INVESTMENT instead.

Two conventions are load-bearing:

• The _roi suffix on the payout. The purchase already wrote the funding debit under the bare investment id, and referenceId is unique platform-wide. Reusing it raised a duplicate-key error that rolled the whole settlement back and stranded every general investment ACTIVE for ever.
• The result inside the payout's idempotency key. The payout amount varies by outcome, so if an outcome is ever changed between runs the two differing credits must not collapse into one deduplicated credit.

The platform's own leg

Settlement also books the house side against adminProfit under type INVESTMENT, via recordInvestmentOutcome:

Neither helper ever throws. A treasury with no reserves never blocks a customer's winnings — the negative adminProfit row is still written, so Finance → Revenue Analytics nets out correctly, and a shortfall is logged as a warning.

The switch, the KYC feature and the cron

Admin → System → Platform Settings → Features → Investment. Read by exactly three things: the purchase endpoint, which refuses with a 403 when it is not the string "true"; the customer site menu (Investments → Investment Plans); and the site footer, whose Products section drops its Investment link on the same key. The settlement cron does not consult it — turning the feature off stops new money coming in and does not stop payouts on money already in.

The toggle reads on over an empty settings table because the admin screen renders over built-in defaults, and only changed fields are saved. On a fresh install, toggle it off, back on, and save to create the row.

The gate is inert unless both kycStatus and kycFeatureEnforcement are on, and the second is off by default on every install.

A core job: registered unconditionally in the scheduler's constructor, needing no extension row, and present on System → System Monitoring → Scheduled Tasks (/admin/system/cron) on every install. Viewing needs view.cron; triggering it by hand needs manage.cron.

There is no WebSocket surface for this product.

Related

• General Investment: enable and configure
• Investment plans and durations
• The general investment lifecycle
• General Investment troubleshooting
• Scheduled jobs reference