Install https://phinx.org/ tool to make it easier version database. It's necessary create the migration scripts to tables urls and url_logs;

Follow the table structure:

Url Table

create table urls
(
    id             binary(16)                                 not null,
    uuid           varchar(36)                                not null,
    user_id        binary(16)                                 null,
    long_url       longtext                                   not null,
    short_url_path varchar(15)                                not null,
    type           enum ('RANDOM', 'CUSTOM') default 'RANDOM' not null,
    economy_rate   decimal(10, 2)            default 0.00     not null,
    meta           json                                       null,
    created_at     timestamp                                  not null,
    constraint urls_id_uindex
        unique (id),
    constraint urls_short_url_path_uindex
        unique (short_url_path),
    constraint urls_uuid_uindex
        unique (uuid)
);

alter table urls
    add primary key (id);

Url Logs Table

create table urls_logs
(
    id         binary(16)  not null,
    uuid       varchar(36) not null,
    url_id     binary(16)  not null,
    created_at timestamp   not null,
    meta       json        null,
    constraint urls_logs_id_uindex
        unique (id),
    constraint urls_logs_uuid_uindex
        unique (uuid),
    constraint fk_urls_logs_url_id
        foreign key (url_id) references urls (id)
            on update cascade on delete cascade
);

alter table urls_logs
    add primary key (id);

These are the procedures to make it easy to convert UUID to Binary and vice versa:

BIN_TO_UUID()

CREATE DEFINER=`smll_main`@`%.%.%.%` FUNCTION `smll_main`.`BIN_TO_UUID`(b BINARY(16)) RETURNS char(36) CHARSET latin1
BEGIN
    DECLARE hexStr CHAR(32);
    SET hexStr = HEX(b);
    RETURN LOWER(CONCAT(
        SUBSTR(hexStr, 1, 8), '-',
        SUBSTR(hexStr, 9, 4), '-',
        SUBSTR(hexStr, 13, 4), '-',
        SUBSTR(hexStr, 17, 4), '-',
        SUBSTR(hexStr, 21)
    ));
END;

UUID_TO_BIN()

CREATE DEFINER=`smll_main`@`%.%.%.%` FUNCTION `smll_main`.`UUID_TO_BIN`(uuid CHAR(36)) RETURNS binary(16)
RETURN UNHEX(REPLACE(uuid, '-', ''));

• To create a new migration: docker exec -it smallish-app vendor/bin/phinx create <MyNewMigration>
• To run all migrations: docker exec -it smallish-app vendor/bin/phinx migrate -e <environment> (all environments are configured in phinx.php file on root folder)
• To rollback all migrations: docker exec -it smallish-app vendor/bin/phinx rollback -e <environment> -t 0

To check more commands available, you can visit the Phinx documentation: https://book.cakephp.org/phinx/0/en/intro.html

• Exception classes organization
• Add the https://github.com/filp/whoops when requests are via template
• Add the https://github.com/omniti-labs/jsend convention to async request (REST)

If the user adds the + sign at the end of the shortened URL (https://.../abcde**+**) the user should be redirected to a page and show basic shortened URL metrics: total clicks, origin and last ten clicks informations

When the URL is linked to a user and it has not been clicked within a certain time, send a reminder via email and/or browser notification.

It should have a setting of 3 days by default, but the user who owns the URL can change it at any time.

Creation of a page so that any user can report a link that does not comply with the project's policies and terms. It's very important that the page has a captcha verification, something like the follow image:

Creation of a plugin for Firefox and Chrome browsers. The idea would be to give the user the possibility to shorten a URL when the user selects a URL on some page and the plugin can quickly and easily create a shortened URL