General Ledger Design

CREATE TABLE gl_transaction (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- Transaction details
    transaction_date DATE NOT NULL,
    created_date_time TIMESTAMP NOT NULL,
    description VARCHAR(50),

    -- Type discriminator
    transaction_type VARCHAR(2) NOT NULL,        -- 'OR', 'JN', 'AD', 'RF'

    -- Relationships
    order_id BIGINT,                             -- FK to sales_order (nullable)
    organisation_id BIGINT NOT NULL,

    CONSTRAINT fk_gl_transaction_order
        FOREIGN KEY (order_id) REFERENCES sales_order(id),
    CONSTRAINT fk_gl_transaction_organisation
        FOREIGN KEY (organisation_id) REFERENCES organisation(id)
);

CREATE TABLE gl_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- Amount (positive = debit, negative = credit)
    amount DECIMAL(21,2) NOT NULL,
    posted_date DATE NOT NULL,

    -- Delta indicator
    is_delta BOOLEAN DEFAULT FALSE,

    -- Relationships
    account_id BIGINT NOT NULL,
    transaction_id BIGINT NOT NULL,
    order_line_item_id BIGINT,                   -- FK to order_line_item (nullable)

    CONSTRAINT fk_gl_record_account
        FOREIGN KEY (account_id) REFERENCES gl_account(id),
    CONSTRAINT fk_gl_record_transaction
        FOREIGN KEY (transaction_id) REFERENCES gl_transaction(id),
    CONSTRAINT fk_gl_record_line_item
        FOREIGN KEY (order_line_item_id) REFERENCES order_line_item(id)
);

@Service
public class OrderGlService {

    public GlTransaction createTransactionForOrder(Order order) {
        // PaymentProcessor is required - determines GL accounts
        PaymentProcessor processor = order.getPaymentProcessor();
        if (processor == null) {
            throw new IllegalStateException("Order must have a PaymentProcessor");
        }

        GlTransaction tx = new GlTransaction();
        tx.setTransactionDate(LocalDate.now());
        tx.setCreatedDateTime(Instant.now());
        tx.setTransactionType(GlTransactionType.ORDER);
        tx.setOrder(order);
        tx.setOrganisation(order.getOrganisation());
        tx.setDescription("Order #" + order.getNumber());

        BigDecimal totalNet = BigDecimal.ZERO;

        // Create records for each line item (income and fee)
        for (OrderLineItem item : order.getLineItems()) {
            // CR: Income record per line item
            createRecord(tx, processor.getIncomeAccount(),
                item.getGross().negate(), item);

            // DR: Fee record per line item
            createRecord(tx, processor.getFeeAccount(),
                item.getFee(), item);

            totalNet = totalNet.add(item.getNet());
        }

        // DR: Bank/asset record for total received (consolidated)
        createRecord(tx, processor.getBankAccount(), totalNet, null);

        return glTransactionRepository.save(tx);
    }

    private void createRecord(GlTransaction tx, GlAccount account,
            BigDecimal amount, OrderLineItem lineItem) {
        GlRecord record = new GlRecord();
        record.setTransaction(tx);
        record.setAccount(account);
        record.setAmount(amount);
        record.setPostedDate(LocalDate.now());
        record.setOrderLineItem(lineItem);
        record.setIsDelta(false);
        tx.getRecords().add(record);
    }
}

public void addDeltaRecords(Order order, List<OrderLineItemChange> changes) {
    GlTransaction tx = order.getGlTransaction();

    if (tx.getTransactionType() != GlTransactionType.JOURNAL) {
        // Not yet journaled - modify directly
        updateExistingRecords(tx, changes);
        return;
    }

    // Already journaled - create delta records
    for (OrderLineItemChange change : changes) {
        GlRecord delta = new GlRecord();
        delta.setAmount(change.getAmountDelta());  // Can be negative
        delta.setPostedDate(LocalDate.now());
        delta.setIsDelta(true);
        delta.setAccount(determineAccount(change.getLineItem()));
        delta.setOrderLineItem(change.getLineItem());
        delta.setTransaction(tx);

        glRecordRepository.save(delta);
    }
}

public void deleteJournal(Long journalId) {
    GlTransaction journal = glTransactionRepository.findById(journalId)
        .orElseThrow(() -> new NotFoundException("Journal not found"));

    if (journal.getTransactionType() != GlTransactionType.JOURNAL) {
        throw new InvalidOperationException("Not a journal transaction");
    }

    // Find transactions that were included in this journal
    // (Those with type=JOURNAL and date within journal period)
    List<GlTransaction> includedTxs = findIncludedTransactions(journal);

    // Revert to ORDER type
    for (GlTransaction tx : includedTxs) {
        if (tx.getOrder() != null) {
            tx.setTransactionType(GlTransactionType.ORDER);
        }
    }

    // Delete the journal transaction
    glTransactionRepository.delete(journal);
}

ALTER TABLE payment_processor
    ADD COLUMN fee_account_id BIGINT,
    ADD COLUMN income_account_id BIGINT;

ALTER TABLE payment_processor
    ADD CONSTRAINT fk_payment_processor_fee_account
    FOREIGN KEY (fee_account_id) REFERENCES gl_account(id);

ALTER TABLE payment_processor
    ADD CONSTRAINT fk_payment_processor_income_account
    FOREIGN KEY (income_account_id) REFERENCES gl_account(id);

ALTER TABLE gl_transaction
    ADD COLUMN transaction_type VARCHAR(2) DEFAULT 'OR',
    ADD COLUMN order_id BIGINT;

ALTER TABLE gl_transaction
    ADD CONSTRAINT fk_gl_transaction_order
    FOREIGN KEY (order_id) REFERENCES sales_order(id);

ALTER TABLE gl_record
    ADD COLUMN is_delta BOOLEAN DEFAULT FALSE,
    ADD COLUMN order_line_item_id BIGINT;

ALTER TABLE gl_record
    ADD CONSTRAINT fk_gl_record_line_item
    FOREIGN KEY (order_line_item_id) REFERENCES order_line_item(id);